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;
That is a good one Kerry!!
Please correct me if I didnt use the correct words … but to be direct it is:
– APPEND skips HWM;
– NOLOGGING skips redolog online entries;
– PARALLEL executes in more threads, in this case 4 when involves the table orders_tab.
Alex
Hi Alex,
That’s what the developer wanted it to do but it didn’t work out as planned. Look a little closer at the syntax. 😉
Kerry
Hi Kerry,
unfortunately, hints do not support “,” seperators. Additionally, NOLOGGING is not a valid hint. Moreover, hints following a non-valid character, like “,”, are ignored anyway.
A good way to find out why a hint did not work is to check tail of the 10053 trace file:
End of Optimizer State Dump
Dumping Hints
=============
atom_hint=(@=0xcbbd2160 err=0 resol=0 used=1 token=814 org=1 lvl=1 txt=APPEND ())
====================== END SQL Statement Dump ======================
This shows that only the append hint did make it into the trace file.
Best regards,
Martin
PS: Looking forward to reading your exadata book…
Hi Kerry,
Now you’ve got me lost, because I performed a simple test creating a table with 5.242.880 rows.
Using this way: insert /*+ append, nologging, parallel(CUSTOMERB, 2) */ INTO CUSTOMERB…
it performed in 5.9sec
Using this way: insert /*+ append nologging parallel(CUSTOMERB, 2) */ INTO CUSTOMERB…
it performed in 11.3sec
And using this way: insert INTO CUSTOMERB…
it performed over 20sec
It makes me think that really works the hints with “,”. Am I right?
Later I will performance a trace with 10046 to convince myself!! hehe
Thank you.
Alex,
The comment will definitely keep the trailing hints from being evaluated (well actually I only tested with 10.2.0.4 and 11.2.02). Trace should show you that. You might want to try a simpler case for testing. Pick any two hints you can verify easily from the plan and use them in your testing.
Kerry
Hi Martin,
Nice job. The comma is precisely the issue. And as you have shown, the append hint is the only one that is used. I’m not sure I would call the comma an invalid character though. It’s actually more like a “reserved word”. (I used quotes because punctuation is not really a word) But the documentation says that you can intermingle valid hints and comments. So why aren’t the commas just treated as “comment” text? Unfortunately, if you happen to use a piece of text that the optimizer recognizes in your comment it will stop the evaluation of the hint at that point. Try the following two examples to see what I mean:
Note: you can verify whether the gather_plan_statistics hint got picked up using XPLAN with the ALLSTATS option like so:
select * from table(dbms_xplan.display_cursor('&sql_id','&child_no','allstats'));Of course you’ll need to find the sql_id first. 😉
Hi Kerry,
IMHO, need to make some considerations:
create table t (x integer, y varchar2(10));
select value
from v$mystat s , v$statname n
where n.name = ‘DML statements parallelized’
and s.statistic# = n.statistic#;
VALUE
———-
0
settembrino@11.2>insert /*+ append, nologging, parallel(t1) */ into t t1 select /*+ nologging, parallel(t2) */ rownum, ‘a’ from dual t2 connect by level select count(*) from t;
select count(*) from t
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel
At this point I only know that the append hint was considered, but I can not
say if the other two hints have been considered
/ * + NOLOGGING * / hint however, has no effect
settembrino@11.2>select log_mode from v$database;
LOG_MODE
————
NOARCHIVELOG
set autotrace traceonly statistics;
settembrino@11.2>truncate table t ;
Table truncated.
settembrino@11.2>set autotrace traceonly statistics;
settembrino@11.2>insert /*+ append nologging */ into t t1 select rownum, ‘a’ from dual t2 connect by level truncate table t ;
Table truncated.
settembrino@11.2>insert /*+ append */ into t t1 select rownum, ‘a’ from dual t2 connect by level select value
2 from v$mystat s , v$statname n
3 where n.name = ‘DML statements parallelized’
4 and s.statistic# = n.statistic#;
VALUE
———-
0
settembrino@11.2>insert /*+ nologging, parallel(t1) */ into t t1 select /*+ nologging, parallel(t2) */ rownum, ‘a’ from dual t2 connect by level select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
——————————————————————————————————————————————————————
SQL_ID 34bc484swsf2x, child number 0
————————————-
insert /*+ nologging, parallel(t1) */ into t t1 select /*+ nologging,
parallel(t2) */ rownum, ‘a’ from dual t2 connect by level select value
2 from v$mystat s , v$statname n
3 where n.name = ‘DML statements parallelized’
4 and s.statistic# = n.statistic#;
VALUE
———-
0
that would seem true, but in this case the parallel hint is not considered for another reason, not a comma which is after NOLOGGING.
In fact, if I check the degree of parallelism on the table t:
settembrino@11.2>select degree from user_tables where table_name = ‘T’;
DEGREE
———-
1
settembrino@11.2>truncate table t ;
Now, if I enable parallel dml:
settembrino@11.2>alter table t parallel(degree 8) ;
Table altered.
settembrino@11.2>alter session enable parallel dml;
Session altered.
captures the statistics before running the insert
settembrino@11.2> select value
2 from v$mystat s , v$statname n
3 where n.name = ‘DML statements parallelized’
4 and s.statistic# = n.statistic#;
VALUE
———-
0
settembrino@11.2>insert /*+ nologging, parallel(t1) */ into t t1 select /*+ nologging, parallel(t2) */ rownum, ‘a’ from dual t2 connect by level select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
—————————————————————————————————————————————————————-
SQL_ID 34bc484swsf2x, child number 0
————————————-
insert /*+ nologging, parallel(t1) */ into t t1 select /*+ nologging,
parallel(t2) */ rownum, ‘a’ from dual t2 connect by level S | QC (RAND) |
| 3 | LOAD AS SELECT | | | | | Q1,01 | PCWP | |
| 4 | PX RECEIVE | | 1 | 2 (0)| 00:00:01 | Q1,01 | PCWP | |
| 5 | PX SEND ROUND-ROBIN | :TQ10000 | 1 | 2 (0)| 00:00:01 | | S->P | RND-ROBIN |
| 6 | COUNT | | | | | | | |
| 7 | CONNECT BY WITHOUT FILTERING| | | | | | | |
| 8 | FAST DUAL | | 1 | 2 (0)| 00:00:01 | | | |
——————————————————————————————————————–
settembrino@11.2> select value
2 from v$mystat s , v$statname n
3 where n.name = ‘DML statements parallelized’
4 and s.statistic# = n.statistic#;
VALUE
———-
1
the parallel DML was executed even if the hint, I added “NOLOGGING,”.
So I think the correct answer to your question about what happens (considering my comments) is as follows:
settembrino@11.2> select value
2 from v$mystat s , v$statname n
3 where n.name = ‘DML statements parallelized’
4 and s.statistic# = n.statistic#;
VALUE
———-
1
settembrino@11.2>insert /*+ append, nologging, parallel(t1) */ into t t1 select /*+ nologging, parallel(t2) */ rownum, ‘a’ from dual t2 connect by level select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
————————————————————————————————————————————————————————–
SQL_ID 506ugu72pdbtb, child number 0
————————————-
insert /*+ append, nologging, parallel(t1) */ into t t1 select /*+
nologging, parallel(t2) */ rownum, ‘a’ from dual t2 connect by level
S | QC (RAND) |
| 3 | LOAD AS SELECT | | | | | Q1,01 | PCWP | |
| 4 | PX RECEIVE | | 1 | 2 (0)| 00:00:01 | Q1,01 | PCWP | |
| 5 | PX SEND ROUND-ROBIN | :TQ10000 | 1 | 2 (0)| 00:00:01 | | S->P | RND-ROBIN |
| 6 | COUNT | | | | | | | |
| 7 | CONNECT BY WITHOUT FILTERING| | | | | | | |
| 8 | FAST DUAL | | 1 | 2 (0)| 00:00:01 | | | |
——————————————————————————————————————–
22 rows selected.
Elapsed: 00:00:00.23
settembrino@11.2> select value
2 from v$mystat s , v$statname n
3 where n.name = ‘DML statements parallelized’
4 and s.statistic# = n.statistic#;
VALUE
———-
2
settembrino@11.2>select count(*) from t;
select count(*) from t
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel
are considered the parallel and append hint, the hint NOLOGGING is ignored
the comma (“,”) in the section of the hint is ignored …
at least I think 🙂
Regards,
Donatello Settembrino
Hi Donatello,
I’d suggest trying a simpler test. I have updated the post with an easier to verify example.
Kerry
A number of single-character words are also reserved words. I got bitten by this when I used A, B, C, D, etc in a hint/comment section to differentiate between different test runs of a SQL. A, D, G, E, H and multiple more are reserved words in Oracle (v$reserved_words). And as Kerry said, there’s an issue that Oracle ignores hints which come after a reserved word (which is not a hint) in the hint section.
It’s also documented here:
Note 826893.1: Invalid Hint in 10g Can Cause Other Hints To Be Ignored, Including Parallel Hints