Why does Optimizer not pick up indexes? - Page 3
DBAsupport.com Forums - Powered by vBulletin
Page 3 of 3 FirstFirst 123
Results 21 to 29 of 29

Thread: Why does Optimizer not pick up indexes?

  1. #21
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Quote Originally Posted by lgaorcl
    The fact table in those queries are partitioned. As I mentioned before, there are two changes here. 1. I created some bitmap indexes on some foreign key columns. I was planning to enable star transformation. 2. We loaded some historical data over the last a couple of month to the fact table.
    Well, you'll definitely be wanting to enable star transformations. Try that and see what happens.

    What column are you partitioning on, and by what method? If it is by SERVICE_CASE_PROCESS_DATE_KEY then I doubt that you're getting the partition pruning that you deserve.

    Is this query generated by some tool or other? I'm intrigued by the complex in-line view you have in there.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  2. #22
    Join Date
    Jun 2000
    Posts
    315
    The table is partitioned range-hash at model_year and vehicle_key(subparition).
    I am going to implement star transformation after this performance issue get resolved. I tried enable star transformation at session level, and then check the explain plan. The plan was much better. But now, because the entire database is doing very bad, so it didn't make too much difference in response time.
    Did you set optimizer_mode = all_rows while you enabled star transformation at your database? What would be the best for optimizer_mode for a database warehouse environment? Currently, our optimizer_mode = choose.

  3. #23
    Join Date
    Jun 2000
    Posts
    315
    What can I do with these wait?

    rdbms ipc message
    library cache lock
    db file sequential read

    Thanks!

  4. #24
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    "all rows" would be fine ... it's what you'll be getting with that kind of query anyway.

    rdbms ipc message: background wait event, not a problem
    library cache lock: what version are you on?
    db file sequential read: http://metalink.oracle.com/metalink/...T&p_id=34559.1
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #25
    Join Date
    Jul 2002
    Location
    Northampton, England
    Posts
    612
    Quote Originally Posted by slimdave
    In retrospect, it would have been a good idea to store execution plans for queries while they were running well, for comparison when things go badly. Not too late to start n that now though.
    Dave

    When you said store execution plans for queries whilst they are running well, are you refering to the use of PLAN_TABLE for all queries against the database for the application? How on earth would you go about collecting that? Sounds like a huge project!! If you know some sneaky trick to make said collection easier, i'd be very happy to hear it

    ...I assume, you aren't talking about using stored outlines...
    Assistance is Futile...

  6. #26
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    I shouldn't think it necessary to do all of them, just a good representative sample. You're hopefully not using bind variables so the same query with different values could yield different execution plans anyway.

    commonly executed reports would be a good place to start, along with the longer running reports. You might be able to set tracing on at the instance level and capture most of them, but then if you have your front-end metadata in the same database then you'll get a load that you're not so interested in as well. Watch your disk space if you go down that route.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  7. #27
    Join Date
    Jun 2000
    Posts
    315
    Our database versioin is 9.2.0.6.

    What could we do with 'library cache lock'?
    It seems that's the reason for our database slow down. What's the reason for 'library chache lock?

    Thanks!

  8. #28
    Join Date
    Jul 2002
    Location
    Northampton, England
    Posts
    612
    Quote Originally Posted by lgaorcl
    Our database versioin is 9.2.0.6.

    What could we do with 'library cache lock'?
    It seems that's the reason for our database slow down. What's the reason for 'library chache lock?

    Thanks!

    Taken from the manuals:

    Library Cache Locks
    When a database object, such as a table, view, procedure, package, or index, is referenced during the parsing of a SQL, DML, DDL, PL/SQL, or Java statement, the process that parses the statement acquires a library cache lock. In Oracle9i, the lock is held only until the parse or compilation completes, or, for the duration of the parse call.

    So my guess is you aren't using bind variables, so you're spending alot more time hard parsing than you should be.
    Assistance is Futile...

  9. #29
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Use a 10046 trace to get the wait events, not v$session_wait.

    I really don't think that you want bind variables here. You want the optimizer to be sensitive to column histograms in the fact table (although the heavy use here of synthetic keys may be making that impossible). (Yes, I know. Ironic, huh?)
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width