The following is a (short and quick) scenario I recently faced when analyzing a parallel query and its response time. This may not apply to earlier versions of Oracle though. The experiments were carried out in 19c.
The ‘weird’ behavior I observed was that V$SQL.EXECUTIONS column didn’t get updated for the query under analysis when executed. The first gotcha is, perhaps, “That must be a bug“. Let’s be more systematic however, and arrive at the root cause gradually.
Here is a test case. First, I am simply executing the query three times and checking the v$sql.
SQL> select sql_id, sql_text, executions, child_number from v$sql where sql_text like '%test1%';
SQL_ID SQL_TEXT EXECUTIONS CHILD_NUMBER
------------- --------------------------------- ---------- ------------
4gy00kap2kf3n select /*+ test1 */ * from table1 3 0
4gy00kap2kf3n select /*+ test1 */ * from table1 0 1
2 rows selected.
SQL> The output shows that the database decided to generate another child (CHILD_NUMBER=1) for this statement. However, the primary question here is “Why are there NO executions for the second child?“.
The reason for not sharing that can be found from v$sql_shared_cursor is not of interest, although it may shed some light on the question. Still, let’s take a different strategy without consulting this view.
Two approaches can be taken to move forward with this ‘issue’: a) Try A, try B, try C,… and hope it helps to find out the reason, and perhaps, eventually one of them will and b) Ask the right questions. Before blaming the database for bugs, lets put it aside as the last resort. There must be something about this that at this point not known yet and that may lead to the clue.
The simplest question one can ask here can be something like “Would there by any other statistics that applies to child cursors which has probably been missed out?” This is a separate child and, thus, the entry point should be v$sql. Scanning the column names of the view for ‘%EXEC’% reveals another field: PX_SERVERS_EXECUTIONS. Let’s query this column as well:
SQL> select sql_id, sql_text, executions, px_servers_executions, child_number from v$sql where sql_text like '%test1%';
SQL_ID SQL_TEXT EXECUTIONS PX_SERVERS_EXECUTIONS CHILD_NUMBER
------------- ---------------------------------- ---------- --------------------- ------------
4gy00kap2kf3n select /*+ test1 */ * from table1 3 0 0
4gy00kap2kf3n select /*+ test1 */ * from table1 0 12 1
2 rows selected.
SQL>
PX_SERVERS_EXECUTIONS holds the total number of executions by the slaves of a parallel query. This explains why the second cursor executions are not reflected in V$SQL.EXECUTIONS column. For parallel slaves Oracle tracks it in a different column: V$SQL.PX_SERVERS_EXECUTIONS and in this case it has been executed 12 times so far. Since the query does have any parallelism forced by the hint, hence the table-level parallelism must set which is confirmed by dba_tables.degree being equal to 4. The one with CHILD_NUMBER=0 is actually the coordinator. The query executes in parallel:
SQL> select * from dbms_xplan.display_cursor('4gy00kap2kf3n', 0, 'ALLSTATS ALL');
SQL_ID 4gy00kap2kf3n, child number 0
-------------------------------------
select /*+ test1 */ * from table1
Plan hash value: 1574891232
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 73216 | 9438K| 110 (0)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 73216 | 9438K| 110 (0)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 73216 | 9438K| 110 (0)| 00:00:01 | Q1,00 | PCWC | |
| 4 | TABLE ACCESS FULL| TABLE1 | 73216 | 9438K| 110 (0)| 00:00:01 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------------
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (E - Syntax error (1))
---------------------------------------------------------------------------
1 - SEL$1
E - test1
Note
-----
- Degree of Parallelism is 4 because of table property
28 rows selected.
SQL>Morale: Step-by-step approach is the key regardless of the issue and the DBMS.