quer Performance issue

[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.

Noteata is same in prod & DEV.

Cheers,
Rajesh Gudaru

[vnktummala]

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

In Dev...

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 ...

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,

[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

[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,

[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

[PAVB]

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:

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