0 Replies Latest reply: Sep 25, 2013 2:15 AM by Harsh_v RSS

    huge inactive session

    Harsh_v

      SQL> select index_name,column_name,COLUMN_POSITION  from user_ind_columns  where  table_name='LOAN_RECOVERY_DETAILS' order by 1;

       

       

      INDEX_NAME                     COLUMN_NAME          COLUMN_POSITION

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

      DATE_LOANRECO1                 RECOVERYDATE                       1

      LOAN_RECO_MSISDN1              MSISDN                             1

      TID_INDX_RECO1                 TID                                1

       

       

       

       

       

       

       

      explain plan for select count(1),'LOAN_RECO_CNT',sum(RECOVERYAMNT),'LOAN_RECO_AMT',CIRCLE from loan_recovery_details where RECOVERYDATE>=to_date('2013-09-18','yyyy-mm-dd') group by CIRCLE;

       

       

      SQL>  @?/rdbms/admin/utlxpls.sql

       

       

      PLAN_TABLE_OUTPUT

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

      Plan hash value: 418663161

       

       

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

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

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

      |   0 | SELECT STATEMENT          |                       |    23 |   391 | 53579   (1)| 00:10:43 |       |       |

      |   1 |  HASH GROUP BY            |                       |    23 |   391 | 53579   (1)| 00:10:43 |       |       |

      |   2 |   PARTITION RANGE ITERATOR|                       |  7195K|   116M| 53346   (1)| 00:10:41 |    29 |    36 |

      |*  3 |    TABLE ACCESS FULL      | LOAN_RECOVERY_DETAILS |  7195K|   116M| 53346   (1)| 00:10:41 |    29 |    36 |

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

       

       

       

       

      PLAN_TABLE_OUTPUT

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

      Predicate Information (identified by operation id):

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

       

       

         3 - filter("RECOVERYDATE" IS NOT NULL)

       

       

      15 rows selected.