RE: Short-Circuiting a MINUS operation
- From: "Chitale, Hemant K" <Hemant-K.Chitale@xxxxxx>
- To: "Sayan Malakshinov" <xt.and.r@xxxxxxxxx>
- Date: Tue, 8 Apr 2014 20:51:21 +0800
Thanks !
It works
SQL>l
1* select /*+ MONITOR opt_param('_convert_set_to_join','true') */
count(*) from (select * from hkc_target_1 minus select * from
hkc_source_1)
SQL>/
COUNT(*)
----------
0
1 row selected.
SQL>SELECT DBMS_SQLTUNE.report_sql_monitor(session_id=>userenv('SID'),
type => 'TEXT',report_level=>'ALL') AS report FROM dual;
REPORT
------------------------------------------------------------------------
------------------------------------------------------------------------
---
------------------------------------------------------------------------
----------------------------
SQL Monitoring Report
SQL Text
------------------------------
select /*+ MONITOR opt_param('_convert_set_to_join','true') */ count(*)
from (select * from hkc_target_1 minus select * from hkc_source_1)
Global Information
------------------------------
Status : DONE (ALL ROWS)
Instance ID : 1
Session : SQL (6162:1331)
SQL ID : 4wunv3g1mwrx2
SQL Execution ID : 16777216
Execution Started : 04/08/2014 20:48:19
First Refresh Time : 04/08/2014 20:48:19
Last Refresh Time : 04/08/2014 20:48:19
Duration : .096984s
Module/Action : SQL*Plus/-
Service : HEMANT
Program : sqlplus.exe
Fetch Calls : 1
Global Stats
======================================
| Elapsed | Cpu | Fetch | Buffer |
| Time(s) | Time(s) | Calls | Gets |
======================================
| 0.10 | 0.10 | 1 | 25884 |
======================================
SQL Plan Monitoring Details (Plan Hash Value=803388762)
========================================================================
=====================================================================
| Id | Operation | Name | Rows | Cost | Time
| Start | Execs | Rows | Mem | Activity | Activity Detail |
| | | | (Estim) | |
Active(s) | Active | | (Actual) | (Max) | (%) | (# samples)
|
========================================================================
=====================================================================
| 0 | SELECT STATEMENT | | | |
1 | +0 | 1 | 1 | | | |
| 1 | SORT AGGREGATE | | 1 | |
1 | +0 | 1 | 1 | | | |
| 2 | VIEW | | 1 | 6772 |
| | 1 | | | | |
| 3 | HASH UNIQUE | | 1 | 6772 |
| | 1 | | | | |
| 4 | HASH JOIN ANTI | | 1 | 6771 |
| | 1 | | 169K | | |
| 5 | TABLE ACCESS FULL | HKC_TARGET_1 | 1 | 4524 |
| | 1 | | | | |
| 6 | TABLE ACCESS FULL | HKC_SOURCE_1 | 769K | 2244 |
| | | | | | |
========================================================================
=====================================================================
1 row selected.
SQL>
(Previously I had this :
Global Stats
=================================================
| Elapsed | Cpu | Other | Fetch | Buffer |
| Time(s) | Time(s) | Waits(s) | Calls | Gets |
=================================================
| 1.95 | 1.93 | 0.01 | 1 | 38610 |
=================================================
SQL Plan Monitoring Details (Plan Hash Value=1765823105)
========================================================================
======================================================================
| Id | Operation | Name | Rows | Cost | Time
| Start | Execs | Rows | Mem | Activity | Activity Detail |
| | | | (Estim) | |
Active(s) | Active | | (Actual) | (Max) | (%) | (# samples)
|
========================================================================
======================================================================
| 0 | SELECT STATEMENT | | | |
1 | +2 | 1 | 1 | | | |
| 1 | SORT AGGREGATE | | 1 | |
1 | +2 | 1 | 1 | | | |
| 2 | VIEW | | 1 | 26541 |
| | 1 | | | | |
| 3 | MINUS | | | |
1 | +2 | 1 | 0 | | | |
| 4 | SORT UNIQUE | | 1 | 4525 |
| | 1 | | | | |
| 5 | TABLE ACCESS FULL | HKC_TARGET_1 | 1 | 4524 |
1 | +0 | 1 | 0 | | 50.00 | Cpu (1) |
| 6 | SORT UNIQUE | | 769K | 22016 |
2 | +1 | 1 | 769K | 129M | 50.00 | Cpu (1) |
| 7 | TABLE ACCESS FULL | HKC_SOURCE_1 | 769K | 2244 |
1 | +2 | 1 | 769K | | | |
========================================================================
======================================================================
)
Hemant K Chitale
From: Sayan Malakshinov [mailto:xt.and.r@xxxxxxxxx]
Sent: Tuesday, April 08, 2014 5:33 PM
To: Chitale, Hemant K
Cc: ORACLE-L
Subject: Re: Short-Circuiting a MINUS operation
I think would be helpful hint: opt_param('_convert_set_to_join','true')
Best regards,
Sayan Malakshinov
Senior Oracle performance tuning engineer
PSBANK
http://orasql.org
This email and any attachments are confidential and may also be privileged. If
you are not the intended recipient, please delete all copies and notify the
sender immediately. You may wish to refer to the incorporation details of
Standard Chartered PLC, Standard Chartered Bank and their subsidiaries at
https://www.sc.com/en/incorporation-details.html.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
