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