RE: Influencing execution plan via SQL Profiles
Date: Tue, 22 Nov 2016 01:16:54 +0000
Message-ID: <AF02C941134B1A4AB5F61A726D08DCED201AF207_at_USA7109MB012.na.xerox.net>
Thanks.
I have tried it a few times but it is not running:
SELECT /*+ leading(aeh,xet,xte) use_hash(xet,xec,xeca) use_nl(xte) swap_join_inputs(xet) swap_join_inputs(xec) swap_join_inputs(xeca) */
With the execution plan of the following:
SELECT /*+ full(xte) parallel(xte,8) */
What it is doing is that instead of scanning the table in parallel, it is doing a single-threaded FTS.
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Mohamed Houri Sent: Saturday, November 19, 2016 3:25 AM To: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> Cc: oracle-l_at_freelists.org; Hameed, Amir <Amir.Hameed_at_xerox.com> Subject: Re: Influencing execution plan via SQL Profiles
Here's below an example with the corresponding SQL script attached
SQL> select /*+ full(t1) */ count(1) from t1 where owner ='C##MHOURI';
COUNT(1)
45155
SQL> start xpsimp
SQL_ID 70qkj0rf0m9s0, child number 0
select /*+ full(t1) */ count(1) from t1 where owner ='C##MHOURI'
Plan hash value: 3724264953
| Id | Operation | Name | Rows | Bytes |
| 0 | SELECT STATEMENT | | | |
| 1 | SORT AGGREGATE | | 1 | 6 |
|* 2 | TABLE ACCESS FULL| T1 | 192K| 1126K|
Predicate Information (identified by operation id):
2 - filter("OWNER"='C##MHOURI')
SQL> select /*+ index(t1) */ count(1) from t1 where owner ='C##MHOURI';
COUNT(1)
45155
SQL> start xpsimp
SQL_ID 6argzx87r89rv, child number 0
select /*+ index(t1) */ count(1) from t1 where owner ='C##MHOURI'
Plan hash value: 383751810
| Id | Operation | Name | Rows | Bytes |
| 0 | SELECT STATEMENT | | | |
| 1 | SORT AGGREGATE | | 1 | 6 |
|* 2 | INDEX RANGE SCAN| T1_IDX | 192K| 1126K|
Predicate Information (identified by operation id):
2 - access("OWNER"='C##MHOURI')
SQL> _at_FixProfileFromXmlFromCache
Enter value for sql_id_from: 6argzx87r89rv
Enter value for child_no_from: 0
Enter value for sql_id_to: 70qkj0rf0m9s0
Enter value for sql_id_to: 70qkj0rf0m9s0
Enter value for sql_id_to: 70qkj0rf0m9s0
PL/SQL procedure successfully completed.
SQL> select /*+ full(t1) */ count(1) from t1 where owner ='C##MHOURI';
COUNT(1)
45155
SQL> start xpsimp
SQL_ID 70qkj0rf0m9s0, child number 0
select /*+ full(t1) */ count(1) from t1 where owner ='C##MHOURI'
Plan hash value: 383751810
| Id | Operation | Name | Rows | Bytes |
| 0 | SELECT STATEMENT | | | |
| 1 | SORT AGGREGATE | | 1 | 6 |
|* 2 | INDEX RANGE SCAN| T1_IDX | 192K| 1126K|
Predicate Information (identified by operation id):
2 - access("OWNER"='C##MHOURI')
Note
- SQL profile profile_70qkj0rf0m9s0_attach used for this statement
Best regards
Mohamed Houri
2016-11-18 19:56 GMT+01:00 Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk<mailto:jonathan_at_jlcomp.demon.co.uk>>:
One option would be to use the dbms_sqldiag_internal "sql_patch" procedure described at: https://blogs.oracle.com/optimizer/entry/how_can_i_hint_a
Regards
Jonathan Lewis
From: oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org> <oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org>> on behalf of Hameed, Amir <Amir.Hameed_at_xerox.com<mailto:Amir.Hameed_at_xerox.com>> Sent: 18 November 2016 18:01:19
To: oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org> Subject: Influencing execution plan via SQL Profiles
Hi,
I am trying to figure out how to remove/nullify existing hints of SQL statement and insert different hints without modifying the code:
Original Statement:
UPDATE
SELECT /*+ leading(aeh,xet,xte) use_hash(xet,xec,xeca) use_nl(xte)
swap_join_inputs(xet) swap_join_inputs(xec) swap_join_inputs(xeca) */ aeh.program_update_date -- added hint per performance change 7259699 ,aeh.program_id ,aeh.request_id ,aeh.gl_transfer_date ,aeh.gl_transfer_status_code ,aeh.group_id
TO
Modified statement:
UPDATE
SELECT /*+ full(xte) parallel(xte,8) */
aeh.program_update_date -- added hint per performance change 7259699 ,aeh.program_id ,aeh.request_id ,aeh.gl_transfer_date ,aeh.gl_transfer_status_code ,aeh.group_id
This is a packaged application and therefore, it is not possible for us to change the code at the moment.
Thanks,
Amir
--
http://www.freelists.org/webpage/oracle-l
--
Houri Mohamed
Oracle DBA-Developer-Performance & Tuning
Member of Oraworld-team<http://www.oraworld-team.com/>
Visit My - Blog<http://www.hourim.wordpress.com/>
Let's Connect - <http://fr.linkedin.com/pub/mohamed-houri/11/329/857/> Linkedin Profile<http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>
My Twitter<https://twitter.com/MohamedHouri> - MohamedHouri<https://twitter.com/MohamedHouri>
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Nov 22 2016 - 02:16:54 CET