13 Replies Latest reply: May 31, 2014 12:53 AM by Vikas0204 RSS

    Process execution take too much time

    Vikas0204

      Hello All,

       

      I have a poroblem  in process execution. it takes to much time to execute.

       

      Database 11g

      RAM 16GB

       

      This is the out put of trace file.

       

      OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

       

       

      call     count       cpu    elapsed       disk      query    current        rows

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

      Parse   284343    406.47     784.69      63412     431196        453           0

      Execute 285307  21126.20   21156.70       3826  444470399     323572       79961

      Fetch   418095    342.44     912.56     628238    2468307          4      287302

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

      total   987745  21875.12   22853.97     695476  447369902     324029      367263

       

       

      Misses in library cache during parse: 41485

      Misses in library cache during execute: 224

       

       

      41591  user  SQL statements in session.

      37201  internal SQL statements in session.

      78792  SQL statements in session.

      ********************************************************************************

      Trace file: agtriv_ora_8395.trc

      Trace file compatibility: 11.1.0.7

      Sort options: default

       

       

             1  session in tracefile.

         41591  user  SQL statements in trace file.

         37201  internal SQL statements in trace file.

         78792  SQL statements in trace file.

         40956  unique SQL statements in trace file.

      3449571  lines in trace file.

         22878  elapsed seconds in trace file.

       

       

      ===========================================================================================

       

      and an special query out put which is executed 2 times in whole process.

       

      SELECT

                  BK.FUND_ID

               FROM

                  BK_YR_BAL BK

               WHERE

                  BK.FIN_YR_FROM  =  '01-JAN-12'

                  AND BK.FIN_YR_to  ='01-JAN-13'

                            AND BK.STMT_NO  =         '12'

               GROUP BY

                  BK.FUND_ID

               HAVING SUM(BK.P_VOTED) <> 0

       

       

      call     count       cpu    elapsed       disk      query    current        rows

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

      Parse        1      0.17       0.22          9         44          0           0

      Execute      1      0.00       0.00          0          0          0           0

      Fetch        1     83.50     116.11     158792     300596          0           1

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

      total        3     83.67     116.34     158801     300640          0           1

       

       

      BK_YR_BAL is a view

      ========================

      Help me to tune this process

       

      Regards

      Vikas Sharma

        • 1. Re: Process execution take too much time
          Top.Gun

          The trace file has 40956 unique statements, so you need to identify the most expensive statements.

          • 2. Re: Process execution take too much time
            Top.Gun

            >tkprof

            Usage: tkprof tracefile outputfile [explain= ] [table= ]

                          [print= ] [insert= ] [sys= ] [sort= ]

              table=schema.tablename   Use 'schema.tablename' with 'explain=' option.

              explain=user/password    Connect to ORACLE and issue EXPLAIN PLAN.

              print=integer    List only the first 'integer' SQL statements.

              aggregate=yes|no

              insert=filename  List SQL statements and data inside INSERT statements.

              sys=no           TKPROF does not list SQL statements run as user SYS.

              record=filename  Record non-recursive statements found in the trace file.

              waits=yes|no     Record summary for any wait events found in the trace file.

              sort=option      Set of zero or more of the following sort options:

                prscnt  number of times parse was called

                prscpu  cpu time parsing

                prsela  elapsed time parsing

                prsdsk  number of disk reads during parse

                prsqry  number of buffers for consistent read during parse

                prscu   number of buffers for current read during parse

                prsmis  number of misses in library cache during parse

                execnt  number of execute was called

                execpu  cpu time spent executing

                exeela  elapsed time executing

                exedsk  number of disk reads during execute

                exeqry  number of buffers for consistent read during execute

                execu   number of buffers for current read during execute

                exerow  number of rows processed during execute

                exemis  number of library cache misses during execute

                fchcnt  number of times fetch was called

                fchcpu  cpu time spent fetching

                fchela  elapsed time fetching

                fchdsk  number of disk reads during fetch

                fchqry  number of buffers for consistent read during fetch

                fchcu   number of buffers for current read during fetch

                fchrow  number of rows fetched

                userid  userid of user that parsed the cursor

            • 3. Re: Process execution take too much time
              Vikas0204

              SELECT

                          BK.FUND_ID

                       FROM

                          BK_YR_BAL BK

                       WHERE

                          BK.FIN_YR_FROM  =  '01-JAN-12'

                          AND BK.FIN_YR_to  ='01-JAN-13'

                                    AND BK.STMT_NO  =         '12'

                       GROUP BY

                          BK.FUND_ID

                       HAVING SUM(BK.P_VOTED) <> 0

               

               

              call     count       cpu    elapsed       disk      query    current        rows

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

              Parse        1      0.17       0.22          9         44          0           0

              Execute      1      0.00       0.00          0          0          0           0

              Fetch        1     83.50     116.11     158792     300596          0           1

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

              total        3     83.67     116.34     158801     300640          0           1

               

               

              BK_YR_BAL is a view

               

              this is only query which is most expensive and this is a view

              • 4. Re: Process execution take too much time
                Top.Gun

                So to determine the most expensive SQL you need to sort the output of the tkprof:

                tkprof <tracefile> <outputfile> sort=prsela,exeela,fchela

                • 5. Re: Process execution take too much time
                  Top.Gun

                  So what is the SQL in the view?

                  What tables are in the view?

                  What index keys are against the tables in the view?

                  • 6. Re: Process execution take too much time
                    Vikas0204

                    This is the output

                     

                    Trace file: agtriv_ora_8395.trc

                    Sort options: exeela 

                    ********************************************************************************

                    count    = number of times OCI procedure was executed

                    cpu      = cpu time in seconds executing

                    elapsed  = elapsed time in seconds executing

                    disk     = number of physical reads of buffers from disk

                    query    = number of buffers gotten for consistent read

                    current  = number of buffers gotten in current mode (usually for update)

                    rows     = number of rows processed by the fetch or execute call

                    ********************************************************************************

                     

                     

                    SQL ID: 44z7snw61qx9x Plan Hash: 588296293

                     

                     

                    MERGE INTO sqlobj$auxdata                                                    

                      USING dual ON (:1 IS NULL)                                                 

                        WHEN MATCHED THEN                                                        

                            UPDATE SET description = :2,                                         

                                         creator = nvl(:3, creator),                             

                                           origin = :4,                                          

                                             version = :5,                                       

                                               created = :6,                                     

                                                 last_modified = :7,                             

                                                   last_verified = nvl(:8, last_verified),       

                                                     parse_cpu_time = null,                      

                                                       optimizer_cost = nvl(:9, optimizer_cost), 

                                                         module = nvl(:10, module),              

                                                           action = nvl(:11, action),            

                                                             priority = nvl(:12, priority),      

                                                               optimizer_env = nvl(:13,

                      optimizer_env),                                      bind_data = nvl(:14,

                      bind_data),                                             

                      parsing_schema_name = nvl(:15, parsing_schema_name),                       

                        executions = nvl(:16, executions),                                       

                          elapsed_time = nvl(:17, elapsed_time),                                 

                            cpu_time = nvl(:18, cpu_time),                                       

                              buffer_gets = nvl(:19, buffer_gets),                               

                                disk_reads = nvl(:20, disk_reads),                               

                                  direct_writes = nvl(:21, direct_writes),                       

                                    rows_processed = nvl(:22, rows_processed),                   

                                      fetches = nvl(:23, fetches),                               

                                        end_of_fetch_count = nvl(:24, end_of_fetch_count),       

                                          task_id = nvl(:25, task_id),                           

                                            task_exec_name = nvl(:26, task_exec_name),           

                                              task_obj_id = nvl(:27, task_obj_id),               

                                                task_fnd_id = nvl(:28, task_fnd_id),             

                                                  task_rec_id = nvl(:29, task_rec_id),           

                                                    flags = 0,                                   

                                                      spare1 = null,                             

                                                        spare2 = null                            

                                               WHERE signature = :30                             

                                                       AND category = :31                        

                                                         AND obj_type = :32                      

                                                           AND plan_id = :33                     

                                                     WHEN NOT MATCHED THEN                       

                                                         INSERT (signature, category, obj_type,

                      plan_id,                                       description, creator, origin,

                       version,                                        created, last_modified,

                      last_verified, parse_cpu_time,                        optimizer_cost,

                      module, action, priority,                                     optimizer_env,

                       bind_data, parsing_schema_name, executions,                   

                      elapsed_time, cpu_time, buffer_gets, disk_reads,                           

                        direct_writes, rows_processed, fetches,end_of_fetch_count,               

                          task_id, task_exec_name, task_obj_id, task_fnd_id,                     

                            task_rec_id, flags, spare1, spare2)                                  

                      VALUES (:34, :35, :36, :37,                                                

                                :38, :39, :40, :41,                                              

                                  :42, :43, null, null,                                          

                                    :44, :45, :46, :47,                                          

                                      :48, :49, :50, :51,                                        

                                        :52, :53, :54, :55,                                      

                                          :56, :57, :58, :59,                                    

                                            :60, :61, :62, :63,                                  

                                              :64, 0, null, null)

                     

                     

                     

                     

                    call     count       cpu    elapsed       disk      query    current        rows

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

                    Parse    18077      1.95       1.96          0          0          0           0

                    Execute  18077  13307.42   13324.00       2014  321339952      39145       18077

                    Fetch        0      0.00       0.00          0          0          0           0

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

                    total    36154  13309.37   13325.97       2014  321339952      39145       18077

                     

                     

                    Misses in library cache during parse: 1

                    Misses in library cache during execute: 3

                    Optimizer mode: RULE

                    Parsing user id: SYS   (recursive depth: 2)

                    Number of plan statistics captured: 18077

                     

                     

                    Rows (1st) Rows (avg) Rows (max)  Row Source Operation

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

                             0          0          0  MERGE  SQLOBJ$AUXDATA (cr=17776 pr=0 pw=0 time=23977 us)

                             1     118450     363852   VIEW  (cr=17776 pr=0 pw=0 time=213732 us)

                             1     118450     363852    NESTED LOOPS OUTER (cr=17776 pr=0 pw=0 time=90831 us cost=4981 size=9534000 card=2000)

                             1          1          1     TABLE ACCESS FULL DUAL (cr=3 pr=0 pw=0 time=49 us cost=29 size=2 card=1)

                             0     118450     363852     VIEW  (cr=17773 pr=0 pw=0 time=1160 us cost=4952 size=9530000 card=2000)

                             0     118450     363852      FILTER  (cr=17773 pr=0 pw=0 time=102022 us)

                             0     118450     363852       TABLE ACCESS FULL SQLOBJ$AUXDATA (cr=17773 pr=0 pw=0 time=8481 us cost=4952 size=9530000 card=2000)

                     

                     

                    ********************************************************************************

                     

                     

                    SQL ID: 7xm5j53mxbtpn Plan Hash: 197817870

                     

                     

                    MERGE INTO sqlobj$                                                           

                      USING dual ON (:1 IS NULL)                                                 

                        WHEN MATCHED THEN                                                        

                            UPDATE SET name = :2,                                                

                                         flags = :3,                                             

                                           last_executed = :4                                    

                                  WHERE signature = :5                                           

                                          AND category = :6                                      

                                            AND obj_type = :7                                    

                                              AND plan_id = :8                                   

                                        WHEN NOT MATCHED THEN                                    

                                            INSERT (signature, category, obj_type, plan_id, name,

                                                      flags, last_executed)                      

                                                VALUES (:9, :10, :11, :12, :13, :14, :15)

                     

                     

                     

                     

                    call     count       cpu    elapsed       disk      query    current        rows

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

                    Parse    18078      0.95       0.97          0          0          0           0

                    Execute  18078   7760.18    7765.72        411  123045993      39215       18078

                    Fetch        0      0.00       0.00          0          0          0           0

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

                    total    36156   7761.14    7766.70        411  123045993      39215       18078

                     

                     

                    Misses in library cache during parse: 1

                    Misses in library cache during execute: 2

                    Optimizer mode: RULE

                    Parsing user id: SYS   (recursive depth: 2)

                    Number of plan statistics captured: 18078

                     

                     

                    Rows (1st) Rows (avg) Rows (max)  Row Source Operation

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

                             0          0          0  MERGE  SQLOBJ$ (cr=6806 pr=0 pw=0 time=191868 us)

                             1     118463     363852   VIEW  (cr=6806 pr=0 pw=0 time=193035 us)

                             1     118463     363852    NESTED LOOPS OUTER (cr=6806 pr=0 pw=0 time=78513 us cost=38 size=824000 card=2000)

                             1          1          1     TABLE ACCESS FULL DUAL (cr=3 pr=0 pw=0 time=55 us cost=29 size=2 card=1)

                             0     118463     363852     VIEW  (cr=6803 pr=0 pw=0 time=213876 us cost=9 size=820000 card=2000)

                             0     118463     363852      FILTER  (cr=6803 pr=0 pw=0 time=85481 us)

                             0     118463     363852       INDEX FAST FULL SCAN SQLOBJ$_PKEY (cr=6803 pr=0 pw=0 time=219122 us cost=9 size=820000 card=2000)(object id 206)

                     

                     

                    ********************************************************************************

                     

                     

                    SQL ID: 9zshcv60xux34 Plan Hash: 0

                     

                     

                    INSERT INTO sql$ (signature, inuse_features, flags, spare1, spare2)          

                    VALUES

                    (:1, :2, :3, null, null)

                     

                     

                     

                     

                    call     count       cpu    elapsed       disk      query    current        rows

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

                    Parse    17847      0.28       0.29          0          0          0           0

                    Execute  17847      3.00       5.05         88      17847     108145       17847

                    Fetch        0      0.00       0.00          0          0          0           0

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

                    total    35694      3.29       5.34         88      17847     108145       17847

                     

                     

                    Misses in library cache during parse: 1

                    Misses in library cache during execute: 1

                    Optimizer mode: RULE

                    Parsing user id: SYS   (recursive depth: 2)

                    Number of plan statistics captured: 16

                     

                     

                    Rows (1st) Rows (avg) Rows (max)  Row Source Operation

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

                             0          0          0  LOAD TABLE CONVENTIONAL  (cr=1 pr=0 pw=0 time=940 us)

                     

                     

                    ********************************************************************************

                     

                     

                    SQL ID: d2whwtj1sd881 Plan Hash: 438777051

                     

                     

                    DELETE FROM sql$                                                             

                    WHERE

                    signature = :1

                     

                     

                     

                     

                    call     count       cpu    elapsed       disk      query    current        rows

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

                    Parse    17704      0.69       0.71          0          0          0           0

                    Execute  17704      4.28       4.28         14      53112      72031       17704

                    Fetch        0      0.00       0.00          0          0          0           0

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

                    total    35408      4.98       5.00         14      53112      72031       17704

                     

                     

                    Misses in library cache during parse: 1

                    Misses in library cache during execute: 1

                    Optimizer mode: RULE

                    Parsing user id: SYS   (recursive depth: 2)

                    Number of plan statistics captured: 19

                     

                     

                    Rows (1st) Rows (avg) Rows (max)  Row Source Operation

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

                             0          0          0  DELETE  SQL$ (cr=3 pr=0 pw=0 time=152 us)

                             1          1          1   INDEX UNIQUE SCAN I_SQL$_PKEY (cr=3 pr=0 pw=0 time=28 us)(object id 195)

                     

                     

                    ********************************************************************************

                     

                     

                    SQL ID: 47y3mqvyhpkvs Plan Hash: 1888265482

                     

                     

                    SELECT obj_type, plan_id, name, flags, last_executed                         

                    FROM

                    sqlobj$                                                                 

                      WHERE signature = :1                                                       

                              AND category = :2

                     

                     

                     

                     

                    call     count       cpu    elapsed       disk      query    current        rows

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

                    Parse    34234      0.61       0.60          0          0          0           0

                    Execute  34234      1.62       1.62          0          0          0           0

                    Fetch    99406      1.19       1.15          0     167876          0       65172

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

                    total   167874      3.44       3.38          0     167876          0       65172

                     

                     

                    Misses in library cache during parse: 1

                    Misses in library cache during execute: 1

                    Optimizer mode: RULE

                    Parsing user id: SYS   (recursive depth: 2)

                    Number of plan statistics captured: 5

                     

                     

                    Rows (1st) Rows (avg) Rows (max)  Row Source Operation

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

                             2          4          6  INDEX RANGE SCAN SQLOBJ$_PKEY (cr=7 pr=0 pw=0 time=26 us cost=2 size=99 card=1)(object id 206)

                     

                     

                    ********************************************************************************

                     

                     

                    SQL ID: 21m8k7puzg06s Plan Hash: 0

                     

                     

                    INSERT INTO sql$text (signature, sql_handle, sql_text)                       

                    VALUES

                    (:1, :2, :3)

                     

                     

                     

                     

                    call     count       cpu    elapsed       disk      query    current        rows

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

                    Parse      143      0.00       0.00          0          0          0           0

                    Execute    143      0.05       1.50        312        287       1527         143

                    Fetch        0      0.00       0.00          0          0          0           0

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

                    total      286      0.06       1.51        312        287       1527         143

                     

                     

                    Misses in library cache during parse: 1

                    Misses in library cache during execute: 1

                    Optimizer mode: RULE

                    Parsing user id: SYS   (recursive depth: 2)

                    Number of plan statistics captured: 13

                     

                     

                    Rows (1st) Rows (avg) Rows (max)  Row Source Operation

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

                             0          0          0  LOAD TABLE CONVENTIONAL  (cr=2 pr=3 pw=0 time=15407 us)

                     

                     

                    ********************************************************************************

                     

                     

                    SQL ID: bt51szvfrcdpn Plan Hash: 1876815449

                     

                     

                    SELECT obj_type, plan_id, comp_data                                          

                    FROM

                    sqlobj$data                                                             

                      WHERE signature = :1                                                       

                              AND category = :2

                     

                     

                     

                     

                    call     count       cpu    elapsed       disk      query    current        rows

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

                    Parse    30517      0.54       0.56          0          0          0           0

                    Execute  30517      1.19       1.19          0          0          0           0

                    Fetch    92115      3.48     178.25      20359     153153          0       61598

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

                    total   153149      5.23     180.01      20359     153153          0       61598

                     

                     

                    Misses in library cache during parse: 1

                    Misses in library cache during execute: 1

                    Optimizer mode: RULE

                    Parsing user id: SYS   (recursive depth: 2)

                    Number of plan statistics captured: 9

                     

                     

                    Rows (1st) Rows (avg) Rows (max)  Row Source Operation

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

                             2          3          6  INDEX RANGE SCAN SQLOBJ$DATA_PKEY (cr=6 pr=0 pw=0 time=1955 us cost=2 size=244 card=1)(object id 213)

                     

                     

                    ********************************************************************************

                     

                     

                    SQL ID: 69subccxd9b03 Plan Hash: 4264803741

                     

                     

                    SELECT signature, inuse_features, flags                                      

                    FROM

                    sql$                                                                    

                      WHERE signature = :1

                     

                     

                     

                     

                    call     count       cpu    elapsed       disk      query    current        rows

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

                    Parse    34201      0.59       0.61          0          0          0           0

                    Execute  34201      1.06       1.07          0          0          0           0

                    Fetch    34201      1.41      59.29       9060     136588          0       33985

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

                    total   102603      3.08      60.98       9060     136588          0       33985

                     

                     

                    Misses in library cache during parse: 2

                    Misses in library cache during execute: 2

                    Optimizer mode: RULE

                    Parsing user id: SYS   (recursive depth: 2)

                    Number of plan statistics captured: 6

                     

                     

                    Rows (1st) Rows (avg) Rows (max)  Row Source Operation

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

                             1          1          1  TABLE ACCESS BY INDEX ROWID SQL$ (cr=4 pr=0 pw=0 time=1710 us)

                             1          1          1   INDEX UNIQUE SCAN I_SQL$_PKEY (cr=3 pr=0 pw=0 time=976 us)(object id 195)

                     

                     

                    ********************************************************************************

                     

                     

                    SQL ID: 1zatw56z32vcg Plan Hash: 1469453527

                     

                     

                    SELECT obj_type, plan_id, description, creator, origin,                      

                             created, last_modified                                              

                       

                    FROM

                    sqlobj$auxdata                                                          

                      WHERE signature = :1                                                       

                              AND category = :2

                     

                     

                     

                     

                    call     count       cpu    elapsed       disk      query    current        rows

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

                    Parse    17935      0.41       0.42          0          0          0           0

                    Execute  17935      0.99       1.01          0          0          0           0

                    Fetch    52767      1.92     148.97      14418     123469          0       34832

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

                    total    88637      3.33     150.41      14418     123469          0       34832

                     

                     

                    Misses in library cache during parse: 2

                    Misses in library cache during execute: 2

                    Optimizer mode: RULE

                    Parsing user id: SYS   (recursive depth: 2)

                    Number of plan statistics captured: 55

                     

                     

                    Rows (1st) Rows (avg) Rows (max)  Row Source Operation

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

                             2          2          2  TABLE ACCESS BY INDEX ROWID SQLOBJ$AUXDATA (cr=6 pr=1 pw=0 time=5061 us)

                             2          2          2   INDEX RANGE SCAN I_SQLOBJ$AUXDATA_PKEY (cr=5 pr=1 pw=0 time=5174 us)(object id 217)

                     

                     

                    ********************************************************************************

                     

                     

                    SQL ID: f946p6b0qhh5t Plan Hash: 2181403217

                     

                     

                    SELECT /*+ INDEX(sqlobj$ (signature category obj_type plan_id)) */           

                        DISTINCT category                                                        

                       

                    FROM

                    sqlobj$                                                                 

                      WHERE signature = :1

                     

                     

                     

                     

                    call     count       cpu    elapsed       disk      query    current        rows

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

                    Parse    33986      0.59       0.63          0          0          0           0

                    Execute  33986      0.88       0.94          0          0          0           0

                    Fetch    67972      1.05       1.07          1     103464          0       33986

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

                    total   135944      2.53       2.65          1     103464          0       33986

                     

                     

                    Misses in library cache during parse: 2

                    Misses in library cache during execute: 2

                    Optimizer mode: RULE

                    Parsing user id: SYS   (recursive depth: 2)

                    Number of plan statistics captured: 11

                     

                     

                    Rows (1st) Rows (avg) Rows (max)  Row Source Operation

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

                             1          1          1  SORT UNIQUE NOSORT (cr=3 pr=0 pw=0 time=42 us cost=3 size=600 card=20)

                             2          2          6   INDEX RANGE SCAN SQLOBJ$_PKEY (cr=3 pr=0 pw=0 time=28 us cost=2 size=600 card=20)(object id 206)

                     

                     

                    ********************************************************************************

                     

                     

                    SQL ID: 1vxm21mhmgy07 Plan Hash: 3960238002

                     

                     

                    MERGE INTO sqlobj$data                                                       

                      USING dual ON (:1 IS NULL)                                                 

                        WHEN MATCHED THEN                                                        

                            UPDATE SET comp_data = :2                                            

                              WHERE signature = :3                                               

                                      AND category = :4                                          

                                        AND obj_type = :5                                        

                                          AND plan_id = :6                                       

                                    WHEN NOT MATCHED THEN                                        

                                        INSERT (signature, category, obj_type, plan_id, comp_data,

                                                  spare1, spare2)                                

                                            VALUES (:7, :8, :9, :10, :11, null, null)

                     

                     

                     

                     

                    call     count       cpu    elapsed       disk      query    current        rows

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

                    Parse      373      0.01       0.01          0          0          2           0

                    Execute    373      0.23       0.84        165       3913       5922         373

                    Fetch        0      0.00       0.00          0          0          0           0

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

                    total      746      0.24       0.86        165       3913       5924         373

                     

                     

                    Misses in library cache during parse: 2

                    Misses in library cache during execute: 2

                    Optimizer mode: RULE

                    Parsing user id: SYS   (recursive depth: 2)

                    Number of plan statistics captured: 373

                     

                     

                    Rows (1st) Rows (avg) Rows (max)  Row Source Operation

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

                             0          0          0  MERGE  SQLOBJ$DATA (cr=10 pr=0 pw=1 time=2162 us)

                             1          1          1   VIEW  (cr=3 pr=0 pw=0 time=42 us)

                             1          1          1    NESTED LOOPS OUTER (cr=3 pr=0 pw=0 time=38 us cost=38 size=1114000 card=2000)

                             1          1          1     TABLE ACCESS FULL DUAL (cr=3 pr=0 pw=0 time=30 us cost=29 size=2 card=1)

                             0          0          0     VIEW  (cr=0 pr=0 pw=0 time=4 us cost=9 size=1110000 card=2000)

                             0          0          0      FILTER  (cr=0 pr=0 pw=0 time=1 us)

                             0          0          0       INDEX FAST FULL SCAN SQLOBJ$DATA_PKEY (cr=0 pr=0 pw=0 time=0 us cost=9 size=1110000 card=2000)(object id 213)

                     

                     

                    ********************************************************************************

                     

                     

                    SQL ID: 9zr2sbbp1d4fs Plan Hash: 2462756431

                     

                     

                    SELECT /*+ INDEX(sqlobj$ (name obj_type)) */ signature, category             

                    FROM

                    sqlobj$                                                                 

                      WHERE name = :1                                                            

                              AND obj_type = :2

                     

                     

                     

                     

                    call     count       cpu    elapsed       disk      query    current        rows

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

                    Parse    18078      0.42       0.42          0          0          0           0

                    Execute  18078      0.69       0.69          0          0          0           0

                    Fetch    18078      1.26     137.34      15326      54248          0       17705

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

                    total    54234      2.38     138.46      15326      54248          0       17705

                     

                     

                    Misses in library cache during parse: 1

                    Misses in library cache during execute: 1

                    Optimizer mode: RULE

                    Parsing user id: SYS   (recursive depth: 2)

                    Number of plan statistics captured: 50

                     

                     

                    Rows (1st) Rows (avg) Rows (max)  Row Source Operation

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

                             0          0          1  INDEX RANGE SCAN I_SQLOBJ$NAME_TYPE (cr=3 pr=1 pw=0 time=5345 us cost=1 size=60 card=1)(object id 207)

                     

                     

                    ********************************************************************************

                     

                     

                    SQL ID: 0p0csu22kp7rn Plan Hash: 2804496117

                     

                     

                    TRUNCATE TABLE BK_YR_BAL_ACC

                     

                     

                     

                     

                    call     count       cpu    elapsed       disk      query    current        rows

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

                    Parse        5      0.00       0.00          0          0          0           0

                    Execute      5      0.18       0.32        257          3        757           0

                    Fetch        0      0.00       0.00          0          0          0           0

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

                    total       10      0.18       0.33        257          3        757           0

                     

                     

                    Misses in library cache during parse: 5

                    Optimizer mode: RULE

                    Parsing user id: 1107     (recursive depth: 1)

                    ********************************************************************************

                     

                     

                    UPDATE BAL_BY_YR_ABS_ACC

                                         SET NP_VOTED= NP_VOTED+ 1,

                                         STATUS_OB = 'n+1ob'

                                     WHERE

                                    FIN_YR_FROM =            '01-JAN-12'

                                        AND FIN_YR_to  =         '01-JAN-13'

                                        AND STMT_NO =                   '12'

                                   AND FUND_ID =       '1'

                                    AND FUND_CATG =     'B'

                                    AND SECT_CODE =     'C'

                                    AND NVL(SUB_SECT_CODE,0) =  NVL('F',0)

                                    AND NVL(SSUB_SECT_CODE,0) = NVL('0',0)

                                    AND MJR_HEAD =          '2851'

                                        AND NVL(SM_HEAD,0) =         NVL('00',0)

                                        AND NVL(MIN_HEAD,0) =       NVL('103',0)

                                        AND NVL(SUB_HEAD,0) =       NVL('95',0)

                                        AND NVL(BDGT_HEAD,0) =      NVL('00',0)

                                        AND NVL(DTL_HEAD,0) =       NVL('00',0)

                                        AND NVL(SOE_HEAD,0) =       NVL('00',0)

                     

                     

                    call     count       cpu    elapsed       disk      query    current        rows

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

                    Parse        1      0.00       0.00          0          0          0           0

                    Execute      1      0.00       0.25          3         10          4           1

                    Fetch        0      0.00       0.00          0          0          0           0

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

                    total        2      0.01       0.25          3         10          4           1

                     

                     

                    Misses in library cache during parse: 1

                    Misses in library cache during execute: 1

                    Optimizer mode: RULE

                    Parsing user id: 1107     (recursive depth: 1)

                    Number of plan statistics captured: 1

                     

                     

                    Rows (1st) Rows (avg) Rows (max)  Row Source Operation

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

                             0          0          0  UPDATE  BAL_BY_YR_ABS_ACC (cr=10 pr=3 pw=0 time=250046 us)

                             1          1          1   TABLE ACCESS BY INDEX ROWID BAL_BY_YR_ABS_ACC (cr=10 pr=3 pw=0 time=249935 us cost=5 size=59 card=1)

                            12         12         12    INDEX RANGE SCAN PK_BAL_BY_YR_ABS_ACC (cr=5 pr=0 pw=0 time=111 us cost=4 size=0 card=1)(object id 192011)

                     

                     

                    ********************************************************************************

                    • 7. Re: Process execution take too much time
                      Top.Gun

                      So the statement (below) you say is the biggest problem is not mentioned in the tkpro output

                      SELECT

                                  BK.FUND_ID

                               FROM

                                  BK_YR_BAL BK

                               WHERE

                                  BK.FIN_YR_FROM  =  '01-JAN-12'

                                  AND BK.FIN_YR_to  ='01-JAN-13'

                                            AND BK.STMT_NO  =         '12'

                               GROUP BY

                                  BK.FUND_ID

                               HAVING SUM(BK.P_VOTED) <> 0

                      • 8. Re: Process execution take too much time
                        Vikas0204

                        It is mentioned in tkprof

                        from line  242 to 947

                        SELECT

                                    BK.FUND_ID

                                 FROM

                                    BK_YR_BAL BK

                                 WHERE

                                    BK.FIN_YR_FROM  =  '01-JAN-12'

                                    AND BK.FIN_YR_to  ='01-JAN-13'

                                              AND BK.STMT_NO  =         '12'

                                 GROUP BY

                                    BK.FUND_ID

                                 HAVING SUM(BK.P_VOTED) <> 0

                         

                         

                        call     count       cpu    elapsed       disk      query    current        rows

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

                        Parse        1      0.17       0.22          9         44          0           0

                        Execute      1      0.00       0.00          0          0          0           0

                        Fetch        1     83.50     116.11     158792     300596          0           1

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

                        total        3     83.67     116.34     158801     300640          0           1

                         

                         

                        Misses in library cache during parse: 1

                        Optimizer mode: RULE

                        Parsing user id: 1107     (recursive depth: 1)

                        ********************************************************************************

                         

                         

                        SELECT

                                    BK.FUND_ID

                                 FROM

                                    BK_YR_BAL BK

                                 WHERE

                                    BK.FIN_YR_FROM  =  '01-JAN-12'

                                    AND BK.FIN_YR_to  ='01-JAN-13'

                                              AND BK.STMT_NO  =         '12'

                                 GROUP BY

                                    BK.FUND_ID

                                 HAVING SUM(BK.NP_CHARGED) <> 0

                         

                         

                        call     count       cpu    elapsed       disk      query    current        rows

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

                        Parse        1      0.17       0.19          8         44          0           0

                        Execute      1      0.00       0.00          0          0          0           0

                        Fetch        2     65.26      77.55     191221     300596          0           1

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

                        total        4     65.43      77.74     191229     300640          0           1

                         

                         

                        Misses in library cache during parse: 1

                        Optimizer mode: RULE

                        Parsing user id: 1107     (recursive depth: 1)

                        Number of plan statistics captured: 1

                         

                         

                        Rows (1st) Rows (avg) Rows (max)  Row Source Operation

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

                                 1          1          1  FILTER  (cr=300596 pr=191221 pw=0 time=77550319 us)

                                 1          1          1   HASH GROUP BY (cr=300596 pr=191221 pw=0 time=77550316 us cost=14900 size=5625 card=375)

                             16238      16238      16238    VIEW  BK_YR_BAL (cr=300596 pr=191221 pw=0 time=77551153 us cost=14899 size=5625 card=375)

                             16238      16238      16238     SORT UNIQUE (cr=300596 pr=191221 pw=0 time=77547954 us cost=14899 size=44087 card=375)

                             16238      16238      16238      UNION-ALL  (cr=300596 pr=191221 pw=0 time=255330 us)

                                 0          0          0       FILTER  (cr=0 pr=0 pw=0 time=51 us)

                                 0          0          0        HASH GROUP BY (cr=0 pr=0 pw=0 time=51 us cost=1 size=67 card=1)

                                 0          0          0         FILTER  (cr=0 pr=0 pw=0 time=3 us)

                                 0          0          0          HASH JOIN  (cr=0 pr=0 pw=0 time=0 us cost=53943 size=10988 card=164)

                                 0          0          0           HASH JOIN  (cr=0 pr=0 pw=0 time=0 us cost=7 size=2254 card=98)

                                 0          0          0            TABLE ACCESS FULL ME_FUND_ACC (cr=0 pr=0 pw=0 time=0 us cost=3 size=1428 card=102)

                                 0          0          0            TABLE ACCESS FULL ME_MJR_HEAD_ACC (cr=0 pr=0 pw=0 time=0 us cost=3 size=882 card=98)

                                 0          0          0           PARTITION RANGE ALL PARTITION: 1 21 (cr=0 pr=0 pw=0 time=0 us cost=53936 size=39468 card=897)

                                 0          0          0            TABLE ACCESS FULL CLSF_DATA PARTITION: 1 21 (cr=0 pr=0 pw=0 time=0 us cost=53936 size=39468 card=897)

                              3133       3133       3133       FILTER  (cr=93 pr=0 pw=0 time=227830 us)

                              3894       3894       3894        HASH GROUP BY (cr=93 pr=0 pw=0 time=226706 us cost=2910 size=28696 card=211)

                              3901       3901       3901         HASH JOIN  (cr=93 pr=0 pw=0 time=221307 us cost=2908 size=108256 card=796)

                               212        212        212          TABLE ACCESS FULL ME_MJR_HEAD_ACC (cr=6 pr=0 pw=0 time=254 us cost=3 size=1602 card=178)

                            397902     397902     397902          MERGE JOIN CARTESIAN (cr=87 pr=0 pw=0 time=244637 us cost=2903 size=27410664 card=215832)

                              3901       3901       3901           VIEW  (cr=81 pr=0 pw=0 time=26520 us cost=29 size=239108 card=2116)

                              3901       3901       3901            SORT UNIQUE (cr=81 pr=0 pw=0 time=25387 us cost=29 size=112776 card=2116)

                              3903       3903       3903             UNION-ALL  (cr=81 pr=0 pw=0 time=15353 us)

                              3530       3530       3530              HASH GROUP BY (cr=75 pr=0 pw=0 time=10946 us cost=24 size=94176 card=1744)

                              3530       3530       3530               TABLE ACCESS FULL FA_STMT_12_13 (cr=75 pr=0 pw=0 time=2820 us cost=22 size=94176 card=1744)

                               373        373        373              HASH GROUP BY (cr=6 pr=0 pw=0 time=1333 us cost=5 size=18600 card=372)

                               373        373        373               TABLE ACCESS FULL STMT_13_DTL (cr=6 pr=0 pw=0 time=285 us cost=3 size=18600 card=372)

                            397902     397902     397902           BUFFER SORT (cr=6 pr=0 pw=0 time=122646 us cost=2903 size=1428 card=102)

                               102        102        102            TABLE ACCESS FULL ME_FUND_ACC (cr=6 pr=0 pw=0 time=730 us cost=1 size=1428 card=102)

                             13105      13105      13105       FILTER  (cr=300503 pr=191221 pw=0 time=77240274 us)

                             13826      13826      13826        HASH GROUP BY (cr=300503 pr=191221 pw=0 time=77236810 us cost=11980 size=14725 card=155)

                             24035      24035      24035         HASH JOIN  (cr=300503 pr=191221 pw=0 time=77233387 us cost=11978 size=150860 card=1588)

                               212        212        212          TABLE ACCESS FULL ME_MJR_HEAD_ACC (cr=6 pr=0 pw=0 time=124 us cost=3 size=1602 card=178)

                           2451570    2451570    2451570          MERGE JOIN CARTESIAN (cr=300497 pr=191221 pw=0 time=77288374 us cost=11972 size=37044156 card=430746)

                             24035      24035      24035           VIEW  (cr=300491 pr=191221 pw=0 time=75999134 us cost=6240 size=304056 card=4223)

                             24035      24035      24035            SORT UNIQUE (cr=300491 pr=191221 pw=0 time=75991202 us cost=6240 size=226985 card=4223)

                             24035      24035      24035             UNION-ALL  (cr=300491 pr=191221 pw=0 time=75807040 us)

                             11980      11980      11980              HASH GROUP BY (cr=300387 pr=191125 pw=0 time=75782336 us cost=6201 size=21120 card=480)

                            465225     465225     465225               CONCATENATION  (cr=300387 pr=191125 pw=0 time=16964824 us)

                              6853       6853       6853                TABLE ACCESS BY GLOBAL INDEX ROWID CLSF_DATA PARTITION: ROW LOCATION ROW LOCATION (cr=92571 pr=92267 pw=0 time=16095437 us cost=3094 size=484 card=11)

                              6853       6853       6853                 INDEX SKIP SCAN CLSF_DATA_PK (cr=90714 pr=90714 pw=0 time=17927588 us cost=3093 size=0 card=2)(object id 195358)

                            458372     458372     458372                TABLE ACCESS BY GLOBAL INDEX ROWID CLSF_DATA PARTITION: ROW LOCATION ROW LOCATION (cr=207816 pr=98858 pw=0 time=40091257 us cost=3106 size=15796 card=359)

                            458372     458372     458372                 INDEX SKIP SCAN CLSF_DATA_PK (cr=158234 pr=84327 pw=0 time=46387621 us cost=3093 size=0 card=58)(object id 195358)

                             12055      12055      12055              HASH GROUP BY (cr=104 pr=96 pw=0 time=118350 us cost=37 size=205865 card=3743)

                             12064      12064      12064               VIEW  (cr=104 pr=96 pw=0 time=104870 us cost=35 size=205865 card=3743)

                             12064      12064      12064                SORT UNIQUE (cr=104 pr=96 pw=0 time=101313 us cost=35 size=172201 card=3743)

                             12064      12064      12064                 UNION-ALL  (cr=104 pr=96 pw=0 time=77805 us)

                             11691      11691      11691                  HASH GROUP BY (cr=98 pr=96 pw=0 time=69043 us cost=30 size=158437 card=3371)

                             11691      11691      11691                   TABLE ACCESS FULL BAL_BY_YR_OB_BAL (cr=98 pr=96 pw=0 time=10318 us cost=28 size=485510 card=10330)

                               373        373        373                  HASH GROUP BY (cr=6 pr=0 pw=0 time=755 us cost=5 size=13764 card=372)

                               373        373        373                   TABLE ACCESS FULL STMT_13_DTL (cr=6 pr=0 pw=0 time=527 us cost=3 size=13764 card=372)

                           2451570    2451570    2451570           BUFFER SORT (cr=6 pr=0 pw=0 time=689063 us cost=11972 size=1428 card=102)

                               102        102        102            TABLE ACCESS FULL ME_FUND_ACC (cr=6 pr=0 pw=0 time=836 us cost=1 size=1428 card=102)

                                 0          0          0       FILTER  (cr=0 pr=0 pw=0 time=44 us)

                                 0          0          0        HASH GROUP BY (cr=0 pr=0 pw=0 time=44 us cost=1 size=72 card=1)

                                 0          0          0         FILTER  (cr=0 pr=0 pw=0 time=3 us)

                                 0          0          0          HASH JOIN  (cr=0 pr=0 pw=0 time=0 us cost=3133 size=3096 card=43)

                                 0          0          0           HASH JOIN  (cr=0 pr=0 pw=0 time=0 us cost=7 size=575 card=25)

                                 0          0          0            TABLE ACCESS FULL ME_FUND_ACC (cr=0 pr=0 pw=0 time=0 us cost=3 size=70 card=5)

                                 0          0          0            TABLE ACCESS FULL ME_MJR_HEAD_ACC (cr=0 pr=0 pw=0 time=0 us cost=3 size=4257 card=473)

                                 0          0          0           TABLE ACCESS BY GLOBAL INDEX ROWID CLSF_DATA PARTITION: ROW LOCATION ROW LOCATION (cr=0 pr=0 pw=0 time=0 us cost=3126 size=43953 card=897)

                                 0          0          0            INDEX SKIP SCAN CLSF_DATA_PK (cr=0 pr=0 pw=0 time=0 us cost=3093 size=0 card=145)(object id 195358)

                                 0          0          0       FILTER  (cr=0 pr=0 pw=0 time=31 us)

                                 0          0          0        HASH GROUP BY (cr=0 pr=0 pw=0 time=31 us cost=1 size=57 card=1)

                                 0          0          0         FILTER  (cr=0 pr=0 pw=0 time=2 us)

                                 0          0          0          HASH JOIN  (cr=0 pr=0 pw=0 time=0 us cost=14 size=57 card=1)

                                 0          0          0           HASH JOIN  (cr=0 pr=0 pw=0 time=0 us cost=7 size=575 card=25)

                                 0          0          0            TABLE ACCESS FULL ME_FUND_ACC (cr=0 pr=0 pw=0 time=0 us cost=3 size=70 card=5)

                                 0          0          0            TABLE ACCESS FULL ME_MJR_HEAD_ACC (cr=0 pr=0 pw=0 time=0 us cost=3 size=4257 card=473)

                                 0          0          0           TABLE ACCESS FULL CLSF_HB_DATA1 (cr=0 pr=0 pw=0 time=0 us cost=7 size=34 card=1)

                                 0          0          0       FILTER  (cr=0 pr=0 pw=0 time=26 us)

                                 0          0          0        HASH GROUP BY (cr=0 pr=0 pw=0 time=25 us cost=1 size=70 card=1)

                                 0          0          0         FILTER  (cr=0 pr=0 pw=0 time=2 us)

                                 0          0          0          HASH JOIN  (cr=0 pr=0 pw=0 time=0 us cost=53943 size=13790 card=197)

                                 0          0          0           HASH JOIN  (cr=0 pr=0 pw=0 time=0 us cost=7 size=2691 card=117)

                                 0          0          0            TABLE ACCESS FULL ME_FUND_ACC (cr=0 pr=0 pw=0 time=0 us cost=3 size=322 card=23)

                                 0          0          0            TABLE ACCESS FULL ME_MJR_HEAD_ACC (cr=0 pr=0 pw=0 time=0 us cost=3 size=4257 card=473)

                                 0          0          0           PARTITION RANGE ALL PARTITION: 1 21 (cr=0 pr=0 pw=0 time=0 us cost=53936 size=42159 card=897)

                                 0          0          0            TABLE ACCESS FULL CLSF_DATA PARTITION: 1 21 (cr=0 pr=0 pw=0 time=0 us cost=53936 size=42159 card=897)

                                 0          0          0       FILTER  (cr=0 pr=0 pw=0 time=28 us)

                                 0          0          0        HASH GROUP BY (cr=0 pr=0 pw=0 time=26 us cost=1 size=57 card=1)

                                 0          0          0         FILTER  (cr=0 pr=0 pw=0 time=1 us)

                                 0          0          0          HASH JOIN  (cr=0 pr=0 pw=0 time=0 us cost=14 size=57 card=1)

                                 0          0          0           HASH JOIN  (cr=0 pr=0 pw=0 time=0 us cost=7 size=2691 card=117)

                                 0          0          0            TABLE ACCESS FULL ME_FUND_ACC (cr=0 pr=0 pw=0 time=0 us cost=3 size=322 card=23)

                                 0          0          0            TABLE ACCESS FULL ME_MJR_HEAD_ACC (cr=0 pr=0 pw=0 time=0 us cost=3 size=4257 card=473)

                                 0          0          0           TABLE ACCESS FULL CLSF_HB_DATA1 (cr=0 pr=0 pw=0 time=0 us cost=7 size=34 card=1)

                                 0          0          0       FILTER  (cr=0 pr=0 pw=0 time=32 us)

                                 0          0          0        HASH GROUP BY (cr=0 pr=0 pw=0 time=30 us cost=1 size=70 card=1)

                                 0          0          0         FILTER  (cr=0 pr=0 pw=0 time=0 us)

                                 0          0          0          HASH JOIN  (cr=0 pr=0 pw=0 time=0 us cost=54002 size=9590 card=137)

                                 0          0          0           HASH JOIN  (cr=0 pr=0 pw=0 time=0 us cost=7 size=1863 card=81)

                                 0          0          0            TABLE ACCESS FULL ME_FUND_ACC (cr=0 pr=0 pw=0 time=0 us cost=3 size=224 card=16)

                                 0          0          0            TABLE ACCESS FULL ME_MJR_HEAD_ACC (cr=0 pr=0 pw=0 time=0 us cost=3 size=4257 card=473)

                                 0          0          0           PARTITION RANGE ALL PARTITION: 1 21 (cr=0 pr=0 pw=0 time=0 us cost=53995 size=42112 card=896)

                                 0          0          0            TABLE ACCESS FULL CLSF_DATA PARTITION: 1 21 (cr=0 pr=0 pw=0 time=0 us cost=53995 size=42112 card=896)

                                 0          0          0       FILTER  (cr=0 pr=0 pw=0 time=24 us)

                                 0          0          0        HASH GROUP BY (cr=0 pr=0 pw=0 time=23 us cost=1 size=57 card=1)

                                 0          0          0         FILTER  (cr=0 pr=0 pw=0 time=2 us)

                                 0          0          0          HASH JOIN  (cr=0 pr=0 pw=0 time=0 us cost=14 size=57 card=1)

                                 0          0          0           HASH JOIN  (cr=0 pr=0 pw=0 time=0 us cost=7 size=1863 card=81)

                                 0          0          0            TABLE ACCESS FULL ME_FUND_ACC (cr=0 pr=0 pw=0 time=0 us cost=3 size=224 card=16)

                                 0          0          0            TABLE ACCESS FULL ME_MJR_HEAD_ACC (cr=0 pr=0 pw=0 time=0 us cost=3 size=4257 card=473)

                                 0          0          0           TABLE ACCESS FULL CLSF_HB_DATA1 (cr=0 pr=0 pw=0 time=0 us cost=7 size=34 card=1)

                                 0          0          0       FILTER  (cr=0 pr=0 pw=0 time=25 us)

                                 0          0          0        HASH GROUP BY (cr=0 pr=0 pw=0 time=25 us cost=1 size=136 card=1)

                                 0          0          0         FILTER  (cr=0 pr=0 pw=0 time=2 us)

                                 0          0          0          HASH JOIN  (cr=0 pr=0 pw=0 time=0 us cost=54 size=21896 card=161)

                                 0          0          0           TABLE ACCESS FULL ME_FUND_ACC (cr=0 pr=0 pw=0 time=0 us cost=3 size=1428 card=102)

                                 0          0          0           HASH JOIN  (cr=0 pr=0 pw=0 time=0 us cost=50 size=19642 card=161)

                                 0          0          0            TABLE ACCESS FULL ME_MJR_HEAD_ACC (cr=0 pr=0 pw=0 time=0 us cost=3 size=4257 card=473)

                                 0          0          0            VIEW  (cr=0 pr=0 pw=0 time=0 us cost=47 size=18193 card=161)

                                 0          0          0             SORT UNIQUE (cr=0 pr=0 pw=0 time=0 us cost=47 size=9177 card=161)

                                 0          0          0              UNION-ALL  (cr=0 pr=0 pw=0 time=0 us)

                                 0          0          0               TABLE ACCESS FULL FA_SAL_SUBSIDY (cr=0 pr=0 pw=0 time=0 us cost=22 size=1311 card=23)

                                 0          0          0               TABLE ACCESS FULL FA_SAL_SUBSIDY (cr=0 pr=0 pw=0 time=0 us cost=22 size=7866 card=138)

                                 0          0          0       FILTER  (cr=0 pr=0 pw=0 time=25 us)

                                 0          0          0        HASH GROUP BY (cr=0 pr=0 pw=0 time=23 us cost=1 size=80 card=1)

                                 0          0          0         FILTER  (cr=0 pr=0 pw=0 time=2 us)

                                 0          0          0          HASH JOIN  (cr=0 pr=0 pw=0 time=0 us cost=29 size=67680 card=846)

                                 0          0          0           HASH JOIN  (cr=0 pr=0 pw=0 time=0 us cost=7 size=4531 card=197)

                                 0          0          0            TABLE ACCESS FULL ME_FUND_ACC (cr=0 pr=0 pw=0 time=0 us cost=3 size=1428 card=102)

                                 0          0          0            TABLE ACCESS FULL ME_MJR_HEAD_ACC (cr=0 pr=0 pw=0 time=0 us cost=3 size=1773 card=197)

                                 0          0          0           TABLE ACCESS FULL FA_SAL_SUBSIDY (cr=0 pr=0 pw=0 time=0 us cost=22 size=47994 card=842)

                         

                         

                        ********************************************************************************

                         

                         

                        SELECT

                                    BK.FUND_ID

                                 FROM

                                    BK_YR_BAL BK

                                 WHERE

                                    BK.FIN_YR_FROM  =  '01-JAN-12'

                                    AND BK.FIN_YR_to  ='01-JAN-13'

                                              AND BK.STMT_NO  =         '12'

                                 GROUP BY

                                    BK.FUND_ID

                                 HAVING SUM(BK.NP_VOTED) <> 0

                         

                         

                        call     count       cpu    elapsed       disk      query    current        rows

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

                        Parse        1      0.27       0.40          9         44          0           0

                        Execute      1      0.00       0.00          0          0          0           0

                        Fetch        2     62.77      66.51      70777     300596          0           1

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

                        total        4     63.04      66.91      70786     300640          0           1

                         

                         

                        Misses in library cache during parse: 1

                        Optimizer mode: RULE

                        Parsing user id: 1107     (recursive depth: 1)

                        Number of plan statistics captured: 1

                         

                         

                        Rows (1st) Rows (avg) Rows (max)  Row Source Operation

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

                                 1          1          1  FILTER  (cr=300596 pr=70777 pw=0 time=66511245 us)

                                 1          1          1   HASH GROUP BY (cr=300596 pr=70777 pw=0 time=66511241 us cost=14900 size=5625 card=375)

                             16238      16238      16238    VIEW  BK_YR_BAL (cr=300596 pr=70777 pw=0 time=66513435 us cost=14899 size=5625 card=375)

                             16238      16238      16238     SORT UNIQUE (cr=300596 pr=70777 pw=0 time=66507424 us cost=14899 size=44087 card=375)

                             16238      16238      16238      UNION-ALL  (cr=300596 pr=70777 pw=0 time=265152 us)

                                 0          0          0       FILTER  (cr=0 pr=0 pw=0 time=53 us)

                                 0          0          0        HASH GROUP BY (cr=0 pr=0 pw=0 time=50 us cost=1 size=67 card=1)

                                 0          0          0         FILTER  (cr=0 pr=0 pw=0 time=2 us)

                                 0          0          0          HASH JOIN  (cr=0 pr=0 pw=0 time=0 us cost=53943 size=10988 card=164)

                                 0          0          0           HASH JOIN  (cr=0 pr=0 pw=0 time=0 us cost=7 size=2254 card=98)

                                 0          0          0            TABLE ACCESS FULL ME_FUND_ACC (cr=0 pr=0 pw=0 time=0 us cost=3 size=1428 card=102)

                                 0          0          0            TABLE ACCESS FULL ME_MJR_HEAD_ACC (cr=0 pr=0 pw=0 time=0 us cost=3 size=882 card=98)

                                 0          0          0           PARTITION RANGE ALL PARTITION: 1 21 (cr=0 pr=0 pw=0 time=0 us cost=53936 size=39468 card=897)

                                 0          0          0            TABLE ACCESS FULL CLSF_DATA PARTITION: 1 21 (cr=0 pr=0 pw=0 time=0 us cost=53936 size=39468 card=897)

                              3133       3133       3133       FILTER  (cr=93 pr=0 pw=0 time=236493 us)

                              3894       3894       3894        HASH GROUP BY (cr=93 pr=0 pw=0 time=234993 us cost=2910 size=28696 card=211)

                              3901       3901       3901         HASH JOIN  (cr=93 pr=0 pw=0 time=231906 us cost=2908 size=108256 card=796)

                               212        212        212          TABLE ACCESS FULL ME_MJR_HEAD_ACC (cr=6 pr=0 pw=0 time=267 us cost=3 size=1602 card=178)

                            397902     397902     397902          MERGE JOIN CARTESIAN (cr=87 pr=0 pw=0 time=245126 us cost=2903 size=27410664 card=215832)

                              3901       3901       3901           VIEW  (cr=81 pr=0 pw=0 time=37215 us cost=29 size=239108 card=2116)

                              3901       3901       3901            SORT UNIQUE (cr=81 pr=0 pw=0 time=35701 us cost=29 size=112776 card=2116)

                              3903       3903       3903             UNION-ALL  (cr=81 pr=0 pw=0 time=22160 us)

                              3530       3530       3530              HASH GROUP BY (cr=75 pr=0 pw=0 time=15110 us cost=24 size=94176 card=1744)

                              3530       3530       3530               TABLE ACCESS FULL FA_STMT_12_13 (cr=75 pr=0 pw=0 time=3960 us cost=22 size=94176 card=1744)

                               373        373        373              HASH GROUP BY (cr=6 pr=0 pw=0 time=1760 us cost=5 size=18600 card=372)

                               373        373        373               TABLE ACCESS FULL STMT_13_DTL (cr=6 pr=0 pw=0 time=293 us cost=3 size=18600 card=372)

                            397902     397902     397902           BUFFER SORT (cr=6 pr=0 pw=0 time=115151 us cost=2903 size=1428 card=102)

                               102        102        102            TABLE ACCESS FULL ME_FUND_ACC (cr=6 pr=0 pw=0 time=939 us cost=1 size=1428 card=102)

                             13105      13105      13105       FILTER  (cr=300503 pr=70777 pw=0 time=66190002 us)

                             13826      13826      13826        HASH GROUP BY (cr=300503 pr=70777 pw=0 time=66185138 us cost=11980 size=14725 card=155)

                             24035      24035      24035         HASH JOIN  (cr=300503 pr=70777 pw=0 time=66133720 us cost=11978 size=150860 card=1588)

                               212        212        212          TABLE ACCESS FULL ME_MJR_HEAD_ACC (cr=6 pr=0 pw=0 time=126 us cost=3 size=1602 card=178)

                           2451570    2451570    2451570          MERGE JOIN CARTESIAN (cr=300497 pr=70777 pw=0 time=66302944 us cost=11972 size=37044156 card=430746)

                             24035      24035      24035           VIEW  (cr=300491 pr=70777 pw=0 time=64953975 us cost=6240 size=304056 card=4223)

                             24035      24035      24035            SORT UNIQUE (cr=300491 pr=70777 pw=0 time=64946432 us cost=6240 size=226985 card=4223)

                             24035      24035      24035             UNION-ALL  (cr=300491 pr=70777 pw=0 time=64819089 us)

                             11980      11980      11980              HASH GROUP BY (cr=300387 pr=70681 pw=0 time=64797198 us cost=6201 size=21120 card=480)

                            465225     465225     465225               CONCATENATION  (cr=300387 pr=70681 pw=0 time=19639618 us)

                              6853       6853       6853                TABLE ACCESS BY GLOBAL INDEX ROWID CLSF_DATA PARTITION: ROW LOCATION ROW LOCATION (cr=92571 pr=593 pw=0 time=2711860 us cost=3094 size=484 card=11)

                              6853       6853       6853                 INDEX SKIP SCAN CLSF_DATA_PK (cr=90714 pr=566 pw=0 time=3206925 us cost=3093 size=0 card=2)(object id 195358)

                            458372     458372     458372                TABLE ACCESS BY GLOBAL INDEX ROWID CLSF_DATA PARTITION: ROW LOCATION ROW LOCATION (cr=207816 pr=70088 pw=0 time=37496748 us cost=3106 size=15796 card=359)

                            458372     458372     458372                 INDEX SKIP SCAN CLSF_DATA_PK (cr=158234 pr=61697 pw=0 time=43367361 us cost=3093 size=0 card=58)(object id 195358)

                             12055      12055      12055              HASH GROUP BY (cr=104 pr=96 pw=0 time=72188 us cost=37 size=205865 card=3743)

                             12064      12064      12064               VIEW  (cr=104 pr=96 pw=0 time=57946 us cost=35 size=205865 card=3743)

                             12064      12064      12064                SORT UNIQUE (cr=104 pr=96 pw=0 time=54388 us cost=35 size=172201 card=3743)

                             12064      12064      12064                 UNION-ALL  (cr=104 pr=96 pw=0 time=29395 us)

                             11691      11691      11691                  HASH GROUP BY (cr=98 pr=96 pw=0 time=21649 us cost=30 size=158437 card=3371)

                             11691      11691      11691                   TABLE ACCESS FULL BAL_BY_YR_OB_BAL (cr=98 pr=96 pw=0 time=5796 us cost=28 size=485510 card=10330)

                               373        373        373                  HASH GROUP BY (cr=6 pr=0 pw=0 time=660 us cost=5 size=13764 card=372)

                               373        373        373                   TABLE ACCESS FULL STMT_13_DTL (cr=6 pr=0 pw=0 time=405 us cost=3 size=13764 card=372)

                           2451570    2451570    2451570           BUFFER SORT (cr=6 pr=0 pw=0 time=725304 us cost=11972 size=1428 card=102)

                               102        102        102            TABLE ACCESS FULL ME_FUND_ACC (cr=6 pr=0 pw=0 time=730 us cost=1 size=1428 card=102)

                                 0          0          0       FILTER  (cr=0 pr=0 pw=0 time=72 us)

                                 0          0          0        HASH GROUP BY (cr=0 pr=0 pw=0 time=72 us cost=1 size=72 card=1)

                                 0          0          0         FILTER  (cr=0 pr=0 pw=0 time=3 us)

                                 0          0          0          HASH JOIN  (cr=0 pr=0 pw=0 time=0 us cost=3133 size=3096 card=43)

                                 0          0          0           HASH JOIN  (cr=0 pr=0 pw=0 time=0 us cost=7 size=575 card=25)

                                 0          0          0            TABLE ACCESS FULL ME_FUND_ACC (cr=0 pr=0 pw=0 time=0 us cost=3 size=70 card=5)

                                 0          0          0            TABLE ACCESS FULL ME_MJR_HEAD_ACC (cr=0 pr=0 pw=0 time=0 us cost=3 size=4257 card=473)

                                 0          0          0           TABLE ACCESS BY GLOBAL INDEX ROWID CLSF_DATA PARTITION: ROW LOCATION ROW LOCATION (cr=0 pr=0 pw=0 time=0 us cost=3126 size=43953 card=897)

                                 0          0          0            INDEX SKIP SCAN CLSF_DATA_PK (cr=0 pr=0 pw=0 time=0 us cost=3093 size=0 card=145)(object id 195358)

                                 0          0          0       FILTER  (cr=0 pr=0 pw=0 time=30 us)

                                 0          0          0        HASH GROUP BY (cr=0 pr=0 pw=0 time=28 us cost=1 size=57 card=1)

                                 0          0          0         FILTER  (cr=0 pr=0 pw=0 time=0 us)

                                 0          0          0          HASH JOIN  (cr=0 pr=0 pw=0 time=0 us cost=14 size=57 card=1)

                                 0          0          0           HASH JOIN  (cr=0 pr=0 pw=0 time=0 us cost=7 size=575 card=25)

                                 0          0          0            TABLE ACCESS FULL ME_FUND_ACC (cr=0 pr=0 pw=0 time=0 us cost=3 size=70 card=5)

                                 0          0          0            TABLE ACCESS FULL ME_MJR_HEAD_ACC (cr=0 pr=0 pw=0 time=0 us cost=3 size=4257 card=473)

                                 0          0          0           TABLE ACCESS FULL CLSF_HB_DATA1 (cr=0 pr=0 pw=0 time=0 us cost=7 size=34 card=1)

                                 0          0          0       FILTER  (cr=0 pr=0 pw=0 time=33 us)

                                 0          0          0        HASH GROUP BY (cr=0 pr=0 pw=0 time=33 us cost=1 size=70 card=1)

                                 0          0          0         FILTER  (cr=0 pr=0 pw=0 time=2 us)

                                 0          0          0          HASH JOIN  (cr=0 pr=0 pw=0 time=0 us cost=53943 size=13790 card=197)

                                 0          0          0           HASH JOIN  (cr=0 pr=0 pw=0 time=0 us cost=7 size=2691 card=117)

                                 0          0          0            TABLE ACCESS FULL ME_FUND_ACC (cr=0 pr=0 pw=0 time=0 us cost=3 size=322 card=23)

                                 0          0          0            TABLE ACCESS FULL ME_MJR_HEAD_ACC (cr=0 pr=0 pw=0 time=0 us cost=3 size=4257 card=473)

                                 0          0          0           PARTITION RANGE ALL PARTITION: 1 21 (cr=0 pr=0 pw=0 time=0 us cost=53936 size=42159 card=897)

                                 0          0          0            TABLE ACCESS FULL CLSF_DATA PARTITION: 1 21 (cr=0 pr=0 pw=0 time=0 us cost=53936 size=42159 card=897)

                                 0          0          0       FILTER  (cr=0 pr=0 pw=0 time=28 us)

                                 0          0          0        HASH GROUP BY (cr=0 pr=0 pw=0 time=27 us cost=1 size=57 card=1)

                                 0          0          0         FILTER  (cr=0 pr=0 pw=0 time=1 us)

                                 0          0          0          HASH JOIN  (cr=0 pr=0 pw=0 time=0 us cost=14 size=57 card=1)

                                 0          0          0           HASH JOIN  (cr=0 pr=0 pw=0 time=0 us cost=7 size=2691 card=117)

                                 0          0          0            TABLE ACCESS FULL ME_FUND_ACC (cr=0 pr=0 pw=0 time=0 us cost=3 size=322 card=23)

                                 0          0          0            TABLE ACCESS FULL ME_MJR_HEAD_ACC (cr=0 pr=0 pw=0 time=0 us cost=3 size=4257 card=473)

                                 0          0          0           TABLE ACCESS FULL CLSF_HB_DATA1 (cr=0 pr=0 pw=0 time=0 us cost=7 size=34 card=1)

                                 0          0          0       FILTER  (cr=0 pr=0 pw=0 time=28 us)

                                 0          0          0        HASH GROUP BY (cr=0 pr=0 pw=0 time=28 us cost=1 size=70 card=1)

                                 0          0          0         FILTER  (cr=0 pr=0 pw=0 time=1 us)

                                 0          0          0          HASH JOIN  (cr=0 pr=0 pw=0 time=0 us cost=54002 size=9590 card=137)

                                 0          0          0           HASH JOIN  (cr=0 pr=0 pw=0 time=0 us cost=7 size=1863 card=81)

                                 0          0          0            TABLE ACCESS FULL ME_FUND_ACC (cr=0 pr=0 pw=0 time=0 us cost=3 size=224 card=16)

                                 0          0          0            TABLE ACCESS FULL ME_MJR_HEAD_ACC (cr=0 pr=0 pw=0 time=0 us cost=3 size=4257 card=473)

                                 0          0          0           PARTITION RANGE ALL PARTITION: 1 21 (cr=0 pr=0 pw=0 time=0 us cost=53995 size=42112 card=896)

                                 0          0          0            TABLE ACCESS FULL CLSF_DATA PARTITION: 1 21 (cr=0 pr=0 pw=0 time=0 us cost=53995 size=42112 card=896)

                                 0          0          0       FILTER  (cr=0 pr=0 pw=0 time=26 us)

                                 0          0          0        HASH GROUP BY (cr=0 pr=0 pw=0 time=26 us cost=1 size=57 card=1)

                                 0          0          0         FILTER  (cr=0 pr=0 pw=0 time=0 us)

                                 0          0          0          HASH JOIN  (cr=0 pr=0 pw=0 time=0 us cost=14 size=57 card=1)

                                 0          0          0           HASH JOIN  (cr=0 pr=0 pw=0 time=0 us cost=7 size=1863 card=81)

                                 0          0          0            TABLE ACCESS FULL ME_FUND_ACC (cr=0 pr=0 pw=0 time=0 us cost=3 size=224 card=16)

                                 0          0          0            TABLE ACCESS FULL ME_MJR_HEAD_ACC (cr=0 pr=0 pw=0 time=0 us cost=3 size=4257 card=473)

                                 0          0          0           TABLE ACCESS FULL CLSF_HB_DATA1 (cr=0 pr=0 pw=0 time=0 us cost=7 size=34 card=1)

                                 0          0          0       FILTER  (cr=0 pr=0 pw=0 time=35 us)

                                 0          0          0        HASH GROUP BY (cr=0 pr=0 pw=0 time=34 us cost=1 size=136 card=1)

                                 0          0          0         FILTER  (cr=0 pr=0 pw=0 time=1 us)

                                 0          0          0          HASH JOIN  (cr=0 pr=0 pw=0 time=0 us cost=54 size=21896 card=161)

                                 0          0          0           TABLE ACCESS FULL ME_FUND_ACC (cr=0 pr=0 pw=0 time=0 us cost=3 size=1428 card=102)

                                 0          0          0           HASH JOIN  (cr=0 pr=0 pw=0 time=0 us cost=50 size=19642 card=161)

                                 0          0          0            TABLE ACCESS FULL ME_MJR_HEAD_ACC (cr=0 pr=0 pw=0 time=0 us cost=3 size=4257 card=473)

                                 0          0          0            VIEW  (cr=0 pr=0 pw=0 time=0 us cost=47 size=18193 card=161)

                                 0          0          0             SORT UNIQUE (cr=0 pr=0 pw=0 time=0 us cost=47 size=9177 card=161)

                                 0          0          0              UNION-ALL  (cr=0 pr=0 pw=0 time=0 us)

                                 0          0          0               TABLE ACCESS FULL FA_SAL_SUBSIDY (cr=0 pr=0 pw=0 time=0 us cost=22 size=1311 card=23)

                                 0          0          0               TABLE ACCESS FULL FA_SAL_SUBSIDY (cr=0 pr=0 pw=0 time=0 us cost=22 size=7866 card=138)

                                 0          0          0       FILTER  (cr=0 pr=0 pw=0 time=27 us)

                                 0          0          0        HASH GROUP BY (cr=0 pr=0 pw=0 time=27 us cost=1 size=80 card=1)

                                 0          0          0         FILTER  (cr=0 pr=0 pw=0 time=1 us)

                                 0          0          0          HASH JOIN  (cr=0 pr=0 pw=0 time=0 us cost=29 size=67680 card=846)

                                 0          0          0           HASH JOIN  (cr=0 pr=0 pw=0 time=0 us cost=7 size=4531 card=197)

                                 0          0          0            TABLE ACCESS FULL ME_FUND_ACC (cr=0 pr=0 pw=0 time=0 us cost=3 size=1428 card=102)

                                 0          0          0            TABLE ACCESS FULL ME_MJR_HEAD_ACC (cr=0 pr=0 pw=0 time=0 us cost=3 size=1773 card=197)

                                 0          0          0           TABLE ACCESS FULL FA_SAL_SUBSIDY (cr=0 pr=0 pw=0 time=0 us cost=22 size=47994 card=842)

                         

                         

                        ********************************************************************************

                         

                         

                        SQL ID: 69subccxd9b03 Plan Hash: 4264803741

                         

                         

                        SELECT signature, inuse_features, flags                                      

                        FROM

                        sql$                                                                    

                          WHERE signature = :1

                         

                         

                         

                         

                        call     count       cpu    elapsed       disk      query    current        rows

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

                        Parse    34201      0.59       0.61          0          0          0           0

                        Execute  34201      1.06       1.07          0          0          0           0

                        Fetch    34201      1.41      59.29       9060     136588          0       33985

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

                        total   102603      3.08      60.98       9060     136588          0       33985

                         

                         

                        Misses in library cache during parse: 2

                        Misses in library cache during execute: 2

                        Optimizer mode: RULE

                        Parsing user id: SYS   (recursive depth: 2)

                        Number of plan statistics captured: 6

                         

                         

                        Rows (1st) Rows (avg) Rows (max)  Row Source Operation

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

                                 1          1          1  TABLE ACCESS BY INDEX ROWID SQL$ (cr=4 pr=0 pw=0 time=1710 us)

                                 1          1          1   INDEX UNIQUE SCAN I_SQL$_PKEY (cr=3 pr=0 pw=0 time=976 us)(object id 195)

                         

                         

                        ********************************************************************************

                         

                         

                        SELECT

                                    BK.FUND_ID

                                 FROM

                                    BK_YR_BAL BK

                                 WHERE

                                    BK.FIN_YR_FROM  =  '01-JAN-12'

                                    AND BK.FIN_YR_to  ='01-JAN-13'

                                              AND BK.STMT_NO  =         '12'

                                 GROUP BY

                                    BK.FUND_ID

                                 HAVING SUM(BK.CSS) <> 0

                         

                         

                        call     count       cpu    elapsed       disk      query    current        rows

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

                        Parse        1      0.17       0.21          8         44          0           0

                        Execute      1      0.00       0.00          0          0          0           0

                        Fetch        2     58.05      58.07      70780     300596          0           1

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

                        total        4     58.22      58.28      70788     300640          0           1

                         

                         

                        Misses in library cache during parse: 1

                        Optimizer mode: RULE

                        Parsing user id: 1107     (recursive depth: 1)

                        Number of plan statistics captured: 1

                         

                         

                        Rows (1st) Rows (avg) Rows (max)  Row Source Operation

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

                                 1          1          1  FILTER  (cr=300596 pr=70780 pw=0 time=58071270 us)

                                 1          1          1   HASH GROUP BY (cr=300596 pr=70780 pw=0 time=58071267 us cost=14900 size=5625 card=375)

                             16238      16238      16238    VIEW  BK_YR_BAL (cr=300596 pr=70780 pw=0 time=58072656 us cost=14899 size=5625 card=375)

                             16238      16238      16238     SORT UNIQUE (cr=300596 pr=70780 pw=0 time=58068050 us cost=14899 size=44087 card=375)

                             16238      16238      16238      UNION-ALL  (cr=300596 pr=70780 pw=0 time=249732 us)

                                 0          0          0       FILTER  (cr=0 pr=0 pw=0 time=44 us)

                                 0          0          0        HASH GROUP BY (cr=0 pr=0 pw=0 time=44 us cost=1 size=67 card=1)

                                 0          0          0         FILTER  (cr=0 pr=0 pw=0 time=3 us)

                                 0          0          0          HASH JOIN  (cr=0 pr=0 pw=0 time=0 us cost=53943 size=10988 card=164)

                                 0          0          0           HASH JOIN  (cr=0 pr=0 pw=0 time=0 us cost=7 size=2254 card=98)

                                 0          0          0            TABLE ACCESS FULL ME_FUND_ACC (cr=0 pr=0 pw=0 time=0 us cost=3 size=1428 card=102)

                                 0          0          0            TABLE ACCESS FULL ME_MJR_HEAD_ACC (cr=0 pr=0 pw=0 time=0 us cost=3 size=882 card=98)

                                 0          0          0           PARTITION RANGE ALL PARTITION: 1 21 (cr=0 pr=0 pw=0 time=0 us cost=53936 size=39468 card=897)

                                 0          0          0            TABLE ACCESS FULL CLSF_DATA PARTITION: 1 21 (cr=0 pr=0 pw=0 time=0 us cost=53936 size=39468 card=897)

                              3133       3133       3133       FILTER  (cr=93 pr=0 pw=0 time=221594 us)

                              3894       3894       3894        HASH GROUP BY (cr=93 pr=0 pw=0 time=220596 us cost=2910 size=28696 card=211)

                              3901       3901       3901         HASH JOIN  (cr=93 pr=0 pw=0 time=214291 us cost=2908 size=108256 card=796)

                               212        212        212          TABLE ACCESS FULL ME_MJR_HEAD_ACC (cr=6 pr=0 pw=0 time=137 us cost=3 size=1602 card=178)

                            397902     397902     397902          MERGE JOIN CARTESIAN (cr=87 pr=0 pw=0 time=225531 us cost=2903 size=27410664 card=215832)

                              3901       3901       3901           VIEW  (cr=81 pr=0 pw=0 time=23035 us cost=29 size=239108 card=2116)

                              3901       3901       3901            SORT UNIQUE (cr=81 pr=0 pw=0 time=21776 us cost=29 size=112776 card=2116)

                              3903       3903       3903             UNION-ALL  (cr=81 pr=0 pw=0 time=13606 us)

                              3530       3530       3530              HASH GROUP BY (cr=75 pr=0 pw=0 time=8696 us cost=24 size=94176 card=1744)

                              3530       3530       3530               TABLE ACCESS FULL FA_STMT_12_13 (cr=75 pr=0 pw=0 time=2307 us cost=22 size=94176 card=1744)

                               373        373        373              HASH GROUP BY (cr=6 pr=0 pw=0 time=950 us cost=5 size=18600 card=372)

                               373        373        373               TABLE ACCESS FULL STMT_13_DTL (cr=6 pr=0 pw=0 time=155 us cost=3 size=18600 card=372)

                            397902     397902     397902           BUFFER SORT (cr=6 pr=0 pw=0 time=115584 us cost=2903 size=1428 card=102)

                               102        102        102            TABLE ACCESS FULL ME_FUND_ACC (cr=6 pr=0 pw=0 time=727 us cost=1 size=1428 card=102)

                             13105      13105      13105       FILTER  (cr=300503 pr=70780 pw=0 time=57767519 us)

                             13826      13826      13826        HASH GROUP BY (cr=300503 pr=70780 pw=0 time=57764055 us cost=11980 size=14725 card=155)

                             24035      24035      24035         HASH JOIN  (cr=300503 pr=70780 pw=0 time=57732312 us cost=11978 size=150860 card=1588)

                               212        212        212          TABLE ACCESS FULL ME_MJR_HEAD_ACC (cr=6 pr=0 pw=0 time=337 us cost=3 size=1602 card=178)

                           2451570    2451570    2451570          MERGE JOIN CARTESIAN (cr=300497 pr=70780 pw=0 time=57831384 us cost=11972 size=37044156 card=430746)

                             24035      24035      24035           VIEW  (cr=300491 pr=70780 pw=0 time=56558570 us cost=6240 size=304056 card=4223)

                             24035      24035      24035            SORT UNIQUE (cr=300491 pr=70780 pw=0 time=56551920 us cost=6240 size=226985 card=4223)

                             24035      24035      24035             UNION-ALL  (cr=300491 pr=70780 pw=0 time=56425871 us)

                             11980      11980      11980              HASH GROUP BY (cr=300387 pr=70684 pw=0 time=56405385 us cost=6201 size=21120 card=480)

                            465225     465225     465225               CONCATENATION  (cr=300387 pr=70684 pw=0 time=3632711 us)

                              6853       6853       6853                TABLE ACCESS BY GLOBAL INDEX ROWID CLSF_DATA PARTITION: ROW LOCATION ROW LOCATION (cr=92571 pr=597 pw=0 time=2700414 us cost=3094 size=484 card=11)

                              6853       6853       6853                 INDEX SKIP SCAN CLSF_DATA_PK (cr=90714 pr=567 pw=0 time=3184849 us cost=3093 size=0 card=2)(object id 195358)

                            458372     458372     458372                TABLE ACCESS BY GLOBAL INDEX ROWID CLSF_DATA PARTITION: ROW LOCATION ROW LOCATION (cr=207816 pr=70087 pw=0 time=35616115 us cost=3106 size=15796 card=359)

                            458372     458372     458372                 INDEX SKIP SCAN CLSF_DATA_PK (cr=158234 pr=61698 pw=0 time=41802691 us cost=3093 size=0 card=58)(object id 195358)

                             12055      12055      12055              HASH GROUP BY (cr=104 pr=96 pw=0 time=68615 us cost=37 size=205865 card=3743)

                             12064      12064      12064               VIEW  (cr=104 pr=96 pw=0 time=56731 us cost=35 size=205865 card=3743)

                             12064      12064      12064                SORT UNIQUE (cr=104 pr=96 pw=0 time=52411 us cost=35 size=172201 card=3743)

                             12064      12064      12064                 UNION-ALL  (cr=104 pr=96 pw=0 time=28985 us)

                             11691      11691      11691                  HASH GROUP BY (cr=98 pr=96 pw=0 time=22510 us cost=30 size=158437 card=3371)

                             11691      11691      11691                   TABLE ACCESS FULL BAL_BY_YR_OB_BAL (cr=98 pr=96 pw=0 time=5261 us cost=28 size=485510 card=10330)

                               373        373        373                  HASH GROUP BY (cr=6 pr=0 pw=0 time=866 us cost=5 size=13764 card=372)

                               373        373        373                   TABLE ACCESS FULL STMT_13_DTL (cr=6 pr=0 pw=0 time=404 us cost=3 size=13764 card=372)

                           2451570    2451570    2451570           BUFFER SORT (cr=6 pr=0 pw=0 time=692986 us cost=11972 size=1428 card=102)

                               102        102        102            TABLE ACCESS FULL ME_FUND_ACC (cr=6 pr=0 pw=0 time=730 us cost=1 size=1428 card=102)

                                 0          0          0       FILTER  (cr=0 pr=0 pw=0 time=73 us)

                                 0          0          0        HASH GROUP BY (cr=0 pr=0 pw=0 time=71 us cost=1 size=72 card=1)

                                 0          0          0         FILTER  (cr=0 pr=0 pw=0 time=2 us)

                                 0          0          0          HASH JOIN  (cr=0 pr=0 pw=0 time=0 us cost=3133 size=3096 card=43)

                                 0          0          0           HASH JOIN  (cr=0 pr=0 pw=0 time=0 us cost=7 size=575 card=25)

                                 0          0          0            TABLE ACCESS FULL ME_FUND_ACC (cr=0 pr=0 pw=0 time=0 us cost=3 size=70 card=5)

                                 0          0          0            TABLE ACCESS FULL ME_MJR_HEAD_ACC (cr=0 pr=0 pw=0 time=0 us cost=3 size=4257 card=473)

                                 0          0          0           TABLE ACCESS BY GLOBAL INDEX ROWID CLSF_DATA PARTITION: ROW LOCATION ROW LOCATION (cr=0 pr=0 pw=0 time=0 us cost=3126 size=43953 card=897)

                                 0          0          0            INDEX SKIP SCAN CLSF_DATA_PK (cr=0 pr=0 pw=0 time=0 us cost=3093 size=0 card=145)(object id 195358)

                                 0          0          0       FILTER  (cr=0 pr=0 pw=0 time=27 us)

                                 0          0          0        HASH GROUP BY (cr=0 pr=0 pw=0 time=26 us cost=1 size=57 card=1)

                                 0          0          0         FILTER  (cr=0 pr=0 pw=0 time=1 us)

                                 0          0          0          HASH JOIN  (cr=0 pr=0 pw=0 time=0 us cost=14 size=57 card=1)

                                 0          0          0           HASH JOIN  (cr=0 pr=0 pw=0 time=0 us cost=7 size=575 card=25)

                                 0          0          0            TABLE ACCESS FULL ME_FUND_ACC (cr=0 pr=0 pw=0 time=0 us cost=3 size=70 card=5)

                                 0          0          0            TABLE ACCESS FULL ME_MJR_HEAD_ACC (cr=0 pr=0 pw=0 time=0 us cost=3 size=4257 card=473)

                                 0          0          0           TABLE ACCESS FULL CLSF_HB_DATA1 (cr=0 pr=0 pw=0 time=0 us cost=7 size=34 card=1)

                                 0          0          0       FILTER  (cr=0 pr=0 pw=0 time=29 us)

                                 0          0          0        HASH GROUP BY (cr=0 pr=0 pw=0 time=28 us cost=1 size=70 card=1)

                                 0          0          0         FILTER  (cr=0 pr=0 pw=0 time=1 us)

                                 0          0          0          HASH JOIN  (cr=0 pr=0 pw=0 time=0 us cost=53943 size=13790 card=197)

                                 0          0          0           HASH JOIN  (cr=0 pr=0 pw=0 time=0 us cost=7 size=2691 card=117)

                                 0          0          0            TABLE ACCESS FULL ME_FUND_ACC (cr=0 pr=0 pw=0 time=0 us cost=3 size=322 card=23)

                                 0          0          0            TABLE ACCESS FULL ME_MJR_HEAD_ACC (cr=0 pr=0 pw=0 time=0 us cost=3 size=4257 card=473)

                                 0          0          0           PARTITION RANGE ALL PARTITION: 1 21 (cr=0 pr=0 pw=0 time=0 us cost=53936 size=42159 card=897)

                                 0          0          0            TABLE ACCESS FULL CLSF_DATA PARTITION: 1 21 (cr=0 pr=0 pw=0 time=0 us cost=53936 size=42159 card=897)

                                 0          0          0       FILTER  (cr=0 pr=0 pw=0 time=26 us)

                                 0          0          0        HASH GROUP BY (cr=0 pr=0 pw=0 time=26 us cost=1 size=57 card=1)

                                 0          0          0         FILTER  (cr=0 pr=0 pw=0 time=1 us)

                                 0          0          0          HASH JOIN  (cr=0 pr=0 pw=0 time=0 us cost=14 size=57 card=1)

                                 0          0          0           HASH JOIN  (cr=0 pr=0 pw=0 time=0 us cost=7 size=2691 card=117)

                                 0          0          0            TABLE ACCESS FULL ME_FUND_ACC (cr=0 pr=0 pw=0 time=0 us cost=3 size=322 card=23)

                                 0          0          0            TABLE ACCESS FULL ME_MJR_HEAD_ACC (cr=0 pr=0 pw=0 time=0 us cost=3 size=4257 card=473)

                                 0          0          0           TABLE ACCESS FULL CLSF_HB_DATA1 (cr=0 pr=0 pw=0 time=0 us cost=7 size=34 card=1)

                                 0          0          0       FILTER  (cr=0 pr=0 pw=0 time=25 us)

                                 0          0          0        HASH GROUP BY (cr=0 pr=0 pw=0 time=24 us cost=1 size=70 card=1)

                                 0          0          0         FILTER  (cr=0 pr=0 pw=0 time=1 us)

                                 0          0          0          HASH JOIN  (cr=0 pr=0 pw=0 time=0 us cost=54002 size=9590 card=137)

                                 0          0          0           HASH JOIN  (cr=0 pr=0 pw=0 time=0 us cost=7 size=1863 card=81)

                                 0          0          0            TABLE ACCESS FULL ME_FUND_ACC (cr=0 pr=0 pw=0 time=0 us cost=3 size=224 card=16)

                                 0          0          0            TABLE ACCESS FULL ME_MJR_HEAD_ACC (cr=0 pr=0 pw=0 time=0 us cost=3 size=4257 card=473)

                                 0          0          0           PARTITION RANGE ALL PARTITION: 1 21 (cr=0 pr=0 pw=0 time=0 us cost=53995 size=42112 card=896)

                                 0          0          0            TABLE ACCESS FULL CLSF_DATA PARTITION: 1 21 (cr=0 pr=0 pw=0 time=0 us cost=53995 size=42112 card=896)

                                 0          0          0       FILTER  (cr=0 pr=0 pw=0 time=27 us)

                                 0          0          0        HASH GROUP BY (cr=0 pr=0 pw=0 time=26 us cost=1 size=57 card=1)

                                 0          0          0         FILTER  (cr=0 pr=0 pw=0 time=0 us)

                                 0          0          0          HASH JOIN  (cr=0 pr=0 pw=0 time=0 us cost=14 size=57 card=1)

                                 0          0          0           HASH JOIN  (cr=0 pr=0 pw=0 time=0 us cost=7 size=1863 card=81)

                                 0          0          0            TABLE ACCESS FULL ME_FUND_ACC (cr=0 pr=0 pw=0 time=0 us cost=3 size=224 card=16)

                                 0          0          0            TABLE ACCESS FULL ME_MJR_HEAD_ACC (cr=0 pr=0 pw=0 time=0 us cost=3 size=4257 card=473)

                                 0          0          0           TABLE ACCESS FULL CLSF_HB_DATA1 (cr=0 pr=0 pw=0 time=0 us cost=7 size=34 card=1)

                                 0          0          0       FILTER  (cr=0 pr=0 pw=0 time=28 us)

                                 0          0          0        HASH GROUP BY (cr=0 pr=0 pw=0 time=27 us cost=1 size=136 card=1)

                                 0          0          0         FILTER  (cr=0 pr=0 pw=0 time=1 us)

                                 0          0          0          HASH JOIN  (cr=0 pr=0 pw=0 time=0 us cost=54 size=21896 card=161)

                                 0          0          0           TABLE ACCESS FULL ME_FUND_ACC (cr=0 pr=0 pw=0 time=0 us cost=3 size=1428 card=102)

                                 0          0          0           HASH JOIN  (cr=0 pr=0 pw=0 time=0 us cost=50 size=19642 card=161)

                                 0          0          0            TABLE ACCESS FULL ME_MJR_HEAD_ACC (cr=0 pr=0 pw=0 time=0 us cost=3 size=4257 card=473)

                                 0          0          0            VIEW  (cr=0 pr=0 pw=0 time=0 us cost=47 size=18193 card=161)

                                 0          0          0             SORT UNIQUE (cr=0 pr=0 pw=0 time=0 us cost=47 size=9177 card=161)

                                 0          0          0              UNION-ALL  (cr=0 pr=0 pw=0 time=0 us)

                                 0          0          0               TABLE ACCESS FULL FA_SAL_SUBSIDY (cr=0 pr=0 pw=0 time=0 us cost=22 size=1311 card=23)

                                 0          0          0               TABLE ACCESS FULL FA_SAL_SUBSIDY (cr=0 pr=0 pw=0 time=0 us cost=22 size=7866 card=138)

                                 0          0          0       FILTER  (cr=0 pr=0 pw=0 time=32 us)

                                 0          0          0        HASH GROUP BY (cr=0 pr=0 pw=0 time=31 us cost=1 size=80 card=1)

                                 0          0          0         FILTER  (cr=0 pr=0 pw=0 time=1 us)

                                 0          0          0          HASH JOIN  (cr=0 pr=0 pw=0 time=0 us cost=29 size=67680 card=846)

                                 0          0          0           HASH JOIN  (cr=0 pr=0 pw=0 time=0 us cost=7 size=4531 card=197)

                                 0          0          0            TABLE ACCESS FULL ME_FUND_ACC (cr=0 pr=0 pw=0 time=0 us cost=3 size=1428 card=102)

                                 0          0          0            TABLE ACCESS FULL ME_MJR_HEAD_ACC (cr=0 pr=0 pw=0 time=0 us cost=3 size=1773 card=197)

                                 0          0          0           TABLE ACCESS FULL FA_SAL_SUBSIDY (cr=0 pr=0 pw=0 time=0 us cost=22 size=47994 card=842)

                         

                         

                        ********************************************************************************

                         

                         

                        SELECT

                                    BK.FUND_ID

                                 FROM

                                    BK_YR_BAL BK

                                 WHERE

                                    BK.FIN_YR_FROM  =  '01-JAN-12'

                                    AND BK.FIN_YR_to  ='01-JAN-13'

                                              AND BK.STMT_NO  =         '12'

                                 GROUP BY

                                    BK.FUND_ID

                                 HAVING SUM(BK.P_CHARGED) <> 0

                         

                         

                        call     count       cpu    elapsed       disk      query    current        rows

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

                        Parse        1      0.20       0.24          8         44          0           0

                        Execute      1      0.00       0.00          0          0          0           0

                        Fetch        2     56.64      56.65      70847     300596          0           1

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

                        total        4     56.84      56.90      70855     300640          0           1

                         

                         

                        Misses in library cache during parse: 1

                        Optimizer mode: RULE

                        Parsing user id: 1107     (recursive depth: 1)

                        Number of plan statistics captured: 1

                         

                         

                        Rows (1st) Rows (avg) Rows (max)  Row Source Operation

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

                                 1          1          1  FILTER  (cr=300596 pr=70847 pw=0 time=56659246 us)

                                 1          1          1   HASH GROUP BY (cr=300596 pr=70847 pw=0 time=56659243 us cost=14900 size=5625 card=375)

                             16238      16238      16238    VIEW  BK_YR_BAL (cr=300596 pr=70847 pw=0 time=56660303 us cost=14899 size=5625 card=375)

                             16238      16238      16238     SORT UNIQUE (cr=300596 pr=70847 pw=0 time=56657233 us cost=14899 size=44087 card=375)

                             16238      16238      16238      UNION-ALL  (cr=300596 pr=70847 pw=0 time=248501 us)

                                 0          0          0       FILTER  (cr=0 pr=0 pw=0 time=37 us)

                                 0          0          0        HASH GROUP BY (cr=0 pr=0 pw=0 time=37 us cost=1 size=67 card=1)

                                 0          0          0         FILTER  (cr=0 pr=0 pw=0 time=1 us)

                                 0          0          0          HASH JOIN  (cr=0 pr=0 pw=0 time=0 us cost=53943 size=10988 card=164)

                                 0          0          0           HASH JOIN  (cr=0 pr=0 pw=0 time=0 us cost=7 size=2254 card=98)

                                 0          0          0            TABLE ACCESS FULL ME_FUND_ACC (cr=0 pr=0 pw=0 time=0 us cost=3 size=1428 card=102)

                                 0          0          0            TABLE ACCESS FULL ME_MJR_HEAD_ACC (cr=0 pr=0 pw=0 time=0 us cost=3 size=882 card=98)

                                 0          0          0           PARTITION RANGE ALL PARTITION: 1 21 (cr=0 pr=0 pw=0 time=0 us cost=53936 size=39468 card=897)

                                 0          0          0            TABLE ACCESS FULL CLSF_DATA PARTITION: 1 21 (cr=0 pr=0 pw=0 time=0 us cost=53936 size=39468 card=897)

                              3133       3133       3133       FILTER  (cr=93 pr=0 pw=0 time=220507 us)

                              3894       3894       3894        HASH GROUP BY (cr=93 pr=0 pw=0 time=219760 us cost=2910 size=28696 card=211)

                              3901       3901       3901         HASH JOIN  (cr=93 pr=0 pw=0 time=212732 us cost=2908 size=108256 card=796)

                               212        212        212          TABLE ACCESS FULL ME_MJR_HEAD_ACC (cr=6 pr=0 pw=0 time=247 us cost=3 size=1602 card=178)

                            397902     397902     397902          MERGE JOIN CARTESIAN (cr=87 pr=0 pw=0 time=223097 us cost=2903 size=27410664 card=215832)

                              3901       3901       3901           VIEW  (cr=81 pr=0 pw=0 time=22786 us cost=29 size=239108 card=2116)

                              3901       3901       3901            SORT UNIQUE (cr=81 pr=0 pw=0 time=20897 us cost=29 size=112776 card=2116)

                              3903       3903       3903             UNION-ALL  (cr=81 pr=0 pw=0 time=14076 us)

                              3530       3530       3530              HASH GROUP BY (cr=75 pr=0 pw=0 time=8789 us cost=24 size=94176 card=1744)

                              3530       3530       3530               TABLE ACCESS FULL FA_STMT_12_13 (cr=75 pr=0 pw=0 time=3439 us cost=22 size=94176 card=1744)

                               373        373        373              HASH GROUP BY (cr=6 pr=0 pw=0 time=1058 us cost=5 size=18600 card=372)

                               373        373        373               TABLE ACCESS FULL STMT_13_DTL (cr=6 pr=0 pw=0 time=281 us cost=3 size=18600 card=372)

                            397902     397902     397902           BUFFER SORT (cr=6 pr=0 pw=0 time=106336 us cost=2903 size=1428 card=102)

                               102        102        102            TABLE ACCESS FULL ME_FUND_ACC (cr=6 pr=0 pw=0 time=732 us cost=1 size=1428 card=102)

                             13105      13105      13105       FILTER  (cr=300503 pr=70847 pw=0 time=56356149 us)

                             13826      13826      13826        HASH GROUP BY (cr=300503 pr=70847 pw=0 time=56351923 us cost=11980 size=14725 card=155)

                             24035      24035      24035         HASH JOIN  (cr=300503 pr=70847 pw=0 time=56324837 us cost=11978 size=150860 card=1588)

                               212        212        212          TABLE ACCESS FULL ME_MJR_HEAD_ACC (cr=6 pr=0 pw=0 time=231 us cost=3 size=1602 card=178)

                           2451570    2451570    2451570          MERGE JOIN CARTESIAN (cr=300497 pr=70847 pw=0 time=56445681 us cost=11972 size=37044156 card=430746)

                             24035      24035      24035           VIEW  (cr=300491 pr=70847 pw=0 time=55152391 us cost=6240 size=304056 card=4223)

                             24035      24035      24035            SORT UNIQUE (cr=300491 pr=70847 pw=0 time=55144591 us cost=6240 size=226985 card=4223)

                             24035      24035      24035             UNION-ALL  (cr=300491 pr=70847 pw=0 time=55019996 us)

                             11980      11980      11980              HASH GROUP BY (cr=300387 pr=70751 pw=0 time=55000149 us cost=6201 size=21120 card=480)

                            465225     465225     465225               CONCATENATION  (cr=300387 pr=70751 pw=0 time=3581354 us)

                              6853       6853       6853                TABLE ACCESS BY GLOBAL INDEX ROWID CLSF_DATA PARTITION: ROW LOCATION ROW LOCATION (cr=92571 pr=601 pw=0 time=2687591 us cost=3094 size=484 card=11)

                              6853       6853       6853                 INDEX SKIP SCAN CLSF_DATA_PK (cr=90714 pr=571 pw=0 time=3166174 us cost=3093 size=0 card=2)(object id 195358)

                            458372     458372     458372                TABLE ACCESS BY GLOBAL INDEX ROWID CLSF_DATA PARTITION: ROW LOCATION ROW LOCATION (cr=207816 pr=70150 pw=0 time=34518834 us cost=3106 size=15796 card=359)

                            458372     458372     458372                 INDEX SKIP SCAN CLSF_DATA_PK (cr=158234 pr=61697 pw=0 time=40520892 us cost=3093 size=0 card=58)(object id 195358)

                             12055      12055      12055              HASH GROUP BY (cr=104 pr=96 pw=0 time=67913 us cost=37 size=205865 card=3743)

                             12064      12064      12064               VIEW  (cr=104 pr=96 pw=0 time=56585 us cost=35 size=205865 card=3743)

                             12064      12064      12064                SORT UNIQUE (cr=104 pr=96 pw=0 time=53662 us cost=35 size=172201 card=3743)

                             12064      12064      12064                 UNION-ALL  (cr=104 pr=96 pw=0 time=28716 us)

                             11691      11691      11691                  HASH GROUP BY (cr=98 pr=96 pw=0 time=20463 us cost=30 size=158437 card=3371)

                             11691      11691      11691                   TABLE ACCESS FULL BAL_BY_YR_OB_BAL (cr=98 pr=96 pw=0 time=4492 us cost=28 size=485510 card=10330)

                               373        373        373                  HASH GROUP BY (cr=6 pr=0 pw=0 time=738 us cost=5 size=13764 card=372)

                               373        373        373                   TABLE ACCESS FULL STMT_13_DTL (cr=6 pr=0 pw=0 time=154 us cost=3 size=13764 card=372)

                           2451570    2451570    2451570           BUFFER SORT (cr=6 pr=0 pw=0 time=696529 us cost=11972 size=1428 card=102)

                               102        102        102            TABLE ACCESS FULL ME_FUND_ACC (cr=6 pr=0 pw=0 time=730 us cost=1 size=1428 card=102)

                                 0          0          0       FILTER  (cr=0 pr=0 pw=0 time=43 us)

                                 0          0          0        HASH GROUP BY (cr=0 pr=0 pw=0 time=42 us cost=1 size=72 card=1)

                                 0          0          0         FILTER  (cr=0 pr=0 pw=0 time=1 us)

                                 0          0          0          HASH JOIN  (cr=0 pr=0 pw=0 time=0 us cost=3133 size=3096 card=43)

                                 0          0          0           HASH JOIN  (cr=0 pr=0 pw=0 time=0 us cost=7 size=575 card=25)

                                 0          0          0            TABLE ACCESS FULL ME_FUND_ACC (cr=0 pr=0 pw=0 time=0 us cost=3 size=70 card=5)

                                 0          0          0            TABLE ACCESS FULL ME_MJR_HEAD_ACC (cr=0 pr=0 pw=0 time=0 us cost=3 size=4257 card=473)

                                 0          0          0           TABLE ACCESS BY GLOBAL INDEX ROWID CLSF_DATA PARTITION: ROW LOCATION ROW LOCATION (cr=0 pr=0 pw=0 time=0 us cost=3126 size=43953 card=897)

                                 0          0          0            INDEX SKIP SCAN CLSF_DATA_PK (cr=0 pr=0 pw=0 time=0 us cost=3093 size=0 card=145)(object id 195358)

                                 0          0          0       FILTER  (cr=0 pr=0 pw=0 time=24 us)

                                 0          0          0        HASH GROUP BY (cr=0 pr=0 pw=0 time=24 us cost=1 size=57 card=1)

                                 0          0          0         FILTER  (cr=0 pr=0 pw=0 time=0 us)

                                 0          0          0          HASH JOIN  (cr=0 pr=0 pw=0 time=0 us cost=14 size=57 card=1)

                                 0          0          0           HASH JOIN  (cr=0 pr=0 pw=0 time=0 us cost=7 size=575 card=25)

                                 0          0          0            TABLE ACCESS FULL ME_FUND_ACC (cr=0 pr=0 pw=0 time=0 us cost=3 size=70 card=5)

                                 0          0          0            TABLE ACCESS FULL ME_MJR_HEAD_ACC (cr=0 pr=0 pw=0 time=0 us cost=3 size=4257 card=473)

                                 0          0          0           TABLE ACCESS FULL CLSF_HB_DATA1 (cr=0 pr=0 pw=0 time=0 us cost=7 size=34 card=1)

                                 0          0          0       FILTER  (cr=0 pr=0 pw=0 time=27 us)

                                 0          0          0        HASH GROUP BY (cr=0 pr=0 pw=0 time=27 us cost=1 size=70 card=1)

                                 0          0          0         FILTER  (cr=0 pr=0 pw=0 time=1 us)

                                 0          0          0          HASH JOIN  (cr=0 pr=0 pw=0 time=0 us cost=53943 size=13790 card=197)

                                 0          0          0           HASH JOIN  (cr=0 pr=0 pw=0 time=0 us cost=7 size=2691 card=117)

                                 0          0          0            TABLE ACCESS FULL ME_FUND_ACC (cr=0 pr=0 pw=0 time=0 us cost=3 size=322 card=23)

                                 0          0          0            TABLE ACCESS FULL ME_MJR_HEAD_ACC (cr=0 pr=0 pw=0 time=0 us cost=3 size=4257 card=473)

                                 0          0          0           PARTITION RANGE ALL PARTITION: 1 21 (cr=0 pr=0 pw=0 time=0 us cost=53936 size=42159 card=897)

                                 0          0          0            TABLE ACCESS FULL CLSF_DATA PARTITION: 1 21 (cr=0 pr=0 pw=0 time=0 us cost=53936 size=42159 card=897)

                                 0          0          0       FILTER  (cr=0 pr=0 pw=0 time=22 us)

                                 0          0          0        HASH GROUP BY (cr=0 pr=0 pw=0 time=22 us cost=1 size=57 card=1)

                                 0          0          0         FILTER  (cr=0 pr=0 pw=0 time=1 us)

                                 0          0          0          HASH JOIN  (cr=0 pr=0 pw=0 time=0 us cost=14 size=57 card=1)

                                 0          0          0           HASH JOIN  (cr=0 pr=0 pw=0 time=0 us cost=7 size=2691 card=117)

                                 0          0          0            TABLE ACCESS FULL ME_FUND_ACC (cr=0 pr=0 pw=0 time=0 us cost=3 size=322 card=23)

                                 0          0          0            TABLE ACCESS FULL ME_MJR_HEAD_ACC (cr=0 pr=0 pw=0 time=0 us cost=3 size=4257 card=473)

                                 0          0          0           TABLE ACCESS FULL CLSF_HB_DATA1 (cr=0 pr=0 pw=0 time=0 us cost=7 size=34 card=1)

                                 0          0          0       FILTER  (cr=0 pr=0 pw=0 time=24 us)

                                 0          0          0        HASH GROUP BY (cr=0 pr=0 pw=0 time=24 us cost=1 size=70 card=1)

                                 0          0          0         FILTER  (cr=0 pr=0 pw=0 time=1 us)

                                 0          0          0          HASH JOIN  (cr=0 pr=0 pw=0 time=0 us cost=54002 size=9590 card=137)

                                 0          0          0           HASH JOIN  (cr=0 pr=0 pw=0 time=0 us cost=7 size=1863 card=81)

                                 0          0          0            TABLE ACCESS FULL ME_FUND_ACC (cr=0 pr=0 pw=0 time=0 us cost=3 size=224 card=16)

                                 0          0          0            TABLE ACCESS FULL ME_MJR_HEAD_ACC (cr=0 pr=0 pw=0 time=0 us cost=3 size=4257 card=473)

                                 0          0          0           PARTITION RANGE ALL PARTITION: 1 21 (cr=0 pr=0 pw=0 time=0 us cost=53995 size=42112 card=896)

                                 0          0          0            TABLE ACCESS FULL CLSF_DATA PARTITION: 1 21 (cr=0 pr=0 pw=0 time=0 us cost=53995 size=42112 card=896)

                                 0          0          0       FILTER  (cr=0 pr=0 pw=0 time=23 us)

                                 0          0          0        HASH GROUP BY (cr=0 pr=0 pw=0 time=22 us cost=1 size=57 card=1)

                                 0          0          0         FILTER  (cr=0 pr=0 pw=0 time=1 us)

                                 0          0          0          HASH JOIN  (cr=0 pr=0 pw=0 time=0 us cost=14 size=57 card=1)

                                 0          0          0           HASH JOIN  (cr=0 pr=0 pw=0 time=0 us cost=7 size=1863 card=81)

                                 0          0          0            TABLE ACCESS FULL ME_FUND_ACC (cr=0 pr=0 pw=0 time=0 us cost=3 size=224 card=16)

                                 0          0          0            TABLE ACCESS FULL ME_MJR_HEAD_ACC (cr=0 pr=0 pw=0 time=0 us cost=3 size=4257 card=473)

                                 0          0          0           TABLE ACCESS FULL CLSF_HB_DATA1 (cr=0 pr=0 pw=0 time=0 us cost=7 size=34 card=1)

                                 0          0          0       FILTER  (cr=0 pr=0 pw=0 time=25 us)

                                 0          0          0        HASH GROUP BY (cr=0 pr=0 pw=0 time=24 us cost=1 size=136 card=1)

                                 0          0          0         FILTER  (cr=0 pr=0 pw=0 time=1 us)

                                 0          0          0          HASH JOIN  (cr=0 pr=0 pw=0 time=0 us cost=54 size=21896 card=161)

                                 0          0          0           TABLE ACCESS FULL ME_FUND_ACC (cr=0 pr=0 pw=0 time=0 us cost=3 size=1428 card=102)

                                 0          0          0           HASH JOIN  (cr=0 pr=0 pw=0 time=0 us cost=50 size=19642 card=161)

                                 0          0          0            TABLE ACCESS FULL ME_MJR_HEAD_ACC (cr=0 pr=0 pw=0 time=0 us cost=3 size=4257 card=473)

                                 0          0          0            VIEW  (cr=0 pr=0 pw=0 time=0 us cost=47 size=18193 card=161)

                                 0          0          0             SORT UNIQUE (cr=0 pr=0 pw=0 time=0 us cost=47 size=9177 card=161)

                                 0          0          0              UNION-ALL  (cr=0 pr=0 pw=0 time=0 us)

                                 0          0          0               TABLE ACCESS FULL FA_SAL_SUBSIDY (cr=0 pr=0 pw=0 time=0 us cost=22 size=1311 card=23)

                                 0          0          0               TABLE ACCESS FULL FA_SAL_SUBSIDY (cr=0 pr=0 pw=0 time=0 us cost=22 size=7866 card=138)

                                 0          0          0       FILTER  (cr=0 pr=0 pw=0 time=25 us)

                                 0          0          0        HASH GROUP BY (cr=0 pr=0 pw=0 time=25 us cost=1 size=80 card=1)

                                 0          0          0         FILTER  (cr=0 pr=0 pw=0 time=0 us)

                                 0          0          0          HASH JOIN  (cr=0 pr=0 pw=0 time=0 us cost=29 size=67680 card=846)

                                 0          0          0           HASH JOIN  (cr=0 pr=0 pw=0 time=0 us cost=7 size=4531 card=197)

                                 0          0          0            TABLE ACCESS FULL ME_FUND_ACC (cr=0 pr=0 pw=0 time=0 us cost=3 size=1428 card=102)

                                 0          0          0            TABLE ACCESS FULL ME_MJR_HEAD_ACC (cr=0 pr=0 pw=0 time=0 us cost=3 size=1773 card=197)

                                 0          0          0           TABLE ACCESS FULL FA_SAL_SUBSIDY (cr=0 pr=0 pw=0 time=0 us cost=22 size=47994 card=842)

                         

                         

                        ********************************************************************************

                         

                         

                        SQL ID: 9zshcv60xux34 Plan Hash: 0

                         

                         

                        INSERT INTO sql$ (signature, inuse_features, flags, spare1, spare2)          

                        VALUES

                        (:1, :2, :3, null, null)

                         

                         

                         

                         

                        call     count       cpu    elapsed       disk      query    current        rows

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

                        Parse    17847      0.28       0.29          0          0          0           0

                        Execute  17847      3.00       5.05         88      17847     108145       17847

                        Fetch        0      0.00       0.00          0          0          0           0

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

                        total    35694      3.29       5.34         88      17847     108145       17847

                         

                         

                        Misses in library cache during parse: 1

                        Misses in library cache during execute: 1

                        Optimizer mode: RULE

                        Parsing user id: SYS   (recursive depth: 2)

                        Number of plan statistics captured: 16

                        • 9. Re: Process execution take too much time
                          Vikas0204

                          Please suggest me ..... What should i do...

                          • 10. Re: Process execution take too much time
                            Vikas0204

                            Please help me................

                             

                            Regards

                            Vikas

                            • 11. Re: Process execution take too much time
                              Top.Gun

                              3 days ago I asked:

                              "

                              So what is the SQL in the view?

                              What tables are in the view?

                              What index keys are against the tables in the view?

                              "

                               

                              I'm still waiting....

                              • 12. Re: Process execution take too much time
                                Vikas0204

                                This is an oracle bug

                                 

                                I have applied patch for that.

                                • 13. Re: Process execution take too much time
                                  Vikas0204

                                  This is ORA Bug# 11719151