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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of Mohamed Houri
Sent: Saturday, November 19, 2016 3:25 AM
To: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
Cc: oracle-l@xxxxxxxxxxxxx; Hameed, Amir <Amir.Hameed@xxxxxxxxx>
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> @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@xxxxxxxxxxxxxxxxxx<mailto:jonathan@xxxxxxxxxxxxxxxxxx>>:
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@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>
<oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>> on behalf
of Hameed, Amir <Amir.Hameed@xxxxxxxxx<mailto:Amir.Hameed@xxxxxxxxx>>
Sent: 18 November 2016 18:01:19
To: oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>
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
--
//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>
[https://docs.google.com/uc?export=download&id=0B9S_l_uP8T7XVDBIVFZYNTRTZ2s&revid=0B9S_l_uP8T7XdU8vTW9MMEtYa2VEdGV3aFFJdmxobm5qaThjPQ]
