DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Thread: quer Performance issue

  1. #1
    Join Date
    Dec 2010
    Posts
    7

    Angry quer Performance issue

    Hi Friends,
    I am new to this forum.
    can anyone try to help me to resolve my issue.
    one of my query in the production database giving wron exection plan compare to development database.
    query executing in PROD in 1:50mnts but in dev it takes 700ms.

    Used QUERY
    =========
    select distinct tds . *
    from wtsg . contract_trans npq ,
    wtsg . contract_trans_group tds
    where npq . contract_trans_group_id = tds .id
    and tds . contract_id <> : contractId
    and tds . tradingday_date between : fromTradingDay and : toTradingDay
    and tds . category_id = : categoryId
    and tds . state_id = : entityStateId
    and exists(
    select *
    from wtsg . contract_trans npq1 ,
    wtsg . contract_trans_group tds1
    where npq1 . contract_trans_group_id = tds1 .id
    and tds1 . contract_id = : contractId
    and ( npq . network_point_id = npq1 . network_point_id
    or ( npq . network_point_id is null and npq1 . network_point_id is null))
    and tds . tradingday_date = tds1 . tradingday_date
    and tds . category_id = tds1 . category_id
    and tds . state_id = tds1 . state_id )


    Actions Taken:
    ===============
    analysed both the tables using dbms_stats package
    Taken trace file during the execution
    ------------------------------------
    SQL> alter session set max_dump_file_size = UNLIMITED;
    SQL> alter session set timed_statistics = true;
    SQL> alter session set statistics_level=all;
    SQL> alter session set tracefile_identifier = 'trace_file_10203';
    SQL> alter session set events '10046 trace name context forever, level 12';
    SQL> alter session set events '10053 trace name context forever, level 1';
    SQL> << Run your query here>>
    SQL> select 'close cursor' from dual;
    SQL> alter session set events '10053 trace name context off';
    SQL> alter session set events '10046 trace name context off';

    exection plan for both the DEV AND PROD
    --------------------------------------
    DEV
    ====

    371 HASH UNIQUE (cr=1244 pr=0 pw=0 time=694121 us)
    552 FILTER (cr=1244 pr=0 pw=0 time=685663 us)
    552 HASH JOIN (cr=1244 pr=0 pw=0 time=684928 us)
    79949 TABLE ACCESS FULL CONTRACT_TRANS (cr=374 pr=0 pw=0 time=454 us)
    18414 HASH JOIN (cr=870 pr=0 pw=0 time=320911 us)
    865 HASH JOIN (cr=496 pr=0 pw=0 time=96841 us)
    39 TABLE ACCESS FULL CONTRACT_TRANS_GROUP (cr=248 pr=0 pw=0 time=42534 us)
    682 TABLE ACCESS FULL CONTRACT_TRANS_GROUP (cr=248 pr=0 pw=0 time=45934 us)
    79949 TABLE ACCESS FULL CONTRACT_TRANS (cr=374 pr=0 pw=0 time=227 us)

    PROD
    =====
    364 HASH UNIQUE (cr=313908 pr=616 pw=0 time=80471596 us)
    545 FILTER (cr=313908 pr=616 pw=0 time=80450145 us)
    545 HASH JOIN (cr=313908 pr=616 pw=0 time=80447761 us)
    675 TABLE ACCESS FULL CONTRACT_TRANS_GROUP (cr=248 pr=245 pw=0 time=260344 us)
    2231783 NESTED LOOPS (cr=313660 pr=371 pw=0 time=73930078 us)
    837 HASH JOIN (cr=622 pr=151 pw=0 time=443961 us)
    46 TABLE ACCESS FULL CONTRACT_TRANS_GROUP (cr=248 pr=0 pw=0 time=58425 us)
    91430 TABLE ACCESS FULL CONTRACT_TRANS (cr=374 pr=151 pw=0 time=190828 us)
    2231783 TABLE ACCESS FULL CONTRACT_TRANS (cr=313038 pr=220 pw=0 time=69438577


    In the above we have noticed too many nest loop access more rows in PROD then DEV.
    We are unable to understand why it happens.

    It's an urget issue....

    Cheers,
    rajesh Gudaru

  2. #2
    Join Date
    Nov 2002
    Location
    Mooresville, NC
    Posts
    349
    Your Dev env cr=1,244 and prod env cr=313,908.
    Since your data volume is different how do you expect them to behave in same way.

    Now you need to think how to tune the SQL in prod.
    http://www.perf-engg.com
    A performance engineering forum

  3. #3
    Join Date
    Dec 2010
    Posts
    7
    Hi Malay,
    Thanks for your reply.
    I have taken the dump from the dev environment and created two test schema of both dev and prod and imported the same aomount of data in both the test schemas in PROD and DEV.
    after that i have taken 100046 and 10056 trace files.
    still i notice the diffrent execution path in PROD.I in a confuse state.
    IN DEVLOPEMENT DATABASE
    ----------------------
    SELECT DISTINCT tds.*
    FROM testdev.contract_trans npq, testdev.contract_trans_group tds
    WHERE npq.contract_trans_group_id = tds.id
    AND tds.contract_id <> :contractid
    AND tds.tradingday_date BETWEEN :fromtradingday AND :totradingday
    AND tds.category_id = :categoryid
    AND tds.state_id = :entitystateid
    AND EXISTS
    (SELECT *
    FROM testdev.contract_trans npq1,
    testdev.contract_trans_group tds1
    WHERE npq1.contract_trans_group_id = tds1.id
    AND tds1.contract_id = :contractid
    AND (npq.network_point_id = npq1.network_point_id
    OR (npq.network_point_id IS NULL
    AND npq1.network_point_id IS NULL))
    AND tds.tradingday_date = tds1.tradingday_date
    AND tds.category_id = tds1.category_id
    AND tds.state_id = tds1.state_id)

    call count cpu elapsed disk query current rows
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    Parse 1 0.00 0.01 0 0 0 0
    Execute 1 1.62 1.57 0 0 0 0
    Fetch 2 0.81 0.78 0 1208 0 364
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    total 4 2.43 2.37 0 1208 0 364

    Misses in library cache during parse: 1
    Optimizer mode: CHOOSE
    Parsing user id: 5 (SYSTEM)

    Rows Row Source Operation
    ------- ---------------------------------------------------
    364 HASH UNIQUE (cr=1208 pr=0 pw=0 time=783194 us)
    545 FILTER (cr=1208 pr=0 pw=0 time=775342 us)
    545 HASH JOIN (cr=1208 pr=0 pw=0 time=774670 us)
    99432 TABLE ACCESS FULL CONTRACT_TRANS (cr=382 pr=0 pw=0 time=465 us)
    18225 HASH JOIN (cr=826 pr=0 pw=0 time=359469 us)
    1000 HASH JOIN (cr=445 pr=0 pw=0 time=108528 us)
    46 TABLE ACCESS FULL CONTRACT_TRANS_GROUP (cr=223 pr=0 pw=0 time=48274 us)
    675 TABLE ACCESS FULL CONTRACT_TRANS_GROUP (cr=222 pr=0 pw=0 time=51497 us)
    99432 TABLE ACCESS FULL CONTRACT_TRANS (cr=381 pr=0 pw=0 time=182 us)


    PRODUCTION DATABSAE
    =================================================

    call count cpu elapsed disk query current rows
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    Parse 1 0.00 0.00 0 0 0 0
    Execute 1 0.00 0.00 0 0 0 0
    Fetch 2 80.03 78.40 219 319722 0 364
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    total 4 80.03 78.41 219 319722 0 364

    Misses in library cache during parse: 0
    Optimizer mode: CHOOSE
    Parsing user id: 5 (SYSTEM)

    Rows Row Source Operation
    ------- ---------------------------------------------------
    364 HASH UNIQUE (cr=319722 pr=219 pw=0 time=78406184 us)
    545 FILTER (cr=319722 pr=219 pw=0 time=78385884 us)
    545 HASH JOIN (cr=319722 pr=219 pw=0 time=78384017 us)
    675 TABLE ACCESS FULL CONTRACT_TRANS_GROUP (cr=222 pr=219 pw=0 time=328724 us)
    2380118 NESTED LOOPS (cr=319500 pr=0 pw=0 time=71521450 us)
    837 HASH JOIN (cr=603 pr=0 pw=0 time=315980 us)
    46 TABLE ACCESS FULL CONTRACT_TRANS_GROUP (cr=222 pr=0 pw=0 time=48647 us)
    99432 TABLE ACCESS FULL CONTRACT_TRANS (cr=381 pr=0 pw=0 time=100990 us)
    2380118 TABLE ACCESS FULL CONTRACT_TRANS (cr=318897 pr=0 pw=0 time=70077111 us)



    FROM THE ABOVE I HAVE NOTICE NESTED LOoP SHOWS MORE ROWS.HERE I A CONFUSED WHY THERE IS DIFFERENCE IN EXECUTION PATH having same amount of data.
    I guess we may need to change any tuning parameters for the optimizer.
    Please advice us

  4. #4
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    can you please provide the row counts and blocks occupied for the below tables in prod & dev?

    CONTRACT_TRANS
    CONTRACT_TRANS_GROUP

    also, is the bind variables in the query passing the same value in both the environments?

    Thanks,
    Vijay Tummala

    Try hard to get what you like OR you will be forced to like what you get.

  5. #5
    Join Date
    Dec 2010
    Location
    Bangalore, India
    Posts
    8
    Also let us know what is the optimizer mode in Dev & Prod and Size of the tables will also help us to answer your question.
    Thanks
    Raghav
    ------------------------------------------
    "Winner's never Quit & Quitter's never Win"
    ------------------------------------------

  6. #6
    Join Date
    Dec 2010
    Posts
    7
    Hi Venkat & Ragav,
    Thanks for your reply.
    Below are the details as requested.

    IN PROD
    =========
    DATABASE VERSION:10.2.04
    OS:SUN SOLARIS SPARC
    OPTIMIZER MODE:10.2.03
    Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
    . . imported "TEST"."CONTRACT_TRANS" 2.506 MB 99432 rows
    . . imported "TEST"."CONTRACT_TRANS_GROUP" 1.476 MB 30276 rows

    IN DEV
    ============
    DATABASE VERSION:10.2.04
    OS:SUN SOLARIS SPARC
    OPTIMIZER MODE:10.2.03
    Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
    . . imported "TESTDEV"."CONTRACT_TRANS" 2.506 MB 99432 rows
    . . imported "TESTDEV"."CONTRACT_TRANS_GROUP" 1.476 MB 30276 rows

    Yes we are passing the same bind variables on both the PROD and DEV databases.

    Yesterday i have tested by adding indexes on both the PROD and DEV.
    Then using 10046 and 10052 trace i just generated roo execution plan on both the PROD and DEV.
    I Noticed botb the tkprof files.Both the row execution plnas are showing the same.In that optimizer using index scan.

    But without index i.e using full table scan the row exection plan was showing different.Is anything prameter setting missing in PROD.I just confused.



    Cheers,
    Rajesh Gudaru

  7. #7
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    Yesterday i have tested by adding indexes on both the PROD and DEV.
    Then using 10046 and 10052 trace i just generated roo execution plan on both the PROD and DEV.
    I Noticed botb the tkprof files.Both the row execution plnas are showing the same.In that optimizer using index scan.
    Is the number of blocks occupying by each table is same in both prod & dev? Is the recent table stats gathered??

    Thanks,
    Vijay Tummala

    Try hard to get what you like OR you will be forced to like what you get.

  8. #8
    Join Date
    Dec 2010
    Location
    Bangalore, India
    Posts
    8
    Yes obviously when you create an index execution plan would show you index lookup. When there is no index execution plan will show you the full table scan. But you need to determine on your query which column the index needs to be created. Once you create an index you need to collect the stats as Vijay Suggested. Decide on what column index needs to be created and see the execution plan and the performance of the query.

    Optimizer mode could be either CHOOSE or FIRST_ROWS. Please check that. It cannot 10.2.0.3 thats the database version.
    Thanks
    Raghav
    ------------------------------------------
    "Winner's never Quit & Quitter's never Win"
    ------------------------------------------

  9. #9
    Join Date
    Jul 2006
    Posts
    195
    Run this query on both your dev and prod system and post the results:

    select table_name, partitioned, num_rows, degree, last_analyzed from dba_tables where table_name in ('CONTRACT_TRANS', 'CONTRACT_TRANS_GROUP' )
    order by last_analyzed

    Secondly, flush your shared pool or remove the query in question from
    memory. Once that is done, and add an index hint to your query and see
    if that forces the query to use the index.

  10. #10
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    Quote Originally Posted by raghav.s.dba View Post
    ...obviously when you create an index execution plan would show you index lookup...
    Not necessarily - it all depends on cardinality; as a rule of thumbs Oracle optimizer will choose FTS rather than index scan if more than 15% of the rows have to be retrieved.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Tags for this Thread

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