Enable GATHER_PLAN_STATISTICS by default for every query that is run

user_3536523 0
Viewed 73

I want to enable the hint GATHER_PLAN_STATISTICS for all queries that are run and then disable it once I do not need anymore.

I tried setting the session variable statistics_level to all like below:

alter session set statistics_level = all;

& I also tried:

alter system set statistics_level=all;

But neither worked. I tried using the SYS account and the normal user account but still did not work. Is there any additional things I need to do after running these queries?

EDIT: This is how I invoke the DISPLAY_CURSOR function:

SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(sql_id=>'1srsmak7r2gp1', format=>'ALLSTATS LAST +cost'));
oracle
1 Answers

Setting the level to ALL does work, but it is still your responsibility to query the database with the correct format parameters to dbms_xplan to get the output you want, eg

SQL> select * from 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> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------
SQL_ID  a2dk8bdn0ujx7, child number 1
-------------------------------------
select * from emp

Plan hash value: 3956160932

-------------------------------------------
| Id  | Operation         | Name | E-Rows |
-------------------------------------------
|   0 | SELECT STATEMENT  |      |        |
|   1 |  TABLE ACCESS FULL| EMP  |     14 |
-------------------------------------------

Note
-----
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level


19 rows selected.

SQL> alter session set statistics_level = all;

Session altered.

SQL> select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE          SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17/12/1980        800                    20
      7499 ALLEN      SALESMAN        7698 20/02/1981       1600        300         30
      7521 WARD       SALESMAN        7698 22/02/1981       1250        500         30
      7566 JONES      MANAGER         7839 02/04/1981       2975                    20
      7654 MARTIN     SALESMAN        7698 28/09/1981       1250       1400         30
      7698 BLAKE      MANAGER         7839 01/05/1981       2850                    30
      7782 CLARK      MANAGER         7839 09/06/1981       2450                    10
      7788 SCOTT      ANALYST         7566 09/12/1982       3000                    20
      7839 KING       PRESIDENT            17/11/1981       5000                    10
      7844 TURNER     SALESMAN        7698 08/09/1981       1500                    30
      7876 ADAMS      CLERK           7788 12/01/1983       1100                    20
      7900 JAMES      CLERK           7698 03/12/1981        950                    30
      7902 FORD       ANALYST         7566 03/12/1981       3000                    20
      7934 MILLER     CLERK           7782 23/01/1982       1300                    10

14 rows selected.

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------
SQL_ID  a2dk8bdn0ujx7, child number 0
-------------------------------------
select * from emp

Plan hash value: 3956160932

---------------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |     14 |00:00:00.01 |       7 |      6 |
|   1 |  TABLE ACCESS FULL| EMP  |      1 |     14 |     14 |00:00:00.01 |       7 |      6 |
---------------------------------------------------------------------------------------------
user_73672930
Related