Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Stored outline problems

Stored outline problems

From: Wolfson Larry - lwolfs <lawrence.wolfson_at_acxiom.com>
Date: Mon, 13 Dec 2004 20:41:25 -0600
Message-ID: <433A07749711884D8032B6A0AB1152620596D8F6@conmsx07.corp.acxiom.net> 





Got a client who has one piece of code that somehow got its plan changed to
something horrible.



The only thing I noticed was they changed an index name from "JIMBO" to
UQ_CF.......IX 



We rebuilt the index but plan stayed the same.  They won't let us drop it
and re-create it.



They have a test database that's a copy of PROD from just before
Thanksgiving.  It's still using the good plan.



I've been trying to create stored outlines to see if I could copy it from
test to production but it doesn't seem to be working right.



When I create the outlines they don't seem to be using bind variables even
though I set cursor_sharing = FORCE.



I saw Tom Kyte pointed out if I use CREATE OUTLINE it'll use the exact code
since it didn't really execute, but I didn't do that.



I got a reference from a PEOPLESOFT RED Paper on how to put hints in their
code and from Metalink.  But doesn't seem to be working although I did get 2
outlines created.  That is I tried the swapping techniques described in both
documents but I couldn't verify outline was being used.



The code is below and most of it was from the PS Red Paper
Ran it piecemeal tho.



Then I was thinking since code is really the same on both systems couldn't I
just create an outline and export it from TEST to PROD?




  TIA 



        Larry







SET ECHO     ON



SET FEEDBACK OFF


COL TIMESTAMP            FOR A20
COL HINTCOUNT            FOR 9999999999
SELECT TO_CHAR(TIMESTAMP,'DD-MON-YYYY HH24 MI SS') TIMESTAMP


      ,OL_NAME

      ,HINTCOUNT

      ,HASH_VALUE





  FROM OUTLN.OL$



  ORDER BY TIMESTAMP DESC



;



SET HEADING OFF



SELECT '1) Capture the outline of a SQL statement.' FROM DB;
SET HEADING ON


ALTER SESSION SET CURSOR_SHARING = FORCE;


ALTER SESSION SET CREATE_STORED_OUTLINES=TRUE;


ALTER SESSION SET    USE_STORED_OUTLINES=TRUE;


ALTER SESSION SET CURRENT_SCHEMA=DLEUSER;



SELECT folder.dl_clm_folder_id, folder.clm_folder_status,
       folder.ins_co_dl_cust_id, folder.cust_clm_ref_id,
       folder.ia_internl_track_num, display.veh_ownr, display.veh_yr,
       display.veh_make, display.veh_make_desc, display.veh_model,
       display.veh_ownr_dl_cust_id, latest_ems.est_sys,
       folder.auth_prsn_dl_cust_id, recipient.cust_nm, insco.cust_nm,
       latest_ems.tot_cost_of_repair, display.ordr_sent_dt
  FROM claim_folder folder,
       claim_folder_display display,
       customer_registered recipient,
       customer_registered insco,
       claim_folder_max_ems latest_ems
 WHERE folder.dl_clm_folder_id = display.dl_clm_folder_id
   AND folder.dl_clm_folder_id = latest_ems.dl_clm_folder_id
   AND folder.auth_prsn_dl_cust_id = recipient.dl_cust_id(+)


   AND folder.ins_co_dl_cust_id = insco.dl_cust_id
   AND folder.recp_parnt_dl_cust_id = 123456789
   AND EXISTS (


          SELECT 'RDFSKGLNW'
            FROM claim_folder_detail cfd
           WHERE cfd.dl_clm_folder_id = folder.dl_clm_folder_id
             AND cfd.file_curr_status = 'OPEN') 


;





ALTER SESSION SET CREATE_STORED_OUTLINES=FALSE;


SET HEADING OFF



SELECT '2) Isolate the outline of the SQL statement from others.' FROM DB;
SET HEADING ON



COL TIMESTAMP            FOR A20
COL HINTCOUNT            FOR 9999999999
SELECT TO_CHAR(TIMESTAMP,'DD-MON-YYYY HH24 MI SS') TIMESTAMP


      ,OL_NAME

      ,HINTCOUNT

      ,HASH_VALUE





  FROM OUTLN.OL$



  ORDER BY TIMESTAMP DESC



;



SET PAUSE ON



    PAUSE



EXIT


SELECT OL_NAME



      ,SQL_TEXT




  FROM OUTLN.OL$



;



SELECT OL_NAME



      ,HINT_TEXT




  FROM OUTLN.OL$HINTS



;



ALTER  OUTLINE SYS_OUTLINE_041213195821779 RENAME TO HIGHIOCODE_ORIG;


SET HEADING OFF



SPOOL BO.drp


SELECT 'DROP OUTLINE '||OL_NAME||';' FROM OUTLN.OL$ WHERE OL_NAME LIKE

'SYS%'; 



SPOOL OFF 



SET HEADING ON


SET HEADING OFF



SELECT '3) Manually create an outline on the tuned SQL statement.' FROM DB;
SET HEADING ON


CREATE OUTLINE HIGHIOCODE_ORIG ON


SELECT folder.dl_clm_folder_id, folder.clm_folder_status,
       folder.ins_co_dl_cust_id, folder.cust_clm_ref_id,
       folder.ia_internl_track_num, display.veh_ownr, display.veh_yr,
       display.veh_make, display.veh_make_desc, display.veh_model,
       display.veh_ownr_dl_cust_id, latest_ems.est_sys,
       folder.auth_prsn_dl_cust_id, recipient.cust_nm, insco.cust_nm,
       latest_ems.tot_cost_of_repair, display.ordr_sent_dt
  FROM claim_folder folder,
       claim_folder_display display,
       customer_registered recipient,
       customer_registered insco,
       claim_folder_max_ems latest_ems
 WHERE folder.dl_clm_folder_id = display.dl_clm_folder_id
   AND folder.dl_clm_folder_id = latest_ems.dl_clm_folder_id
   AND folder.auth_prsn_dl_cust_id = recipient.dl_cust_id(+)


   AND folder.ins_co_dl_cust_id = insco.dl_cust_id
   AND folder.recp_parnt_dl_cust_id = 123456789
   AND EXISTS (


          SELECT 'RDFSKGLNW'
            FROM claim_folder_detail cfd
           WHERE cfd.dl_clm_folder_id = folder.dl_clm_folder_id
             AND cfd.file_curr_status = 'OPEN') ; 

CREATE OUTLINE HIGHIOCODE ON SELECT /* TUNED */
       folder.dl_clm_folder_id, folder.clm_folder_status,
       folder.ins_co_dl_cust_id, folder.cust_clm_ref_id,
       folder.ia_internl_track_num, display.veh_ownr, display.veh_yr,
       display.veh_make, display.veh_make_desc, display.veh_model,
       display.veh_ownr_dl_cust_id, latest_ems.est_sys,
       folder.auth_prsn_dl_cust_id, recipient.cust_nm, insco.cust_nm,
       latest_ems.tot_cost_of_repair, display.ordr_sent_dt
  FROM claim_folder folder,
       claim_folder_display display,
       customer_registered recipient,
       customer_registered insco,
       claim_folder_max_ems latest_ems
 WHERE folder.dl_clm_folder_id = display.dl_clm_folder_id
   AND folder.dl_clm_folder_id = latest_ems.dl_clm_folder_id
   AND folder.auth_prsn_dl_cust_id = recipient.dl_cust_id(+)


   AND folder.ins_co_dl_cust_id = insco.dl_cust_id
   AND folder.recp_parnt_dl_cust_id = 123456789
   AND EXISTS (


          SELECT 'RDFSKGLNW'
            FROM claim_folder_detail cfd
           WHERE cfd.dl_clm_folder_id = folder.dl_clm_folder_id
             AND cfd.file_curr_status = 'OPEN') ;





SET HEADING OFF



SELECT '4) Swap the execution plan of the original outline with tuned
outline.' FROM DB;


SET HEADING ON


SELECT TO_CHAR(TIMESTAMP,'DD-MON-YYYY HH24 MI SS') TIMESTAMP



      ,OL_NAME


      ,HINTCOUNT


      ,HASH_VALUE




  FROM OUTLN.OL$



  WHERE OL_NAME IN ('HIGHIOCODE_ORIG','HIGHIOCODE') ;






DECODE(OL_NAME,'HIGHIOCODE','HIGHIOCODE_ORIG','HIGHIOCODE_ORIG','HIGHIOCODE'

)


  Where OL_NAME IN ('HIGHIOCODE','HIGHIOCODE_ORIG') ;
-- Above from Oracle NOTE:92202.1



UPDATE OUTLN.OL$


  SET OL_NAME   = 'TO_DEL'
     ,HINTCOUNT =  &&HintCount
  Where OL_NAME = 'HIGHIOCODE'




;



UPDATE OUTLN.OL$HINTS



  SET OL_NAME   = 'TO_DEL'



  Where OL_NAME = 'HIGHIOCODE_ORIG'


;



DROP OUTLINE TO_DEL;


UPDATE OUTLN.OL$


  SET OL_NAME   = 'HIGHIOCODE'
     ,HINTCOUNT =  &&HintCount
  Where OL_NAME = 'HIGHIOCODE_ORIG'




;




SET HEADING OFF



SELECT '5) Test the outline.' FROM DB;


SET HEADING ON


ALTER  SYSTEM FLUSH SHARED_POOL;



SELECT TO_CHAR(TIMESTAMP,'DD-MON-YYYY HH24 MI SS') TIMESTAMP



      ,OL_NAME


      ,HINTCOUNT


      ,HASH_VALUE




  FROM OUTLN.OL$



  WHERE OL_NAME = 'HIGHIOCODE_ORIG'



;


SELECT folder.dl_clm_folder_id, folder.clm_folder_status,
       folder.ins_co_dl_cust_id, folder.cust_clm_ref_id,
       folder.ia_internl_track_num, display.veh_ownr, display.veh_yr,
       display.veh_make, display.veh_make_desc, display.veh_model,
       display.veh_ownr_dl_cust_id, latest_ems.est_sys,
       folder.auth_prsn_dl_cust_id, recipient.cust_nm, insco.cust_nm,
       latest_ems.tot_cost_of_repair, display.ordr_sent_dt
  FROM claim_folder folder,
       claim_folder_display display,
       customer_registered recipient,
       customer_registered insco,
       claim_folder_max_ems latest_ems
 WHERE folder.dl_clm_folder_id = display.dl_clm_folder_id
   AND folder.dl_clm_folder_id = latest_ems.dl_clm_folder_id
   AND folder.auth_prsn_dl_cust_id = recipient.dl_cust_id(+)


   AND folder.ins_co_dl_cust_id = insco.dl_cust_id
   AND folder.recp_parnt_dl_cust_id = 123456789
   AND EXISTS (


          SELECT 'RDFSKGLNW'
            FROM claim_folder_detail cfd
           WHERE cfd.dl_clm_folder_id = folder.dl_clm_folder_id
             AND cfd.file_curr_status = 'OPEN') ;

SELECT A.OUTLINE_CATEGORY


      ,A.HASH_VALUE

      ,A.FIRST_LOAD_TIME

      ,A.LOADS

      ,A.EXECUTIONS

      ,A.OPTIMIZER_COST

      ,B.USERNAME

  FROM  V$SQL     A
       ,ALL_USERS B
  WHERE A.PARSING_USER_ID = B.USER_ID
   AND  A.HASH_VALUE      = '3967470224'


  ORDER BY A.FIRST_LOAD_TIME DESC



;






The information contained in this communication is
confidential, is intended only for the use of the recipient
named above, and may be legally privileged.
If the reader of this message is not the intended
recipient, you are hereby notified that any dissemination, 
distribution, or copying of this communication is strictly
prohibited.


If you have received this communication in error,
please re-send this communication to the sender and
delete the original message or any copy of it from your
computer system. Thank You.


--
http://www.freelists.org/webpage/oracle-l


Received on Mon Dec 13 2004 - 20:36:55 CST












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US