Understanding SQL Query Output and Session Statistics in Oracle Database 26ai

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 EMP table
• 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.