Wherever our database resides, it is often the case that we want our developer to have access to performance tools in order to ensure that the SQL they write will not cause any issues when it arrives in Production. However, this can present a few challenges when it comes to a database that might not be a “development playground”. Sometimes the database that developers are using requires more stringent privilege control for non-administrative users, and that can be frustrating.
For example, using a 26ai database, lets create a typical developer account.
SQL> create user demo identified by ******************;
User created.
SQL> grant db_developer_role to demo;
Grant succeeded.
SQL> grant select on hr.employees to demo;
Grant succeeded.
If you’re unfamiliar with the DB_DEVELOPER_ROLE role, check out my video on that below:
Now lets connect as the DEMO user and try some of the typical command line facilities. I’ll start with SQL*Plus
SQL> conn demo/******************@my26ai
Connected.
SQL> explain plan for
2 select count(*) from hr.employees;
Explained.
SQL>
SQL> select * from dbms_xplan.display();
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------
Plan hash value: 3580537945
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FULL SCAN| EMP_EMAIL_UK | 107 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------
9 rows selected.
SQL> set autotrace traceonly explain
SQL> select count(*) from hr.employees;
Execution Plan
----------------------------------------------------------
Plan hash value: 3580537945
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FULL SCAN| EMP_EMAIL_UK | 107 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------
So far, so good. I can use the standard means of getting predicted execution plans for my SQL statements. Let’s try some runtime statistics.
SQL> set autotrace traceonly stat
SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report
This is the default for any Oracle database (on-prem or cloud) – access to autotrace is blocked. Luckily the error message tells us what to do. Digging around in $ORACLE_HOME/sqlplus/admin I can find the necessary privs for the PLUSTRACE role to run as an administrator.
SQL> create role plustrace;
Role created.
SQL>
SQL> grant select on v$sesstat to plustrace;
Grant succeeded.
SQL> grant select on v$statname to plustrace;
Grant succeeded.
SQL> grant select on v$mystat to plustrace;
Grant succeeded.
SQL> grant plustrace to demo;
Grant succeeded.
SQL> conn demo/******************@my26ai
Connected.
SQL> set autotrace traceonly stat
SQL> select count(*) from hr.employees;
Statistics
----------------------------------------------------------
15 recursive calls
0 db block gets
29 consistent gets
0 physical reads
0 redo size
315 bytes sent via SQL*Net to client
43 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
That is nice to have but a cautious DBA might not be keen on giving out privileges on V$SESSSTAT. After all, it allows viewing of statistics on all sessions not just your own. Personally, I’m not concerned about just statistics being exposed, but others may have differing opinions. Unfortunately if you try drop that privilege, because you would imagine that V$MYSTAT should be sufficient, the autotrace facility breaks:
SQL> set autotrace traceonly stat
SQL> select count(*) from hr.employees;
Error ORA-942 while gathering statistics
SP2-0612: Error generating AUTOTRACE STATISTIC SID BEFORE SNAPSHOT report
SP2-0612: Error generating AUTOTRACE STATISTIC SID AFTER SNAPSHOT report
SP2-0612: Error generating AUTOTRACE STATISTICS report
Even with the PLUSTRACE privilege enabled, things get worse if you want to use the GATHER_PLAN_STATISTICS hint in order to collect rowsource statistics with the runtime execution plan.
SQL> select /*+ gather_plan_statistics */ count(*) from hr.employees;
COUNT(*)
----------
107
1 row selected.
SQL> select * from dbms_xplan.display_cursor(format=>'ALLSTATS LAST');
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------
User has no SELECT privilege on V$SESSION
1 row selected.
Now the DBA has perhaps more reason for concern, because while giving access to V$SESSTAT is probably harmless, giving out access to V$SQL_PLAN , V$SESSION and V$SQL_PLAN_STATISTICS_ALL exposes a lot more information about other users in your database.
I’ll come back to SQL*Plus shortly, but lets take a look at SQLcl because it behaves slightly differently.
SQLcl offers a more comprehensive statistics analysis, because it uses the existence of deltas in the before/after execution statistics to determine what to display. For example, connected as the ADMIN user, the first occurrence of my HR.EMPLOYEES query shows a lot of stats:
SQLcl: Release 25.3 Production on Thu Dec 04 17:31:04 2025
Copyright (c) 1982, 2025, Oracle. All rights reserved.
Last Successful login time: Thu Dec 04 2025 17:31:04 +08:00
Connected to:
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.16.0.0.0
SQL> set autotrace on stat
Autotrace Enabled
Shows the execution plan as well as statistics of the statement.
SQL> select count(*) from hr.employees;
COUNT(*)
----------
107
Statistics
-----------------------------------------------------------
4 CCursor + sql area evicted
1 CPU used by this session
3 CPU used when call started
5 DB time
4 Requests to/from client
4 SQL*Net roundtrips to/from client
6 Session total flash IO requests
162 buffer is not pinned count
787 bytes received via SQL*Net from client
61456 bytes sent via SQL*Net to client
54 calls to get snapshot scn: kcmgss
3 calls to kcmgcs
49152 cell physical IO interconnect bytes
23 cluster key scan block gets
13 cluster key scans
187 consistent gets
69 consistent gets examination
69 consistent gets examination (fastpath)
187 consistent gets from cache
118 consistent gets pin
112 consistent gets pin (fastpath)
1 cursor authentications
2 cursor reload failures
5 enqueue releases
5 enqueue requests
48 execute count
918 file io wait time
6 free buffer requested
16 index fetch by key
33 index range scans
1531904 logical read bytes from cache
116 no work - consistent read gets
34 non-idle wait count
1 non-idle wait time
48 opened cursors cumulative
1 opened cursors current
2 parse count (hard)
6 parse count (total)
1 parse time cpu
2 parse time elapsed
6 physical read IO requests
49152 physical read bytes
6 physical read total IO requests
49152 physical read total bytes
6 physical reads
6 physical reads cache
109 recursive calls
1 recursive cpu usage
4 session cursor cache count
42 session cursor cache hits
187 session logical reads
6 shared hash latch upgrades - no wait
7 sorts (memory)
1565 sorts (rows)
6 sql area evicted
34 table fetch by rowid
7 table scan blocks gotten
416 table scan disk non-IMC rows gotten
416 table scan rows gotten
1 table scans (short tables)
5 user calls
But for a subsequent execution, the list of statistics displayed is lower, because the query has now been parsed and (in this simple case) the required blocks are likely all contained in the buffer cache.
As a result, many of the session statistics will not change with this execution.
SQL> select count(*) from hr.employees;
COUNT(*)
----------
107
Statistics
-----------------------------------------------------------
4 Requests to/from client
4 SQL*Net roundtrips to/from client
1 buffer is not pinned count
787 bytes received via SQL*Net from client
61495 bytes sent via SQL*Net to client
2 calls to get snapshot scn: kcmgss
2 calls to kcmgcs
1 consistent gets
1 consistent gets from cache
1 consistent gets pin
1 consistent gets pin (fastpath)
2 execute count
1 index range scans
8192 logical read bytes from cache
14 non-idle wait count
2 opened cursors cumulative
1 opened cursors current
2 parse count (total)
1 session logical reads
1 sorts (memory)
1545 sorts (rows)
5 user calls
SQL>
Unfortunately similar issues to SQL*Plus are seen in SQLcl when a non-administrative user is connected. SQLcl is slightly more restrictive in that any of the autotrace facilities require access to potentially sensitive dictionary views.
SQL> conn demo/******************@my26ai
Connected.
SQL> set autotrace traceonly explain
Autotrace TraceOnly
Exhibits the performance statistics with silent query output
SQL> select count(*) from hr.employees;
1 row selected.
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
User has no SELECT privilege on V$SQL
Unable to gather statistics please ensure user has correct access.
The statistic feature requires that the user is granted select on v_$sesstat, v_$statname and v_$session.
SQL>
SQL> set autotrace traceonly stat
Autotrace TraceOnly
Exhibits the performance statistics with silent query output
SQL> select count(*) from hr.employees;
1 row selected.
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
User has no SELECT privilege on V$SQL
Unable to gather statistics please ensure user has correct access.
The statistic feature requires that the user is granted select on v_$sesstat, v_$statname and v_$session.
SQL> set autotrace off
Autotrace Disabled
SQL> select /*+ gather_plan_statistics */ count(*) from hr.employees;
COUNT(*)
----------
107
SQL> select * from dbms_xplan.display_cursor(format=>'ALLSTATS LAST');
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
User has no SELECT privilege on V$SESSION
SQL>
Solutions
So if you’re a developer and your DBA (with reasonable justification) has decided that you should not have access to critical dictionary performance views, are you out of luck? Fortunately not, because I’d contend that when it comes SQL performance, the most valuable tool in a developer’s toolkit is the SQL Monitoring report. Long running SQL statements are automatically monitored, but you can also just add the MONITOR hint to any SQL if you want to diagnose it after execution with the SQL monitoring report.
You’ll need the SQL_ID for your statement, and both SQL*Plus and SQLcl let you access this with the SET FEEDBACK setting.
SQL> set feedback on sql_id
SQL> select /*+ gather_plan_statistics monitor */ count(*) from hr.employees;
COUNT(*)
----------
107
1 row selected.
SQL_ID: d8vqn5cfcy9pc
And now you generate the SQL monitoring report, which does not require any additional special privileges against the performance views.
SQL> select dbms_sqltune.report_sql_monitor(
2 sql_id=>'d8vqn5cfcy9pc',
3 type=>'TEXT',
4 report_level=>'ALL') report;
REPORT
--------------------------------------------------------------------------------------------------------------------------------
SQL Monitoring Report
SQL Text
------------------------------
select /*+ gather_plan_statistics monitor */ count(*) from hr.employees
Global Information
------------------------------
Status : DONE (ALL ROWS)
Instance ID : 1
Session : DEMO (35214:55315)
SQL ID : d8vqn5cfcy9pc
SQL Execution ID : 16777217
Execution Started : 12/02/2025 13:03:31
First Refresh Time : 12/02/2025 13:03:31
Last Refresh Time : 12/02/2025 13:03:31
Duration : .000063s
Module/Action : SQL*Plus/-
Service : my26ai_low.adb.oraclecloud.com
Program : sqlplus.exe
Fetch Calls : 1
Global Stats
======================================
| Elapsed | Cpu | Fetch | Buffer |
| Time(s) | Time(s) | Calls | Gets |
======================================
| 0.00 | 0.00 | 1 | 1 |
======================================
SQL Plan Monitoring Details (Plan Hash Value=3580537945)
================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | (%) | (# samples) |
================================================================================================================================
| 0 | SELECT STATEMENT | | | | 1 | +0 | 1 | 1 | | |
| 1 | SORT AGGREGATE | | 1 | | 1 | +0 | 1 | 1 | | |
| 2 | INDEX FULL SCAN | EMP_EMAIL_UK | 107 | 1 | 1 | +0 | 1 | 107 | | |
================================================================================================================================
1 row selected.
SQL_ID: az0tquava1dnm
SQL>
So if you can’t get access to the V$ views you need, check out SQL Monitor. There’s a good chance it will give you all the information you need to tune your SQL statements.
Footnotes
- Shout out a few people in the community who pointed out that DBMS_SQL_MONITOR is the preferred package instead of DBMS_SQLTUNE from 12c onwards.
- Jonathan Lewis pointed out that he got different results with his database. To clarify, I ran my tests above on Always Free Autonomous 26ai.
- And as always, ensure you use facilities that you are licensed for.
Learning is not a spectator sport 
Got some thoughts? Leave a comment