Home -> Mailing Lists -> Oracle-L -> Re: Force specific plan to be used

Re: Force specific plan to be used

From: Jeffrey Beckstrom <jbeckstrom_at_gcrta.org>
Date: Thu, 31 Oct 2019 14:36:11 -0400
Message-Id: <5DBB299B0200000B000644BD_at_groupwise2014.gcrta.org> 













See below for more details. Same plan hash value but vastly different elapsed time.




SQL_TEXT





SQL_PROFILE				  SQL_PLAN_BASELINE   		 CHILD_NUMBER


---------------------------- ---------------------------- ------------
PLAN_HASH_VALUE FIRST_LOAD_TIME	 LAST_ACTIVE_TIME    EXECUTIONS end of fetch
--------------- ------------------- ------------------- ---------- ------------
  DISK_READS  BUFFER_GETS BUFF_PER_EXEC DISK_PER_EXEC     BUFF_ROW  ROWS_EXEC


------------ ------------ ------------- ------------- ------------ ----------
 HIT_RATIO SORTS_EXEC    CPU_SEC CPU_SEC_PER_EXEC ELAPSED_SEC


---------- ---------- ---------- ---------------- -----------


ELAPSED_SEC_PER_EXEC I I I I  object id PROGRAM_LINE# FORCE_MATCHING_SIGNATURE

-------------------- - - - - ---------- ------------- ------------------------


SELECT 1 x FROM user_tables u WHERE u.table_name='FERRYLINE' UNION SELECT 1 x
FROM all_synonyms s INNER JOIN all_tables t ON s.synonym_name=t.table_name AND
s.owner='PUBLIC' AND s.synonym_name=s.table_name WHERE
s.synonym_name='FERRYLINE' UNION SELECT 1 x FROM user_views WHERE view_name =


'FERRYLINE' UNION SELECT 1 x FROM all_synonyms s INNER JOIN all_views v ON


s.synonym_name = v.view_name AND s.owner = 'PUBLIC' AND s.synonym_name =
s.table_name WHERE s.synonym_name = 'FERRYLINE'

																	 0
	  144319191 2019-10-31/13:01:22 10/31/2019 14:34:32   	   1  		  1
		   3  	  2,497   	  2,497   		  3   	 2,497		  1
99.8798558		  4   2.481887 	    2.481887     3.47002
			 3.47002 N N Y Y		  0   		  0     14299696840961403209





SELECT 1 x FROM user_tables u WHERE u.table_name='FERRYLINE' UNION SELECT 1 x
FROM all_synonyms s INNER JOIN all_tables t ON s.synonym_name=t.table_name AND
s.owner='PUBLIC' AND s.synonym_name=s.table_name WHERE
s.synonym_name='FERRYLINE' UNION SELECT 1 x FROM user_views WHERE view_name =


'FERRYLINE' UNION SELECT 1 x FROM all_synonyms s INNER JOIN all_views v ON


s.synonym_name = v.view_name AND s.owner = 'PUBLIC' AND s.synonym_name =
s.table_name WHERE s.synonym_name = 'FERRYLINE'

																	 1
	  144319191 2019-10-31/13:01:22 10/31/2019 14:20:09   	   7  		  7
		   0  	  4,509   	    644 		    0 		 644		  1
	   100  	    4     3.2556 	  .465085714    6.323123
		  .903303286 N N N   		 0			 0	 14299696840961403209





>>> "Jeffrey Beckstrom" <jbeckstrom_at_gcrta.org> 10/31/19 2:27 PM >>>
As stated, the parent and child cursor have the same plan_hash value. The SQL uses literals and they are the same. In the good child, it states "statistics feedback used for this statement" - that makes it better.
>>> Chris Taylor <christopherdtaylor1994_at_gmail.com> 10/31/19 2:22 PM >>>
So, to be clear, the all the cursors have the same plan ? (Both good and bad) ? If that's true , then a sql plan isn't the problem. Sounds like one of the child cursors is working on a lot less data if its using the same execution plan and performing much better.



However, if the one good cursor is showing a different plan, look up the coe_xfr_sql_profile.sql from Carlos. It takes a SQL_ID and a PLAN_HASH_VALUE and creates an sql script to force a SQL profile for that specific plan. Then you execute the sql script that is generated to create the profile in the db.



Chris




On Thu, Oct 31, 2019 at 2:03 PM Jeffrey Beckstrom <jbeckstrom_at_gcrta.org> wrote:




I have a couple of SQL statements that have multiple child cursors. Each child cursor has the same plan hash value. The plans all show "this is an adaptive plan (rows marked '-' are inactive)". The difference is that on the "good" child cursor, the plan also shows "statistics feedback used for this statement". Since all of the plans have the same plan hash value, I can not use baselines (or can I).



Any suggestions on how to force Oracle to always use the "good" child cursor.





Jeffrey Beckstrom


Lead Database Administrator


Information Technology Department


Greater Cleveland Regional Transit Authority
1240 W. 6th Street


Cleveland, Ohio 44113



 


This email has been scanned for spam and viruses. Click here
( https://attseg.cloud-protect.net/index01.php?mod_id=11&mod_option=logitem&mail_id=1572546192-5KRkM39NwgMG&r_address=jbeckstrom%40gcrta.org&report=1)  to report this email as spam.




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


Received on Thu Oct 31 2019 - 19:36:11 CET












Original text of this message