Weird Hint – Open Book Quiz
I ran into a slightly strange hint last week:
insert /*+ append, nologging, parallel(orders_tab, 4) */ …
Anyone know how this hint will behave right off the top of their head?
When I was in school we used to occasionally have quizzes where we were allowed to use our books. So feel free to use any documentation you can find on the topic.
Note: Be sure and see the comments on this one …
UPDATE 01-JUN-11: – Here’s a test case using two easy to verify hints
SYS@SANDBOX1> select /*+ monitor, GATHER_PLAN_STATISTICS */ * from dual
2 ;
D
-
X
Elapsed: 00:00:00.00
SYS@SANDBOX1> @fsx
Enter value for sql_text: select /*+ monitor, GATHER_PLAN_STATISTICS */ * from dual%
Enter value for sql_id:
SQL_ID CHILD PLAN_HASH EXECS AVG_ETIME AVG_PX OFFLOAD IO_SAVED_% SQL_TEXT
------------- ------ ---------- ------ ---------- ------ ------- ---------- ----------------------------------------------------------------------
d8yaqqyc0yb9k 0 272002086 1 .00 0 No .00 select /*+ monitor, GATHER_PLAN_STATISTICS */ * from dual
Elapsed: 00:00:00.04
SYS@SANDBOX1> select * from table(dbms_xplan.display_cursor('&sql_id','&child_no','allstats'));
Enter value for sql_id: d8yaqqyc0yb9k
Enter value for child_no:
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID d8yaqqyc0yb9k, child number 0
-------------------------------------
select /*+ monitor, GATHER_PLAN_STATISTICS */ * from dual
Plan hash value: 272002086
---------------------------------------------------
| Id | Operation | Name | E-Rows |
---------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | TABLE ACCESS STORAGE FULL| DUAL | 1 |
---------------------------------------------------
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.
Elapsed: 00:00:00.04
As you can see in the Note section, the GATHER_PLAN_STATISTICS hint was not obeyed (actually it wasn’t even evaluated). This is due to the fact that the parser quits evaluating the hint text when it hits the comma. Here’s the same statement with the comma removed.
SYS@SANDBOX1> select /*+ monitor GATHER_PLAN_STATISTICS */ * from dual;
D
-
X
Elapsed: 00:00:00.01
SYS@SANDBOX1> @fsx
Enter value for sql_text: select /*+ monitor GATHER_PLAN_STATISTICS */ * from dual
Enter value for sql_id:
SQL_ID CHILD PLAN_HASH EXECS AVG_ETIME AVG_PX OFFLOAD IO_SAVED_% SQL_TEXT
------------- ------ ---------- ------ ---------- ------ ------- ---------- ----------------------------------------------------------------------
1scryy04ggv60 0 272002086 1 .00 0 No .00 select /*+ monitor GATHER_PLAN_STATISTICS */ * from dual
Elapsed: 00:00:00.19
SYS@SANDBOX1> select * from table(dbms_xplan.display_cursor('&sql_id','&child_no','allstats'));
Enter value for sql_id: 1scryy04ggv60
Enter value for child_no:
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 1scryy04ggv60, child number 0
-------------------------------------
select /*+ monitor GATHER_PLAN_STATISTICS */ * from dual
Plan hash value: 272002086
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 2 | 2 |
| 1 | TABLE ACCESS STORAGE FULL| DUAL | 1 | 1 | 1 |00:00:00.01 | 2 | 2 |
-----------------------------------------------------------------------------------------------------
13 rows selected.
Elapsed: 00:00:00.08
Notice that this time the GATHER_PLAN_STATISTICS hint was obeyed and so the xplan output has the A-Rows column and there is no Note section complaining about missing plan statistics. The documentation states that comment text can be interspersed with valid hints though. So why isn’t the comma treated as comment text. Well apparently it is a reserved word. Oddly enough, the word COMMENT is a reserved word as well. Watch this:
SYS@SANDBOX1> select /*+ monitor COMMENT GATHER_PLAN_STATISTICS */ * from dual;
D
-
X
Elapsed: 00:00:00.00
SYS@SANDBOX1> @fsx
Enter value for sql_text: select /*+ monitor COMMENT GATHER_PLAN_STATISTICS */ * from dual
Enter value for sql_id:
SQL_ID CHILD PLAN_HASH EXECS AVG_ETIME AVG_PX OFFLOAD IO_SAVED_% SQL_TEXT
------------- ------ ---------- ------ ---------- ------ ------- ---------- ----------------------------------------------------------------------
8dxup58bgaxsy 0 272002086 2 .02 0 No .00 select /*+ monitor COMMENT GATHER_PLAN_STATISTICS */ * from dual
Elapsed: 00:00:00.03
SYS@SANDBOX1> @dplan_allstats
Enter value for sql_id: 8dxup58bgaxsy
Enter value for child_no:
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 8dxup58bgaxsy, child number 0
-------------------------------------
select /*+ monitor COMMENT GATHER_PLAN_STATISTICS */ * from dual
Plan hash value: 272002086
---------------------------------------------------
| Id | Operation | Name | E-Rows |
---------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | TABLE ACCESS STORAGE FULL| DUAL | 1 |
---------------------------------------------------
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.
Elapsed: 00:00:00.03
So the moral is don’t put comment text in your hints. If you must have a comment in your SQL, put it in a separate comment structure like so:
select /*+ monitor GATHER_PLAN_STATISTICS */ /* This is a comment */ * from dual;
And if you really feel you need commas between your hints try something like this:
select /*+ monitor"," GATHER_PLAN_STATISTICS */ * from dual;