Forum Stats

  • 3,734,281 Users
  • 2,246,937 Discussions
  • 7,857,218 Comments

Discussions

Will security Patching /CPU Patching affects using WITH Clause ?

User_7D9EY
User_7D9EY Member Posts: 9 Employee

Hi All,

After applying CPU patching , our queries using WITH clause started failing . Did anyone experienced the sam e? Please share your thoughts.

My query structure is as below

CREATE VIEW test_view

WITH table_1 AS

(select .. from a, b ,c) ,

WITH table_2 AS

(select ... from a, table_1 , c, d e..)

SELECT ....

FROM x , table_1 , table_2...

UNION

SELECT ....

FROM x , table_1 ..

AND NOT EXISTS (select 'x' from table_2);


Error :  ORA-32036: unsupported case for inlining of query name in WITH clause

32036. 00000 - "unsupported case for inlining of query name in WITH clause"

*Cause:   There is at least one query name which is inlined more than once

          because it's definition query is too simple and references another

          query name. This is currently unsupported yet.

*Action:  remove such query name and retry

Error at Line: 1 Column: 40.

Please note that , error is not related to syntax and not related to data . Same data is working in other instances .. It is related to the usage of table_2.

When I remove the NOT exists condition(last line) , then it works.


Thank You ,

Ashwini

Best Answer

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,607 Gold Crown
    Accepted Answer

    Can you show us a screen shot, or cut-and-paste, of exactly what you did to generate the CBO trace file, and how you found the correct tracefile name.

    I can think of only three reasons at present why you can't find anything in the trace file

    a) Your "select from dual" is using something like '"select user from dual" or "select sysdate from dual" which has been optimised by some other session already - that's why I suggested "some random text".

    b) (Unlikely) If you're using a very new version of Oracle maybe the optimizer has been tweaked to recognise that there's only one path for selecting anything from dual, and uses a pre-pared path.

    c) You're using a tool that keeps making new connections so that the 'alter session' is executed in a different session from the select, or you're running the 'select from v$process' in a separate session from the alter session/select from dual

    To get an optimizer trace file generated you need to set the event and then execute in the same session a statement that hasn't previously been optimized.


    A couple of thoughts about the error:

    1) If the structures of the views are identical then it's easy to argue that there's a defect in the optimizer that allows one to produce the error while the other survive.

    2) For the structure to be identical there are some details that the human eye might overlook - in particular there might be a difference in "NOT NULL" or "UNIQUE" declarations on join columns or even on columns with literal filter predicates. There may be type mismatches between columns in the WHERE clauses in one view that don't appear in the other.

    3) It is possible for the problem to be related to the actual values of bind variables (or literals) used. A value that produces a small cardinality estimate while optimising one view may mean that a particular transformation is not considered and an entire branch of transformations is bypassed in one case, while a larger cardinality estimate may push the optimizer into following a transformation that eventually leads to this problem.

    Checking (2) should be relatively easy; checking (3) can take a lot of work and relies on confidence in reading and using execution plans and may necessitate looking at a CBO (10053) trace file.


    Regards

    Jonathan Lewis

Answers

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,607 Gold Crown


    Please clarify.

    Do some of your existing "create view" statements now fail on a patched instance when they had previously worked on that instance, or is this a new "create view" statement that looks similar to others that do still work. Have you got a specific example that raises this error on this instance but doesn't on other unpatched instances.


    Have you checked parameter settings - to see how they differ between instances that behave and this instance. The error message is fairly precise in what it's trying to complain about: materialized views have gone inline when they shouldn't. Off the top of my head I can think of four reasons for this

    a) this particular example has an /*+ inline */ hint in it that is not present in other examples and it has been applied in a case where it's not supported

    b) the "_with_subquery" parameter has been set to INLINE rather than being left to default

    c) an optimizer change (or change of parameter) means the optimizer has transformed the statement into a pattern that it has not used before for this statement, and that change has caused the error to appear

    d) "secure view merging" has come into play in a case where it used to be bypassed and that has resulted in this error (maybe because of a patch, maybe because of a parameter change)


    Regards

    Jonathan Lewis




    )

  • User_7D9EY
    User_7D9EY Member Posts: 9 Employee

    Hi Jonathan,


    Thank You for the quick response.

    This is an existing view started troubling after patching . There are many views like this are working in the same instance.

    It is not a data issue . I have taken complete data set to another instance then executed my view. It is working .

    Not sure what is really stopping at this view particularly .

    Thank You ,

    Ashwini

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,607 Gold Crown


    So we are talking about views that have already been created in this instance before the patch, NOT about the "create view" statement itself.

    Suddenly, after the patch, queries against some of these views start failing with the supplied Oracle error. The error is obviously NOT (directly) about the data since the description tells you it's about an unsupported case of how the query is constructed. (I say "directly" because there's always the possibility that one part of the optimizer code has transformed a query in a way that it thinks is legal, and a subsequent part of the optimizer code recognises the transformation as illegal and raises the error; and it's just possible that the transformation is driven by something about the data)

    Can's see any obvious difference between views that raise the error and views that don't; and if you can't spot any difference in optimizer parameters between the instance where a specific example works and where it doesn't work, then try this:

    alter session set events '10053 trace name context forever';
    select 'some random text' from dual;
    exit
    

    This will generate a CBO trace file. Locate the file and serarch for the word "PARAMETERS" This will lead you to a section headed: PARAMETERS USED BY THE OPTIMIZER - compare the two lists from the two instances to see if that shows you any differences.

    If there's no difference then search the trace files for the phrase: "Bug Fix Control Environment" (you may need to skip the first occurrence and go to the second). This will list the state of all fix controls. Again, compare between the two instances.


    Regards

    Jonathan Lewis

  • User_7D9EY
    User_7D9EY Member Posts: 9 Employee

    Hi Jonathan ,

    Thank You for the quick response,

    I tried to get the trace to compare. However I did not find the key word PARAMETER or OPTIMIZER in both the instance . Am I missing anything?

    Is the id 10053 correct ? Or it differs from instance , project etc .


    Thank You,

    Ashwini

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,607 Gold Crown

    Can you show us a screen shot, or cut-and-paste, of exactly what you did to generate the CBO trace file, and how you found the correct tracefile name.

    It's always possible if you had an SQL*Plus running (from the command prompte) on one server that it would connect to the Oracle instance on the other server, but since you checked the trace file directories on both servers you should have found the file.

    A query like the following should give you the correct tracefile name:

    select  tracefile 
    from    v$process 
    where   addr = (
                    select  paddr 
                    from    v$session 
                    where   sid = sys_context('userenv','sid')
                   )
    ;
    

    Regards

    Jonathan Lewis

  • User_7D9EY
    User_7D9EY Member Posts: 9 Employee

    Hi Jonathan,

    I am happy to see your interest on helping my issue . :)

    I executed the trace file query shared by you . It gave me the path : /u01/app/oracle/diag/rdbms/orcl_iad1bc/ORCL/trace/ORCL_ora_9082.trc

    Downloaded the trace file from DB server to local . searched for the key word PARAMETER. Did not find any.

    The issue is with only one view. The other views having the same construct as mentioned in my first post are working .


    Thank You ,

    Ashwini

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,607 Gold Crown
    Accepted Answer

    Can you show us a screen shot, or cut-and-paste, of exactly what you did to generate the CBO trace file, and how you found the correct tracefile name.

    I can think of only three reasons at present why you can't find anything in the trace file

    a) Your "select from dual" is using something like '"select user from dual" or "select sysdate from dual" which has been optimised by some other session already - that's why I suggested "some random text".

    b) (Unlikely) If you're using a very new version of Oracle maybe the optimizer has been tweaked to recognise that there's only one path for selecting anything from dual, and uses a pre-pared path.

    c) You're using a tool that keeps making new connections so that the 'alter session' is executed in a different session from the select, or you're running the 'select from v$process' in a separate session from the alter session/select from dual

    To get an optimizer trace file generated you need to set the event and then execute in the same session a statement that hasn't previously been optimized.


    A couple of thoughts about the error:

    1) If the structures of the views are identical then it's easy to argue that there's a defect in the optimizer that allows one to produce the error while the other survive.

    2) For the structure to be identical there are some details that the human eye might overlook - in particular there might be a difference in "NOT NULL" or "UNIQUE" declarations on join columns or even on columns with literal filter predicates. There may be type mismatches between columns in the WHERE clauses in one view that don't appear in the other.

    3) It is possible for the problem to be related to the actual values of bind variables (or literals) used. A value that produces a small cardinality estimate while optimising one view may mean that a particular transformation is not considered and an entire branch of transformations is bypassed in one case, while a larger cardinality estimate may push the optimizer into following a transformation that eventually leads to this problem.

    Checking (2) should be relatively easy; checking (3) can take a lot of work and relies on confidence in reading and using execution plans and may necessitate looking at a CBO (10053) trace file.


    Regards

    Jonathan Lewis

Sign In or Register to comment.