OraFAQ Forum: Performance Tuning » Achieve same execution plan

Home » RDBMS Server » Performance Tuning » Achieve same execution plan (Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit)

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

Achieve same execution plan [message #552984]

Mon, 30 April 2012 12:26

manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA

Senior Member

Hi,

Please refere to below 2 queries and their execution plans:

First Query

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, 435361232, original_value, NULL)) AS value1,
              MAX (DECODE (control_column_seq_no, 435361228, original_value, NULL)) AS value2,
              MAX (DECODE (control_column_seq_no, 435361227, original_value, NULL)) AS value3
      FROM (SELECT /*+ dynamic_sampling(vdt 3) dymanic_sampling_est_cdn(vdt) */ rownumber,
                  VALUE AS original_value,control_column_seq_no
             FROM temp_vendor_data vdt
            WHERE vdt.KEY = 'Y'
              AND error_flag IS NULL) temp 
      GROUP BY rownumber) tvd,
      temp_vendor_record tvr 
WHERE vd1.study_seq_no = 99903
  AND vd1.control_column_seq_no = 435361232
  AND tvd.value1 = vd1.original_value
  AND vd2.study_seq_no = 99903
  AND vd2.control_column_seq_no = 435361228
  AND tvd.value2 = vd2.original_value
  AND vd1.vendor_record_seq_no = vd2.vendor_record_seq_no
  AND vd3.study_seq_no = 99903
  AND vd3.control_column_seq_no = 435361227
  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

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

Second Query

SELECT vd1.vendor_record_seq_no, tvr.checksum, tvr.rownumber, tvr.transaction_type, 'U' 
FROM ( select * from vendor_data vd1
where vd1.study_seq_no = 99903
 AND vd1.control_column_seq_no = 435361232
)vd1,
( select * from vendor_data vd2
where vd2.study_seq_no = 99903
 AND vd2.control_column_seq_no = 435361228
)vd2,
( select * from vendor_data vd3
where vd3.study_seq_no = 99903
 AND vd3.control_column_seq_no = 435361227
)vd3,
     (SELECT  rownumber,
              MAX (DECODE (control_column_seq_no, 435361232, original_value, NULL)) AS value1,
              MAX (DECODE (control_column_seq_no, 435361228, original_value, NULL)) AS value2,
              MAX (DECODE (control_column_seq_no, 435361227, original_value, NULL)) AS value3
      FROM (SELECT  rownumber,
                  VALUE AS original_value,control_column_seq_no
             FROM temp_vendor_data vdt
            WHERE vdt.KEY = 'Y'
              AND error_flag IS NULL) temp 
      GROUP BY rownumber) tvd,
      temp_vendor_record tvr 
WHERE tvd.value1 = vd1.original_value
 AND tvd.value2 = vd2.original_value
 AND vd1.vendor_record_seq_no = vd2.vendor_record_seq_no
  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

------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT             |                         |       |       | 36433 (100)|          |       |       |
|   1 |  NESTED LOOPS                |                         |     1 |  6128 | 36433   (1)| 00:07:18 |       |       |
|   2 |   NESTED LOOPS               |                         |     1 |  6115 | 36432   (1)| 00:07:18 |       |       |
|*  3 |    HASH JOIN                 |                         |  2808 |    16M| 30812   (1)| 00:06:10 |       |       |
|   4 |     VIEW                     |                         | 19999 |   114M|  1548   (5)| 00:00:19 |       |       |
|   5 |      SORT GROUP BY           |                         | 19999 |   332K|  1548   (5)| 00:00:19 |       |       |
|*  6 |       TABLE ACCESS FULL      | TEMP_VENDOR_DATA        |   349K|  5810K|  1524   (4)| 00:00:19 |       |       |
|*  7 |     HASH JOIN                |                         |  2836K|   173M| 29241   (1)| 00:05:51 |       |       |
|   8 |      PARTITION HASH SINGLE   |                         |  1809K|    55M| 13955   (1)| 00:02:48 |    95 |    95 |
|*  9 |       INDEX RANGE SCAN       | VENDOR_DATA_IDX04       |  1809K|    55M| 13955   (1)| 00:02:48 |    95 |    95 |
|  10 |      PARTITION HASH SINGLE   |                         |  1976K|    60M| 15242   (1)| 00:03:03 |    95 |    95 |
|* 11 |       INDEX RANGE SCAN       | VENDOR_DATA_IDX04       |  1976K|    60M| 15242   (1)| 00:03:03 |    95 |    95 |
|  12 |    PARTITION HASH SINGLE     |                         |     1 |    32 |     2   (0)| 00:00:01 |    95 |    95 |
|* 13 |     INDEX UNIQUE SCAN        | VENDOR_DATA_IDX04       |     1 |    32 |     2   (0)| 00:00:01 |    95 |    95 |
|* 14 |   TABLE ACCESS BY INDEX ROWID| TEMP_VENDOR_RECORD      |     1 |    13 |     1   (0)| 00:00:01 |       |       |
|* 15 |    INDEX UNIQUE SCAN         | TEMP_VENDOR_RECORD_IDX1 |     1 |       |     0   (0)|          |       |       |
------------------------------------------------------------------------------------------------------------------------

Both are to achieve same output but written in different ways. CAn I get same exectuion plan from 1st query as there is for 2nd using hints. I tried but failed. Any suggestions.

Thanks,
Manu

[Updated on: Tue, 01 May 2012 11:54] by Moderator

Report message to a moderator

Re: Achieve same execution plan [message #552985 is a reply to message #552984]

Mon, 30 April 2012 12:31

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

consider using STORED OUTLINE

Report message to a moderator

Re: Achieve same execution plan [message #552989 is a reply to message #552985]

Mon, 30 April 2012 12:42

manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA

Senior Member

Confused.

Stored outline work for the different execution plan of the same query.

Here query is written differently to achieve a better execution plan. So just asking if plan of first query can be forced to look like plan of second query.

Regards,
Manu

[Corrected spelling mistake]

[Updated on: Mon, 30 April 2012 12:46]

Report message to a moderator

Re: Achieve same execution plan [message #553075 is a reply to message #552984]

Tue, 01 May 2012 10:53

LNossov
Messages: 318
Registered: July 2011
Location: Germany

Senior Member

It is possible. If you have Tuning Pack licence, then you can use undocumented procedure dbms_sqltune.import_sql_profile for creating of sql profile including outlines of your hinted sql. You can get outlines to the execution plan of your hinted sql for ex. by calling dbms_xplan.display with ADVANCED format.

If you don't have this licence, then you can create two stored outlines (hinted and with original text) and then exchange them. For ex. with the following script:

-- E. Nossova, Product TuTool : www.tutool.de

/* inserts hidden hints in stored outline
for a sql without hints from stored outline
for the same sql with hints,
input parameters: 
     originalsql_outln_name - name of stored
                              outline for sql
                              without hints,
     hintedsql_outln_name - name of stored 
                            outline for the
                            same sql with hints*/


define originalsql_outln_name='&originalsql_outln_name'
define hintedsql_outln_name='&hintedsql_outln_name'

set verify off

begin
	update outln.ol$hints set ol_name = decode(ol_name, '&hintedsql_outln_name', '&originalsql_outln_name', 
'&originalsql_outln_name', '&hintedsql_outln_name')
	where
	ol_name in ('&hintedsql_outln_name', '&originalsql_outln_name') and
	2 = (select count(*) from outln.ol$ where ol_name in ('&hintedsql_outln_name', '&originalsql_outln_name'));
	
	if sql%rowcount = 0 then 
		raise_application_error(-20001,'outline names failure');
	end if;

	execute immediate 'drop outline &hintedsql_outln_name';

end;
/

undefine originalsql_outln_name
undefine hintedsql_outln_name

I suppose, the second method should be easier for you.

[Updated on: Tue, 01 May 2012 11:53] by Moderator

Report message to a moderator

Re: Achieve same execution plan [message #553078 is a reply to message #552989]

Tue, 01 May 2012 11:30

LNossov
Messages: 318
Registered: July 2011
Location: Germany

Senior Member

Forget my answer please. I didn't check your sql's carefully and didn't notice, that you have 2 sql's with different query blocks. Two methods, I described, can help in case of hidden hints (i.e. for sql's, that differ only in hints).

Try to tune your sql by hints. Than you can apply one of the methods.

Report message to a moderator

Re: Achieve same execution plan [message #553080 is a reply to message #552984]

Tue, 01 May 2012 12:12

LNossov
Messages: 318
Registered: July 2011
Location: Germany

Senior Member

Try please with the following hints:

SELECT 
/*+ no_merge(vd1) no_merge(vd2) no_merge(vd3) no_merge(tvd) use_nl(vd1 vd2 vd3) leading (vd1 vd2 vd3 tvd) */ 
vd1.vendor_record_seq_no, tvr.checksum, tvr.rownumber, tvr.transaction_type, 'U' 
FROM ( select * from vendor_data vd1
where vd1.study_seq_no = 99903
 AND vd1.control_column_seq_no = 435361232
)vd1,
( select * from vendor_data vd2
where vd2.study_seq_no = 99903
 AND vd2.control_column_seq_no = 435361228
)vd2,
( select * from vendor_data vd3
where vd3.study_seq_no = 99903
 AND vd3.control_column_seq_no = 435361227
)vd3,
     (SELECT  rownumber,
              MAX (DECODE (control_column_seq_no, 435361232, original_value, NULL)) AS value1,
              MAX (DECODE (control_column_seq_no, 435361228, original_value, NULL)) AS value2,
              MAX (DECODE (control_column_seq_no, 435361227, original_value, NULL)) AS value3
      FROM (SELECT  rownumber,
                  VALUE AS original_value,control_column_seq_no
             FROM temp_vendor_data vdt
            WHERE vdt.KEY = 'Y'
              AND error_flag IS NULL) temp 
      GROUP BY rownumber) tvd,
      temp_vendor_record tvr 
WHERE tvd.value1 = vd1.original_value
 AND tvd.value2 = vd2.original_value
 AND vd1.vendor_record_seq_no = vd2.vendor_record_seq_no
  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;

I hope, you'll get an execution plan very similar to the first one. Unfortunately you don't upload ddl's (especially for VENDOR_DATA_IDX04).

[Updated on: Tue, 01 May 2012 12:33] by Moderator

Report message to a moderator

Re: Achieve same execution plan [message #553117 is a reply to message #553080]

Wed, 02 May 2012 00:48

LNossov
Messages: 318
Registered: July 2011
Location: Germany

Senior Member

1. You can use hints as each other feature of Oracle.
2. Hints have some disadvantages. But practically each feature has them (at least under certan circumstances).
3. Oracle recommend to be aware of these disadvantages bei using hints.

Report message to a moderator

Re: Achieve same execution plan [message #553195 is a reply to message #553117]

Wed, 02 May 2012 09:10

manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA

Senior Member

LNossov,

I don't know why you used no_merge with all the tables and not included "tvr" in hint portion.
Any specific reason?

Thanks for your response!!

Report message to a moderator