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
====
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)
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
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.
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.
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"
------------------------------------------
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.
...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.
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.
quer Performance issue
Reply With Quote
Originally Posted by raghav.s.dba
Bookmarks