quer Performance issue

Printable View

12-23-2010, 12:07 PM
Rajesh Gudaru

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
12-24-2010, 07:17 AM
malay_biswal

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.
12-24-2010, 09:44 AM
Rajesh Gudaru

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
12-27-2010, 02:51 AM
vnktummala

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,
12-27-2010, 06:44 AM
raghav.s.dba

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.
12-28-2010, 03:40 AM
Rajesh Gudaru

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
12-28-2010, 04:39 AM
vnktummala

Quote:

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,
12-28-2010, 06:00 AM
raghav.s.dba

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.
12-28-2010, 09:54 AM
BeefStu

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.
12-28-2010, 02:01 PM
PAVB

Quote:

Originally Posted by raghav.s.dba

...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.
12-29-2010, 07:22 AM
Rajesh Gudaru

Hi,
I appreciate your comments.
Sorry for wrong information passed by me.

The optimizer mode used in both the PROD and DEV is CHOOSE.
Secondly,
It's a production database i can't alow to do flush the share pool size.

Thirdly:
The main issue is difference in excution plan in PROD compare to DEV as i mentioned earlier.
As i mentioned earlier:
IN DEV:
===========
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)

IN PROD
=============
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)

The above row execution plan where we absorve diffenence in execution.
My big worry is thta in PROD executon plan why too much nexteing is happening durinh FTP.

Note:Data is same in prod & DEV.

Cheers,
Rajesh Gudaru
12-29-2010, 07:49 AM
vnktummala

The production plan is clearly showing that you have more rows in production. No confusion in that.

In Dev...

Quote:

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)

In Prod ...

Quote:

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)

Can you please check which join or condition in production is fetching more data and investigate from there??

Thanks,
12-29-2010, 07:54 AM
Rajesh Gudaru

Hi Friends,
As earlier post details are below:

IN PROD
=============
OWNER TABLE_NAME PAR NUM_ROWS DEGREE LAST_ANALY
------ ----------------- --- ---------- ---------- ----------
TEST CONTRACT_TRANS NO 92056 1 21/12/2010
TEST CONTRACT_TRANS_GROUP NO 29636 1 22/12/2010
WTSG CONTRACT_TRANS NO 94520 1 23/12/2010
WTSG CONTRACT_TRANS_GROUP NO 28687 1 23/12/2010

IN DEV
==========
OWNER TABLE_NAME PAR NUM_ROWS DEGREE LAST_ANALY
------ ------------ --- ---------- ---------- ----------
TESTDEV CONTRACT_TRANS NO 92056 1 21/12/2010
WTSG CONTRACT_TRANS NO 92056 1 21/12/2010
WTSG CONTRACT_TRANS_GROUP NO 29636 1 22/12/2010
TESTDEV CONTRACT_TRANS_GROUP NO 29636 1 22/12/2010

Cheers,
Rajesh Gudaru
12-29-2010, 08:07 AM
vnktummala

Rajesh,

Please generate the explain plan (in dev & prod) using

explain plan for
SQL statement

properly format it and attached to your post.

Thanks,
12-29-2010, 08:20 AM
Rajesh Gudaru

Hi Venkat,
As i mentioned earlier both the PROD and DEV having the same rows.
But in pROD execution plan it was showing more rows.
Here i am just confused.

Cheers,
Rajesh Gudaru
12-29-2010, 11:44 AM
PAVB

Quote:

Originally Posted by Rajesh Gudaru

As i mentioned earlier both the PROD and DEV having the same rows.
But in pROD execution plan it was showing more rows.
Here i am just confused.

Are you fully qualifying table_names in your query?

Please run and post:

Code:

select owner, table_name, num_rows, last_analyzed from sys.dba_tables where table_name in('CONTRACT_TRANS');