|
D "UNIT_ID" = :b9 AND ROWNUM = 1',
v_hints,
'SPS_SZ_TB_XX_XXXX_XXXX_XXX',
force_match => true);
end;
/
--验证执行计划是否只走IX_AA_AAA_AAAAAAA：
SQL> explain plan for
2 DELETE FROM "SPS_SZ"."TB_XX_XXXX_XXXX_XXX"
3 WHERE "LOG_ID" = :b0
4 AND "PLAN_ID" = :b1
5 AND "LOG_NO" = :b2
6 AND "PLAN_RESULT" = :b3
7 AND "STAFF_CODE" = :b4
8 AND "WORK_STAFF" = :b5
9 AND "OP_DATE" = :b6
10 AND "OP_TIME" = :b7
11 AND "LOG_NOTES" = :b8
12 AND "UNIT_ID" = :b9
13 AND ROWNUM = 1;
Explained.
SQL> @getplan
'general,outline,starts'
Enter value for plan type:
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------
Plan hash value: 2457304297
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 1 | 352 | 744K (1)| 02:53:43 |
| 1 | DELETE | TB_XX_XXXX_XXXX_XXX | | | | |
|* 2 | COUNT STOPKEY | | | | | |
|* 3 | TABLE ACCESS BY INDEX ROWID| TB_XX_XXXX_XXXX_XXX | 1 | 352 | 744K (1)| 02:53:43 |
|* 4 | INDEX RANGE SCAN | IX_AA_AAA_AAAAAAA | 1757K| | 14 (79)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(ROWNUM=1)
3 - filter("STAFF_CODE"=:B4 AND "WORK_STAFF"=:B5 AND "OP_DATE"=:B6 AND "OP_TIME"=:B7 AND
"LOG_NOTES"=:B8 AND "PLAN_ID"=TO_NUMBER(:B1) AND "LOG_NO"=TO_NUMBER(:B2) AND
"PLAN_RESULT"=TO_NUMBER(:B3) AND "UNIT_ID"=TO_NUMBER(:B9))
4 - access("LOG_ID"=TO_NUMBER(:B0))
Note
-----
- dynamic sampling used for this statement
- SQL profile "SPS_SZ_TB_XX_XXXX_XXXX_XXX" used for this statement
--执行计划对了。虽然纠正了执行计划，但毕竟索引IX_AA_AAA_AAAAAAA有11G，cpu还是有点偏高，扫描起来还是有点慢的，只有delete操作完了后才会降下来。但是现在效率上已经好很多了。
--相关的视图
SQL> select name,created from dba_sql_profiles order by created;
NAME CREATED
------------------------------ -------------------
SPS_SZ_TB_XX_XXXX_XXXX_XXX 2014-11-05 10:49:58
5 rows selected.
SQL> col attr_val for a80
SQL> select name,attr_val from dba_sql_profiles a, sys.sqlprof$attr b
2 where a.signature = b.signature
3 and a.name = 'SPS_SZ_TB_XX_XXXX_XXXX_XXX';
NAME ATTR_VAL
------------------------------ --------------------------------------------------------------------------------
SPS_SZ_TB_XX_XXXX_XXXX_XXX INDEX_RS_ASC(@"DEL$1" "TB_XX_XXXX_XXXX_XXX"@"DEL$1" ("TB_XX_XXXX_XXXX_XXX"."LOG_ID
"))
1 row selected.
--@脚本
@getsql_spid
set lines 200 pages 200 long 100000
col USERNAME for a10
col PROGRAM for a15
col EVENT for a20
col sid_serial for a10
col sql_id_num for a15
col p_name for a15
col p_value for a15
col addr new_value addr
col sql_id new_value sql_id
SELECT addr FROM gv$process c WHERE c.spid = &ospid;
select
a.sid ||','|| a.SERIAL# sid_serial,
a.SQL_ID ||','|| a.sql_child_number sql_id_num,
P1TEXT||chr(10)||P2TEXT||chr(10)||P3TEXT p_name,
a.p1||chr(10)||a.p2||chr(10)||a.p3 p_value,
decode(a.SQL_ID,null,a.PREV_SQL_ID,a.SQL_ID) sql_id,
a.USERNAME |