5 Replies Latest reply: Dec 19, 2013 3:23 PM by Martin Preiss RSS

    Help Needed on Query Performance

    ORA-00007

      Hi Everyone,

                    I have a new requirment for a query , I have made this query but i want to fine tune it .

      Eg

      select COLL , COL1 , COL2 , COL3  (

       

      SELECT 'AAA'  COLL ,COL1 A , SOME_FN(COL2) B ,COL3 CFROM TABLE_1 WHERE COL1='X'  AND ...

      UNION ALL

      SELECT 'BBB' ,  COL1, SOME_FN_2(COL2),COL3 FROM TABLE_2 WHERE COL1='Y' AND ...

      UNION ALL

      SELECT 'CCC' , COL1, SOME_FN_3(COL2),COL3 FROM TABLE_3 WHERE COL1='Z' AND ...

      UNION ALL

      SELECT 'DDD' , COL1, SOME_FN_4(COL2),COL3 FROM TABLE_4 WHERE COL1='AS  AND ...)

       

      WHERE COLL IN (parameters which is passed by the java interface );

       

      I am not using this query in pl/sql , we are using in Oracle Reports, so it cannot be manipulated by pl/sql .

      In front end they will pass the parameters which will decide the o/p of the query(like IN  AAA, BBB ...)

      regarding the performance all the query when running indivudually  is running  good and finely tuned

       

      The Problem which i face here is

      1. If I select like AAA and BBB from the interface all the 4 tables  will be executed and the o/p is filtered from that inline view ,

      so it is double the time of processing happens

       

       

      So , Please tell me Is there any other work around to make it happen , so that the selected query only will run from the backend ,

      and i want it in the single query .

      Note : = All the 4 tables are different and we cannot make joins on them

        • 1. Re: Help Needed on Query Performance
          ORA-00007

          Sorry not from the back end , Just the selected  in the interface to run .

          • 2. Re: Help Needed on Query Performance
            Martin Preiss

            which version are you using?

             

            I am not sure how reliable the plan information is in this case. Here is a small test with autotrace:

             

            -- 11.2.0.1

            drop table t1;

            drop table t2;

            drop table t3;

             

            create table t1

            as

            select rownum col1

              from dual

            connect by level <= 1000;

             

            create table t2

            as

            select rownum col1

              from dual

            connect by level <= 1000;

             

            create table t3

            as

            select rownum col1

              from dual

            connect by level <= 1000;

             

            -- without a condition

            select coll, col1

              from (select 'AAA' coll, col1 from t1

                      union all

                    select 'BBB' coll, col1 from t2

                      union all

                    select 'CCC' coll, col1 from t3

                   );

             

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

            | Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

            |   0 | SELECT STATEMENT    |      |  3000 | 54000 |     6   (0)| 00:00:01 |

            |   1 |  VIEW               |      |  3000 | 54000 |     6   (0)| 00:00:01 |

            |   2 |   UNION-ALL         |      |       |       |            |          |

            |   3 |    TABLE ACCESS FULL| T1   |  1000 |  4000 |     2   (0)| 00:00:01 |

            |   4 |    TABLE ACCESS FULL| T2   |  1000 |  4000 |     2   (0)| 00:00:01 |

            |   5 |    TABLE ACCESS FULL| T3   |  1000 |  4000 |     2   (0)| 00:00:01 |

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

             

            Statistiken

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

                      0  recursive calls

                      0  db block gets

                    212  consistent gets

                      0  physical reads

                      0  redo size

                  63739  bytes sent via SQL*Net to client

                   2689  bytes received via SQL*Net from client

                    201  SQL*Net roundtrips to/from client

                      0  sorts (memory)

                      0  sorts (disk)

                   3000  rows processed

             

            -- with literal in condition

            select coll, col1

              from (select 'AAA' coll, col1 from t1

                      union all

                    select 'BBB' coll, col1 from t2

                      union all

                    select 'CCC' coll, col1 from t3

                   )

            where coll = 'AAA';

             

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

            | Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

            |   0 | SELECT STATEMENT     |      |  1002 | 18036 |     2   (0)| 00:00:01 |

            |   1 |  VIEW                |      |  1002 | 18036 |     2   (0)| 00:00:01 |

            |   2 |   UNION-ALL          |      |       |       |            |          |

            |   3 |    TABLE ACCESS FULL | T1   |  1000 |  4000 |     2   (0)| 00:00:01 |

            |*  4 |    FILTER            |      |       |       |            |          |

            |   5 |     TABLE ACCESS FULL| T2   |  1000 |  4000 |     2   (0)| 00:00:01 |

            |*  6 |    FILTER            |      |       |       |            |          |

            |   7 |     TABLE ACCESS FULL| T3   |  1000 |  4000 |     2   (0)| 00:00:01 |

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

             

            Predicate Information (identified by operation id):

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

               4 - filter(NULL IS NOT NULL)

               6 - filter(NULL IS NOT NULL)

             

            Statistiken

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

                      0  recursive calls

                      0  db block gets

                     71  consistent gets

                      0  physical reads

                      0  redo size

                  21610  bytes sent via SQL*Net to client

                   1226  bytes received via SQL*Net from client

                     68  SQL*Net roundtrips to/from client

                      0  sorts (memory)

                      0  sorts (disk)

                   1000  rows processed

             

            -- with bind value in condition

            var test varchar2(30);

            exec :test := 'AAA';

             

            select coll, col1

              from (select 'AAA' coll, col1 from t1

                      union all

                    select 'BBB' coll, col1 from t2

                      union all

                    select 'CCC' coll, col1 from t3

                   )

            where coll = :test;

             

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

            | Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

            |   0 | SELECT STATEMENT     |      |  3000 | 54000 |     6   (0)| 00:00:01 |

            |   1 |  VIEW                |      |  3000 | 54000 |     6   (0)| 00:00:01 |

            |   2 |   UNION-ALL          |      |       |       |            |          |

            |*  3 |    FILTER            |      |       |       |            |          |

            |   4 |     TABLE ACCESS FULL| T1   |  1000 |  4000 |     2   (0)| 00:00:01 |

            |*  5 |    FILTER            |      |       |       |            |          |

            |   6 |     TABLE ACCESS FULL| T2   |  1000 |  4000 |     2   (0)| 00:00:01 |

            |*  7 |    FILTER            |      |       |       |            |          |

            |   8 |     TABLE ACCESS FULL| T3   |  1000 |  4000 |     2   (0)| 00:00:01 |

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

             

            Predicate Information (identified by operation id):

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

               3 - filter('AAA'=:TEST)

               5 - filter('BBB'=:TEST)

               7 - filter('CCC'=:TEST)

             

            Statistiken

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

                      1  recursive calls

                      0  db block gets

                     71  consistent gets

                      0  physical reads

                      0  redo size

                  21610  bytes sent via SQL*Net to client

                   1226  bytes received via SQL*Net from client

                     68  SQL*Net roundtrips to/from client

                      0  sorts (memory)

                      0  sorts (disk)

                   1000  rows processed

             

            With the literal in the where clause the optimizer is able to add additional filter predicates "filter(NULL IS NOT NULL)" and to avoid the table access completely. With the bind variable in the where clause the plan shows only simple filter predicates "filter('AAA'=:TEST)" - and so it seems that the full table scan takes place. But when I look at the consistent gets value I see 71 for the two restricted queries while the query without a where clause uses 212 consistent gets. So I think that the runtime engine is able to avoid the unneccessary scans even though the plan does not include this information.

             

            I hope someone corrects me if I am wrong ...

            • 3. Re: Help Needed on Query Performance
              Randolf Geist

              Hi Martin,

               

              I think you're right - the FILTER operation is always checked first if it can be fully evaluated without having to refer to the child operations of the FILTER - and in case of the expression :TEST = <some value> this is possible - so the underlying operations won't be started if the expression can be evaluated to FALSE. This could be confirmed by running the same test with rowsource statistics enabled (or via Real-Time SQL Monitoring) - the corresponding skipped child operations should be shown with STARTS = 0.

               

              Randolf

              • 4. Re: Help Needed on Query Performance
                ORA-00007

                Yes Martin , you are right , this works fine.

                • 5. Re: Help Needed on Query Performance
                  Martin Preiss

                  Hi Randolf,

                   

                  thank you for explaining the behaviour of the runtime engine. And of course you are right with your prognosis of the rowsource statistics plan:

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

                  | Id  | Operation            | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |

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

                  |   0 | SELECT STATEMENT     |      |      1 |        |   1000 |00:00:00.01 |      71 |

                  |   1 |  VIEW                |      |      1 |   3000 |   1000 |00:00:00.01 |      71 |

                  |   2 |   UNION-ALL          |      |      1 |        |   1000 |00:00:00.01 |      71 |

                  |*  3 |    FILTER            |      |      1 |        |   1000 |00:00:00.01 |      71 |

                  |   4 |     TABLE ACCESS FULL| T1   |      1 |   1000 |   1000 |00:00:00.01 |      71 |

                  |*  5 |    FILTER            |      |      1 |        |      0 |00:00:00.01 |       0 |

                  |   6 |     TABLE ACCESS FULL| T2   |      0 |   1000 |      0 |00:00:00.01 |       0 |

                  |*  7 |    FILTER            |      |      1 |        |      0 |00:00:00.01 |       0 |

                  |   8 |     TABLE ACCESS FULL| T3   |      0 |   1000 |      0 |00:00:00.01 |       0 |

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

                   

                  Predicate Information (identified by operation id):

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

                     3 - filter('AAA'=:TEST)

                     5 - filter('BBB'=:TEST)

                     7 - filter('CCC'=:TEST)

                   

                  Martin