Home » RDBMS Server » Performance Tuning » Achieve same execution plan (Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit)
Achieve same execution plan [message #552984] |
Mon, 30 April 2012 12:26 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
manubatham20
Messages: 566 Registered: September 2010 Location: Seattle, WA, USA
|
Senior Member |
![manubatham2006@gmail.com](/forum/theme/orafaq/images/google.png)
|
|
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 #552989 is a reply to message #552985] |
Mon, 30 April 2012 12:42 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
manubatham20
Messages: 566 Registered: September 2010 Location: Seattle, WA, USA
|
Senior Member |
![manubatham2006@gmail.com](/forum/theme/orafaq/images/google.png)
|
|
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 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/58ea53b2eb012dca61431eb2407e3263?s=64&d=mm&r=g) |
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 #553080 is a reply to message #552984] |
Tue, 01 May 2012 12:12 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/58ea53b2eb012dca61431eb2407e3263?s=64&d=mm&r=g) |
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
|
|
|
|
|
|
|
Goto Forum:
Current Time: Sun May 19 15:01:59 CDT 2024
|