Skip to Main Content
  • Questions

  • Not able to get A-TIME and A-ROW in explain plan

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question.

Asked: August 17, 2021 - 1:44 am UTC

Last updated: August 18, 2021 - 5:57 am UTC

Version: 12C

Viewed 1000+ times

You Asked

Hi Gurus,

I encountered a issue when generating sql plan.
I tried "
ALTER SESSION SET STATISTICS_LEVEL=ALL;
" and using "
/*+ GATHER_PLAN_STATISTICS */
" hint
after running query, I run below statement, Not sure why the output doesn't show A-TIME and A-ROW. 
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(
  SQL_ID => 'SQL_ID',
  FORMAT => 'ALL ALLSTATS LAST')) T;


Please share you suggestion.

thanks in advance.

and Connor said...

We'll need to see your top to bottom test case, because that works fine 

SQL> ALTER SESSION SET STATISTICS_LEVEL=ALL;

Session altered.

SQL>
SQL> select /*+ GATHER_PLAN_STATISTICS */ * from scott.emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 09-DEC-82       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500                    30
      7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.

SQL>
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(
  2    FORMAT => 'ALL ALLSTATS LAST')) T;

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------
SQL_ID  9n0y4yp82wp0q, child number 0
-------------------------------------
select /*+ GATHER_PLAN_STATISTICS */ * from scott.emp

Plan hash value: 3956160932

-----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |
-----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |       |     3 (100)|          |     14 |00:00:00.01 |       7 |      6 |
|   1 |  TABLE ACCESS FULL| EMP  |      1 |     14 |   518 |     3   (0)| 00:00:01 |     14 |00:00:00.01 |       7 |      6 |
-----------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / EMP@SEL$1

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "EMP"."EMPNO"[NUMBER,22], "EMP"."ENAME"[VARCHAR2,10], "EMP"."JOB"[VARCHAR2,9], "EMP"."MGR"[NUMBER,22],
       "EMP"."HIREDATE"[DATE,7], "EMP"."SAL"[NUMBER,22], "EMP"."COMM"[NUMBER,22], "EMP"."DEPTNO"[NUMBER,22]


24 rows selected.


Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, August 17, 2021 - 12:46 pm UTC

thanks Connor for your quick response.
after further investigation, I found a weird thing. if the query return 0 record, then the plan output the A-Row and A-time information. and if I put rownum <10, it output the information as well. but if I put rownum < 10000, same query in same environment, it doesn't show the information. is this because of bug?
my oracle version is Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production



Connor McDonald
August 18, 2021 - 5:57 am UTC

Not something I've heard of, so we need a test case for that
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.