首页 » ORACLE 9i-23ai » Tuning: Temporary table don’t collect statistics (临时表不要收集统计信息)
Tuning: Temporary table don’t collect statistics (临时表不要收集统计信息)
还是那个应用，第二天又出了新问题，还是CPU使用高，有一部份CBC wait event, 我们再接着分析。下面我附上一段AWR信息
Snap Id Snap Time Sessions Curs/Sess
--------- ------------------- -------- ---------
Begin Snap: 139997 15-Feb-16 00:30:25 1,128 2.8
End Snap: 139998 15-Feb-16 01:00:52 1,102 2.9
Elapsed: 30.45 (mins)
DB Time: 2,596.47 (mins)
Top 5 Timed Foreground Events
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Avg
wait % DB
Event Waits Time(s) (ms) time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
DB CPU 49,264 31.6
log file sync 26,169 12,458 476 8.0 Commit
db file sequential read 27,725 4,928 178 3.2 User I/O
SQL*Net message from dblink 19,218 3,583 186 2.3 Network
latch: cache buffers chains 9,867 2,765 280 1.8 Concurrenc
Instance CPU
~~~~~~~~~~~~
% of total CPU for Instance: 99.2
% of busy CPU for Instance: 99.2
%DB time waiting for CPU - Resource Mgr: 0.0
Statistic Name Time (s) % of DB Time
------------------------------------------ ------------------ ------------
sql execute elapsed time 142,711.4 91.6
DB CPU 49,263.7 31.6
parse time elapsed 1,835.7 1.2
hard parse elapsed time 1,763.0 1.1
connection management call elapsed time 15.2 .0
sequence load elapsed time 15.1 .0
PL/SQL execution elapsed time 15.1 .0
PL/SQL compilation elapsed time 7.7 .0
SQL ordered by CPU Time
CPU CPU per Elapsed
Time (s) Executions Exec (s) %Total Time (s) %CPU %IO SQL Id
---------- ------------ ---------- ------ ---------- ------ ------ -------------
20,156.1 3 6,718.70 40.9 48,616.0 41.5 .0 62uf32w9vvbja
Module: anbob_p1@kyyz1 (TNS V1-V3)
SELECT B.SUBSID, B.VALTYPE, B.TARGET_ID, B.TARGET_VAL FROM CH_ANBOB_P1_SUBS_TA
RGET partition(PART_316_201601)B WHERE EXISTS (SELECT 1 FROM CH_SETTLE_TEMP_SUB
C WHERE B.SUBSID =C.SUBSID) AND B.CARRYINGTYPE = 'cedevSubs' AND B.TARGET_ID IN
(SELECT ITEMID FROM CH_ANBOB_P1_TARGET_ITEM WHERE REGION IN (316, 999) ) ORDER
Segments by Logical Reads DB/Inst: UCISA/ucisa1 Snaps: 139997-139998
-> Total Logical Reads: 650,314,556
-> Captured Segments account for 105.9% of Total
Tablespace Subobject Obj. Logical
Owner Name Object Name Name Type Reads %Total
---------- ---------- -------------------- ---------- ----- ------------ -------
ANBOB_P1 DATALOG316 CH_ANBOB_P1_SUBS_TARGET 316_201601 TABLE 433,451,744 66.65
ANBOB_P1 DATALOG316 IDX_NGSUBS_TARTGE_CA 316_201601 INDEX 212,187,744 32.63
INTUNIT_HW USERS I_INTUNITLOG INDEX 18,110,592 2.78
Note:
当时的负载也比较高，我们看到有条SQL的占用DB cpu很高，单次运行时间也比较长，采集到用了48,616秒执行了3次，我们应该知道logical read也是一个比较耗用CPU的，从逻辑读的对象看也是top cpu sql的对象。
下面我们手动拿出来尝试。
SQL> EXPLAIN PLAN
FOR
SELECT B.SUBSID,
B.VALTYPE,
B.TARGET_ID,
B.TARGET_VAL
FROM anbob_p1.CH_ANBOB_P1_SUBS_TARGET PARTITION (PART_316_201601) B
WHERE EXISTS
(SELECT 1
FROM anbob_p1.CH_SETTLE_TEMP_SUB C
WHERE B.SUBSID = C.SUBSID)
AND B.CARRYINGTYPE = 'cedevSubs'
AND B.TARGET_ID IN (SELECT ITEMID
FROM anbob_p1.CH_ANBOB_P1_TARGET_ITEM
WHERE REGION IN (316, 999))
ORDER BY B.SUBSID;
Explained.
Elapsed: 00:00:00.01
SQL> @x2
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 839464873
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 55 | 2 (50)| 00:00:01 | | |
| 1 | NESTED LOOPS SEMI | | 1 | 55 | 2 (50)| 00:00:01 | | |
| 2 | NESTED LOOPS | | 1 | 45 | 1 (100)| 00:00:01 | | |
| 3 | SORT UNIQUE | | 1 | 13 | 0 (0)| 00:00:01 | | |
| 4 | INDEX FULL SCAN | IDX_TEMP_SUB | 1 | 13 | 0 (0)| 00:00:01 | | |
| 5 | PARTITION RANGE SINGLE | | 1 | 32 | 0 (0)| 00:00:01 | 96 | 96 |
|* 6 | TABLE ACCESS BY LOCAL INDEX ROWID| CH_ANBOB_P1_SUBS_TARGET | 1 | 32 | 0 (0)| 00:00:01 | 96 | 96 |
|* 7 | INDEX RANGE SCAN | IDX_NGSUBS_TARTGE_CARRYING | 38892 | | 0 (0)| 00:00:01 | 96 | 96 |
| 8 | INLIST ITERATOR | | | | | | | |
|* 9 | INDEX UNIQUE SCAN | PK_TARGET_ITEMIDREGION | 76 | 760 | 1 (0)| 00:00:01 | | |
-----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - filter("B"."SUBSID"="C"."SUBSID")
7 - access("B"."CARRYINGTYPE"='cedevSubs')
9 - access("B"."TARGET_ID"="ITEMID" AND ("REGION"=316 OR "REGION"=999))
23 rows selected.
Elapsed: 00:00:00.03
SQL> @ind anbob_p1.CH_ANBOB_P1_SUBS_TARGET
Display indexes where table or index name matches %anbob_p1.CH_ANBOB_P1_SUBS_TARGET%...
TABLE_OWNER TABLE_NAME INDEX_NAME POS# COLUMN_NAME DSC
-------------------- ------------------------------ ------------------------------ ---- ------------------------------ ----
ANBOB_P1 CH_ANBOB_P1_SUBS_TARGET IDX_NGSUBS_TARTGE_CARRYING 1 CARRYINGTYPE
2 TARGET_ID
IDX_NGSUBS_TARTGE_SUBSID 1 SUBSID
2 TARGET_ID
3 REGION
4 CYCLE
IDX_SUBSTARTGE_CARRYINGTYPE 1 CARRYINGTYPE
2 REGION
3 CYCLE
IDX_SUBSTARTGE_OBJECTTYPE 1 OBJECTTYPE
2 REGION
3 CYCLE
IDX_SUBS_TARGET1 1 REGION
2 CYCLE
3 CARRYINGTYPE
4 SUBSID
INDEX_OWNER TABLE_NAME INDEX_NAME IDXTYPE UNIQ STATUS PART TEMP H LFBLKS NDK NUM_ROWS CLUF LAST_ANALYZED DEGREE VISIBILIT
-------------------- ------------------------------ ------------------------------ ---------- ---- -------- ---- ---- -- ---------- ------------- ---------- ---------- ----------------- ------ ---------
ANBOB_P1 CH_ANBOB_P1_SUBS_TARGET IDX_NGSUBS_TARTGE_CARRYING NORMAL NO N/A YES N 3 3633900 180 1695875700 12697300 20160108 09:21:22 1 VISIBLE
CH_ANBOB_P1_SUBS_TARGET IDX_NGSUBS_TARTGE_SUBSID NORMAL NO N/A YES N 3 5018000 1473881480 1751078000 1708580200 20160214 13:16:07 1 VISIBLE
CH_ANBOB_P1_SUBS_TARGET IDX_SUBSTARTGE_CARRYINGTYPE NORMAL NO N/A YES N 3 4080900 144 1657582200 8912900 20160108 09:16:40 1 VISIBLE
CH_ANBOB_P1_SUBS_TARGET IDX_SUBSTARTGE_OBJECTTYPE NORMAL NO N/A YES N 3 3969800 134 1642200600 8916900 20160108 09:19:01 1 VISIBLE
CH_ANBOB_P1_SUBS_TARGET IDX_SUBS_TARGET1 NORMAL NO N/A YES N 4 5443200 28582626 1672961600 1633426300 20160108 09:24:02 1 VISIBLE
Note:
从当时CBO的执行计划的估算和索引列，我们发现那个执行计划还较不错，但是前提是执行计划使用的统计信息是真实与实际相符，注意到IDX_TEMP_SUB估算是1行记录，该索引是CH_SETTLE_TEMP_SUB表上。询问了业务人员及应用人员，反馈说不可能，因为该表不是heap table是一个临时表，所以是会话级或表的填充语句才能了解它的真实数据， 平时单个进程操作时该表致少在2万左右，那我们看表的统计信息。
SQL> @tab anbob_p1.CH_SETTLE_TEMP_SUB Show tables matching condition "%anbob_p1.CH_SETTLE_TEMP_SUB%" (if schema is not specified then current user's tables only are shown)... OWNER TABLE_NAME TYPE NUM_ROWS BLOCKS EMPTY AVGSPC ROWLEN TAB_LAST_ANALYZED DEGREE COMPRESS -------------------- ------------------------------ ---- ------------ ------------- --------- ------ ------ ----------------- ---------- -------- ANBOB_P1 CH_SETTLE_TEMP_SUB TAB 0 0 0 0 0 20150429 16:51:44 1 DISABLED
note:
统计信息是0行， 因为它是临时表，用其它进程统计是看不到当时的数据的。解决方法就删掉统计信息，让CBO动态采样.
SQL> exec dbms_stats.delete_table_stats('anbob_p1','CH_SETTLE_TEMP_SUB');
PL/SQL procedure successfully completed.
SQL> @tab anbob_p1.CH_SETTLE_TEMP_SUB
Show tables matching condition "%anbob_p1.CH_SETTLE_TEMP_SUB%" (if schema is not specified then current user's tables only are shown)...
OWNER TABLE_NAME TYPE NUM_ROWS BLOCKS EMPTY AVGSPC ROWLEN TAB_LAST_ANALYZED DEGREE COMPRESS
-------------------- ------------------------------ ---- ------------ ------------- --------- ------ ------ ----------------- ---------- --------
ANBOB_P1 CH_SETTLE_TEMP_SUB TAB 1 DISABLED
SQL> EXPLAIN PLAN
2 FOR
3 SELECT B.SUBSID,
4 B.VALTYPE,
5 B.TARGET_ID,
6 B.TARGET_VAL
7 FROM anbob_p1.CH_ANBOB_P1_SUBS_TARGET B
8 WHERE b.region=316 and b.cycle=201601 and EXISTS
9 (SELECT 1
10 FROM anbob_p1.CH_SETTLE_TEMP_SUB C
11 WHERE B.SUBSID = C.SUBSID)
12 AND B.CARRYINGTYPE = 'cedevSubs'
13 AND B.TARGET_ID IN (SELECT ITEMID
14 FROM anbob_p1.CH_ANBOB_P1_TARGET_ITEM
15 WHERE REGION IN (316, 999))
16 ORDER BY B.SUBSID;
Explained.
SQL> @x2
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3158802586
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 989 | 63296 | 118 (91)| 00:00:01 | | |
| 1 | SORT ORDER BY | | 989 | 63296 | 118 (91)| 00:00:01 | | |
|* 2 | HASH JOIN RIGHT SEMI | | 989 | 63296 | 117 (91)| 00:00:01 | | |
|* 3 | INDEX FULL SCAN | PK_TARGET_ITEMIDREGION | 76 | 760 | 1 (0)| 00:00:01 | | |
|* 4 | HASH JOIN | | 989 | 53406 | 116 (92)| 00:00:01 | | |
| 5 | SORT UNIQUE | | 16360 | 207K| 9 (0)| 00:00:01 | | |
| 6 | INDEX FAST FULL SCAN | IDX_TEMP_SUB | 16360 | 207K| 9 (0)| 00:00:01 | | |
| 7 | PARTITION RANGE SINGLE | | 4640K| 181M| 1 (0)| 00:00:01 | 96 | 96 |
|* 8 | TABLE ACCESS BY LOCAL INDEX ROWID| CH_ANBOB_P1_SUBS_TARGET | 4640K| 181M| 1 (0)| 00:00:01 | 96 | 96 |
|* 9 | INDEX SKIP SCAN | IDX_SUBSTARTGE_OBJECTTYPE | 116K| | 1 (0)| 00:00:01 | 96 | 96 |
-----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("B"."TARGET_ID"="ITEMID")
3 - filter("REGION"=316 OR "REGION"=999)
4 - access("B"."SUBSID"="C"."SUBSID")
8 - filter("B"."CARRYINGTYPE"='cedevSubs')
9 - access("B"."REGION"=316 AND "B"."CYCLE"=201601)
filter("B"."REGION"=316 AND "B"."CYCLE"=201601)
26 rows selected.
NOte：
现在IDX_TEMP_SUB估算与实际的较为接近，但是还有一个是ISS的CH_ANBOB_P1_SUBS_TARGET表，也就是上面看到了逻辑读最多的对象，虽然现在已从上面的NL JOIN变成了HASH JOIN, 但也是不最优，我们注意到有个索引更合适，所以找业务的做了一个临时表的复本，来测试这两个索引的性能。
SQL> SELECT B.SUBSID,
2 B.VALTYPE,
3 B.TARGET_ID,
4 B.TARGET_VAL
5 FROM anbob_p1.CH_ANBOB_P1_SUBS_TARGET B
6 WHERE b.region=316 and b.cycle=201601 and EXISTS
7 (SELECT 1
8 FROM anbob_p1.CH_SETTLE_TEMP_SUB1 C
9 WHERE B.SUBSID = C.SUBSID)
10 AND B.CARRYINGTYPE = 'cedevSubs'
11 AND B.TARGET_ID IN (SELECT ITEMID
12 FROM anbob_p1.CH_ANBOB_P1_TARGET_ITEM
13 WHERE REGION IN (316, 999))
14 ORDER BY B.SUBSID;
16994 rows selected.
Elapsed: 00:05:19.61
Execution Plan
----------------------------------------------------------
Plan hash value: 1924156115
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 277 | 17728 | 112 (95)| 00:00:01 | | |
| 1 | SORT ORDER BY | | 277 | 17728 | 112 (95)| 00:00:01 | | |
|* 2 | HASH JOIN RIGHT SEMI | | 277 | 17728 | 111 (95)| 00:00:01 | | |
|* 3 | INDEX FULL SCAN | PK_TARGET_ITEMIDREGION | 76 | 760 | 1 (0)| 00:00:01 | | |
|* 4 | HASH JOIN | | 277 | 14958 | 109 (96)| 00:00:01 | | |
| 5 | SORT UNIQUE | | 4581 | 59553 | 4 (0)| 00:00:01 | | |
| 6 | INDEX FAST FULL SCAN | IDX_TEMP_SUB1 | 4581 | 59553 | 4 (0)| 00:00:01 | | |
| 7 | PARTITION RANGE SINGLE | | 4640K| 181M| 1 (0)| 00:00:01 | 96 | 96 |
|* 8 | TABLE ACCESS BY LOCAL INDEX ROWID| CH_ANBOB_P1_SUBS_TARGET | 4640K| 181M| 1 (0)| 00:00:01 | 96 | 96 |
|* 9 | INDEX SKIP SCAN | IDX_SUBSTARTGE_OBJECTTYPE | 116K| | 1 (0)| 00:00:01 | 96 | 96 |
-----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("B"."TARGET_ID"="ITEMID")
3 - filter("REGION"=316 OR "REGION"=999)
4 - access("B"."SUBSID"="C"."SUBSID")
8 - filter("B"."CARRYINGTYPE"='cedevSubs')
9 - access("B"."REGION"=316 AND "B"."CYCLE"=201601)
filter("B"."REGION"=316 AND "B"."CYCLE"=201601)
Statistics
----------------------------------------------------------
311 recursive calls
0 db block gets
182096 consistent gets
103559 physical reads
4271688 redo size
281129 bytes sent via SQL*Net to client
883 bytes received via SQL*Net from client
35 SQL*Net roundtrips to/from client
29 sorts (memory)
0 sorts (disk)
16994 rows processed
SQL> SELECT /*+index(b IDX_SUBS_TARGET1)*/ B.SUBSID,
B.VALTYPE,
B.TARGET_ID,
B.TARGET_VAL
FROM anbob_p1.CH_ANBOB_P1_SUBS_TARGET B
WHERE b.region=316 and b.cycle=201601 and EXISTS
(SELECT 1
FROM anbob_p1.CH_SETTLE_TEMP_SUB1 C
WHERE B.SUBSID = C.SUBSID)
AND B.CARRYINGTYPE = 'cedevSubs'
AND B.TARGET_ID IN (SELECT ITEMID
FROM anbob_p1.CH_ANBOB_P1_TARGET_ITEM
WHERE REGION IN (316, 999))
ORDER BY B.SUBSID;
16994 rows selected.
Elapsed: 00:00:03.32
Execution Plan
----------------------------------------------------------
Plan hash value: 692822361
--------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 277 | 17728 | 724 (1)| 00:00:03 | | |
| 1 | SORT ORDER BY | | 277 | 17728 | 724 (1)| 00:00:03 | | |
|* 2 | HASH JOIN RIGHT SEMI | | 277 | 17728 | 723 (1)| 00:00:03 | | |
|* 3 | INDEX FULL SCAN | PK_TARGET_ITEMIDREGION | 76 | 760 | 1 (0)| 00:00:01 | | |
| 4 | NESTED LOOPS | | | | | | | |
| 5 | NESTED LOOPS | | 277 | 14958 | 722 (1)| 00:00:03 | | |
| 6 | SORT UNIQUE | | 4581 | 59553 | 4 (0)| 00:00:01 | | |
| 7 | INDEX FAST FULL SCAN | IDX_TEMP_SUB1 | 4581 | 59553 | 4 (0)| 00:00:01 | | |
| 8 | PARTITION RANGE SINGLE | | 1 | | 1 (0)| 00:00:01 | 96 | 96 |
|* 9 | INDEX RANGE SCAN | IDX_SUBS_TARGET1 | 1 | | 1 (0)| 00:00:01 | 96 | 96 |
| 10 | TABLE ACCESS BY LOCAL INDEX ROWID| CH_ANBOB_P1_SUBS_TARGET | 2 | 82 | 5 (0)| 00:00:01 | 96 | 96 |
--------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("B"."TARGET_ID"="ITEMID")
3 - filter("REGION"=316 OR "REGION"=999)
9 - access("B"."REGION"=316 AND "B"."CYCLE"=201601 AND "B"."CARRYINGTYPE"='cedevSubs' AND "B"."SUBSID"="C"."SUBSID")
Statistics
----------------------------------------------------------
13 recursive calls
0 db block gets
25405 consistent gets
4622 physical reads
0 redo size
280980 bytes sent via SQL*Net to client
883 bytes received via SQL*Net from client
35 SQL*Net roundtrips to/from client
8 sorts (memory)
0 sorts (disk)
16994 rows processed
Note:
可以看到相同SQL响应时间又已从5m19s提升到了我们指定索引后的3秒，一致读也从182096降到了25405。更比一开始的AWR中的用了48,616秒执行了3次提升了很多。
Summary:
对于临时表不应该收集统计信息，TEMP TABLE是会话级数据，因为每个进程可以填充的数据不一样，这点CBO可以再改进一下
对于索引的选择应该测试后再上应用，确认效率和原因
应该使用绑定变量，至少以后比如固定计划方便些
微信扫一扫，打赏作者吧～
对不起，这篇文章暂时关闭评论。