sql profile使用

SQL> set autotrace traceonly

SQL> SELECT NODEID
2 FROM ICDMIP.LHB_TEST T
WHERE --T.INANITIONID is null AND
3 4 PARTID = SUBSTR(201302130046087636, 5, 4)
5 ;

596762 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 514708567

----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 412K| 10M| 3513 (1)| 00:00:43 | | |
| 1 | PARTITION LIST SINGLE| | 412K| 10M| 3513 (1)| 00:00:43 | 44 | 44 |
| 2 | TABLE ACCESS FULL | LHB_TEST | 412K| 10M| 3513 (1)| 00:00:43 | 44 | 44 | --這裡發現走的全表掃描，全表有240G，引起效能問題
----------------------------------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
54997 consistent gets --嚴重的邏輯讀，SQL語句效能低下
580 physical reads
0 redo size
20489108 bytes sent via SQL*Net to client
438116 bytes received via SQL*Net from client
39786 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
596762 rows processed

SQL> set autotrace off
SQL> alter session set current_schema=ICDMIP
2 ;

Session altered.

SQL> explain plan for SELECT/*+ INDEX(T PK_MIP_OPERATIONPROCESS)*/ NODEID
2 FROM ICDMIP.LHB_TEST T
3 WHERE T.INANITIONID = :B2
4 AND PARTID = SUBSTR(:B1, 5, 4);

Explained.

SQL> set linesize 500 pages 900
SQL> select * from table(dbms_xplan.display(null,null,'outline');
select * from table(dbms_xplan.display(null,null,'outline')
*
ERROR at line 1:
ORA-00907: missing right parenthesis

SQL> a )
1* select * from table(dbms_xplan.display(null,null,'outline'))
SQL> /

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1671553065

------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 44 | 4 (0)| 00:00:01 | | |
| 1 | PARTITION LIST SINGLE | | 1 | 44 | 4 (0)| 00:00:01 | KEY | KEY |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| LHB_TEST | 1 | 44 | 4 (0)| 00:00:01 | KEY | KEY |
|* 3 | INDEX UNIQUE SCAN | PK_MIP_OPERATIONPROCESS | 1 | | 3 (0)| 00:00:01 | KEY | KEY | --SQL已使用相關索引
------------------------------------------------------------------------------------------------------------------------------

Outline Data
-------------

/*+
BEGIN_OUTLINE_DATA
INDEX_RS_ASC(@"SEL$1" "T"@"SEL$1" ("LHB_TEST"."INANITIONID" "LHB_TEST"."PARTID"))
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
OPT_PARAM('optimizer_dynamic_sampling' 1)
OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("T"."INANITIONID"=:B2 AND "PARTID"=SUBSTR(:B1,5,4))

29 rows selected.

SQL> declare
2 v_hints sys.sqlprof_attr;
3 begin
4 v_hints := sys.sqlprof_attr(
5 'BEGIN_OUTLINE_DATA',
6 'IGNORE_OPTIM_EMBEDDED_HINTS',
7 'OPTIMIZER_FEATURES_ENABLE(''10.2.0.3'')',
8 'DB_VERSION(''10.2.0.3'')',
9 'ALL_ROWS',
10 'OUTLINE_LEAF(@"SEL$1")',
11 'INDEX_RS_ASC(@"SEL$1" "T"@"SEL$1" ("LHB_TEST"."INANITIONID" "LHB_TEST"."PARTID"))',
12 'END_OUTLINE_DATA');
13 dbms_sqltune.import_sql_profile(
14 'SELECT NODEID FROM ICDMIP.LHB_TEST T WHERE T.INANITIONID = :B2 AND PARTID = SUBSTR(:B1, 5, 4)',
15 v_hints,
16 'SQLPROFILE_T_M_O', --sql profile 名稱
17 force_match=>true,
18 replace=>true);
19 end;
20 /

PL/SQL procedure successfully completed.

SQL> explain plan for SELECT NODEID FROM ICDMIP.LHB_TEST T WHERE T.INANITIONID = :B2 AND PARTID = SUBSTR(:B1, 5, 4);

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1671553065

------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 44 | 4 (0)| 00:00:01 | | |
| 1 | PARTITION LIST SINGLE | | 1 | 44 | 4 (0)| 00:00:01 | KEY | KEY |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| LHB_TEST | 1 | 44 | 4 (0)| 00:00:01 | KEY | KEY |
|* 3 | INDEX UNIQUE SCAN | PK_MIP_OPERATIONPROCESS | 1 | | 3 (0)| 00:00:01 | KEY | KEY |
------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("T"."INANITIONID"=:B2 AND "PARTID"=SUBSTR(:B1,5,4))

Note
-----
- SQL profile "SQLPROFILE_T_M_O" used for this statement--注意，當生效後，這裡會提示sql profile已使用

19 rows selected.

SQL> exit

注：
刪除sql profile方法：
exec dbms_sqltune.drop_sql_profile(name => '&sql_profile');