Oracle insert语句慢的背后

问题：用户反映一个insert语句执行很慢。

1、检查表索引并不多，相比表，索引确实有点大，但应该不是问题所在。

SQL> select owner,segment_name,segment_type,bytes/1024/1024/1024 from dba_segments 
  2  where segment_name in ('ANDY_TAB','ANDY_IND_STAU','ANDY_IND_WN');
OWNER       SEGMENT_NAME       SEGMENT_TYPE       BYTES/1024/1024/1024
----------- ------------------ -----------------  -------
ANDY        ANDY_TAB           TABLE              36.5195313
ANDY        ANDY_IND_STAU      INDEX              17.4375
ANDY        ANDY_IND_WN        INDEX              24.2226563

 2、检查是否有大事物

SQL> select start_time,used_ublk,addr from v$transaction order by used_ublk desc;
START_TIME            USED_UBLK ADDR
-------------------- ---------- ----------------
08/28/13 03:12:57       8712941 0000001FD7D20AA0
08/28/13 17:16:22        931507 0000001E9F65B848
SQL> select sid,username,osuser,machine,terminal,program,sql_id,prev_sql_id,status,to_char(LOGON_TIME,'yyyy-mm-dd hh24:mi:ss'),event from 
  2  v$session where taddr in ('0000001FD7D20AA0','0000001E9F65B848');
SID  USERNAME  OSUSER   MACHINE   TERMINAL   PROGRAM              SQL_ID        PREV_SQL_ID   STATUS   TO_CHAR(LOGON_TIME, EVENT
---- ---------------- --------------- ------------------------    ----------------------- ------------- -------- ------------------- ------------------
8863 ANDY     oraandy  andynode1 UNKNOWN   oracle@andynode1 (J017)              am1uxbjnabny6 ACTIVE   2013-08-27 11:35:16 db file sequential read                                                                                                                 
3578 ANDY     oraandy  andynode1 UNKNOWN   andy.exe               5jfhf2quksh4t 5jfhf2quksh4t ACTIVE   2013-08-28 17:15:26 latch: KCL gc element parent latch
SQL> select * from table(dbms_xplan.display_cursor('am1uxbjnabny6',null,'ALLSTATS +PEEKED_BINDS'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
SQL_ID  am1uxbjnabny6, child number 0
DELETE FROM ANDY_TAB WHERE ANDY_TAB.PROCESSING_STATUS=4
NOTE: cannot fetch plan for SQL_ID: am1uxbjnabny6, CHILD_NUMBER: 0
      Please verify value of SQL_ID and CHILD_NUMBER;
      It could also be that the plan is no longer in cursor cache (check v$sql_plan)
SQL> select * from table(dbms_xplan.display_awr('am1uxbjnabny6',null,null,'basic'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
SQL_ID am1uxbjnabny6
--------------------
DELETE  FROM ANDY_TAB WHERE ANDY_TAB.PROCESSING_STATUS=4
Plan hash value: 326604565
-----------------------------------------------------------
| Id  | Operation          | Name                         |
-----------------------------------------------------------
|   0 | DELETE STATEMENT   |                              |
|   1 |  DELETE            | ANDY_TAB                     |
|   2 |   TABLE ACCESS FULL| ANDY_TAB                     |
-----------------------------------------------------------
15 rows selected.

3、检查undo段使用

SQL> select status,sum(bytes)/1024/1024/1024 from dba_undo_extents where tablespace_name='UNDOTBS1' group by status;
STATUS    SUM(BYTES)/1024/1024/1024
--------- -------------------------
ACTIVE                   82.8392334
EXPIRED                  11.3452759
UNEXPIRED                 38.531189
SQL> select xidusn,ses_addr,XIDSLOT,XIDSQN from v$transaction where addr='0000001FD7D20AA0';
    XIDUSN SES_ADDR           XIDSLOT     XIDSQN  
---------- ---------------- ---------- ----------
       304 0000002057DBB0E8        22    1876023
SQL> select KTUXESIZ,KTUXESTA,KTUXEUSN,KTUXESLT,KTUXESQN,KTUXECFL from x$ktuxe where KTUXEUSN=304 and KTUXESLT=22 and KTUXESQN=1876023;
  KTUXESIZ KTUXESTA           KTUXEUSN   KTUXESLT   KTUXESQN KTUXECFL
---------- ---------------- ---------- ---------- ---------- ------------------------
   8576766 ACTIVE                  304         22    1876023 NONE
SQL> select * from v$rollstat r where r.usn =304;
USN LATCH EXTENTS     RSSIZE     WRITES XACTS       GETS WAITS OPTSIZE    HWMSIZE SHRINKS WRAPS EXTENDS  AVESHRINK  AVEACTIVE STATUS CUREXT CURBLK
--- ----- ------- ---------- ---------- ----- ---------- ----- ------- ---------- ------- ----- ------- ---------- ---------- ------------- ------
304    36   25647 3730481152 3535930178     1   11174305   106         4294492160      14 25792   25675   20971520 2480191123 ONLINE   4821   7394
 
SQL> select r.rssize/1024/1024/1024 from v$rollstat r where r.usn =304;
R.RSSIZE/1024/1024/1024
-----------------------
             3.47428131
SQL> select * from dba_rollback_segs rs where rs.segment_id=304;
SEGMENT_NAME OWNER  TABLESPACE_NAME SEGMENT_ID FILE_ID BLOCK_ID INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE STATUS INSTANCE_NUM RELATIVE_FNO
------------------- -------------------------- ------- -------- -------------- ----------- ----------- ----------- ------------ --------------------------------
_SYSSMU304$  PUBLIC UNDOTBS1               304     544    42713         131072                       2       32765              ONLINE 1                     544
SQL> select * from dba_undo_extents where segment_name='_SYSSMU304$' and rownum<10;
OWN SEGMENT_NAME   TABLESPACE_NAME EXTENT_ID    FILE_ID   BLOCK_ID      BYTES     BLOCKS RELATIVE_FNO COMMIT_JTIME COMMIT_WTIME STATUS
--- ---------------------------------------- ---------- ---------- ---------- ---------- ------------ ------------ -------------------
SYS _SYSSMU304$    UNDOTBS1                0        544      42713      65536          8          544                           ACTIVE
SYS _SYSSMU304$    UNDOTBS1                1        539     330249      65536          8          539                           ACTIVE
SYS _SYSSMU304$    UNDOTBS1                2        541      70537    8388608       1024          541                           ACTIVE
SYS _SYSSMU304$    UNDOTBS1                3        543      44169    1048576        128          543                           ACTIVE
SYS _SYSSMU304$    UNDOTBS1                4       1132       1417    1048576        128          109                           ACTIVE
SYS _SYSSMU304$    UNDOTBS1                5       1133       4105    1048576        128          110                           ACTIVE
SYS _SYSSMU304$    UNDOTBS1                6       1134      23049    1048576        128          111                           ACTIVE
SYS _SYSSMU304$    UNDOTBS1                7       1135      35465    1048576        128          112                           ACTIVE
SYS _SYSSMU304$    UNDOTBS1                8        539      86153    8388608       1024          539                           ACTIVE
9 rows selected.
SQL> select sum(bytes)/1024/1024/1024 from dba_undo_extents where segment_name='_SYSSMU304$';
SUM(BYTES)/1024/1024/1024
-------------------------
               71.4747314

4、判断多久可以恢复

SQL> set serveroutput on
SQL> declare
  2  l_start number;
  3  l_end number;
  4  begin
  5    select ktuxesiz into l_start from x$ktuxe where KTUXEUSN=304 and KTUXESLT=22 and KTUXESQN=1876023;
  6    dbms_lock.sleep(240);
  7    select ktuxesiz into l_end from x$ktuxe where KTUXEUSN=304 and KTUXESLT=22 and KTUXESQN=1876023;
  8    dbms_output.put_line('time est hours:'|| round(l_end/(l_start -l_end)/60,2));
  9  end;
 10  / 
time est hours:17.26
PL/SQL procedure successfully completed.
SQL> select * from gv$session_longops where sql_id='am1uxbjnabny6';
no rows selected
SQL> select * from gv$session_longops where sid=8863;
no rows selected
 
alter TABLESPACE UNDOTBS1 add DATAFILE '+DATADG' SIZE 8000M AUTOEXTEND OFF;

sql_id里没有，prev里才有，且v$session_longops里也没有，且undo段71G也没在增加，且ktuxe里在减少。应该是delete执行完了，但undo还没恢复完。

5、强行终止

注意以下参数
v$tranactions查不到死事物，看v$fast_start_transactions和V$FAST_START_SERVERS，不如直接看x$ktuxe

select KTUXESIZ, KTUXESTA, KTUXEUSN, KTUXESLT, KTUXESQN, KTUXECFL
  from x$ktuxe
 where KTUXECFL = 'DEAD'
   and KTUXESIZ > 0
 order by KTUXESIZ desc;

KTUXESIZ的单位是block

fast_start_parallel_rollback与parallel_max_servers与recovery_parallelism
fast_start_parallel_rollback可在线修改
false：使用串行回滚，往往在并行回滚走全扫描或是存在row cache lock dc_rollback_segments竞争时采用
low：  rollback进程为2*cpu_count个
high： rollback进程为4*cpu_count个

并行回滚进程不超过parallel_max_servers，在rac中，还要看parallel_threads_per_cpu。

fast_start_parallel_rollback与recovery_parallelism不同的，recovery_parallelism参数是指在进行instance crash recovery时的并行恢复进程个数。

还有个隐含参数_cleanup_rollback_entries可以继续研究下。