"Select * from view" takes a very long time,on second attempt 1

[billo102]

oracle database 11g
IBM AIX 6.1

Running a select * from a view takes just a few minutes and produces 60K rows. When running the same query for a second time, it appears to hang. It's actually progressing in the background but taking a very long time.
The view is quite complex, many joins/sorts etc, and hits a list of tables. Seems to be hitting columns on tables that are not indexed also and so there's several full table scans etc.

We're under the impression it should run quicker the second time round if anything. Our question is , why would it be a lot slower on subsequent retries ? The explain plan seems to change, how can this happen?

when issuing the *rule* hint with the select statement, the command executes quickly each time, but this is not supported in 11g so cannot be used.

 

[fredericofonseca]

That is one of the problems with CBO

For ways of "improving" it look at

http://www.dba-oracle.com/oracle_tips_rbo_cbo.htm

Also look at using SQL Plan Management

http://www.oracle-base.com/articles/11g/sql-plan-management-11gr1.php

And if you are who I think you are I left instructions on how to do this last bit

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[billo102]

Ah Fred !
Fair play to you, thanks for the response (small world!)

I did remember the SQL PLan management tool from last year or so when we discussed it. I've already started giving it a go, so I'll see if that solves the problem.
On first execution I need to 'manually' load the plan to the dba_sql_plan_baselines table. It should always use that plan then on subsequent retries.

I also tried using the FIRST_ROWS hint rather than the RULE hint and it also seemed to make an improvement. I suppose the confusion here was why does the CBO get it right the first time but gets it so wrong the second. Sometimes it's not even the second time but 3rd or 4th.

Many thanks