3 Replies Latest reply: Jan 13, 2013 11:49 PM by AnkitV RSS

    TIME column not visible in PLAN_TABLE output

    AnkitV
      Hi

      I got following output after executing --> SELECT plan_table_output FROM TABLE( DBMS_XPLAN.DISPLAY());

      -----------------------------------------------------------------------------
      | Id | Operation | Name | Rows | Bytes | Cost |
      -----------------------------------------------------------------------------
      | 0 | SELECT STATEMENT | | 1 | 7 | 18 |
      | 1 | SORT UNIQUE | | 1 | 7 | 18 |
      | 2 | VIEW | MTC EXCPN COMPANY V | 1 | 7 | 12 |
      | 3 | SORT ORDER BY | | 1 | 91 | 12 |
      |* 4 | TABLE ACCESS FULL| MTC_CD_LKUP | 1 | 91 | 6 |
      -----------------------------------------------------------------------------

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

      4 - filter ***************

      Note: cpu costing is off


      I am not getting TIME column as following (below o/p is from a different database):

      ----------------------------------------------------------------------------------
      | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
      ----------------------------------------------------------------------------------
      | 0 | SELECT STATEMENT | | 1 | 36 | 6 (17)| 00:00:01 |
      | 1 | HASH UNIQUE | | 1 | 36 | 6 (17)| 00:00:01 |
      |* 2 | TABLE ACCESS FULL| MTC_CD_LKUP | 1 | 36 | 5 (0)| 00:00:01 |
      ----------------------------------------------------------------------------------

      1) I believe the total of values in TIME column shows total time that will be taken by query. Please correct me if I am wrong.
      2) Please tell me how can I get TIME also in first case. Is is because of "cpu costing is off" ?


      Select * from v$version;
      -------------------------------------
      Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
      PL/SQL Release 9.2.0.8.0 - Production
      "CORE     9.2.0.8.0     Production"
      TNS for IBM/AIX RISC System/6000: Version 9.2.0.8.0 - Production
      NLSRTL Version 9.2.0.8.0 - Production

      Thanks a lot in advance.