使用 sql_profile 固定SQL执行计划

1. 扯蛋
2. 利用SQL PROFILE固定执行计划

1 扯蛋

近日，每天晚上固定时间，数据库会出现大量的latch:cache buffer chains 等待。经查看发现原SQL语句走错执行计划，240G的表，进行全表扫描，引起热点块争用。通过固定 SQL语句的执行计划，使其可正常使用索引扫描，可以将该问题解决。下面为操作过程.

2 利用SQL PROFILE固定执行计划

2.1 查看原来语句的执行计划

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;

Session altered.

2.2 指定SQL使用选择性比较好的索引

提前已查看，条件PARTID可以使用索引PK_MIP_OPERATIONPROCESS.来试试，使用这个索引后，执行计划怎么样。

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.

2.3 生成sql profile

注意对比第二步中'Outline Data' 数据与本步骤中sys.sqlprof_attr内容的关系

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.

2.4 验证sql profile 是否生效

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

note: 删除sql profile方法 exec dbms_sqltune.drop_sql_profile(name => '&sql_profile');

sql_profile 固定SQL执行计划