OraFAQ Forum: Performance Tuning

Home » RDBMS Server » Performance Tuning » Execution Plan

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Execution Plan [message #248266]

Thu, 28 June 2007 08:51

eggplant
Messages: 8
Registered: June 2007

Junior Member

Recently upgrade the db from 9i to 10g,and one statement has performance issue.
Here is the statement,I know that it was poorly written.

select fls.fill_line_sts_cd, count(fls.fill_line_sts_cd)
from ddf_owner.ddf_fill_line_sts fls, ddf_owner.ddf_organic_request_lines rl
where fls.rqst_id = rl.rqst_id and fls.rqst_line_id = rl.rqst_line_id and
fls.FILL_LINE_STS_CD not in ('C', 'H') and
fls.RQST_ID in
(select distinct scl.RQST_ID from ddf_owner.ddf_shpmnt_cntnr_list scl where scl.SHPMNT_ID =29026 )
group by fls.fill_line_sts_cd

In our QAR db the execution plan is
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 180 | 6 |
| 1 | HASH GROUP BY | | 6 | 180 | 6 |
| 2 | NESTED LOOPS | | 219 | 6570 | 5 |
| 3 | NESTED LOOPS | | 219 | 4599 | 3 |
| 4 | SORT UNIQUE | | 19 | 190 | 1 |
| 5 | TABLE ACCESS BY INDEX ROWID| DDF_SHPMNT_CNTNR_LIST | 19 | 190 | 1 |
| 6 | INDEX RANGE SCAN | DDF_SHPMNT_CNTNR_LIST_A01 | 21 | | 1 |
| 7 | TABLE ACCESS BY INDEX ROWID | DDF_FILL_LINE_STS | 12 | 132 | 1 |
| 8 | INDEX RANGE SCAN | DDF_FILL_LINE_STS_A03 | 17 | | 1 |
| 9 | INDEX UNIQUE SCAN | DDF_ORGANIC_REQUEST_LINES_P01 | 1 | 9 | 1 |
-------------------------------------------------------------------------------------------------

but in our PRD db, the execution is
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 150 | 288 |
| 1 | HASH GROUP BY | | 5 | 150 | 288 |
| 2 | HASH JOIN RIGHT SEMI | | 37668 | 1103K| 280 |
| 3 | TABLE ACCESS BY INDEX ROWID| DDF_SHPMNT_CNTNR_LIST | 777 | 7770 | 1 |
| 4 | INDEX RANGE SCAN | DDF_SHPMNT_CNTNR_LIST_A01 | 887 | | 1 |
| 5 | TABLE ACCESS BY INDEX ROWID| DDF_FILL_LINE_STS | 1 | 11 | 1 |
| 6 | NESTED LOOPS | | 830K| 15M| 258 |
| 7 | INDEX FULL SCAN | DDF_ORGANIC_REQUEST_LINES_P01 | 2119K| 18M| 56 |
| 8 | INDEX RANGE SCAN | DDF_FILL_LINE_STS_A03 | 1 | | 1 |
-----------------------------------------------------------------------------------------------

we rebuilt the indexes in PRD ,and the records in QAR and PRD are almost same. No outlines were used in both 2 DBs. I have no idea why the execution plan is so different. BTW, the query ran well in 9i before the upgrade.Of course I can rewrite the query to fix the performance issue,but I want to know why the index was not used in my PRD database.

If anyone has any idea,please let me know.thanks.
Fei

Report message to a moderator

Re: Execution Plan [message #248267 is a reply to message #248266]

Thu, 28 June 2007 08:53

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Please read and follow How to format your posts.
Break your lines to max 80-100 characters when you format.
Please always post your Oracle version (4 decimals).

Regards
Michel

Report message to a moderator

Re: Execution Plan [message #248295 is a reply to message #248266]

Thu, 28 June 2007 10:07

eggplant
Messages: 8
Registered: June 2007

Junior Member

select fls.fill_line_sts_cd, count(fls.fill_line_sts_cd) 
from ddf_owner.ddf_fill_line_sts fls, ddf_owner.ddf_organic_request_lines rl 
where fls.rqst_id = rl.rqst_id and fls.rqst_line_id = rl.rqst_line_id and 
fls.FILL_LINE_STS_CD not in ('C', 'H') and 
fls.RQST_ID in 
(select distinct scl.RQST_ID from ddf_owner.ddf_shpmnt_cntnr_list scl where scl.SHPMNT_ID =29026 )  
group by fls.fill_line_sts_cd

Execution plan in QAR,

-------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name                          | Rows  | Bytes | Cost  |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                               |     6 |   180 |     6 |
|   1 |  HASH GROUP BY                  |                               |     6 |   180 |     6 |
|   2 |   NESTED LOOPS                  |                               |   219 |  6570 |     5 |
|   3 |    NESTED LOOPS                 |                               |   219 |  4599 |     3 |
|   4 |     SORT UNIQUE                 |                               |    19 |   190 |     1 |
|   5 |      TABLE ACCESS BY INDEX ROWID| DDF_SHPMNT_CNTNR_LIST         |    19 |   190 |     1 |
|   6 |       INDEX RANGE SCAN          | DDF_SHPMNT_CNTNR_LIST_A01     |    21 |       |     1 |
|   7 |     TABLE ACCESS BY INDEX ROWID | DDF_FILL_LINE_STS             |    12 |   132 |     1 |
|   8 |      INDEX RANGE SCAN           | DDF_FILL_LINE_STS_A03         |    17 |       |     1 |
|   9 |    INDEX UNIQUE SCAN            | DDF_ORGANIC_REQUEST_LINES_P01 |     1 |     9 |     1 |
------------------------------------------------------------------------------------------------

Execution plan in RPD,

-----------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                          | Rows  | Bytes | Cost  |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                               |     5 |   150 |   288 |
|   1 |  HASH GROUP BY                |                               |     5 |   150 |   288 |
|   2 |   HASH JOIN RIGHT SEMI        |                               | 37668 |  1103K|   280 |
|   3 |    TABLE ACCESS BY INDEX ROWID| DDF_SHPMNT_CNTNR_LIST         |   777 |  7770 |     1 |
|   4 |     INDEX RANGE SCAN          | DDF_SHPMNT_CNTNR_LIST_A01     |   887 |       |     1 |
|   5 |    TABLE ACCESS BY INDEX ROWID| DDF_FILL_LINE_STS             |     1 |    11 |     1 |
|   6 |     NESTED LOOPS              |                               |   830K|    15M|   258 |
|   7 |      INDEX FULL SCAN          | DDF_ORGANIC_REQUEST_LINES_P01 |  2119K|    18M|    56 |
|   8 |      INDEX RANGE SCAN         | DDF_FILL_LINE_STS_A03         |     1 |       |     1 |
-----------------------------------------------------------------------------------------------

Thanks

Report message to a moderator

Re: Execution Plan [message #248313 is a reply to message #248295]

Thu, 28 June 2007 10:59

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Why do you want the plan will be the same when you have 20 lines in one case and 2 million in the other?

Use dbms_xplan to display your plan you then have much more information.

Regards
Michel

Report message to a moderator

Re: Execution Plan [message #248342 is a reply to message #248266]

Thu, 28 June 2007 12:39

eggplant
Messages: 8
Registered: June 2007

Junior Member

As I said in the first post,the records in both 2 dbs are almost same.

Report message to a moderator

Re: Execution Plan [message #248345 is a reply to message #248342]

Thu, 28 June 2007 12:52

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

I don't believe you.
Prove it.

Regards
Michel

Report message to a moderator

Re: Execution Plan [message #248357 is a reply to message #248266]

Thu, 28 June 2007 13:48

eggplant
Messages: 8
Registered: June 2007

Junior Member

QAR
<code>
SQL> select count(*) from ddf_owner.DDF_ORGANIC_REQUEST_LINES;

COUNT(*)
----------
2041122

SQL> select count(*) from ddf_owner.DDF_SHPMNT_CNTNR_LIST;

COUNT(*)
----------
861827

SQL> select count(*) from ddf_owner.DDF_FILL_LINE_STS;

COUNT(*)
----------
758403
</code>
PRD
<code>
SQL> select count(*) from ddf_owner.DDF_ORGANIC_REQUEST_LINES;

COUNT(*)
----------
2121339

SQL> select count(*) from ddf_owner.DDF_SHPMNT_CNTNR_LIST;

COUNT(*)
----------
964380

SQL> select count(*) from ddf_owner.DDF_FILL_LINE_STS;

COUNT(*)
----------
837110
</code>

Report message to a moderator

Re: Execution Plan [message #248358 is a reply to message #248266]

Thu, 28 June 2007 13:51

eggplant
Messages: 8
Registered: June 2007

Junior Member

QAR

SQL> select count(*) from ddf_owner.DDF_ORGANIC_REQUEST_LINES;

COUNT(*)
----------
2041122

SQL> select count(*) from ddf_owner.DDF_SHPMNT_CNTNR_LIST;

COUNT(*)
----------
861827

SQL> select count(*) from ddf_owner.DDF_FILL_LINE_STS;

COUNT(*)
----------
758403

PRD

SQL> select count(*) from ddf_owner.DDF_ORGANIC_REQUEST_LINES;

COUNT(*)
----------
2121339

SQL> select count(*) from ddf_owner.DDF_SHPMNT_CNTNR_LIST;

COUNT(*)
----------
964380

SQL> select count(*) from ddf_owner.DDF_FILL_LINE_STS;

COUNT(*)
----------
837110

Report message to a moderator

Re: Execution Plan [message #248359 is a reply to message #248266]

Thu, 28 June 2007 13:56

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Does this perform any better?

select fls.fill_line_sts_cd, count(fls.fill_line_sts_cd) 
from ddf_owner.ddf_fill_line_sts fls
where fls.FILL_LINE_STS_CD not in ('C', 'H') 
and fls.RQST_ID in (select distinct scl.RQST_ID from ddf_owner.ddf_shpmnt_cntnr_list scl where scl.SHPMNT_ID =29026 )  
and exists (select '1' from ddf_owner.ddf_organic_request_lines rl 
            where fls.rqst_id = rl.rqst_id 
              and fls.rqst_line_id = rl.rqst_line_id)
group by fls.fill_line_sts_cd

Report message to a moderator

Re: Execution Plan [message #248360 is a reply to message #248358]

Thu, 28 June 2007 13:57

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

So why the optimizer thinks otherwise?
Are you statistics up to date in both side?

Once again, use dbms_xplan to post your explain plan.

Regards
Michel

Report message to a moderator

Re: Execution Plan [message #248361 is a reply to message #248266]

Thu, 28 June 2007 13:57

michael_bialik
Messages: 621
Registered: July 2006

Senior Member

It does NOT prove anyrhing.

Are stats identical as well?

Michael

Report message to a moderator

Re: Execution Plan [message #248363 is a reply to message #248266]

Thu, 28 June 2007 14:07

eggplant
Messages: 8
Registered: June 2007

Junior Member

To Anacedent:I am able to rewrite the code to get better performance.I just want to find the reason why it doesn't use indexes in my PRD environment.Thanks.

To Michel Cadot: is the execution plan I attached in my first thread you wanted? I just used

select * from table(dbms_xplan.dispay)

to get the execution plan. Is there other command I can use to get the explain plan?Thanks.

To Michael_bialik: the stats are definitely not identical.however,I rebuilt the index and analyzed the table/indexes,but I still get the same result.

Thanks again for your guys' help.

Report message to a moderator

Re: Execution Plan [message #248367 is a reply to message #248363]

Thu, 28 June 2007 14:22

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Do you have a plan table up to date?
You should have much more information sections.
For instance:

SQL> explain plan for select table_name from user_tables where table_name='T';

Explained.

SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------
Plan hash value: 1430774513

------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                |     1 |   163 |     8  (13)| 00:00:01 |
|   1 |  NESTED LOOPS OUTER                 |                |     1 |   163 |     8  (13)| 00:00:01 |
|   2 |   NESTED LOOPS OUTER                |                |     1 |   160 |     7  (15)| 00:00:01 |
|   3 |    NESTED LOOPS OUTER               |                |     1 |   152 |     6  (17)| 00:00:01 |
|   4 |     NESTED LOOPS OUTER              |                |     1 |   147 |     6  (17)| 00:00:01 |
|   5 |      NESTED LOOPS                   |                |     1 |   136 |     5  (20)| 00:00:01 |
|   6 |       NESTED LOOPS                  |                |     1 |   133 |     4  (25)| 00:00:01 |
|   7 |        MERGE JOIN CARTESIAN         |                |     1 |   104 |     3  (34)| 00:00:01 |
|*  8 |         HASH JOIN                   |                |     1 |    68 |     1 (100)| 00:00:01 |
|*  9 |          FIXED TABLE FULL           | X$KSPPI        |     1 |    55 |     0   (0)| 00:00:01 |
|  10 |          FIXED TABLE FULL           | X$KSPPCV       |   100 |  1300 |     0   (0)| 00:00:01 |
|  11 |         BUFFER SORT                 |                |     1 |    36 |     3  (34)| 00:00:01 |
|* 12 |          TABLE ACCESS BY INDEX ROWID| OBJ$           |     1 |    36 |     2   (0)| 00:00:01 |
|* 13 |           INDEX RANGE SCAN          | I_OBJ2         |     1 |       |     1   (0)| 00:00:01 |
|* 14 |        TABLE ACCESS CLUSTER         | TAB$           |     1 |    29 |     1   (0)| 00:00:01 |
|* 15 |         INDEX UNIQUE SCAN           | I_OBJ#         |     1 |       |     0   (0)| 00:00:01 |
|  16 |       TABLE ACCESS CLUSTER          | TS$            |     1 |     3 |     1   (0)| 00:00:01 |
|* 17 |        INDEX UNIQUE SCAN            | I_TS#          |     1 |       |     0   (0)| 00:00:01 |
|  18 |      TABLE ACCESS CLUSTER           | SEG$           |     2 |    22 |     1   (0)| 00:00:01 |
|* 19 |       INDEX UNIQUE SCAN             | I_FILE#_BLOCK# |     1 |       |     0   (0)| 00:00:01 |
|* 20 |     INDEX UNIQUE SCAN               | I_OBJ1         |     1 |     5 |     0   (0)| 00:00:01 |
|  21 |    TABLE ACCESS BY INDEX ROWID      | OBJ$           |     1 |     8 |     1   (0)| 00:00:01 |
|* 22 |     INDEX UNIQUE SCAN               | I_OBJ1         |     1 |       |     0   (0)| 00:00:01 |
|  23 |   TABLE ACCESS CLUSTER              | USER$          |     1 |     3 |     1   (0)| 00:00:01 |
|* 24 |    INDEX UNIQUE SCAN                | I_USER#        |     1 |       |     0   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   8 - access("KSPPI"."INDX"="KSPPCV"."INDX")
   9 - filter("KSPPI"."KSPPINM"='_dml_monitoring_enabled')
  12 - filter(BITAND("O"."FLAGS",128)=0)
  13 - access("O"."OWNER#"=USERENV('SCHEMAID') AND "O"."NAME"='T')
  14 - filter(BITAND("T"."PROPERTY",1)=0)
  15 - access("O"."OBJ#"="T"."OBJ#")
  17 - access("T"."TS#"="TS"."TS#")
  19 - access("T"."TS#"="S"."TS#"(+) AND "T"."FILE#"="S"."FILE#"(+) AND
              "T"."BLOCK#"="S"."BLOCK#"(+))
  20 - access("T"."BOBJ#"="CO"."OBJ#"(+))
  22 - access("T"."DATAOBJ#"="CX"."OBJ#"(+))
  24 - access("CX"."OWNER#"="CU"."USER#"(+))

47 rows selected.

Regards
Michel

Report message to a moderator

Re: Execution Plan [message #248373 is a reply to message #248266]

Thu, 28 June 2007 14:52

eggplant
Messages: 8
Registered: June 2007

Junior Member

in PRD

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------
Plan hash value: 3400760556

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                               |     5 |   150 |   288  (52)| 00:00:04 |
|   1 |  HASH GROUP BY                |                               |     5 |   150 |   288  (52)| 00:00:04 |
|*  2 |   HASH JOIN RIGHT SEMI        |                               | 37668 |  1103K|   280  (50)| 00:00:04 |
|*  3 |    TABLE ACCESS BY INDEX ROWID| DDF_SHPMNT_CNTNR_LIST         |   777 |  7770 |     1   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | DDF_SHPMNT_CNTNR_LIST_A01     |   887 |       |     1   (0)| 00:00:01 |
|*  5 |    TABLE ACCESS BY INDEX ROWID| DDF_FILL_LINE_STS             |     1 |    11 |     1   (0)| 00:00:01 |
|   6 |     NESTED LOOPS              |                               |   830K|    15M|   258  (47)| 00:00:04 |
|   7 |      INDEX FULL SCAN          | DDF_ORGANIC_REQUEST_LINES_P01 |  2119K|    18M|    56   (2)| 00:00:01 |
|*  8 |      INDEX RANGE SCAN         | DDF_FILL_LINE_STS_A03         |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("FLS"."RQST_ID"="SCL"."RQST_ID")
   3 - filter("SCL"."RQST_ID" IS NOT NULL)
   4 - access("SCL"."SHPMNT_ID"=29026)

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------
   5 - filter("FLS"."FILL_LINE_STS_CD"<>'C' AND "FLS"."FILL_LINE_STS_CD"<>'H')
   8 - access("FLS"."RQST_ID"="RL"."RQST_ID" AND "FLS"."RQST_LINE_ID"="RL"."RQST_LINE_ID")

24 rows selected.

in QAR

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------
Plan hash value: 4201419942

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name                          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                               |     6 |   180 |     6  (34)| 00:00:01 |
|   1 |  HASH GROUP BY                  |                               |     6 |   180 |     6  (34)| 00:00:01 |
|   2 |   NESTED LOOPS                  |                               |   219 |  6570 |     5  (20)| 00:00:01 |
|   3 |    NESTED LOOPS                 |                               |   219 |  4599 |     3  (34)| 00:00:01 |
|   4 |     SORT UNIQUE                 |                               |    19 |   190 |     1   (0)| 00:00:01 |
|*  5 |      TABLE ACCESS BY INDEX ROWID| DDF_SHPMNT_CNTNR_LIST         |    19 |   190 |     1   (0)| 00:00:01 |
|*  6 |       INDEX RANGE SCAN          | DDF_SHPMNT_CNTNR_LIST_A01     |    21 |       |     1   (0)| 00:00:01 |
|*  7 |     TABLE ACCESS BY INDEX ROWID | DDF_FILL_LINE_STS             |    12 |   132 |     1   (0)| 00:00:01 |
|*  8 |      INDEX RANGE SCAN           | DDF_FILL_LINE_STS_A03         |    17 |       |     1   (0)| 00:00:01 |
|*  9 |    INDEX UNIQUE SCAN            | DDF_ORGANIC_REQUEST_LINES_P01 |     1 |     9 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - filter("SCL"."RQST_ID" IS NOT NULL)
   6 - access("SCL"."SHPMNT_ID"=29026)

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------
   7 - filter("FLS"."FILL_LINE_STS_CD"<>'C' AND "FLS"."FILL_LINE_STS_CD"<>'H')
   8 - access("FLS"."RQST_ID"="SCL"."RQST_ID")
   9 - access("FLS"."RQST_ID"="RL"."RQST_ID" AND "FLS"."RQST_LINE_ID"="RL"."RQST_LINE_ID")

25 rows selected.

Thanks.

Report message to a moderator

Previous Topic:	Help- Tuning a Query
Next Topic:	poor performance of selecting from view.

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Jan 08 23:33:12 CST 2025