RE: Influencing execution plan via SQL Profiles

From: Hameed, Amir <Amir.Hameed_at_xerox.com>
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>

[https://docs.google.com/uc?export=download&id=0B9S_l_uP8T7XVDBIVFZYNTRTZ2s&revid=0B9S_l_uP8T7XdU8vTW9MMEtYa2VEdGV3aFFJdmxobm5qaThjPQ]

--
http://www.freelists.org/webpage/oracle-l
Received on Tue Nov 22 2016 - 02:16:54 CET

Original text of this message