This discussion is archived
13 Replies Latest reply: Feb 28, 2013 3:34 AM by 940856 RSS

Recursive sql spending most of the time

940856 Newbie
Currently Being Moderated
Hi All,

One of our client reported the performance issue during their data load job. I extracted the AWR and found one of the sql , which spending majority of time happens to be a recursive sql. It was strange to see that AWR did not populate the execution and elap per exec column. I also tried to fetch them from cursor and AWR , but oracle did not return anything. What do you think , what should be my approach to tune this sql. Thanks a lot in advance.
DB Version :- 11.1.0.6.0.
HOST --- HPUX itanium

SELECT UD2 , UD2X , UD2R , UD2F , ChangeSign , tWCLSADM54369324637."ROWID" From tWCLSADM54369324637 Where (((UD2) Like :"SYS_B_000") AND ((CalcAcctType)>=:"SYS_B_001") AND ((UD2F)<>:"SYS_B_002") AND ((DataKey) IN(:"SYS_B_003", :"SYS_B_004", :"SYS_B_005", :"SYS_B_006", :"SYS_B_007", :"SYS_B_008", :"SYS_B_009", :"SYS_B_010", :"SYS_B_011", :"SYS_B_012", :"SYS_B_013", :"SYS_B_014", :"SYS_B_015", :"SYS_B_016", :"SYS_B_017", :"SYS_B_018", :"SYS_B_019", :"SYS_B_020", :"SYS_B_021", :"SYS_B_022", :"SYS_B_023", :"SYS_B_024", :"SYS_B_025", :"SYS_B_026", :"SYS_B_027", :"SYS_B_028", :"SYS_B_029", :"SYS_B_030", :"SYS_B_031", :"SYS_B_032", :"SYS_B_033", :"SYS_B_034", :"SYS_B_035", :"SYS_B_036", :"SYS_B_037", :"SYS_B_038", :"SYS_B_039", :"SYS_B_040", :"SYS_B_041", :"SYS_B_042", :"SYS_B_043", :"SYS_B_044", :"SYS_B_045", :"SYS_B_046", :"SYS_B_047", :"SYS_B_048", :"SYS_B_049", :"SYS_B_050", :"SYS_B_051", :"SYS_B_052", :"SYS_B_053", :"SYS_B_054", :"SYS_B_055", :"SYS_B_056", :"SYS_B_057", :"SYS_B_058", :"SYS_B_059", :"SYS_B_060", :"SYS_B_061", :"SYS_B_062", :"SYS_B_063", :"SYS_B_064", :"SYS_B_065", :"SYS_B_066", :"SYS_B_067", :"SYS_B_068", :"SYS_B_069", :"SYS_B_070", :"SYS_B_071", :"SYS_B_072", :"SYS_B_073", :"SYS_B_074", :"SYS_B_075", :"SYS_B_076", :"SYS_B_077", :"SYS_B_078", :"SYS_B_079", :"SYS_B_080", :"SYS_B_081", :"SYS_B_082", :"SYS_B_083", :"SYS_B_084", :"SYS_B_085", :"SYS_B_086", :"SYS_B_087 ", :"SYS_B_088", :"SYS_B_089", :"SYS_B_090", :"SYS_B_091", :"SYS_B_092", :"SYS_B_093", :"SYS_B_094", :"SYS_B_095", :"SYS_B_096", :"SYS_B_097", :"SYS_B_098", :"SYS_B_099", :"SYS_B_100", :"SYS_B_101", :"SYS_B_102"))) 

Elapsed Time (s) CPU Time (s) Executions  Elap per Exec (s)  % Total DB Time SQL Id SQL Module SQL Text 
1,176 1,066     51.67 6qvhh9ha088rw    SELECT UD2 , UD2X , UD2R , ... 
128 92 1 128.12 5.63 fs0zwwndczzcg    BEGIN SYS.KUPW$WORKER.MAIN... 
128 91 1 127.71 5.61 bcdzgq1wzp83s Data Pump Worker  BEGIN SYS.KUPW$WORKER.DISPATCH... 
99 49 1 99.49 4.37 9ayqnmcmu4w3p upsAppSv.exe  UPDATE tWCLSADM54369324637 SET... 
97 94 9 10.77 4.26 7kuk1y6y170gc upsAppSv.exe  SELECT COUNT(*) AS RECORDCOUNT... 
92 69 1 92.47 4.06 g8c3asawyxxvv upsAppSv.exe  INSERT /*+ Append */ INTO tDat... 
90 31 1 89.52 3.93 55nhcjudyydd6 upsAppSv.exe  DECLARE CURSOR cur_DataMap I... 
88 30 6 14.75 3.89 f226ad0h1m66n upsAppSv.exe  UPDATE TWCLSADM54369324637 SET... 
86 85 8 10.79 3.79 fhzynt55gt3as upsAppSv.exe  SELECT * FROM (SELECT ROWNUM R... 
61 21 1 60.69 2.67 b2dcw0mxbkkcd upsAppSv.exe  UPDATE tWCLSADM54369324637 SET... 
Regards
  • 1. Re: Recursive sql spending most of the time
    damorgan Oracle ACE Director
    Currently Being Moderated
    I start with the basics.

    1. Why is the client on a totally unpatched version of the database so old it is in desupport mode?
    2. Where is the SQL?
    3. Where is the Explain Plan for the SQL?
    4. Define "performance issue." What was it before? What is it now?
    5. What has changed between the last known good performance and the first known poor performance?
  • 2. Re: Recursive sql spending most of the time
    940856 Newbie
    Currently Being Moderated
    Thanks a lot for your inputs. In addition of this , why the Executions and CPU per Exec (s) are blank in AWR report in sql ordered by elapsed time section.
    Why is the client on a totally unpatched version of the database so old it is in desupport mode?
    Yes, we are already into sustainable support. but client has not itention to upgrade it.
    Where is the SQL?
    SELECT UD2 , UD2X , UD2R , UD2F , ChangeSign , tWCLSADM54369324637."ROWID" From tWCLSADM54369324637
    Where (((UD2) Like :"SYS_B_000")
    AND ((CalcAcctType)>=:"SYS_B_001")
    AND ((UD2F)<>:"SYS_B_002")
    AND ((DataKey)
    IN(:"SYS_B_003", :"SYS_B_004", :"SYS_B_005", :"SYS_B_006", :"SYS_B_007", :"SYS_B_008", :"SYS_B_009", :"SYS_B_010", :"SYS_B_011", :"SYS_B_012", :"SYS_B_013", :"SYS_B_014", :"SYS_B_015", :"SYS_B_016", :"SYS_B_017", :"SYS_B_018", :"SYS_B_019", :"SYS_B_020", :"SYS_B_021", :"SYS_B_022", :"SYS_B_023", :"SYS_B_024", :"SYS_B_025", :"SYS_B_026", :"SYS_B_027", :"SYS_B_028", :"SYS_B_029", :"SYS_B_030", :"SYS_B_031", :"SYS_B_032", :"SYS_B_033", :"SYS_B_034", :"SYS_B_035", :"SYS_B_036", :"SYS_B_037", :"SYS_B_038", :"SYS_B_039", :"SYS_B_040", :"SYS_B_041", :"SYS_B_042", :"SYS_B_043", :"SYS_B_044", :"SYS_B_045", :"SYS_B_046", :"SYS_B_047", :"SYS_B_048", :"SYS_B_049", :"SYS_B_050", :"SYS_B_051", :"SYS_B_052", :"SYS_B_053", :"SYS_B_054", :"SYS_B_055", :"SYS_B_056", :"SYS_B_057", :"SYS_B_058", :"SYS_B_059", :"SYS_B_060", :"SYS_B_061", :"SYS_B_062", :"SYS_B_063", :"SYS_B_064", :"SYS_B_065", :"SYS_B_066", :"SYS_B_067", :"SYS_B_068", :"SYS_B_069", :"SYS_B_070", :"SYS_B_071", :"SYS_B_072", :"SYS_B_073", :"SYS_B_074", :"SYS_B_075", :"SYS_B_076", :"SYS_B_077", :"SYS_B_078", :"SYS_B_079", :"SYS_B_080", :"SYS_B_081", :"SYS_B_082", :"SYS_B_083", :"SYS_B_084", :"SYS_B_085", :"SYS_B_086", :"SYS_B_087 ", :"SYS_B_088", :"SYS_B_089", :"SYS_B_090", :"SYS_B_091", :"SYS_B_092", :"SYS_B_093", :"SYS_B_094", :"SYS_B_095", :"SYS_B_096", :"SYS_B_097", :"SYS_B_098", :"SYS_B_099", :"SYS_B_100", :"SYS_B_101", :"SYS_B_102")))

    Where is the Explain Plan for the SQL?
    Note
    -----
    - dynamic sampling used for this statement

    SQL_ID 6qvhh9ha088rw, child number 0
    -------------------------------------
    An uncaught error happened in prepare_sql_statement : ORA-01422: exact fetch returns more than requested number of rows

    Plan hash value: 64458001

    -----------------------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    -----------------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | | | 6251 (100)| |
    |* 1 | TABLE ACCESS FULL| TWCLSADM54369324637 | 1 | 305 | 6251 (1)| 00:01:16 |
    | 0 | SELECT STATEMENT | | | | 6143 (100)| |
    |* 1 | TABLE ACCESS FULL| TWCLSADM54369324637 | 1 | 305 | 6143 (1)| 00:01:14 |
    | 0 | SELECT STATEMENT | | | | 6047 (100)| |
    |* 1 | TABLE ACCESS FULL| TWCLSADM54369324637 | 1 | 305 | 6047 (1)| 00:01:13 |
    | 0 | SELECT STATEMENT | | | | 6251 (100)| |
    |* 1 | TABLE ACCESS FULL| TWCLSADM54369324637 | 1 | 305 | 6251 (1)| 00:01:16 |
    | 0 | SELECT STATEMENT | | | | 6143 (100)| |
    |* 1 | TABLE ACCESS FULL| TWCLSADM54369324637 | 1 | 305 | 6143 (1)| 00:01:14 |
    | 0 | SELECT STATEMENT | | | | 6047 (100)| |
    |* 1 | TABLE ACCESS FULL| TWCLSADM54369324637 | 1 | 305 | 6047 (1)| 00:01:13 |
    | 0 | SELECT STATEMENT | | | | 6251 (100)| |
    |* 1 | TABLE ACCESS FULL| TWCLSADM54369324637 | 1 | 305 | 6251 (1)| 00:01:16 |
    | 0 | SELECT STATEMENT | | | | 6143 (100)| |
    |* 1 | TABLE ACCESS FULL| TWCLSADM54369324637 | 1 | 305 | 6143 (1)| 00:01:14 |
    | 0 | SELECT STATEMENT | | | | 6047 (100)| |
    |* 1 | TABLE ACCESS FULL| TWCLSADM54369324637 | 1 | 305 | 6047 (1)| 00:01:13 |
    -----------------------------------------------------------------------------------------

    Predicate Information (identified by operation id):
    ---------------------------------------------------

    1 - filter((INTERNAL_FUNCTION("DATAKEY") AND "CALCACCTTYPE">=:SYS_B_001 AND
    "UD2F"<>:SYS_B_002 AND "UD2" LIKE :SYS_B_000))
    1 - filter((INTERNAL_FUNCTION("DATAKEY") AND "CALCACCTTYPE">=:SYS_B_001 AND
    "UD2F"<>:SYS_B_002 AND "UD2" LIKE :SYS_B_000))
    1 - filter((INTERNAL_FUNCTION("DATAKEY") AND "CALCACCTTYPE">=:SYS_B_001 AND
    "UD2F"<>:SYS_B_002 AND "UD2" LIKE :SYS_B_000))
    1 - filter((INTERNAL_FUNCTION("DATAKEY") AND "CALCACCTTYPE">=:SYS_B_001 AND
    "UD2F"<>:SYS_B_002 AND "UD2" LIKE :SYS_B_000))
    1 - filter((INTERNAL_FUNCTION("DATAKEY") AND "CALCACCTTYPE">=:SYS_B_001 AND
    "UD2F"<>:SYS_B_002 AND "UD2" LIKE :SYS_B_000))
    1 - filter((INTERNAL_FUNCTION("DATAKEY") AND "CALCACCTTYPE">=:SYS_B_001 AND
    "UD2F"<>:SYS_B_002 AND "UD2" LIKE :SYS_B_000))
    1 - filter((INTERNAL_FUNCTION("DATAKEY") AND "CALCACCTTYPE">=:SYS_B_001 AND
    "UD2F"<>:SYS_B_002 AND "UD2" LIKE :SYS_B_000))
    1 - filter((INTERNAL_FUNCTION("DATAKEY") AND "CALCACCTTYPE">=:SYS_B_001 AND
    "UD2F"<>:SYS_B_002 AND "UD2" LIKE :SYS_B_000))
    1 - filter((INTERNAL_FUNCTION("DATAKEY") AND "CALCACCTTYPE">=:SYS_B_001 AND
    "UD2F"<>:SYS_B_002 AND "UD2" LIKE :SYS_B_000))
    Note
    -----
       - dynamic sampling used for this statement
    
    SQL_ID  6qvhh9ha088rw, child number 0
    -------------------------------------
    An uncaught error happened in prepare_sql_statement : ORA-01422: exact fetch returns more than requested number of rows
    
    Plan hash value: 64458001
    
    -----------------------------------------------------------------------------------------
    | Id  | Operation         | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
    -----------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |                     |       |       |  6251 (100)|          |
    |*  1 |  TABLE ACCESS FULL| TWCLSADM54369324637 |     1 |   305 |  6251   (1)| 00:01:16 |
    |   0 | SELECT STATEMENT  |                     |       |       |  6143 (100)|          |
    |*  1 |  TABLE ACCESS FULL| TWCLSADM54369324637 |     1 |   305 |  6143   (1)| 00:01:14 |
    |   0 | SELECT STATEMENT  |                     |       |       |  6047 (100)|          |
    |*  1 |  TABLE ACCESS FULL| TWCLSADM54369324637 |     1 |   305 |  6047   (1)| 00:01:13 |
    |   0 | SELECT STATEMENT  |                     |       |       |  6251 (100)|          |
    |*  1 |  TABLE ACCESS FULL| TWCLSADM54369324637 |     1 |   305 |  6251   (1)| 00:01:16 |
    |   0 | SELECT STATEMENT  |                     |       |       |  6143 (100)|          |
    |*  1 |  TABLE ACCESS FULL| TWCLSADM54369324637 |     1 |   305 |  6143   (1)| 00:01:14 |
    |   0 | SELECT STATEMENT  |                     |       |       |  6047 (100)|          |
    |*  1 |  TABLE ACCESS FULL| TWCLSADM54369324637 |     1 |   305 |  6047   (1)| 00:01:13 |
    |   0 | SELECT STATEMENT  |                     |       |       |  6251 (100)|          |
    |*  1 |  TABLE ACCESS FULL| TWCLSADM54369324637 |     1 |   305 |  6251   (1)| 00:01:16 |
    |   0 | SELECT STATEMENT  |                     |       |       |  6143 (100)|          |
    |*  1 |  TABLE ACCESS FULL| TWCLSADM54369324637 |     1 |   305 |  6143   (1)| 00:01:14 |
    |   0 | SELECT STATEMENT  |                     |       |       |  6047 (100)|          |
    |*  1 |  TABLE ACCESS FULL| TWCLSADM54369324637 |     1 |   305 |  6047   (1)| 00:01:13 |
    -----------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - filter((INTERNAL_FUNCTION("DATAKEY") AND "CALCACCTTYPE">=:SYS_B_001 AND
                  "UD2F"<>:SYS_B_002 AND "UD2" LIKE :SYS_B_000))
       1 - filter((INTERNAL_FUNCTION("DATAKEY") AND "CALCACCTTYPE">=:SYS_B_001 AND
                  "UD2F"<>:SYS_B_002 AND "UD2" LIKE :SYS_B_000))
       1 - filter((INTERNAL_FUNCTION("DATAKEY") AND "CALCACCTTYPE">=:SYS_B_001 AND
                  "UD2F"<>:SYS_B_002 AND "UD2" LIKE :SYS_B_000))
       1 - filter((INTERNAL_FUNCTION("DATAKEY") AND "CALCACCTTYPE">=:SYS_B_001 AND
                  "UD2F"<>:SYS_B_002 AND "UD2" LIKE :SYS_B_000))
       1 - filter((INTERNAL_FUNCTION("DATAKEY") AND "CALCACCTTYPE">=:SYS_B_001 AND
                  "UD2F"<>:SYS_B_002 AND "UD2" LIKE :SYS_B_000))
       1 - filter((INTERNAL_FUNCTION("DATAKEY") AND "CALCACCTTYPE">=:SYS_B_001 AND
                  "UD2F"<>:SYS_B_002 AND "UD2" LIKE :SYS_B_000))
       1 - filter((INTERNAL_FUNCTION("DATAKEY") AND "CALCACCTTYPE">=:SYS_B_001 AND
                  "UD2F"<>:SYS_B_002 AND "UD2" LIKE :SYS_B_000))
       1 - filter((INTERNAL_FUNCTION("DATAKEY") AND "CALCACCTTYPE">=:SYS_B_001 AND
                  "UD2F"<>:SYS_B_002 AND "UD2" LIKE :SYS_B_000))
       1 - filter((INTERNAL_FUNCTION("DATAKEY") AND "CALCACCTTYPE">=:SYS_B_001 AND
                  "UD2F"<>:SYS_B_002 AND "UD2" LIKE :SYS_B_000))
    
    Note
    -----
       - dynamic sampling used for this statement
    165 rows selected.
    Define "performance issue." What was it before? What is it now?
    Well, it's a newly build system and they see there is some performance issue , althoug I am going to ask same from them
    What has changed between the last known good performance and the first known poor performance?
    First time , I performed the analysis on this system.
  • 3. Re: Recursive sql spending most of the time
    940856 Newbie
    Currently Being Moderated
    Thanks a lot for your inputs. In addition of this , why the Executions and CPU per Exec (s) are blank in AWR report in sql ordered by elapsed time section.
    Why is the client on a totally unpatched version of the database so old it is in desupport mode?
    Yes, we are already into sustainable support. but client has not itention to upgrade it.
    Where is the SQL?
    SELECT UD2 , UD2X , UD2R , UD2F , ChangeSign , tWCLSADM54369324637."ROWID" From tWCLSADM54369324637
    Where (((UD2) Like :"SYS_B_000")
    AND ((CalcAcctType)>=:"SYS_B_001")
    AND ((UD2F)<>:"SYS_B_002")
    AND ((DataKey)
    IN(:"SYS_B_003", :"SYS_B_004", :"SYS_B_005", :"SYS_B_006", :"SYS_B_007", :"SYS_B_008", :"SYS_B_009", :"SYS_B_010", :"SYS_B_011", :"SYS_B_012", :"SYS_B_013", :"SYS_B_014", :"SYS_B_015", :"SYS_B_016", :"SYS_B_017", :"SYS_B_018", :"SYS_B_019", :"SYS_B_020", :"SYS_B_021", :"SYS_B_022", :"SYS_B_023", :"SYS_B_024", :"SYS_B_025", :"SYS_B_026", :"SYS_B_027", :"SYS_B_028", :"SYS_B_029", :"SYS_B_030", :"SYS_B_031", :"SYS_B_032", :"SYS_B_033", :"SYS_B_034", :"SYS_B_035", :"SYS_B_036", :"SYS_B_037", :"SYS_B_038", :"SYS_B_039", :"SYS_B_040", :"SYS_B_041", :"SYS_B_042", :"SYS_B_043", :"SYS_B_044", :"SYS_B_045", :"SYS_B_046", :"SYS_B_047", :"SYS_B_048", :"SYS_B_049", :"SYS_B_050", :"SYS_B_051", :"SYS_B_052", :"SYS_B_053", :"SYS_B_054", :"SYS_B_055", :"SYS_B_056", :"SYS_B_057", :"SYS_B_058", :"SYS_B_059", :"SYS_B_060", :"SYS_B_061", :"SYS_B_062", :"SYS_B_063", :"SYS_B_064", :"SYS_B_065", :"SYS_B_066", :"SYS_B_067", :"SYS_B_068", :"SYS_B_069", :"SYS_B_070", :"SYS_B_071", :"SYS_B_072", :"SYS_B_073", :"SYS_B_074", :"SYS_B_075", :"SYS_B_076", :"SYS_B_077", :"SYS_B_078", :"SYS_B_079", :"SYS_B_080", :"SYS_B_081", :"SYS_B_082", :"SYS_B_083", :"SYS_B_084", :"SYS_B_085", :"SYS_B_086", :"SYS_B_087 ", :"SYS_B_088", :"SYS_B_089", :"SYS_B_090", :"SYS_B_091", :"SYS_B_092", :"SYS_B_093", :"SYS_B_094", :"SYS_B_095", :"SYS_B_096", :"SYS_B_097", :"SYS_B_098", :"SYS_B_099", :"SYS_B_100", :"SYS_B_101", :"SYS_B_102")))

    Where is the Explain Plan for the SQL?
    Note
    -----
       - dynamic sampling used for this statement
    
    SQL_ID  6qvhh9ha088rw, child number 0
    -------------------------------------
    An uncaught error happened in prepare_sql_statement : ORA-01422: exact fetch returns more than requested number of rows
    
    Plan hash value: 64458001
    
    -----------------------------------------------------------------------------------------
    | Id  | Operation         | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
    -----------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |                     |       |       |  6251 (100)|          |
    |*  1 |  TABLE ACCESS FULL| TWCLSADM54369324637 |     1 |   305 |  6251   (1)| 00:01:16 |
    |   0 | SELECT STATEMENT  |                     |       |       |  6143 (100)|          |
    |*  1 |  TABLE ACCESS FULL| TWCLSADM54369324637 |     1 |   305 |  6143   (1)| 00:01:14 |
    |   0 | SELECT STATEMENT  |                     |       |       |  6047 (100)|          |
    |*  1 |  TABLE ACCESS FULL| TWCLSADM54369324637 |     1 |   305 |  6047   (1)| 00:01:13 |
    |   0 | SELECT STATEMENT  |                     |       |       |  6251 (100)|          |
    |*  1 |  TABLE ACCESS FULL| TWCLSADM54369324637 |     1 |   305 |  6251   (1)| 00:01:16 |
    |   0 | SELECT STATEMENT  |                     |       |       |  6143 (100)|          |
    |*  1 |  TABLE ACCESS FULL| TWCLSADM54369324637 |     1 |   305 |  6143   (1)| 00:01:14 |
    |   0 | SELECT STATEMENT  |                     |       |       |  6047 (100)|          |
    |*  1 |  TABLE ACCESS FULL| TWCLSADM54369324637 |     1 |   305 |  6047   (1)| 00:01:13 |
    |   0 | SELECT STATEMENT  |                     |       |       |  6251 (100)|          |
    |*  1 |  TABLE ACCESS FULL| TWCLSADM54369324637 |     1 |   305 |  6251   (1)| 00:01:16 |
    |   0 | SELECT STATEMENT  |                     |       |       |  6143 (100)|          |
    |*  1 |  TABLE ACCESS FULL| TWCLSADM54369324637 |     1 |   305 |  6143   (1)| 00:01:14 |
    |   0 | SELECT STATEMENT  |                     |       |       |  6047 (100)|          |
    |*  1 |  TABLE ACCESS FULL| TWCLSADM54369324637 |     1 |   305 |  6047   (1)| 00:01:13 |
    -----------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - filter((INTERNAL_FUNCTION("DATAKEY") AND "CALCACCTTYPE">=:SYS_B_001 AND
                  "UD2F"<>:SYS_B_002 AND "UD2" LIKE :SYS_B_000))
       1 - filter((INTERNAL_FUNCTION("DATAKEY") AND "CALCACCTTYPE">=:SYS_B_001 AND
                  "UD2F"<>:SYS_B_002 AND "UD2" LIKE :SYS_B_000))
       1 - filter((INTERNAL_FUNCTION("DATAKEY") AND "CALCACCTTYPE">=:SYS_B_001 AND
                  "UD2F"<>:SYS_B_002 AND "UD2" LIKE :SYS_B_000))
       1 - filter((INTERNAL_FUNCTION("DATAKEY") AND "CALCACCTTYPE">=:SYS_B_001 AND
                  "UD2F"<>:SYS_B_002 AND "UD2" LIKE :SYS_B_000))
       1 - filter((INTERNAL_FUNCTION("DATAKEY") AND "CALCACCTTYPE">=:SYS_B_001 AND
                  "UD2F"<>:SYS_B_002 AND "UD2" LIKE :SYS_B_000))
       1 - filter((INTERNAL_FUNCTION("DATAKEY") AND "CALCACCTTYPE">=:SYS_B_001 AND
                  "UD2F"<>:SYS_B_002 AND "UD2" LIKE :SYS_B_000))
       1 - filter((INTERNAL_FUNCTION("DATAKEY") AND "CALCACCTTYPE">=:SYS_B_001 AND
                  "UD2F"<>:SYS_B_002 AND "UD2" LIKE :SYS_B_000))
       1 - filter((INTERNAL_FUNCTION("DATAKEY") AND "CALCACCTTYPE">=:SYS_B_001 AND
                  "UD2F"<>:SYS_B_002 AND "UD2" LIKE :SYS_B_000))
       1 - filter((INTERNAL_FUNCTION("DATAKEY") AND "CALCACCTTYPE">=:SYS_B_001 AND
                  "UD2F"<>:SYS_B_002 AND "UD2" LIKE :SYS_B_000))
    
    Note
    -----
       - dynamic sampling used for this statement
    165 rows selected.
    Define "performance issue." What was it before? What is it now?
    Well, it's a newly build system and they see there is some performance issue , althoug I am going to ask same from them
    What has changed between the last known good performance and the first known poor performance?
    First time , I performed the analysis on this system.
  • 4. Re: Recursive sql spending most of the time
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    Executions and Elapsed Time per Exec would not be populated if the SQL was still running when the AWR snapshot was collected.

    How do you say that this SQL is a recursive SQL ? It is not on the data dictionary but on some application schema table.


    Hemant K Chitale
  • 5. Re: Recursive sql spending most of the time
    940856 Newbie
    Currently Being Moderated
    Thanks Hemant for putting up your thought here.Yes , you are right these are executed from some Application user. I was under different impression. Going forward, here, I found that this particular sql has very high version count (2570) . even though , the bind variable seem to be used here , but somehow , the same child cursor were not sharable , as per my understanding from AWR. the curosr_sharing set to similar here. I was also failed to diagnose the cause of multiple child cursors.
    I am in process of doing further analysis and will update you once I find anything on this. Also, would really appreciate , if you could offer anything on this.
    SQL_ID        SQL_TEXT                                                                                   EXECUTIONS CHILD_LATCH VERSION_COUNT PLAN_HASH_VALUE
     HASH_VALUE
    ------------- ------------------------------------------------------------------------------------------ ---------- ----------- ------------- ---------------
     ----------
    6qvhh9ha088rw SELECT UD2 , UD2X , UD2R , UD2F , ChangeSign , tWCLSADM54369324637."ROWID"  From tWCLSADM5       2570           0          2570  64458001335815420
                    4369324637 Where  (((UD2) Like :"SYS_B_000") AND ((CalcAcctType)>=:"SYS_B_001") AND ((UD2F
                  )<>:"SYS_B_002") AND ((DataKey) IN(:"SYS_B_003", :"SYS_B_004", :"SYS_B_005", :"SYS_B_006",
                   :"SYS_B_007", :"SYS_B_008", :"SYS_B_009", :"SYS_B_010", :"SYS_B_011", :"SYS_B_012", :"SYS
                  _B_013", :"SYS_B_014", :"SYS_B_015", :"SYS_B_016", :"SYS_B_017", :"SYS_B_018", :"SYS_B_019
                  ", :"SYS_B_020", :"SYS_B_021", :"SYS_B_022", :"SYS_B_023", :"SYS_B_024", :"SYS_B_025", :"S
                  YS_B_026", :"SYS_B_027", :"SYS_B_028", :"SYS_B_029", :"SYS_B_030", :"SYS_B_031", :"SYS_B_0
                  32", :"SYS_B_033", :"SYS_B_034", :"SYS_B_035", :"SYS_B_036", :"SYS_B_037", :"SYS_B_038", :
                  "SYS_B_039", :"SYS_B_040", :"SYS_B_041", :"SYS_B_042", :"SYS_B_043", :"SYS_B_044", :"SYS_B
                  _045", :"SYS_B_046", :"SYS_B_047", :"SYS_B_048", :"SYS_B_049", :"SYS_B_050", :"SYS_B_051",
                   :"SYS_B_052", :"SYS_B_053", :"SYS_B_054", :"SYS_B_055", :"SYS_B_056", :"SYS_B_057", :"SYS
                  _B_058", :
    
    /
    
    
    NAME                                 TYPE                             VALUE
    ------------------------------------ -------------------------------- --------------
    _optimizer_adaptive_cursor_sharing   boolean                          TRUE
    _optimizer_extended_cursor_sharing   string                           UDO
    _optimizer_extended_cursor_sharing_r string                           SIMPLE
    el
    cursor_sharing                       string                           similar
    cursor_space_for_time                boolean                          FALSE
    open_cursors                         integer                          5000
    session_cached_cursors               integer                          50
  • 6. Re: Recursive sql spending most of the time
    940856 Newbie
    Currently Being Moderated
    Anyone had any clue on this , as I am yet to identify the reason of too many cursors created.
  • 7. Re: Recursive sql spending most of the time
    sb92075 Guru
    Currently Being Moderated
    937853 wrote:
    Anyone had any clue on this , as I am yet to identify the reason of too many cursors created.
    EXPLAIN PLAN reports that table has only 1 row.
    How many rows actually exist in table?

    Does every column in WHERE clause have an INDEX?

    Are statistics current for table & all indexes?


    HOW To Make TUNING request
    SQL and PL/SQL FAQ
  • 8. Re: Recursive sql spending most of the time
    Dom Brooks Guru
    Currently Being Moderated
    as I am yet to identify the reason of too many cursors created.
    See V$SQL_SHARED_CURSOR.
    In the absence of any additional information, my initial guess is the BIND_MISMATCH reason.

    Or it could just be the SIMILAR setting of CURSOR_SHARING:
    https://blogs.oracle.com/optimizer/entry/why_are_there_more_cursors_in_11g_for_my_query_containing_bind_variables_1
    TWCLSADM54369324637
    Snappy table names...

    According to the dodgy execution plan output, it seems to be doing a FULL TABLE SCAN.
    What execution plan are you expecting?
    What indexes on which columns do you have?

    Bind variables can cause problems in themselves.
    Consider this:
    (UD2) Like :"SYS_B_000")
    The optimizer does not know whether any bind might contain wildcards, leading wildcard, trailing wildcards.
    LIKE can often be implemented more efficiently using an Oracle Text index. Depends.

    More information required.

    Useful starting points:
    [url https://forums.oracle.com/forums/thread.jspa?threadID=863295]How to post a sql tuning request
    [url https://forums.oracle.com/forums/thread.jspa?messageID=1812597]When your query takes too long

    Edited by: Dom Brooks on Feb 27, 2013 6:00 PM
  • 9. Re: Recursive sql spending most of the time
    940856 Newbie
    Currently Being Moderated
    Thanks Brooks for detailed explanation . I tried to find out the reason , but it seems , oracle seems to flushes out the data. However, I found a similar sql , which also has high version count. but again , the bind mismatch only flagged as 'NO'. One more thing , i like to ask here that the sql reported by me does not show any data under the execution and elap per second column in sql ordered by elapsed time section. As pointed out by Hemant , the sql is still running , does this mean , the first execution of this sql is still running and if that is the case , how come too many execution , as reported by v$sqlarea. Please clarify this , as I am clueless here.

    v$sql_shared_cursor for previous sql_id does not has any rows.
    
    SELECT child_number, distinct(bind_mismatch)
    FROM v$sql_shared_cursor
    WHERE sql_id = '83g47jzwjpz0d' 
    ;
    
    one more sql similar to previous one .
    
    
    select sql_id, sql_text, executions, child_latch, version_count, plan_hash_value, hash_value
    from v$sqlarea where sql_is='83g47jzwjpz0d'
    /
    
    SQL_ID        SQL_TEXT                                           EXECUTIONS CHILD_LATCH VERSION_COUNT PLAN_HASH_VALUE HASH_VALUE
    ------------- -------------------------------------------------- ---------- ----------- ------------- --------------- ----------
    83g47jzwjpz0d SELECT UD2 , UD2X , UD2R , UD2F , ChangeSign , tWC       2586           0          2586      1613784057 4179295245
                  LSADM40043792113."ROWID"  From tWCLSADM40043792113
                   Where  (((UD2) Like :"SYS_B_000") AND ((CalcAcctT
                  ype)>=:"SYS_B_001") AND ((UD2F)<>:"SYS_B_002") AND
                   ((DataKey) IN(:"SYS_B_003", :"SYS_B_004", :"SYS_B
                  _005", :"SYS_B_006", :"SYS_B_007", :"SYS_B_008", :
                  "SYS_B_009", :"SYS_B_010", :"SYS_B_011", :"SYS_B_0
                  12", :"SYS_B_013", :"SYS_B_014", :"SYS_B_015", :"S
                  YS_B_016", :"SYS_B_017", :"SYS_B_018", :"SYS_B_019
                  ", :"SYS_B_020", :"SYS_B_021", :"SYS_B_022", :"SYS
                  _B_023", :"SYS_B_024", :"SYS_B_025", :"SYS_B_026",
                   :"SYS_B_027", :"SYS_B_028", :"SYS_B_029", :"SYS_B
                  _030", :"SYS_B_031", :"SYS_B_032", :"SYS_B_033", :
                  "SYS_B_034", :"SYS_B_035", :"SYS_B_036", :"SYS_B_0
                  37", :"SYS_B_038", :"SYS_B_039", :"SYS_B_040", :"S
                  YS_B_041", :"SYS_B_042", :"SYS_B_043", :"SYS_B_044
                  ", :"SYS_B_045", :"SYS_B_046", :"SYS_B_047", :"SYS
                  _B_048", :"SYS_B_049", :"SYS_B_050", :"SYS_B_051",
                   :"SYS_B_052", :"SYS_B_053", :"SYS_B_054", :"SYS_B
                  _055", :"SYS_B_056", :"SYS_B_057", :"SYS_B_058", :
    
                                                                                                        
    SQL> select distinct(bind_mismatch) from v$sql_shared_cursor where sql_id='83g47jzwjpz0d';          
                                                                                                        
    B                                                                                                   
    -                                                                                                   
    N
    
    -------------------- for better readability , partial output out of 2586 rows---------------------------------------
    SQL> SELECT child_number, is_bind_sensitive, is_bind_aware, is_shareable
    FROM v$sql
    WHERE sql_id = '83g47jzwjpz0d'
    ORDER BY child_number;  2    3    4
    
    CHILD_NUMBER I I I
    ------------ - - -
               0 N N Y
               0 N N Y
               0 N N Y
               1 N N Y
               1 N N Y
               1 N N Y
               2 N N Y
               2 N N Y
               2 N N Y
               3 N N Y
               3 N N Y
               3 N N Y
               4 N N Y
               4 N N Y
                                                                                                       
                                                                                                        
  • 10. Re: Recursive sql spending most of the time
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    Is there a particular reason ("highly recommended by application vendor" , "mandatory by application vendor", "tuned by previous DBA", "set by previous DBA because he thought it would help") why you have CURSOR_SHARING='SIMILAR' ?

    There's a note in the 11gR2 Performance Tuning Guide which says :
    Starting with Oracle Database 11g Release 2, setting the value of the CURSOR_SHARING to SIMILAR is obsolete. Consider using adaptive cursor sharing instead.
    SIMILAR had already been deprecated.

    You do seem to have adaptive cursor sharing.

    With EXACT, you may well have very many child cursors but adaptive cursor sharing may kick in.

    Hemant K Chitale

    PS : I see that you have
    DB Version :- 11.1.0.6.0.
    Nevertheless, I suggest that you test with CURSOR_SHARING='EXACT' in a test environment, test sessions.

    Edited by: Hemant K Chitale on Feb 28, 2013 10:31 AM
    Added PS
  • 11. Re: Recursive sql spending most of the time
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    I also suggest that you apply the latest patchset to your base 11gR1 install OR upgrade to 11gR2 11.2.0.3

    Hemant K Chitale
  • 12. Re: Recursive sql spending most of the time
    Rob_J Journeyer
    Currently Being Moderated
    This may have been pointed out already but it says dynamic sampling used on this statement. Does that not mean you have no stats on the table/indexes? Have you checked that first? I think SB pointed out about stats being up-to-date.
  • 13. Re: Recursive sql spending most of the time
    940856 Newbie
    Currently Being Moderated
    Thanks a ton Hemant. Yes, indeed you are right here , I was also suspecting the same. Since adaptive cursor will make use other possibilities in order to arrive on efficient plan and in order to get this , it tend to create too many child cursors for different values passes at run time. Am I right ? Also, My question still UN-answered
    to me that since AWR says , that it yett not finished ,does this mean , the first execution of this still underway ? if that is the case then how come too many execution showed by v$sqlarea. I'll also try to get this sql run and see , how it's behaving. Many thanks to all once again.

    Regards

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points