Let's create a scenario. Firstly, create 2 tables and 1 index in each one.
sqlplus system
SQL*Plus: Release 11.2.0.3.0 Production on Sun Sep 15 20:32:10 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> CREATE TABLE SYSTEM.OBJECTS AS SELECT * FROM DBA_OBJECTS;
Table created.
SQL> CREATE INDEX SYSTEM.OBJ_NAME ON SYSTEM.OBJECTS(OBJECT_NAME);
Index created.
SQL> CREATE TABLE SYSTEM.SEGMENTS AS SELECT * FROM DBA_SEGMENTS;
Table created.
SQL> CREATE INDEX SYSTEM.SEG_NAME ON SYSTEM.SEGMENTS(SEGMENT_NAME);
Index created.We will use a query that joins those tables using the indexed columns.
SQL> SET AUTOT TRACEONLY
SQL> SET TIMING ON
SQL> SET LINESIZE 200
SQL> SELECT OBJECT_ID,
OBJECT_NAME,
SEGMENT_TYPE,
TABLESPACE_NAME
FROM SYSTEM.OBJECTS, SYSTEM.SEGMENTS
WHERE OBJECT_NAME = SEGMENT_NAME AND SEGMENT_NAME LIKE 'DBA%';
Elapsed: 00:00:00.02
Execution Plan
----------------------------------------------------------
Plan hash value: 98178177
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 895 | 130K| 5 (20)| 00:00:01 |
|* 1 | HASH JOIN | | 895 | 130K| 5 (20)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| SEGMENTS | 784 | 54880 | 1 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | SEG_NAME | 141 | | 1 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| OBJECTS | 4970 | 383K| 3 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | OBJ_NAME | 895 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("OBJECT_NAME"="SEGMENT_NAME")
3 - access("SEGMENT_NAME" LIKE 'DBA%')
filter("SEGMENT_NAME" LIKE 'DBA%')
5 - access("OBJECT_NAME" LIKE 'DBA%')
filter("OBJECT_NAME" LIKE 'DBA%')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
14 consistent gets
0 physical reads
0 redo size
892 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processedNow, let's find our query's SQL_ID.
SQL> SELECT SQL_ID, SQL_TEXT
FROM V$SQLAREA
WHERE SQL_TEXT LIKE 'SELECT OBJECT_ID%';| SQL_ID | SQL_TEXT |
| gy6fj888vt27y | SELECT OBJECT_ID, OBJECT_NAME, SEGMENT_TYPE, TABLESPACE_NAME FROM SYSTEM.OBJECTS, SYSTEM.SEGMENTS WHERE OBJECT_NAME = SEGMENT_NAME AND SEGMENT_NAME LIKE 'DBA%' |
Both indexes are used, but let's assume we want to force full table scans on both tables.
SQL> DECLARE
2 SQL_FTEXT CLOB;
BEGIN
SELECT SQL_FULLTEXT INTO SQL_FTEXT FROM V$SQLAREA WHERE SQL_ID = 'gy6fj888vt27y';
DBMS_SQLTUNE.IMPORT_SQL_PROFILE(
SQL_TEXT => SQL_FTEXT,
PROFILE => SQLPROF_ATTR('FULL(@"SEL$1" "OBJECTS"@"SEL$1") FULL(@"SEL$1" "SEGMENTS"@"SEL$1")'),
NAME => 'PROFILE_gy6fj888vt27y',
REPLACE => TRUE,
FORCE_MATCH => TRUE
);
END;
/
PL/SQL procedure successfully completed.Let's see the new execution plan.
Execution Plan
----------------------------------------------------------
Plan hash value: 1823853794 [New plan hash value]
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 895 | 130K| 388 (2)| 00:00:05 |
|* 1 | HASH JOIN | | 895 | 130K| 388 (2)| 00:00:05 |
|* 2 | TABLE ACCESS FULL| SEGMENTS | 784 | 54880 | 53 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| OBJECTS | 4970 | 383K| 334 (1)| 00:00:05 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("OBJECT_NAME"="SEGMENT_NAME")
2 - filter("SEGMENT_NAME" LIKE 'DBA%')
3 - filter("OBJECT_NAME" LIKE 'DBA%')
Note
-----
- SQL profile "PROFILE_gy6fj888vt27y" used for this statement [The SQL profile we created, is used]
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1419 consistent gets
192 physical reads
0 redo size
892 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processedYou've may noticed the hint's format.
/*+ FULL(OBJECTS) FULL(SEGMENTS) */ is equal to 'FULL(@"SEL$1" "OBJECTS"@"SEL$1") FULL(@"SEL$1" "SEGMENTS"@"SEL$1")'.
Similarly, /*+ INDEX(OBJECTS OBJ_NAME) */ is equal to 'INDEX(@"SEL$1" "OBJECTS"@"SEL$1" "OBJ_NAME")'.
