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

Thread: Why does Optimizer not pick up indexes?

  1. #11
    Join Date
    Jun 2000
    Posts
    315
    Can 'event 10046 tracing' be set at session level? That all I can do!
    What information can I get from this event tracing?

    As I mentioned before, I did created more indexes on one of the fact tables about three weeks ago.

    Thanks!

  2. #12
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    yes it can be set at a session level

    you can see the waits in it then

  3. #13
    Join Date
    May 2005
    Location
    Boracay
    Posts
    681
    maybe ur tables have grown bigger and fragmented. You may consider reorganizing it. Consider partitioning and run parallel query on all partitions

  4. #14
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Quote Originally Posted by yxez
    maybe ur tables have grown bigger and fragmented. You may consider reorganizing it. Consider partitioning and run parallel query on all partitions
    i) Fragmented how?
    ii) Why partition?
    iii) Parallelism would speed the result, but use more resources ... seems rather like a brute force method if all else fails.
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  5. #15
    Join Date
    May 2005
    Location
    Boracay
    Posts
    681
    1.) frequent deletes makes tables fragmented.
    2.) large tables are good candidates for partitioning.
    3.) sacrifice resources just to get valuable data.
    4.) peace
    Last edited by yxez; 08-09-2005 at 12:44 AM.

  6. #16
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    i) Ah, you mean that there are fewer rows in a table with the same high water mark. Hmmm, I'm having trouble thinking of how this might cause a longer running query though...
    ii) This will have to be a fundamental area of disagreement .. maybe the reverse is true, that small tables are less likely to be candidates for partitioning, but partitioning is powerful ju-ju which will often cause a slow down in queries if not properly applied.
    iii) Again, powerful stuff. There are many "if's" to work out before commiting to such a plan.
    iv) Of course!
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  7. #17
    Join Date
    Jun 2000
    Posts
    315
    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.

  8. #18
    Join Date
    Dec 2001
    Posts
    337
    How do u analyze your tables and indexes. Oracle recommends dbms_stats.

    Thanks,
    Chucks

  9. #19
    Join Date
    Jun 2000
    Posts
    315
    here is from v$session_wait:

    EVENT WAIT_TIME SECONDS_IN_WAIT
    ------------------------------ ---------- ---------------
    pmon timer 0 8197
    rdbms ipc message 0 18
    rdbms ipc message 0 3
    rdbms ipc message 0 15
    rdbms ipc message 0 0
    rdbms ipc message 0 1
    smon timer 0 62
    rdbms ipc message 0 214
    rdbms ipc message 0 4943
    rdbms ipc message 0 30
    rdbms ipc message 0 4566

    EVENT WAIT_TIME SECONDS_IN_WAIT
    ------------------------------ ---------- ---------------
    SQL*Net message from client 0 87
    library cache lock 0 8487
    SQL*Net message from client 0 1673
    library cache lock 0 2256
    library cache lock 0 9692
    library cache lock 0 8971
    SQL*Net message from client 0 3
    SQL*Net message from client 0 945
    SQL*Net message from client 0 6000
    db file sequential read 1 66308
    library cache lock 0 8861

    EVENT WAIT_TIME SECONDS_IN_WAIT
    ------------------------------ ---------- ---------------
    SQL*Net message from client 0 1673
    library cache lock 0 1896
    library cache lock 0 8911
    library cache lock 0 7020
    library cache lock 0 14339
    db file sequential read -1 73926
    library cache lock 0 6263
    library cache lock 0 6426
    db file scattered read 0 0
    SQL*Net message from client 0 7646
    library cache lock 0 7911

    EVENT WAIT_TIME SECONDS_IN_WAIT
    ------------------------------ ---------- ---------------
    library cache lock 0 2562
    library cache lock 0 7919
    library cache lock 0 3
    library cache lock 0 10958
    library cache lock 0 6835
    SQL*Net message from client 0 4322
    library cache lock 0 8971
    library cache lock 0 6852
    library cache lock 0 7467
    SQL*Net message from client 0 746
    SQL*Net message from client 0 1703

    EVENT WAIT_TIME SECONDS_IN_WAIT
    ------------------------------ ---------- ---------------
    SQL*Net message to client -1 0
    SQL*Net message from client 0 10001
    SQL*Net message from client 0 6610
    library cache lock 0 7859
    SQL*Net message from client 0 1651
    library cache lock 0 2622
    SQL*Net message from client 0 2658
    library cache lock 0 6806
    SQL*Net message from client 0 1
    library cache lock 0 1056
    library cache lock 0 7737

    55 rows selected.

  10. #20
    Join Date
    Jul 2002
    Location
    Northampton, England
    Posts
    612
    Are the bitmap indexes you have recently createed now being used? If so, what are the cardinalities of the columns you have created bitmap indexes on?
    Assistance is Futile...

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