Skip to content

Commit 99ef520

Browse files
nigelbaylissnigelbayliss
committed
Hints
1 parent 3690826 commit 99ef520

File tree

10 files changed

+288
-0
lines changed

10 files changed

+288
-0
lines changed

‎optimizer/autonomous/hints/README.md

Lines changed: 21 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,21 @@
1+
This example demonstrates hint usage in ADWC.
2+
3+
Create a test user using the *user.sql* script.
4+
5+
To run the enture example:
6+
7+
* @tabs - Create test tables (note that it drops tables TABLE1 and TABLE2)
8+
* @q1 - The default query plan
9+
* @q2 - The query includes hints that are not obeyed
10+
* @q3 - The ALTER SESSION allows the optimizer to use hints
11+
12+
### DISCLAIMER
13+
14+
* These scripts are provided for educational purposes only.
15+
* They are NOT supported by Oracle World Wide Technical Support.
16+
* The scripts have been tested and they appear to work as intended.
17+
* You should always run scripts on a test instance.
18+
19+
### WARNING
20+
21+
* These scripts drop and create tables. For use on test databases.

‎optimizer/autonomous/hints/plan.sql

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,8 @@
1+
set linesize 200
2+
set tab off
3+
set pagesize 1000
4+
column plan_table_output format a180
5+
6+
SELECT *
7+
FROM table(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'TYPICAL'));
8+

‎optimizer/autonomous/hints/q1.lst

Lines changed: 59 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,59 @@
1+
SQL> @q1
2+
SQL> select sum(t1.num), sum(t2.num)
3+
2 from table1 t1
4+
3 join table2 t2 on (t1.id = t2.id);
5+
6+
SUM(T1.NUM) SUM(T2.NUM)
7+
----------- -----------
8+
1 10
9+
10+
SQL>
11+
SQL> @plan
12+
SQL> set linesize 200
13+
SQL> set tab off
14+
SQL> set pagesize 1000
15+
SQL> column plan_table_output format a180
16+
SQL>
17+
SQL> SELECT *
18+
2 FROM table(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'TYPICAL'));
19+
20+
PLAN_TABLE_OUTPUT
21+
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
22+
SQL_ID 60t3bw2rtn35d, child number 6
23+
-------------------------------------
24+
select sum(t1.num), sum(t2.num) from table1 t1 join table2 t2 on
25+
(t1.id = t2.id)
26+
27+
Plan hash value: 339338377
28+
29+
---------------------------------------------------------------------------------------------------
30+
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
31+
---------------------------------------------------------------------------------------------------
32+
| 0 | SELECT STATEMENT | | | | 4 (100)| |
33+
| 1 | RESULT CACHE | 86m6ud7jmfq443pumuj63z1bmd | | | | |
34+
| 2 | SORT AGGREGATE | | 1 | 52 | | |
35+
|* 3 | HASH JOIN | | 1 | 52 | 4 (0)| 00:00:01 |
36+
| 4 | TABLE ACCESS FULL| TABLE2 | 1 | 26 | 2 (0)| 00:00:01 |
37+
| 5 | TABLE ACCESS FULL| TABLE1 | 1000 | 26000 | 2 (0)| 00:00:01 |
38+
---------------------------------------------------------------------------------------------------
39+
40+
Predicate Information (identified by operation id):
41+
---------------------------------------------------
42+
43+
3 - access("T1"."ID"="T2"."ID")
44+
45+
Result Cache Information (identified by operation id):
46+
------------------------------------------------------
47+
48+
1 -
49+
50+
Note
51+
-----
52+
- dynamic statistics used: dynamic sampling (level=2)
53+
- automatic DOP: Computed Degree of Parallelism is 1 because of no expensive parallel operation
54+
55+
56+
33 rows selected.
57+
58+
SQL>
59+
SQL> spool off

‎optimizer/autonomous/hints/q1.sql

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,5 @@
1+
select sum(t1.num), sum(t2.num)
2+
from table1 t1
3+
join table2 t2 on (t1.id = t2.id);
4+
5+
@plan

‎optimizer/autonomous/hints/q2.lst

Lines changed: 60 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,60 @@
1+
SQL> @q2
2+
SQL> select /*+ LEADING(t1 t2) USE_NL(t2) */
3+
2 sum(t1.num), sum(t2.num)
4+
3 from table1 t1
5+
4 join table2 t2 on (t1.id = t2.id);
6+
7+
SUM(T1.NUM) SUM(T2.NUM)
8+
----------- -----------
9+
1 10
10+
11+
SQL>
12+
SQL> @plan
13+
SQL> set linesize 200
14+
SQL> set tab off
15+
SQL> set pagesize 1000
16+
SQL> column plan_table_output format a180
17+
SQL>
18+
SQL> SELECT *
19+
2 FROM table(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'TYPICAL'));
20+
21+
PLAN_TABLE_OUTPUT
22+
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
23+
SQL_ID 1c899cktncv5m, child number 0
24+
-------------------------------------
25+
select /*+ LEADING(t1 t2) USE_NL(t2) */ sum(t1.num), sum(t2.num)
26+
from table1 t1 join table2 t2 on (t1.id = t2.id)
27+
28+
Plan hash value: 339338377
29+
30+
---------------------------------------------------------------------------------------------------
31+
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
32+
---------------------------------------------------------------------------------------------------
33+
| 0 | SELECT STATEMENT | | | | 4 (100)| |
34+
| 1 | RESULT CACHE | 86m6ud7jmfq443pumuj63z1bmd | | | | |
35+
| 2 | SORT AGGREGATE | | 1 | 52 | | |
36+
|* 3 | HASH JOIN | | 1 | 52 | 4 (0)| 00:00:01 |
37+
| 4 | TABLE ACCESS FULL| TABLE2 | 1 | 26 | 2 (0)| 00:00:01 |
38+
| 5 | TABLE ACCESS FULL| TABLE1 | 1000 | 26000 | 2 (0)| 00:00:01 |
39+
---------------------------------------------------------------------------------------------------
40+
41+
Predicate Information (identified by operation id):
42+
---------------------------------------------------
43+
44+
3 - access("T1"."ID"="T2"."ID")
45+
46+
Result Cache Information (identified by operation id):
47+
------------------------------------------------------
48+
49+
1 -
50+
51+
Note
52+
-----
53+
- dynamic statistics used: dynamic sampling (level=2)
54+
- automatic DOP: Computed Degree of Parallelism is 1 because of no expensive parallel operation
55+
56+
57+
33 rows selected.
58+
59+
SQL>
60+
SQL> spool off

‎optimizer/autonomous/hints/q2.sql

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,6 @@
1+
select /*+ LEADING(t1 t2) USE_NL(t2) */
2+
sum(t1.num), sum(t2.num)
3+
from table1 t1
4+
join table2 t2 on (t1.id = t2.id);
5+
6+
@plan

0 commit comments

Comments
 (0)