When working with Oracle Database, especially in environments like Autonomous Database 26ai, it’s not just about running queries— it’s equally important to understand what happens behind the scenes.
In this article, we will:
- Run a simple query on the
EMPtable - Analyze the output
- Understand session-level statistics generated by Oracle
Step 1: Querying the EMP Table
Let’s start with a basic query:
SQL> SELECT * FROM emp;
Query Output
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
________ _________ __________ ____ __________ _____ _____ ______
7698 BLAKE MANAGER 7839 01-05-81 2850 30
7902 FORD ANALYST 7566 03-12-81 3000 20
7876 ADAMS CLERK 7788 23-05-87 1100 20
7566 JONES MANAGER 7839 02-04-81 2975 20
7369 SMITH CLERK 7902 17-12-80 800 20
7499 ALLEN SALESMAN 7698 20-02-81 1600 300 30
7900 JAMES CLERK 7698 03-12-81 950 30
7934 MILLER CLERK 7782 23-01-82 1300 10
7839 KING PRESIDENT 17-11-81 5000 10
7521 WARD SALESMAN 7698 22-02-81 1250 500 30
7844 TURNER SALESMAN 7698 08-09-81 1500 0 30
7782 CLARK MANAGER 7839 09-06-81 2450 10
7788 SCOTT ANALYST 7566 19-04-87 3000 20
7654 MARTIN SALESMAN 7698 28-09-81 1250 1400 30
14 rows selected.
This is a classic Oracle sample table showing employee details such as:
- EMPNO – Employee ID
- ENAME – Name
- JOB – Role
- SAL – Salary
- COMM – Commission (if applicable)
- DEPTNO – Department number
Step 2: Understanding SQL Execution Statistics
After executing the query, Oracle provides detailed session-level statistics that help us understand how the query was processed internally.
Statistics Output
Statistics
-----------------------------------------------------------
1 CPU used by this session
1 CPU used when call started
1 DB time
7337 RM usage by this session
3 Requests to/from client
8 SCN increments due to another database
2 SQL*Net roundtrips to/from client
296 bytes received via SQL*Net from client
65061 bytes sent via SQL*Net to client
2 calls to get snapshot scn: kcmgss
2 calls to kcmgcs
1 cursor authentications
2 execute count
2 global enqueue gets sync
2 global enqueue releases
8 non-idle wait count
2 opened cursors cumulative
1 opened cursors current
2 parse count (total)
9 process last non-idle time
1 redo scn array scans
2 session cursor cache count
3 user calls
Breaking Down the Key Statistics
Let’s simplify what these numbers actually mean:
1. CPU & DB Time
- CPU used by this session: Amount of CPU consumed
- DB time: Total time spent executing the query
👉 In our case, both values are very low, indicating a lightweight query.
2. SQL*Net Communication
- Roundtrips: Number of back-and-forth communications between client and database
- Bytes sent/received: Data transferred over the network
👉 65KB sent shows that result data (14 rows) was returned efficiently.
3. Parse and Execute
- Parse count: How many times SQL was parsed
- Execute count: Number of executions
👉 Ideally, parsing should be minimized for better performance.
4. Cursor Activity
- Opened cursors: SQL statements held in memory
- Session cursor cache: Helps reuse SQL statements
5. Wait Events
- Non-idle wait count: Times the session waited for resources
👉 Low wait counts = good performance.
6. SCN (System Change Number)
- SCN increments: Tracks database changes for consistency
👉 Important for read consistency and transaction management.
Why These Statistics Matter
Even for a simple SELECT query, Oracle provides deep insights into:
- Performance behavior
- Resource utilization
- Network overhead
- Execution efficiency
For developers and DBAs, these statistics are extremely useful when:
- Troubleshooting slow queries
- Optimizing SQL performance
- Understanding database workload
Conclusion
A simple query like SELECT * FROM emp may look trivial,
but Oracle Database captures a rich set of execution statistics behind the scenes.
Understanding these metrics helps you move from just writing SQL to mastering database performance and internals.
If you're exploring Oracle 26ai Autonomous Database, make it a habit to review these statistics—they reveal far more than just query results.
