首页 » ORACLE » 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可以再改进一下
对于索引的选择应该测试后再上应用,确认效率和原因
应该使用绑定变量,至少以后比如固定计划方便些

打赏

对不起,这篇文章暂时关闭评论。