Re: Force specific plan to be used
- From: "Jeffrey Beckstrom" <jbeckstrom@xxxxxxxxx>
- To: "Chris Taylor" <christopherdtaylor1994@xxxxxxxxx>
- Date: Thu, 31 Oct 2019 14:27:58 -0400
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@xxxxxxxxx> 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@xxxxxxxxx> 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.
Other related posts:
