9.2 performance issue - runs great on 7.3.4
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: 9.2 performance issue - runs great on 7.3.4

  1. #1
    Join Date
    Sep 2000
    Location
    Chicago, IL
    Posts
    316
    I have a query:

    SELECT *
    FROM ss_vwap.order_wrapper_srv ows, ss_vwap.orders_srv os, ss_vwap.exec_report_view erv
    WHERE ows.vwap_order_id = os.vwap_order_id
    AND os.vwap_order_id = erv.erv_vwap_order_id(+)
    AND os.order_id = erv.erv_order_id(+)
    AND (ows.wrapper_status not in ('Fill', 'Expd', 'Fill-Canc', 'Canc Cnfrm', 'Busted', 'Snd', 'rejected', 'Schd Fail')
    OR TRUNC(ows.record_timestamp) = TRUNC(sysdate) ) ORDER BY ows.vwap_order_id, erv.erv_exerptkeyseqnum

    which run in a 7.3.4 database in 3 mins, but takes 18+ hours on 9.2.0.1.0.

    I have analyzed the 3 tables, re-created the indexes, deleted the statistics, but cannot get the performance to improve.

    I do not want to re-write the query, since it runs just fine in 7.3.4.

    I did run explain plans, trace, before and after the changes, and I do see it doing full scan of one table, sorting, and has a cost of 14,000,000 !!, but I cannot understand why. The explain plan is the same on 7.3.4 db also. BTW, all three tables only have about 90K rows each.

    Does anyone know of a 9.2 specific issue related to my performace problem?

  2. #2
    Join Date
    Sep 2000
    Location
    Chicago, IL
    Posts
    316
    I just got it to work in 1 min, but don't know how I can make the permanent change without hard-coding the fix:

    I used the /+* RULE */ hint - that's all !!!

    Any ideas ?

  3. #3
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    If the RBO is faster than the CBO, that tends to suggest to me that the CBO is not picking up on some useful indexes. Have you ...

    i) computed statistics on the indexes?
    ii) computed statistics on indexed columns?
    iii) Tried using a functiomn-based index on TRUNC(ows.record_timestamp) -- feature that wasn't available in your old version, so read the documentation cause there's a couple of parameters you might have to change to get it to activate.

    Also, there's a couple of init parameters to look at. Reduce the value on "optimizer_index_cost_adj" to something in the region of 10-20 (which you can do dynamically with an "alter session" command to test it's effect, and an "alter system" command once you think you have the right value), and "optimizer_index_caching" can be set to around 50 (also dynamic). Both of these changes will promote the use of indexes by the CBO.

    Actually, when the instance has been up for a while run the following SQL ...

    select 'Actual is '
    ||LTrim(to_char(seq_read_wait/scattered_wait*100,'9990.0'))
    ||', system is set to '||init_value message
    from (
    select average_wait seq_read_wait
    from v$system_event
    where event ='db file sequential read'),
    (
    select average_wait scattered_wait
    from v$system_event
    where event ='db file scattered read'),
    (
    select value init_value from v$parameter
    where name = 'optimizer_index_cost_adj')

    ... and you'll get a result something like ...

    "Actual is 5.5, system is set to 12"

    ... which will tell you what a suitable value of "optimizer_index_cost_adj" would be.

    Good luck, and don't give up by using the RBO.

  4. #4
    Join Date
    Sep 2000
    Location
    Chicago, IL
    Posts
    316
    Thanks slimdave !

    The last thing I want to do is hard code the RULE hint, or something similar...but

    I tried the optimizer... params, but with no luck. The rule hint is amazingly fast !! Why cant the optimizer figure it out !!

    As I stated initially, I already did analyze...compute, re-created the indexes, so stale stats cannot be the issue.

    Anyway, please let me know of anything else I can try or look into, to get more info as to what may be going on.

  5. #5
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Can you post the explain plans for the RBO and CBO versions?

    And you're sure that the compute statistics was run after the indexes were rebuilt? (I only say that because of the order in which you list the steps you carried out)

    And how many rows are acutally returned by the query?

  6. #6
    Join Date
    Jan 2000
    Location
    HOUSTON, TX
    Posts
    75

    Thumbs up

    How you are analyzing? Are you using DBMS_STATS? If not try to use DBMS_STATS package.

    Thanks,
    OraMan

  7. #7
    Join Date
    Sep 2000
    Location
    Chicago, IL
    Posts
    316

    Talking

    MY BAD !!!

    I mis-read one of the object names, and therefore, did not analyze all the tables used by the query.

    Once I deleted the statistics from the 4 tables, everything was great !

  8. #8
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    If you deleted all the statistics, you are back to using the RBO, I believe.

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