DELETE TABLE資料後，查詢變慢，問題處理

1. hang analyze

$ sqlplus / as sysdba;

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Aug 20 14:32:13 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> oradebug setmypid;
Statement processed.
SQL> oradebug hanganalyze 3;
Hang Analysis in /orahome/admin/STCSMES/udump/stcsmes_ora_19360.trc
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@STCS-MES-ARC ~]$ more /orahome/admin/STCSMES/udump/stcsmes_ora_19360.trc
/orahome/admin/STCSMES/udump/stcsmes_ora_19360.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /orahome/10.2
System name: Linux
Node name: STCS-MES-ARC
Release: 2.6.9-78.ELsmp
Version: #1 SMP Wed Jul 9 15:46:26 EDT 2008
Machine: x86_64
Instance name: STCSMES
Redo thread mounted by this instance: 1
Oracle process number: 27
Unix process pid: 19360, image: oracle@STCS-MES-ARC (TNS V1-V3)

*** ACTION NAME:() 2010-08-20 14:32:34.603
*** MODULE NAME:(sqlplus@STCS-MES-ARC (TNS V1-V3)) 2010-08-20 14:32:34.603
*** SERVICE NAME:(SYS$USERS) 2010-08-20 14:32:34.603
*** SESSION ID:(3227.21079) 2010-08-20 14:32:34.603
*** 2010-08-20 14:32:34.603
==============
HANG ANALYSIS:
==============
Open chains found:
Other chains found:
Chain 1 : :
<0/3132/13927/0x222f99b8/17057/db file sequential read>
Chain 2 : :
<0/3133/46205/0x232ef190/17047/db file sequential read>
Chain 3 : :
<0/3136/10036/0x232ed1f0/17031/db file sequential read>
Chain 4 : :
<0/3149/20525/0x232f0160/17055/db file sequential read>
Chain 5 : :
<0/3151/12391/0x232ed9d8/17035/db file sequential read>
Chain 6 : :
<0/3161/185/0x222f89e8/17049/db file sequential read>
Chain 7 : :
<0/3179/21060/0x222f6260/17029/db file sequential read>
Chain 8 : :
<0/3227/21079/0x222f5a78/19360/No Wait>
Chain 9 : :
<0/3237/8804/0x232ee1c0/17039/db file sequential read>
Chain 10 : :
<0/3241/128/0x222f8200/17045/db file sequential read>
Chain 11 : :
<0/3242/18951/0x222f7a18/17041/db file sequential read>
Chain 12 : :
<0/3252/1327/0x232ee9a8/17043/db file sequential read>
Chain 13 : :
<0/3258/21384/0x232eca08/16566/inactive session>
Chain 14 : :
<0/3261/1184/0x232f0948/18301/db file parallel read>
Chain 15 : :
<0/3267/12365/0x222f7230/17037/db file sequential read>
Chain 16 : :
<0/3269/35640/0x222f5290/17027/wait for a undo record>
Chain 17 : :
<0/3270/32241/0x222f6a48/17033/db file sequential read>
Chain 18 : :
<0/3272/6884/0x232ef978/17051/db file sequential read>
Chain 19 : :
<0/3274/7701/0x222f4aa8/18877/db file scattered read>
Chain 20 : :
<0/3276/18534/0x222f2b08/17859/jobq slave wait>
Chain 21 : :
<0/3281/3/0x222f42c0/24582/Streams AQ: qmn slave idle wait>
Chain 22 : :
<0/3282/2/0x232eaa68/24580/Streams AQ: waiting for time man>
Chain 23 : :
<0/3285/1914/0x222f91d0/17053/db file sequential read>
Chain 24 : :
<0/3286/1/0x232ea280/24572/Streams AQ: qmn coordinator idle>
Chain 25 : :
<0/3299/1/0x232e7af8/24552/wait for stopper event to be inc>
Extra information that will be dumped at higher levels:
[level 5] : 25 node dumps -- [SINGLE_NODE] [SINGLE_NODE_NW] [IGN_DMP]
[level 10] : 17 node dumps -- [IGN]

State of nodes
([nodenum]/cnode/sid/sess_srno/session/ospid/state/start/finish/[adjlist]/predecessor):
[3131]/0/3132/13927/0x23e003b8/17057/SINGLE_NODE/1/2//none
[3132]/0/3133/46205/0x22e11b18/17047/SINGLE_NODE/3/4//none
[3133]/0/3134/11925/0x23e01920/15835/IGN/5/6//none
[3135]/0/3136/10036/0x23e02e88/17031/SINGLE_NODE/7/8//none
[3148]/0/3149/20525/0x22e1c658/17055/SINGLE_NODE/9/10//none
[3150]/0/3151/12391/0x22e1dbc0/17035/SINGLE_NODE/11/12//none
[3157]/0/3158/19356/0x23e11a00/16065/IGN/13/14//none
[3160]/0/3161/185/0x22e246c8/17049/SINGLE_NODE/15/16//none
[3177]/0/3178/22570/0x23e1f010/18840/IGN/17/18//none
[3178]/0/3179/21060/0x22e30770/17029/SINGLE_NODE/19/20//none
[3226]/0/3227/21079/0x22e50930/19360/SINGLE_NODE_NW/21/22//none
[3231]/0/3232/141/0x23e43208/19365/IGN/23/24//none
[3236]/0/3237/8804/0x22e57438/17039/SINGLE_NODE/25/26//none
[3240]/0/3241/128/0x22e59f08/17045/SINGLE_NODE/27/28//none
[3241]/0/3242/18951/0x23e49d10/17041/SINGLE_NODE/29/30//none
[3251]/0/3252/1327/0x23e50818/17043/SINGLE_NODE/31/32//none
[3257]/0/3258/21384/0x23e54850/16566/SINGLE_NODE/33/34//none
[3260]/0/3261/1184/0x22e67518/18301/SINGLE_NODE/35/36//none
[3266]/0/3267/12365/0x22e6b550/17037/SINGLE_NODE/37/38//none
[3268]/0/3269/35640/0x22e6cab8/17027/SINGLE_NODE/39/40//none
[3269]/0/3270/32241/0x23e5c8c0/17033/SINGLE_NODE/41/42//none
[3271]/0/3272/6884/0x23e5de28/17051/SINGLE_NODE/43/44//none
[3273]/0/3274/7701/0x23e5f390/18877/SINGLE_NODE/45/46//none
[3275]/0/3276/18534/0x23e608f8/17859/SINGLE_NODE/47/48//none
[3278]/0/3279/50263/0x22e735c0/19127/IGN/49/50//none
[3280]/0/3281/3/0x22e74b28/24582/SINGLE_NODE/51/52//none
[3281]/0/3282/2/0x23e64930/24580/SINGLE_NODE/53/54//none
[3284]/0/3285/1914/0x22e775f8/17053/SINGLE_NODE/55/56//none
[3285]/0/3286/1/0x23e67400/24572/SINGLE_NODE/57/58//none
[3289]/0/3290/1/0x23e69ed0/24570/IGN/59/60//none
[3290]/0/3291/1/0x22e7b630/24568/IGN/61/62//none
[3294]/0/3295/1/0x22e7e100/24560/IGN/63/64//none
[3295]/0/3296/1/0x23e6df08/24558/IGN/65/66//none
[3296]/0/3297/1/0x22e7f668/24556/IGN/67/68//none
[3297]/0/3298/1/0x23e6f470/24554/IGN/69/70//none
[3298]/0/3299/1/0x22e80bd0/24552/SINGLE_NODE/71/72//none
[3299]/0/3300/1/0x23e709d8/24550/IGN/73/74//none
[3300]/0/3301/1/0x22e82138/24548/IGN/75/76//none
[3301]/0/3302/1/0x23e71f40/24546/IGN/77/78//none
[3302]/0/3303/1/0x22e836a0/24544/IGN/79/80//none
[3303]/0/3304/1/0x23e734a8/24542/IGN/81/82//none
[3304]/0/3305/1/0x22e84c08/24540/IGN/83/84//none
====================
END OF HANG ANALYSIS
====================

2.查詢相應session

SQL> select username, event, p1,p2,p3 from v$session where sid in (3261,3269);

USERNAME EVENT P1 P2 P3
------------------------------ ---------------------------------------------------------------- ---------- ---------- ----------
SMP SQL*Net message to client 1650815232 1 0
db file sequential read 100 201213 1

3.檢視rollback併發設定

SQL> show parameters parallel_rollback

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fast_start_parallel_rollback string LOW

4.增加rollback併發

SQL> alter system set fast_start_parallel_rollback=high;

System altered.

5.檢視recove程式數量

SQL> select COUNT(*) from v$fast_start_servers where state='RECOVERING'
2 ;

COUNT(*)
----------
32

$ ps -ef|grep ora_
oracle 17027 1 0 13:40 ? 00:00:01 ora_p000_STCSMES
oracle 17029 1 0 13:40 ? 00:00:32 ora_p001_STCSMES
oracle 17031 1 0 13:40 ? 00:00:32 ora_p002_STCSMES
oracle 17033 1 0 13:40 ? 00:00:32 ora_p003_STCSMES
oracle 17035 1 0 13:40 ? 00:00:32 ora_p004_STCSMES
oracle 17037 1 0 13:40 ? 00:00:32 ora_p005_STCSMES
oracle 17039 1 0 13:40 ? 00:00:32 ora_p006_STCSMES
oracle 17041 1 0 13:40 ? 00:00:33 ora_p007_STCSMES
oracle 17043 1 0 13:40 ? 00:00:33 ora_p008_STCSMES
oracle 17045 1 0 13:40 ? 00:00:32 ora_p009_STCSMES
oracle 17047 1 0 13:40 ? 00:00:32 ora_p010_STCSMES
oracle 17049 1 0 13:40 ? 00:00:32 ora_p011_STCSMES
oracle 17051 1 0 13:40 ? 00:00:32 ora_p012_STCSMES
oracle 17053 1 0 13:40 ? 00:00:33 ora_p013_STCSMES
oracle 17055 1 0 13:40 ? 00:00:33 ora_p014_STCSMES
oracle 17057 1 0 13:40 ? 00:00:33 ora_p015_STCSMES
oracle 20163 1 0 14:58 ? 00:00:06 ora_p016_STCSMES
oracle 20165 1 0 14:58 ? 00:00:07 ora_p017_STCSMES
oracle 20170 1 0 14:58 ? 00:00:06 ora_p018_STCSMES
oracle 20172 1 0 14:58 ? 00:00:06 ora_p019_STCSMES
oracle 20174 1 0 14:58 ? 00:00:06 ora_p020_STCSMES
oracle 20176 1 0 14:58 ? 00:00:07 ora_p021_STCSMES
oracle 20178 1 0 14:58 ? 00:00:06 ora_p022_STCSMES
oracle 20180 1 0 14:58 ? 00:00:06 ora_p023_STCSMES
oracle 20182 1 0 14:58 ? 00:00:06 ora_p024_STCSMES
oracle 20184 1 0 14:58 ? 00:00:06 ora_p025_STCSMES
oracle 20186 1 0 14:58 ? 00:00:06 ora_p026_STCSMES
oracle 20188 1 0 14:58 ? 00:00:07 ora_p027_STCSMES
oracle 20190 1 0 14:58 ? 00:00:07 ora_p028_STCSMES
oracle 20192 1 0 14:58 ? 00:00:06 ora_p029_STCSMES
oracle 20194 1 0 14:58 ? 00:00:07 ora_p030_STCSMES
oracle 20196 1 0 14:58 ? 00:00:06 ora_p031_STCSMES
oracle 21411 1 0 15:31 ? 00:00:00 ora_j000_STCSMES
oracle 22510 17339 0 16:06 pts/3 00:00:00 grep ora_
oracle 24540 1 0 Aug10 ? 00:00:07 ora_pmon_STCSMES
oracle 24542 1 0 Aug10 ? 00:00:05 ora_psp0_STCSMES
oracle 24544 1 0 Aug10 ? 00:00:04 ora_mman_STCSMES
oracle 24546 1 0 Aug10 ? 01:25:32 ora_dbw0_STCSMES
oracle 24548 1 0 Aug10 ? 00:22:20 ora_lgwr_STCSMES
oracle 24550 1 0 Aug10 ? 00:03:31 ora_ckpt_STCSMES
oracle 24552 1 0 Aug10 ? 00:01:23 ora_smon_STCSMES
oracle 24554 1 0 Aug10 ? 00:00:04 ora_reco_STCSMES
oracle 24556 1 0 Aug10 ? 00:00:21 ora_cjq0_STCSMES
oracle 24558 1 0 Aug10 ? 00:00:29 ora_mmon_STCSMES
oracle 24560 1 0 Aug10 ? 00:02:18 ora_mmnl_STCSMES
oracle 24562 1 0 Aug10 ? 00:00:04 ora_d000_STCSMES
oracle 24564 1 0 Aug10 ? 00:00:05 ora_s000_STCSMES
oracle 24568 1 0 Aug10 ? 00:17:42 ora_arc0_STCSMES
oracle 24570 1 0 Aug10 ? 00:20:10 ora_arc1_STCSMES
oracle 24572 1 0 Aug10 ? 00:00:04 ora_qmnc_STCSMES
oracle 24580 1 0 Aug10 ? 00:00:12 ora_q000_STCSMES
oracle 24582 1 0 Aug10 ? 00:00:04 ora_q001_STCSMES

SQL> select s.program,s.event from v$session s ,v$px_process p where s.sid=p.sid;

PROGRAM EVENT
------------------------------------------------ ----------------------------------------------------------------
oracle@STCS-MES-ARC (P016) db file sequential read
oracle@STCS-MES-ARC (P017) db file sequential read
oracle@STCS-MES-ARC (P000) wait for a undo record
oracle@STCS-MES-ARC (P018) db file sequential read
oracle@STCS-MES-ARC (P001) db file sequential read
oracle@STCS-MES-ARC (P002) wait for a undo record
oracle@STCS-MES-ARC (P003) db file sequential read
oracle@STCS-MES-ARC (P004) read by other session
oracle@STCS-MES-ARC (P005) db file sequential read
oracle@STCS-MES-ARC (P006) db file sequential read
oracle@STCS-MES-ARC (P007) db file sequential read

PROGRAM EVENT
------------------------------------------------ ----------------------------------------------------------------
oracle@STCS-MES-ARC (P008) db file sequential read
oracle@STCS-MES-ARC (P009) db file sequential read
oracle@STCS-MES-ARC (P010) db file sequential read
oracle@STCS-MES-ARC (P011) db file sequential read
oracle@STCS-MES-ARC (P012) db file sequential read
oracle@STCS-MES-ARC (P013) db file sequential read
oracle@STCS-MES-ARC (P014) db file sequential read
oracle@STCS-MES-ARC (P015) db file sequential read
oracle@STCS-MES-ARC (P019) db file sequential read
oracle@STCS-MES-ARC (P020) db file sequential read
oracle@STCS-MES-ARC (P021) db file sequential read

PROGRAM EVENT
------------------------------------------------ ----------------------------------------------------------------
oracle@STCS-MES-ARC (P022) db file sequential read
oracle@STCS-MES-ARC (P023) wait for a undo record
oracle@STCS-MES-ARC (P024) db file sequential read
oracle@STCS-MES-ARC (P025) db file sequential read
oracle@STCS-MES-ARC (P026) db file sequential read
oracle@STCS-MES-ARC (P027) db file sequential read
oracle@STCS-MES-ARC (P028) db file sequential read
oracle@STCS-MES-ARC (P029) db file sequential read
oracle@STCS-MES-ARC (P030) db file sequential read
oracle@STCS-MES-ARC (P031) db file sequential read

32 rows selected.

6.等待rollback結束

注：

FAST_START_PARALLEL_ROLLBACK determines the maximum number of processes that can exist for performing parallel rollback. This parameter is useful on systems in which some or all of the transactions are long running.

Values:

FALSE indicates that parallel rollback is disabled

LOW limits the number of rollback processes to 2 * CPU_COUNT

HIGH limits the number of rollback processes to 4 * CPU_COUNT

參考網址：http://cuuzhang.blog.163.com/blog/static/60811529200891633834772/

[@more@]