Home » RDBMS Server » Performance Tuning » Recompute plan each time for the sql (oracle 10.2.0.3)
Recompute plan each time for the sql [message #415372] Mon, 27 July 2009 08:11 Go to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
Can we force the optimizer, to recompute a plan each time for a select query that uses so many bind variables?
In the sql, the where predicate is build dynamically.


Thanks,
Prachi
Re: Recompute plan each time for the sql [message #415375 is a reply to message #415372] Mon, 27 July 2009 08:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't use bind variables... and kill your server.

Regards
Michel
Re: Recompute plan each time for the sql [message #415378 is a reply to message #415375] Mon, 27 July 2009 08:34 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Building the Where clause dynamically will cause a hard parse for each different where clause that gets built.

Why would you want to force a parse when one is not neccessary - of have you misunderstood what the parse step is for?
Re: Recompute plan each time for the sql [message #415379 is a reply to message #415378] Mon, 27 July 2009 08:45 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
Sir,
Below is the sql build dynamically based on input.
I need your suggesion if we can do better with such kinda sql, by forcing optimizer to rebuild a plan each time for this sql.
SELECT * 
FROM ( 
   SELECT 
      ROWNUM RN,
      Q.* 
      FROM ( 
      SELECT 1,
         C.CSID,
         E.PNME ,
         L.DSC ,
         E.IN_TMS ,
         E.IN_TMS ,
         R.VLAST_NME ,
         R.VFIRST_NME ,
         R.VP_CDE ,
         R.CST_NBR ,
         R.CLAST_NME ,
         R.PCFIRST_NME ,
         R.DRUG_NME ,
         R.TR_NBR ,
         R.RC_NBR ,
         C.CS_I_ID 
      FROM PF F, TR R, OTM N, CSIT C, WP E, CDM L 
      WHERE E.SID = C.CSID 
      AND E.STYPE= 'C' 
      AND R.OID = F.OID 
      AND F.C_ID = N.SID 
      AND F.CIS_NBR = N.SUB_SID 
      AND R.OID = C.PRID
      AND E.LID = '07' 
      AND E.PNME NOT IN ('TRN') 
      AND L.CTXT_ID = E.PNME 
      AND L.QTXT = 'B347' 
      AND (((((E.IN1_TMS BETWEEN TO_DATE(NULL, 'mm/dd/yyyy hh:mi:ss am')  AND TO_DATE(NULL, 'mm/dd/yyyy hh:mi:ss am') 
      AND NULL IS NULL ) 
      OR (E.IN2_TMS BETWEEN TO_DATE(NULL, 'mm/dd/yyyy hh:mi:ss am') AND TO_DATE(NULL, 'mm/dd/yyyy hh:mi:ss am') 
      AND NULL IS NOT NULL )) 
      AND ((UPPER(NULL )) IS NULL 
      OR ((UPPER(R.VLAST_NME)) LIKE (UPPER(NULL ))||'%') 
      OR ((UPPER(NULL )) IS NULL 
      AND (UPPER(R.VLAST_NME)) IS NULL)) 
      AND ((UPPER(NULL )) IS NULL 
      OR ((UPPER(R.VFIRST_NME)) LIKE (UPPER(NULL ))||'%') 
      OR ((UPPER(NULL )) IS NULL 
      AND (UPPER(R.VFIRST_NME)) IS NULL)) 
      AND ((UPPER(NULL )) IS NULL 
      OR ((UPPER(R.VP_CDE)) LIKE (UPPER(NULL ))||'%') 
      OR ((UPPER(NULL )) IS NULL 
      AND (UPPER(R.VP_CDE)) IS NULL)) 
      AND ((UPPER(NULL )) IS NULL 
      OR ((UPPER(R.CLAST_NME)) LIKE (UPPER(NULL ))||'%') 
      OR ((UPPER(NULL )) IS NULL 
      AND (UPPER(R.CLAST_NME)) IS NULL)) 
      AND ((UPPER(NULL )) IS NULL 
      OR ((UPPER(R.CFIRST_NME)) LIKE (UPPER(NULL ))||'%') 
      OR ((UPPER(NULL )) IS NULL 
      AND ((UPPER(NULL )) IS NULL 
      OR ((UPPER(R.CST_NBR)) LIKE (UPPER(NULL ))||'%') 
      OR ((UPPER(NULL )) IS NULL 
      AND (UPPER(R.CST_NBR)) IS NULL)))) 
      OR C.CS_I_ID = NULL 
      OR C.CSID = 786987652
      OR R.TR_NBR = NULL 
      OR R.RC_NBR = NULL ) 
      AND F.C_ID = N.SID 
      AND F.CIS_NBR = N.SUB_SID 
      AND N.LAST_TMS = GET_MX_TMS(N.SID, N.S_SID
      ) 
      AND ((N.DEST_ID) = E.PNME 
      OR (N.DEST_ID) LIKE '%NIK') 
      AND F.C_ID = GET_MX_F(C.CS_I_ID) 
      ORDER BY E.IN_TMS, L.DSC DESC) Q
      WHERE ROWNUM <= 100 ) 
      WHERE RN >= 10 

[Updated on: Mon, 27 July 2009 08:54]

Report message to a moderator

Re: Recompute plan each time for the sql [message #415387 is a reply to message #415379] Mon, 27 July 2009 09:35 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Why do you think that forcing a hard parse ( a processing intensive operation) every single time the query is run will make it run faster?
Re: Recompute plan each time for the sql [message #415400 is a reply to message #415387] Mon, 27 July 2009 09:59 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
Sir,
Is there a way to write it in a better way? Do I have to write multiple sql to handle such sql
Re: Recompute plan each time for the sql [message #415433 is a reply to message #415372] Mon, 27 July 2009 17:21 Go to previous messageGo to next message
BlackSwan
Messages: 25037
Registered: January 2009
Location: SoCal
Senior Member
What is purpose of all the NULL in WHERE clause
AND (((((E.IN1_TMS BETWEEN TO_DATE(NULL, 'mm/dd/yyyy hh:mi:ss am')  AND TO_DATE(NULL, 'mm/dd/yyyy hh:mi:ss am') 
      AND NULL IS NULL ) 
      OR (E.IN2_TMS BETWEEN TO_DATE(NULL, 'mm/dd/yyyy hh:mi:ss am') AND TO_DATE(NULL, 'mm/dd/yyyy hh:mi:ss am') 
      AND NULL IS NOT NULL )) 
      AND ((UPPER(NULL )) IS NULL 
      OR ((UPPER(R.VLAST_NME)) LIKE (UPPER(NULL ))||'%') 
      OR ((UPPER(NULL )) IS NULL 
      AND (UPPER(R.VLAST_NME)) IS NULL)) 
      AND ((UPPER(NULL )) IS NULL 
      OR ((UPPER(R.VFIRST_NME)) LIKE (UPPER(NULL ))||'%') 
      OR ((UPPER(NULL )) IS NULL 
      AND (UPPER(R.VFIRST_NME)) IS NULL)) 
      AND ((UPPER(NULL )) IS NULL 
      OR ((UPPER(R.VP_CDE)) LIKE (UPPER(NULL ))||'%') 
      OR ((UPPER(NULL )) IS NULL 
      AND (UPPER(R.VP_CDE)) IS NULL)) 
      AND ((UPPER(NULL )) IS NULL 
      OR ((UPPER(R.CLAST_NME)) LIKE (UPPER(NULL ))||'%') 
      OR ((UPPER(NULL )) IS NULL 
      AND (UPPER(R.CLAST_NME)) IS NULL)) 
      AND ((UPPER(NULL )) IS NULL 
      OR ((UPPER(R.CFIRST_NME)) LIKE (UPPER(NULL ))||'%') 
      OR ((UPPER(NULL )) IS NULL 
      AND ((UPPER(NULL )) IS NULL 
      OR ((UPPER(R.CST_NBR)) LIKE (UPPER(NULL ))||'%') 
      OR ((UPPER(NULL )) IS NULL 
      AND (UPPER(R.CST_NBR)) IS NULL)))) 
      OR C.CS_I_ID = NULL 
      OR C.CSID = 786987652
      OR R.TR_NBR = NULL 
      OR R.RC_NBR = NULL ) 


R.TR_NBR = NULL????? Never true!
Re: Recompute plan each time for the sql [message #415458 is a reply to message #415433] Mon, 27 July 2009 23:32 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I suspect all of the NULLs are actually bind variables, which have been replaced manually in order to run the SQL in a client.

I further suspect that you have this hooked up to a parameter screen and want to solve many problems with one SQL:
- Select everything when no parameters are entered.
- When one or more parameters are entered, optimise the SQL according to the entered parameter.

This method has been tried and failed by many before you. I can be done in limited cases by using OR predicates and a hint to transform internally to a UNION query. This method stops working effectively when there are many parameters and complex conditions - probably true of you query.

The alternative is to work out a few optimal access paths (full scan for no params, index on CS_I_ID when supplied) and create sepeate SQLs for each one. Then in your code - depending on the params supplied - you choose the appropriate SQL.

You could also upgrade to 11g, which has bind-variable re-peeking.

Ross Leishman
Re: Recompute plan each time for the sql [message #415574 is a reply to message #415458] Tue, 28 July 2009 05:03 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
Sir,You are correct ..all of the NULLs are actually the parameter
passed for the stored procedure.


So you meant that the only alternative is to create separate sql?
Index is there on index on CS_I_ID.

Many thanks ...

Re: Recompute plan each time for the sql [message #415587 is a reply to message #415372] Tue, 28 July 2009 05:35 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Why are you generating a statement with all these unnecessary conditions?
You are using dynamic SQL anyway (as I understood from your first message), so create a statement for NOT NULL parameters.

HTH.
Re: Recompute plan each time for the sql [message #415604 is a reply to message #415587] Tue, 28 July 2009 06:08 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
Quote:

Why are you generating a statement with all these unnecessary conditions?



The conditions passed are not unnecessary

Quote:

- Select everything when no parameters are entered.
- When one or more parameters are entered, optimise the SQL according to the entered parameter.



Re: Recompute plan each time for the sql [message #415617 is a reply to message #415372] Tue, 28 July 2009 06:56 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
...
      AND ((UPPER(NULL )) IS NULL 
      OR ((UPPER(R.VLAST_NME)) LIKE (UPPER(NULL ))||'%') 
      OR ((UPPER(NULL )) IS NULL 
      AND (UPPER(R.VLAST_NME)) IS NULL))... 


If I got it right:

When NO value is supplied then you DON'T have to generate the condition.

HTH
Re: Recompute plan each time for the sql [message #415658 is a reply to message #415372] Tue, 28 July 2009 09:41 Go to previous messageGo to next message
BlackSwan
Messages: 25037
Registered: January 2009
Location: SoCal
Senior Member
R.TR_NBR = NULL

Never true!
Re: Recompute plan each time for the sql [message #415671 is a reply to message #415658] Tue, 28 July 2009 10:37 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member

correction :
[code]
OR C.CS_I_ID = :CS_I_ID1
OR C.CSID = 786987652
OR R.TR_NBR = :TR_NBR1
OR R.RC_NBR = :RC_NBR )
[code]
Re: Recompute plan each time for the sql [message #415970 is a reply to message #415458] Thu, 30 July 2009 00:28 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
Quote:

create sepeate SQLs for each one


Sir, But here we have to build sql for n! number of combinations...which makes things difficult for me
Re: Recompute plan each time for the sql [message #416184 is a reply to message #415970] Fri, 31 July 2009 00:47 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
I am thinking also that there is more to this than we are being told. Maybe I missed it, but there are no joins between the tables that I see in this where clause. Hard to optimize a query when you do not have the full query.

However, you could also try using a table function for this. Then you get something like from clause courtesy of the collection type and table function:

create object o_rrowtype as (c1 ...,c2...,...,cn ...)
/
create object c_rrowtype as table of o_rrowtype
/

from   e
     , c
     , cast(tf(:p1,:p2,...,:pn) as c_rrowtype) r


and then inside the table function you can dynamically construct a query that does whole object assignement and return your collection of rows via execute immediate. It would force your query to be parsed each time because you are doing dynamic sql, thus affording you an opportunity to get the best plan possible for the parameters supplied, and there would be no need at all for any of the "did not supply parameter(x) so put in this dummy expression" junk in the where clause.

function tf(p1...,p2...,pn...) return c_rrowtype is
   c_rrowtype_v c_rrowtype;
   sqlv varchar2(32000);
begin
--
-- dynamically construct a sqlstatement that does whole object assignement
-- then execute it, returning results into your collection variable
--
   if p1 is not null then <add it to where clause> end if;
   ...
   if pn is not null then ... end if;
   execute immediate sql_v into c_rrowtype_v;
   return (c_rrowtype_v);
end;
/

whole object assignment is where you cast a select statment into a collection type and then stuff it into a variable based on that type. something like this:

select cast(multiset(select * from(
                                    select *
                                    from t
                                    where ...
                                  )
                    ) as c_rrowtype
            )
into c_rrowtype_v
from dual
;

your dynamic sql will be the select statement in the middle of this thing.

Forgive me if my syntax for execute immediate is off. I often forget the different between returning, into, using etc. Also, the seemingly worthless (select * from( in the above is a fix I found for multicast that does not like comlex selects. The need to use it varies between Oracle versions.

Good luck, Kevin
Previous Topic: Bind variables and SGA size
Next Topic: How to optimize this plan (merged)
Goto Forum:
  


Current Time: Mon Dec 05 08:53:50 CST 2016

Total time taken to generate the page: 0.09707 seconds