INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

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

(OP)
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.

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

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-m...

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
www.syssoft-int.com

FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

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

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close