Home » RDBMS Server » Performance Tuning » Same query using different execution plans, why?? (Oracle Database 10g Enterprise Edition Release 10.2.0.4.0)
Same query using different execution plans, why?? [message #537105] Mon, 26 December 2011 15:24 Go to next message
manubatham20
Messages: 562
Registered: September 2010
Location: Seattle, WA, USA
Senior Member

Hello,

I am executing the query below:

INSERT INTO temp_vendor(vendor_record_seq_no,checksum,rownumber,transaction_type,iu_flag)
SELECT /*+ USE_NL ( vd1 ,vd2 ,vd3  ) leading ( vd1 ,vd2 ,vd3  , tvd) */ vd1.vendor_record_seq_no, tvr.checksum, tvr.rownumber, tvr.transaction_type, 'U' 
FROM vendor_data vd1,
vendor_data vd2,
vendor_data vd3,
     (SELECT  rownumber,
              MAX (DECODE (control_column_seq_no, 92004, original_value, NULL)) AS value1,
              MAX (DECODE (control_column_seq_no, 92000, original_value, NULL)) AS value2,
              MAX (DECODE (control_column_seq_no, 91999, original_value, NULL)) AS value3
      FROM (SELECT /*+ dynamic_sampling(vdt 3) dymanic_sampling_est_cdn(vdt) FULL (vdt) */ rownumber,
                  VALUE AS original_value,control_column_seq_no
             FROM tst_temp_vendor_data_bkp vdt
            WHERE vdt.KEY = 'Y'
              AND error_flag IS NULL) temp 
      GROUP BY rownumber) tvd,
      tst_temp_vendor_record_bkp tvr 
WHERE vd1.study_seq_no = 25707
  AND vd1.control_column_seq_no = 92004
  AND tvd.value1 = vd1.original_value
  AND vd2.study_seq_no = 25707
  AND vd2.control_column_seq_no = 92000
  AND tvd.value2 = vd2.original_value
  AND vd1.vendor_record_seq_no = vd2.vendor_record_seq_no
  AND vd3.study_seq_no = 25707
  AND vd3.control_column_seq_no = 91999
  AND tvd.value3 = vd3.original_value
  AND vd1.vendor_record_seq_no = vd3.vendor_record_seq_no
  AND tvr.error_flag IS NULL
  AND tvr.rownumber = tvd.rownumber


It is taking different approaches (execution plans) while executing for same set of parameters. Due to which sometimes it executes successfully, but sometimes it fills all TEMP space and get failed. I am pasting both the execution plan (different from expalin plan) below:

I. Successfull Execution Plan:
------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name                       | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT          |                            |       |       | 65612 (100)|          |       |       |
|*  1 |  HASH JOIN                |                            |     1 |  6121 | 65612   (1)| 00:13:08 |       |       |
|*  2 |   HASH JOIN               |                            |     1 |  6091 | 65467   (1)| 00:13:06 |       |       |
|   3 |    NESTED LOOPS           |                            | 10808 |   759K| 64520   (1)| 00:12:55 |       |       |
|   4 |     NESTED LOOPS          |                            | 10759 |   504K| 32224   (1)| 00:06:27 |       |       |
|   5 |      PARTITION HASH SINGLE|                            | 10711 |   251K|    73   (0)| 00:00:01 |    96 |    96 |
|*  6 |       INDEX RANGE SCAN    | VENDOR_DATA_IDX04          | 10711 |   251K|    73   (0)| 00:00:01 |    96 |    96 |
|   7 |      PARTITION HASH SINGLE|                            |     1 |    24 |     3   (0)| 00:00:01 |    96 |    96 |
|*  8 |       INDEX RANGE SCAN    | VENDOR_DATA_IDX04          |     1 |    24 |     3   (0)| 00:00:01 |    96 |    96 |
|   9 |     PARTITION HASH SINGLE |                            |     1 |    24 |     3   (0)| 00:00:01 |    96 |    96 |
|* 10 |      INDEX RANGE SCAN     | VENDOR_DATA_IDX04          |     1 |    24 |     3   (0)| 00:00:01 |    96 |    96 |
|  11 |    VIEW                   |                            | 58053 |   333M|   947   (3)| 00:00:12 |       |       |
|  12 |     SORT GROUP BY         |                            | 58053 |   112M|   947   (3)| 00:00:12 |       |       |
|* 13 |      TABLE ACCESS FULL    | TST_TEMP_VENDOR_DATA_BKP   | 58053 |   112M|   943   (3)| 00:00:12 |       |       |
|* 14 |   TABLE ACCESS FULL       | TST_TEMP_VENDOR_RECORD_BKP | 20731 |   607K|   144   (1)| 00:00:02 |       |       |
------------------------------------------------------------------------------------------------------------------------


II. Failed with TEMP space Execution Plan:
--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                       | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT            |                            |       |       |  1967 (100)|          |       |       |
|*  1 |  FILTER                     |                            |       |       |            |          |       |       |
|   2 |   SORT GROUP BY             |                            |     1 |  8233 |  1967   (3)| 00:00:24 |       |       |
|*  3 |    HASH JOIN                |                            |     1 |  8233 |  1966   (3)| 00:00:24 |       |       |
|   4 |     MERGE JOIN CARTESIAN    |                            |     1 |  8191 |  1767   (3)| 00:00:22 |       |       |
|   5 |      NESTED LOOPS           |                            |     1 |  6159 |     0   (0)|          |       |       |
|   6 |       NESTED LOOPS          |                            |     1 |  4106 |     0   (0)|          |       |       |
|   7 |        PARTITION HASH SINGLE|                            |     1 |  2053 |     0   (0)|          |    67 |    67 |
|*  8 |         INDEX RANGE SCAN    | VENDOR_DATA_IDX04          |     1 |  2053 |     0   (0)|          |    67 |    67 |
|   9 |        PARTITION HASH SINGLE|                            |     1 |  2053 |     0   (0)|          |    67 |    67 |
|* 10 |         INDEX RANGE SCAN    | VENDOR_DATA_IDX04          |     1 |  2053 |     0   (0)|          |    67 |    67 |
|  11 |       PARTITION HASH SINGLE |                            |     1 |  2053 |     0   (0)|          |    67 |    67 |
|* 12 |        INDEX RANGE SCAN     | VENDOR_DATA_IDX04          |     1 |  2053 |     0   (0)|          |    67 |    67 |
|  13 |      BUFFER SORT            |                            | 59929 |   116M|  1767   (3)| 00:00:22 |       |       |
|* 14 |       TABLE ACCESS FULL     | TST_TEMP_VENDOR_DATA_BKP   | 59929 |   116M|  1767   (3)| 00:00:22 |       |       |
|* 15 |     TABLE ACCESS FULL       | TST_TEMP_VENDOR_RECORD_BKP | 22130 |   907K|   199   (1)| 00:00:03 |       |       |
--------------------------------------------------------------------------------------------------------------------------


Please suggest the changes in the query (hints or any other changes), so that my query should always execute using the first (I) plan.

Let me know in case any other info. required.
Many thanks!!

[Updated on: Mon, 26 December 2011 15:27]

Report message to a moderator

Re: Same query using different execution plans, why?? [message #537108 is a reply to message #537105] Mon, 26 December 2011 16:29 Go to previous messageGo to next message
John Watson
Messages: 8593
Registered: January 2010
Location: Global Village
Senior Member
I would remove the hints you have in there, analyze the tables, and see what plan(s) you get then. But if you really don't want to do that, fix the plan you want with a stored outline.
Re: Same query using different execution plans, why?? [message #537111 is a reply to message #537108] Mon, 26 December 2011 20:40 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>TST_TEMP_VENDOR_DATA_BKP
>TST_TEMP_VENDOR_RECORD_BKP

more often than not TEMP tables are NOT required by Oracle
more often than not TEMP table do not have valid statistics
more often than not TEMP table are not indexed

any or all of the three items above can result in sub-optimal query performance.
Re: Same query using different execution plans, why?? [message #537123 is a reply to message #537111] Mon, 26 December 2011 23:00 Go to previous messageGo to next message
manubatham20
Messages: 562
Registered: September 2010
Location: Seattle, WA, USA
Senior Member

Thanks John.

Statistics are already gathered for tables except TEMP.

Can you please suggest any good link for understanding stored outline.
Also will it work for the same query with different sql ids and sql having different comparison values?

WHERE vd1.study_seq_no = 25707 --changed value here
  AND vd1.control_column_seq_no = 92004 --changed value here
  AND tvd.value1 = vd1.original_value
  AND vd2.study_seq_no = 25707 --changed value here
  AND vd2.control_column_seq_no = 92000 --changed value here
  AND tvd.value2 = vd2.original_value
  AND vd1.vendor_record_seq_no = vd2.vendor_record_seq_no
  AND vd3.study_seq_no = 25707 --changed value here
  AND vd3.control_column_seq_no = 91999 --changed value here
  AND tvd.value3 = vd3.original_value
  AND vd1.vendor_record_seq_no = vd3.vendor_record_seq_no
  AND tvr.error_flag IS NULL
  AND tvr.rownumber = tvd.rownumber


Will it affect all the queries or the selected one only?
Is there any other workarond?

Thanks!!

[Updated on: Mon, 26 December 2011 23:54]

Report message to a moderator

Re: Same query using different execution plans, why?? [message #537174 is a reply to message #537123] Tue, 27 December 2011 02:44 Go to previous messageGo to next message
John Watson
Messages: 8593
Registered: January 2010
Location: Global Village
Senior Member
This topic will tell you all about stored outlines. You could read the doc's, too:
http://www.orafaq.com/forum/mv/msg/172616/513934/148813/#msg_513934
As for "any other workaround", I've already told you that I would get rid of the hints.

Re: Same query using different execution plans, why?? [message #537241 is a reply to message #537174] Tue, 27 December 2011 06:24 Go to previous messageGo to next message
manubatham20
Messages: 562
Registered: September 2010
Location: Seattle, WA, USA
Senior Member

I have a little question in my mind...

Does inline views steps can be get mingled with main query? According to the article below, it shouldn't, but its happening here.

http://www.akadia.com/services/ora_interpreting_explain_plan.html

Views 

When a view cannot be merged into the main query you will often see a projection view operation. This indicates that the 'view' will be selected from directly as opposed to being broken down into joins on the base tables. A number of constructs make a view non mergeable. Inline views are also non mergeable. 

In the following example the select contains an inline view which cannot be merged: 

SQL> explain plan for
select ename,tot
from emp,
    (select empno,sum(empno) tot from big_emp group by empno) tmp
where emp.empno = tmp.empno; 

Query Plan
------------------------
SELECT STATEMENT [CHOOSE]
  HASH JOIN
    TABLE ACCESS FULL EMP [ANALYZED]
    VIEW
      SORT GROUP BY
        INDEX FULL SCAN BE_IX 

In this case the inline view tmp which contains an aggregate function cannot be merged into the main query. The explain plan shows this as a view step. 



According to II execution plan, Group by Operation used is done afterwards... not while processing inline query... that's actually causing the problem.

How can I force this group by to execute while fetching from TST_TEMP_VENDOR_DATA_BKP?

Thanks!!
Re: Same query using different execution plans, why?? [message #537345 is a reply to message #537241] Tue, 27 December 2011 16:58 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
The NO_MERGE, NO_PUSH_PRED, and NO_QUERY_TRANSFORMATION hints can be used to prevent this type of thing.
In this case, it looks as though your inline view is merging, so I would try the NO_MERGE hint. If that doesn't work, NO_QUERY_TRANSFORMATION would almost certainly prevent the transformation, but it might do a lot more as well. Oracle is able to do all kinds of transformations to your query to optimize it, and this hint will block all of them.

Ross Leishman
Re: Same query using different execution plans, why?? [message #537418 is a reply to message #537345] Wed, 28 December 2011 04:27 Go to previous messageGo to next message
manubatham20
Messages: 562
Registered: September 2010
Location: Seattle, WA, USA
Senior Member

Thanks Ross.

I was just looking for the sentence 'Inline view Merging' but didn't know how to say that...

I will try as you suggested... and let you know...

Previous suggestions I don't want to try... I can't use Profiles and Stored Outlines as its a dynamically generated query, so literals as well as tables changes (i.e. right now there is 3 tables vd1, vd2, vd3 but it may be even more vd4, vd5, vd6... so on).

Thanks!!
Re: Same query using different execution plans, why?? [message #538173 is a reply to message #537105] Wed, 04 January 2012 09:15 Go to previous message
LNossov
Messages: 318
Registered: July 2011
Location: Germany
Senior Member
Just a small additional observation. Your VENDOR_DATA table seems to have wrong optimizer statistics, because of very small optimizer costs. It is possible, that the table was empty at the time of gathering of statistics. If so, your could consider the locking of statistics for solving of your problem.

[Updated on: Wed, 04 January 2012 09:16]

Report message to a moderator

Previous Topic: Performance problem with table when using one column as reserved word METHOD
Next Topic: Query taking long time on MVs
Goto Forum:
  


Current Time: Mon Sep 20 23:27:01 CDT 2021