数据库CPU占用100%
段时间发现每天晚上10点至11点之间，生产数据库负载超高，数据库服务器CPU占用几乎达到100%，其余时间又不高。那个时间段内，没有备份等耗用资源操作。在下对调优这部分不熟，对此问题焦头烂额，还往高人指点！拜谢！！！OS：CentOS release 3.6； Kernel:2.4.21-37.ELsmp; DBMS: Oracle 8.1.7.4.0
-----------------------------------------------------------------------------
TOP记录：
22:20:57 up 94 days, 2:21, 2 users, load average: 14.93, 15.14, 11.59
160 processes: 144 sleeping, 16 running, 0 zombie, 0 stopped
CPU states: cpu user nice system irq softirq iowait idle
total 98.2% 0.0% 1.5% 0.0% 0.1% 0.0% 0.0%
cpu00 98.6% 0.0% 1.1% 0.0% 0.1% 0.0% 0.0%
cpu01 99.0% 0.0% 0.9% 0.0% 0.0% 0.0% 0.0%
cpu02 97.2% 0.0% 2.3% 0.1% 0.1% 0.0% 0.0%
cpu03 98.0% 0.0% 1.7% 0.0% 0.1% 0.0% 0.0%
Mem: 4091560k av, 2642916k used, 1448644k free, 0k shrd, 67116k buff
1983588k actv, 223908k in_d, 32k in_c
Swap: 2048216k av, 25636k used, 2022580k free 2046240k cached
PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU COMMAND
24922 oracle 17 0 227M 227M 226M S 10.7 5.6 7:59 3 oracle
25275 oracle 25 0 249M 249M 248M R 9.2 6.2 4:18 0 oracle
25357 oracle 25 0 247M 247M 246M R 8.4 6.1 3:48 0 oracle
25253 oracle 25 0 249M 249M 248M R 7.7 6.2 3:59 1 oracle
25361 oracle 25 0 248M 248M 247M R 7.5 6.2 4:05 0 oracle
25345 oracle 25 0 248M 248M 247M R 7.1 6.2 3:41 3 oracle
25317 oracle 25 0 246M 246M 245M R 7.0 6.1 3:57 0 oracle
24970 oracle 25 0 268M 268M 267M R 6.9 6.7 5:44 3 oracle
25315 oracle 25 0 523M 523M 522M R 6.6 13.0 4:03 2 oracle
25255 oracle 25 0 250M 250M 249M R 6.4 6.2 4:09 3 oracle
25349 oracle 25 0 251M 251M 250M R 5.5 6.2 3:46 2 oracle
25301 oracle 25 0 243M 243M 242M R 5.2 6.1 3:59 0 oracle
25303 oracle 25 0 245M 245M 244M R 3.2 6.1 3:55 2 oracle
25311 oracle 25 0 248M 248M 247M R 2.0 6.2 4:04 0 oracle
30345 oracle 16 0 690M 690M 689M S 1.9 17.2 98:27 1 oracle
25277 oracle 25 0 250M 250M 249M R 1.7 6.2 4:09 3 oracle
12 root 15 0 0 0 0 SW 0.3 0.0 1175m 2 kscand
30177 oracle 15 0 8004 8000 6304 S 0.2 0.1 352:01 2 oracle
26541 oracle 23 0 9392 9388 8508 S 0.1 0.2 0:00 3 oracle
26545 oracle 23 0 10968 10M 10084 S 0.1 0.2 0:00 3 oracle
26547 oracle 23 0 16464 16M 15496 S 0.1 0.4 0:00 3 oracle
26549 oracle 23 0 10428 10M 9544 S 0.1 0.2 0:00 2 oracle
1 root 15 0 188 156 128 S 0.0 0.0 53:46 2 init
2 root RT 0 0 0 0 SW 0.0 0.0 0:00 0 migration/0
3 root RT 0 0 0 0 SW 0.0 0.0 0:00 1 migration/1
4 root RT 0 0 0 0 SW 0.0 0.0 0:00 2 migration/2
5 root RT 0 0 0 0 SW 0.0 0.0 0:00 3 migration/3
=============================================== STATSPACK report for
DB Name DB Id Instance Inst Num Release OPS Host
------------ ----------- ------------ -------- ----------- --- ------------
SOUTHDB 4111341222 southdb 1 8.1.7.4.0 NO orcl-db02
Snap Id Snap Time Sessions
------- ------------------ --------
Begin Snap: 101 10-Jul-08 22:39:09 87
End Snap: 102 10-Jul-08 23:11:52 87
Elapsed: 32.72 (mins)
Cache Sizes
~~~~~~~~~~~
db_block_buffers: 115200 log_buffer: 655360
db_block_size: 8192 shared_pool_size: 524288000
Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 19,273.48 1,315.32
Logical reads: 55,618.61 3,795.69
Block changes: 120.76 8.24
Physical reads: 257.23 17.55
Physical writes: 13.93 0.95
User calls: 228.11 15.57
Parses: 43.95 3.00
Hard parses: 2.39 0.16
Sorts: 1.14 0.08
Logons: 1.09 0.07
Executes: 67.34 4.60
Transactions: 14.65
% Blocks changed per Read: 0.22 Recursive Call %: 29.35
Rollback per transaction %: 0.00 Rows per Sort: #######
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 99.54 In-memory Sort %: 99.91
Library Hit %: 97.26 Soft Parse %: 94.56
Execute to Parse %: 34.74 Latch Hit %: 99.67
Parse CPU to Parse Elapsd %: 28.17 % Non-Parse CPU: 99.81
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 87.43 83.41
% SQL with executions>1: 55.94 57.83
% Memory for SQL w/exec>1: 42.83 42.82
Top 5 Wait Events
~~~~~~~~~~~~~~~~~ Wait % Total
Event Waits Time (cs) Wt Time
-------------------------------------------- ------------ ------------ -------
latch free 12,562 87,577 64.46
log file sync 28,729 42,299 31.14
log file parallel write 29,577 2,989 2.20
db file sequential read 4,213 2,288 1.68
db file scattered read 61,681 439 .32
-------------------------------------------------------------
Wait Events for DB: SOUTHDB Instance: southdb Snaps: 101 -102
-> cs - centisecond - 100th of a second
-> ms - millisecond - 1000th of a second
-> ordered by wait time desc, waits desc (idle events last)
Avg
Total Wait wait Waits
Event Waits Timeouts Time (cs) (ms) /txn
---------------------------- ------------ ---------- ----------- ------ ------
latch free 12,562 11,835 87,577 70 0.4
log file sync 28,729 7 42,299 15 1.0
log file parallel write 29,577 0 2,989 1 1.0
db file sequential read 4,213 0 2,288 5 0.1
db file scattered read 61,681 0 439 0 2.1
buffer busy waits 26 0 164 63 0.0
LGWR wait for redo copy 101 57 58 6 0.0
enqueue 138 0 19 1 0.0
control file parallel write 639 0 11 0 0.0
SQL*Net break/reset to clien 154 0 4 0 0.0
SQL*Net more data to client 977 0 3 0 0.0
file open 973 0 2 0 0.0
direct path read 2,384 0 1 0 0.1
db file parallel write 625 0 0 0 0.0
control file sequential read 118 0 0 0 0.0
refresh controlfile command 10 0 0 0 0.0
direct path write 4 0 0 0 0.0
SQL*Net message from client 444,838 0 4,624,386 104 15.5
virtual circuit status 63 63 193,536 30720 0.0
SQL*Net message to client 444,829 0 55 0 15.5
SQL*Net more data from clien 10 0 0 0 0.0
-------------------------------------------------------------
Background Wait Events for DB: SOUTHDB Instance: southdb Snaps: 101 -102
-> ordered by wait time desc, waits desc (idle events last)
Avg
Total Wait wait Waits
Event Waits Timeouts Time (cs) (ms) /txn
---------------------------- ------------ ---------- ----------- ------ ------
log file parallel write 29,577 0 2,989 1 1.0
LGWR wait for redo copy 101 57 58 6 0.0
control file parallel write 639 0 11 0 0.0
latch free 5 5 10 20 0.0
db file sequential read 1 0 1 10 0.0
db file parallel write 625 0 0 0 0.0
control file sequential read 60 0 0 0 0.0
rdbms ipc message 29,455 2,036 1,783,505 606 1.0
pmon timer 685 664 196,317 2866 0.0
smon timer 6 6 184,320 ###### 0.0
-------------------------------------------------------------
SQL ordered by Gets for DB: SOUTHDB Instance: southdb Snaps: 101 -102
-> End Buffer Gets Threshold: 10000
-> Note that resources reported for PL/SQL includes the resources used by
all SQL statements called within the PL/SQL code. As individual SQL
statements are also reported, it is possible and valid for the summed
total % to exceed 100
Buffer Gets Executions Gets per Exec % Total Hash Value
--------------- ------------ -------------- ------- ------------
103,684,734 5,450 19,024.7 95.0 2078465777
select count(*) as cou from jysb3_mtlog where phone=:1 and creat
e_date>=to_date('2008-7-01 00:00:00','yyyy-MM-dd HH24:mi:ss')
3,410,316 378 9,022.0 3.1 2213417779
select ID,PHONE,SALE_CONTENT from SD_SALE_AIM where status = 0 a
nd rownum<=50
485,429 28 17,336.8 0.4 946345201
select id,phone,addcent from (select * from BEIJING_USERLOG wher
e type=:1 order by addcent desc) where rownum<=:2
192,305 38,461 5.0 0.2 1560276247
select to_char(sysdate,'HH24:MI:SS') from dual
176,006 1 176,006.0 0.2 3376831664
BEGIN statspack.snap; END;
170,670 1 170,670.0 0.2 625421128
INSERT INTO STATS$SQLTEXT ( HASH_VALUE,TEXT_SUBSET,PIECE,SQL_TEX
T,ADDRESS,COMMAND_TYPE,LAST_SNAP_ID ) SELECT ST1.HASH_VALUE,SS.
TEXT_SUBSET,ST1.PIECE,ST1.SQL_TEXT,ST1.ADDRESS,ST1.COMMAND_TYPE,
SS.SNAP_ID FROM V$SQLTEXT ST1,STATS$SQL_SUMMARY SS WHERE SS.S
NAP_ID = :b1 AND SS.DBID = :b2 AND SS.INSTANCE_NUMBER = :b3 A
99,538 6,042 16.5 0.1 606567490
insert into JYSB3_mtlog(id,phone,content,spnumber,smgid,create_d
ate,cid,province,city) values(SEQ_JYSB3_MTLOG_ID.NEXTVAL,:1,:2,:
3,:4,sysdate,2375,webdev.getPname(:5),webdev.getCityName(:6))
71,929 4,360 16.5 0.1 972324682
insert into JYSB3_MOLOG(id,phone,content,spnumber,smgid,create_d
ate,cid,province,city) values(SEQ_JYSB3_MOLOG_ID.NEXTVAL,:1,:2,:
3,:4,sysdate,2375,webdev.getPname(:5),webdev.getCityName(:6))
70,028 23,445 3.0 0.1 3577479866
SELECT PROVINCENAME FROM XD_TELEROUTER WHERE TELESEG = SUBSTR
(:b1,1,7)
67,210 65 1,034.0 0.1 787405797
select PROVINCENAME from xd_telerouter where teleseg = :1
53,768 52 1,034.0 0.0 1789589189
select CITYNAME from xd_telerouter where teleseg = :1
52,421 2,426 21.6 0.0 3796848796
update BEIJING_USER set cent=:1 ,month_cent=:2,update_time=sysd
ate ,grade=:3 where phone=:4
38,997 13,167 3.0 0.0 112619266
SELECT CITYNAME FROM XD_TELEROUTER WHERE TELESEG = SUBSTR(:b1
SQL ordered by Gets for DB: SOUTHDB Instance: southdb Snaps: 101 -102
-> End Buffer Gets Threshold: 10000
-> Note that resources reported for PL/SQL includes the resources used by
all SQL statements called within the PL/SQL code. As individual SQL
statements are also reported, it is possible and valid for the summed
total % to exceed 100
Buffer Gets Executions Gets per Exec % Total Hash Value
--------------- ------------ -------------- ------- ------------
,1,7)
37,700 20 1,885.0 0.0 394151625
select count(*) from CITYJJYL_LOG where phone = :1 and content=:
2 and create_date>=to_date('2008-7-10 00:00:00','yyyy-MM-dd HH24
:mi:ss') and create_date<=to_date('2008-7-10 23:59:59','yyyy-MM-
dd HH24:mi:ss')
37,697 20 1,884.9 0.0 797325693
select count(*) as cou from CITYJJYL_LOG where phone=:1 and crea
te_date>=to_date('2008-7-01 00:00:00','yyyy-MM-dd HH24:mi:ss') a
nd create_date<=to_date('2008-7-31 23:59:59','yyyy-MM-dd HH24:mi
:ss')
36,900 4,405 8.4 0.0 666545681
insert into BEIJING_USERLOG (id,phone,addcent,answer,reason,pro
vince,city,onlinewrongnum,cid,comm,type,create_date) values (SEQ
_BEIJING_USERLOG_ID.NEXTVAL,:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,sysda
te)
32,111 1 32,111.0 0.0 3838528089
select max(sequenceid) as mosequenceid, smgid as mosmgid, phone
as chargephone, webdev.getpname(phone) as province ,spnumber fro
m beijing_pushuser a where 1=1 and webdev.getpname(phone) not i
n ('北京','福建','山西') and a.smgid in (182,284,280)and a.crea
te_date between to_date('2008-07-10 22:10', 'yyyy-mm-dd hh24:mi'
29,907 1,266 23.6 0.0 2504625627
insert into BEIJING_USER (id,phone,cent,province,city,month_cen
t) values (SEQ_BEIJING_USER_ID.NEXTVAL,:1,:2,:3,:4,:5)
23,782 23 1,034.0 0.0 3336809043
select provincename,cityname from xd_telerouter where teleseg=:1
22,058 3,151 7.0 0.0 3574292278
update JYSB3_USER set MO_COUNT=:1,MT_COUNT=:2,onlinerightnum=:3
,ONLINEANSWER=:4,cent=:5,offlineid=:6, offlinerightnum=:7,OFFLIN
EANSWER=:8,GRADE=:9,onlinewrongnum=:10, answerlimit=:11,msglimit
=:12,updategradelimit=:13,allanswer =:14 ,onlineemptynum=:15,day
mo_count=:16,smgid=:17 where phone=:18
20,355 1,503 13.5 0.0 1296318986
insert into BEIJING_PUSHUSER (id,phone,smgid,cid,spnumber,conten
t,sequenceid,status,mo_count,mt_count,online_count,offline_count
,create_date,push_content) values (Seq_Beijing_Pushuser_Id.nextv
al,:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,sysdate,:12)
17,345 1 17,345.0 0.0 4027067281
update BEIJING_USERLOG set REASON=:1 where PHONE=:2 and ONLINEW
RONGNUM=:3 and cid=:4
SQL ordered by Gets for DB: SOUTHDB Instance: southdb Snaps: 101 -102
-> End Buffer Gets Threshold: 10000
-> Note that resources reported for PL/SQL includes the resources used by
all SQL statements called within the PL/SQL code. As individual SQL
statements are also reported, it is possible and valid for the summed
total % to exceed 100
Buffer Gets Executions Gets per Exec % Total Hash Value
--------------- ------------ -------------- ------- ------------
16,139 4,324 3.7 0.0 1256012280
select * from JYSB3_USER where phone= :1
13,490 1,183 11.4 0.0 1282938696
insert into JYSB3_USER (id,phone,smgid,cent,onlinerightnum,offl
inerightnum,offlineid,province,city, mt_count,mo_count,create_d
ate,onlineanswer,offlineanswer,grade, onlinewrongnum,answerlimi
t,msglimit,updategradelimit,allanswer,onlineemptynum,daymo_count
-------------------------------------------------------------
SQL ordered by Reads for DB: SOUTHDB Instance: southdb Snaps: 101 -102
-> End Disk Reads Threshold: 1000
Physical Reads Executions Reads per Exec % Total Hash Value
--------------- ------------ -------------- ------- ------------
469,784 28 16,778.0 93.0 946345201
select id,phone,addcent from (select * from BEIJING_USERLOG wher
e type=:1 order by addcent desc) where rownum<=:2
16,774 1 16,774.0 3.3 4027067281
update BEIJING_USERLOG set REASON=:1 where PHONE=:2 and ONLINEW
RONGNUM=:3 and cid=:4
15,454 1 15,454.0 3.1 3376831664
BEGIN statspack.snap; END;
7,505 1 7,505.0 1.5 2064955574
INSERT INTO STATS$SQL_SUMMARY ( SNAP_ID,DBID,INSTANCE_NUMBER,TEX
T_SUBSET,SHARABLE_MEM,SORTS,MODULE,LOADED_VERSIONS,EXECUTIONS,LO
ADS,INVALIDATIONS,PARSE_CALLS,DISK_READS,BUFFER_GETS,ROWS_PROCES
SED,ADDRESS,HASH_VALUE,VERSION_COUNT ) SELECT :b1,:b2,:b3,SUBST
RB(SQL_TEXT,1,31),SHARABLE_MEM,SORTS,MODULE,LOADED_VERSIONS,EXEC
7,374 1 7,374.0 1.5 3337740287
INSERT INTO STATS$SQL_STATISTICS ( SNAP_ID,DBID,INSTANCE_NUMBER,
TOTAL_SQL,TOTAL_SQL_MEM,SINGLE_USE_SQL,SINGLE_USE_SQL_MEM ) SEL
ECT :b1,:b2,:b3,COUNT(1),SUM(SHARABLE_MEM),SUM(DECODE(EXECUTIONS
,1,1,0)),SUM(DECODE(EXECUTIONS,1,SHARABLE_MEM,0)) FROM V$SQLXS
1,064 378 2.8 0.2 2213417779
select ID,PHONE,SALE_CONTENT from SD_SALE_AIM where status = 0 a
nd rownum<=50
1,028 1 1,028.0 0.2 4116281121
select * from tb_sendpush_user where status=0
326 1 326.0 0.1 625421128
INSERT INTO STATS$SQLTEXT ( HASH_VALUE,TEXT_SUBSET,PIECE,SQL_TEX
T,ADDRESS,COMMAND_TYPE,LAST_SNAP_ID ) SELECT ST1.HASH_VALUE,SS.
TEXT_SUBSET,ST1.PIECE,ST1.SQL_TEXT,ST1.ADDRESS,ST1.COMMAND_TYPE,
SS.SNAP_ID FROM V$SQLTEXT ST1,STATS$SQL_SUMMARY SS WHERE SS.S
NAP_ID = :b1 AND SS.DBID = :b2 AND SS.INSTANCE_NUMBER = :b3 A
316 2,426 0.1 0.1 3796848796
update BEIJING_USER set cent=:1 ,month_cent=:2,update_time=sysd
ate ,grade=:3 where phone=:4
315 4,324 0.1 0.1 1256012280
select * from JYSB3_USER where phone= :1
75 1,503 0.0 0.0 1296318986
insert into BEIJING_PUSHUSER (id,phone,smgid,cid,spnumber,conten
t,sequenceid,status,mo_count,mt_count,online_count,offline_count
,create_date,push_content) values (Seq_Beijing_Pushuser_Id.nextv
al,:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,sysdate,:12)
39 1 39.0 0.0 421456856
select * from contentInfo
SQL ordered by Reads for DB: SOUTHDB Instance: southdb Snaps: 101 -102
-> End Disk Reads Threshold: 1000
Physical Reads Executions Reads per Exec % Total Hash Value
--------------- ------------ -------------- ------- ------------
17 4 4.3 0.0 4123747973
select count(*) as cou from TB_HDYL_LOG where phone=:1 and type=
1 and createtime>=to_date('2008-7-01 00:00:00','yyyy-MM-dd HH24:
mi:ss')
12 959 0.0 0.0 108540717
update BEIJING_PUSHUSER set phone=:1,smgid=:2,cid=:3,spnumber=:4
,content=:5,sequenceid=:6,status=:7,mo_count=:8,mt_count=:9,onli
ne_count=:10,offline_count=:11,create_date=sysdate,push_content=
:12 where id=:13
12 102 0.1 0.0 1234760274
select VALUE from xlbxt15_online WHERE KEY = :1 and CREATE_DA
TE = :2 ORDER BY dbms_random.value
4 7 0.6 0.0 2137867967
select count(phone) as amount from TB_JFDH_USER where phone = :1
4 1,266 0.0 0.0 2504625627
insert into BEIJING_USER (id,phone,cent,province,city,month_cen
t) values (SEQ_BEIJING_USER_ID.NEXTVAL,:1,:2,:3,:4,:5)
3 4 0.8 0.0 3424214036
select count(phone) as amount from TB_HDYL_USER where phone = :1
2 1 2.0 0.0 1357714092
select * from relation_Info where serviceId=:1
1 3 0.3 0.0 655157552
select * from SDQL_38_ANSWER_MT
1 3 0.3 0.0 835974764
select * from SDQL_38_CONFIG
1 12 0.1 0.0 1104980192
select * from JYSB3_ONLINE where question_date = '2008-07-10'
1 1 1.0 0.0 1212505517
select i.obj#, i.flags, u.name, o.name from sys.obj$ o, sys.
user$ u, sys.ind$ i where (bitand(i.flags, 256) = 256 or bit
and(i.flags, 512) = 512) and (not((i.type# = 9) and bi
tand(i.flags,8) = 8)) and o.obj#=i.obj# and o.owner# =
u.user#
1 14 0.1 0.0 1439163872
select * from JYSB3_CONFIG
1 3 0.3 0.0 1483502489
select * from YZQJ4_OFFLINE order by id
1 1 1.0 0.0 2435902127
select * from TB_TTWQHD_MT_CONFIG
SQL ordered by Reads for DB: SOUTHDB Instance: southdb Snaps: 101 -102
-> End Disk Reads Threshold: 1000
Physical Reads Executions Reads per Exec % Total Hash Value
--------------- ------------ -------------- ------- ------------
1 3 0.3 0.0 2723396353
select * from CITYJJYL_SPCMT
1 1 1.0 0.0 3132505045
select * from TB_TTXBDS_MT
1 24 0.0 0.0 3426566803
select * from SDQL_38_USER where phone= :1
1 23,445 0.0 0.0 3577479866
SELECT PROVINCENAME FROM XD_TELEROUTER WHERE TELESEG = SUBSTR
(:b1,1,7)
1 3 0.3 0.0 3879500953
select * from ZDZYW3_OFFLINE order by id
-------------------------------------------------------------
SQL ordered by Executions for DB: SOUTHDB Instance: southdb Snaps: 101 -102
-> End Executions Threshold: 100
Executions Rows Processed Rows per Exec Hash Value
------------ ---------------- ---------------- ------------
38,461 38,461 1.0 1560276247
select to_char(sysdate,'HH24:MI:SS') from dual
23,445 22,876 1.0 3577479866
SELECT PROVINCENAME FROM XD_TELEROUTER WHERE TELESEG = SUBSTR
(:b1,1,7)
13,167 12,663 1.0 112619266
SELECT CITYNAME FROM XD_TELEROUTER WHERE TELESEG = SUBSTR(:b1
,1,7)
6,042 6,042 1.0 606567490
insert into JYSB3_mtlog(id,phone,content,spnumber,smgid,create_d
ate,cid,province,city) values(SEQ_JYSB3_MTLOG_ID.NEXTVAL,:1,:2,:
3,:4,sysdate,2375,webdev.getPname(:5),webdev.getCityName(:6))
5,450 5,450 1.0 2078465777
select count(*) as cou from jysb3_mtlog where phone=:1 and creat
e_date>=to_date('2008-7-01 00:00:00','yyyy-MM-dd HH24:mi:ss')
4,405 4,405 1.0 666545681
insert into BEIJING_USERLOG (id,phone,addcent,answer,reason,pro
vince,city,onlinewrongnum,cid,comm,type,create_date) values (SEQ
_BEIJING_USERLOG_ID.NEXTVAL,:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,sysda
te)
4,360 4,360 1.0 972324682
insert into JYSB3_MOLOG(id,phone,content,spnumber,smgid,create_d
ate,cid,province,city) values(SEQ_JYSB3_MOLOG_ID.NEXTVAL,:1,:2,:
3,:4,sysdate,2375,webdev.getPname(:5),webdev.getCityName(:6))
4,324 3,151 0.7 1256012280
select * from JYSB3_USER where phone= :1
3,151 3,153 1.0 3574292278
update JYSB3_USER set MO_COUNT=:1,MT_COUNT=:2,onlinerightnum=:3
,ONLINEANSWER=:4,cent=:5,offlineid=:6, offlinerightnum=:7,OFFLIN
EANSWER=:8,GRADE=:9,onlinewrongnum=:10, answerlimit=:11,msglimit
=:12,updategradelimit=:13,allanswer =:14 ,onlineemptynum=:15,day
mo_count=:16,smgid=:17 where phone=:18
2,426 2,426 1.0 3796848796
update BEIJING_USER set cent=:1 ,month_cent=:2,update_time=sysd
ate ,grade=:3 where phone=:4
2,151 2,151 1.0 1475702249
update BEIJING_PUSHUSER set status=1,push_date=sysdate where id=
:1
1,503 1,503 1.0 1296318986
insert into BEIJING_PUSHUSER (id,phone,smgid,cid,spnumber,conten
t,sequenceid,status,mo_count,mt_count,online_count,offline_count
,create_date,push_content) values (Seq_Beijing_Pushuser_Id.nextv
al,:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,sysdate,:12)
SQL ordered by Executions for DB: SOUTHDB Instance: southdb Snaps: 101 -102
-> End Executions Threshold: 100
Executions Rows Processed Rows per Exec Hash Value
------------ ---------------- ---------------- ------------
1,266 1,265 1.0 2504625627
insert into BEIJING_USER (id,phone,cent,province,city,month_cen
t) values (SEQ_BEIJING_USER_ID.NEXTVAL,:1,:2,:3,:4,:5)
1,183 1,183 1.0 1282938696
insert into JYSB3_USER (id,phone,smgid,cent,onlinerightnum,offl
inerightnum,offlineid,province,city, mt_count,mo_count,create_d
ate,onlineanswer,offlineanswer,grade, onlinewrongnum,answerlimi
t,msglimit,updategradelimit,allanswer,onlineemptynum,daymo_count
) values (SEQ_JYSB3_USER_ID.NEXTVAL,:1,:2,:3,:4,:5,:6,:7,:8,:9,:
1,123 1,123 1.0 1425443843
update seq$ set increment$=:2,minvalue=:3,maxvalue=:4,cycle#=:5,
order$=:6,cache=:7,highwater=:8,audit$=:9 where obj#=:1
959 959 1.0 108540717
update BEIJING_PUSHUSER set phone=:1,smgid=:2,cid=:3,spnumber=:4
,content=:5,sequenceid=:6,status=:7,mo_count=:8,mt_count=:9,onli
ne_count=:10,offline_count=:11,create_date=sysdate,push_content=
:12 where id=:13
867 0 0.0 106347135
ALTER SESSION SET NLS_LANGUAGE = 'SIMPLIFIED CHINESE'
867 0 0.0 825060501
ALTER SESSION SET NLS_TERRITORY = 'CHINA'
867 867 1.0 3013728279
select privilege#,level from sysauth$ connect by grantee#=prior
privilege# and privilege#>0 start with (grantee#=:1 or grantee#=
1) and privilege#>0
867 867 1.0 3617023014
SELECT VALUE FROM NLS_INSTANCE_PARAMETERS WHERE PARAMETER ='NLS_
DATE_FORMAT'
640 0 0.0 313510536
select job from sys.job$ where next_date < sysdate and (field1
= :1 or (field1 = 0 and 'Y' = :2)) order by next_date, job
560 488 0.9 2144344806
insert into TB_ZLGAME_LOG(ID,type,cid,smgid,spnumber,phone,conte
nt,provincename,cityname) values(SEQ_TB_TICKET_LOG_ID.NEXTVAL,:1
,:2,:3,:4,:5,:6,:7,:8)
378 0 0.0 2213417779
select ID,PHONE,SALE_CONTENT from SD_SALE_AIM where status = 0 a
nd rownum<=50
280 280 1.0 2923686204
insert into TB_ZLGAME_USER(ID,SMGID,PHONE,PROVINCENAME,CITYNAME,
SEQUENCEID,SPNUMBER,UPDATEDATE,CREATEDATE) values (SEQ_TB_TICKET
_USER_ID.nextval,:1,:2,:3,:4,:5,:6,sysdate,sysdate)
196 0 0.0 415803360
SQL ordered by Executions for DB: SOUTHDB Instance: southdb Snaps: 101 -102
-> End Executions Threshold: 100
Executions Rows Processed Rows per Exec Hash Value
------------ ---------------- ---------------- ------------
select id,sequenceid,smgid,spnumber,phone from STARKNOWHEART_PUS
H where LAST_DATE<=sysdate-0.00138888888 and status = 0 and CONS
TELLATION = 0 and rownum<=40
------------------------------------------------------------- Instance Activity Stats for DB: SOUTHDB Instance: southdb Snaps: 101 -102
Statistic Total per Second per Trans
--------------------------------- ---------------- ------------ ------------
CPU used by this session 616,402 314.0 21.4
CPU used when call started 616,403 314.0 21.4
CR blocks created 2,713 1.4 0.1
Cached Commit SCN referenced 0 0.0 0.0
DBWR buffers scanned 0 0.0 0.0
DBWR checkpoint buffers written 12,363 6.3 0.4
DBWR checkpoints 0 0.0 0.0
DBWR free buffers found 0 0.0 0.0
DBWR lru scans 0 0.0 0.0
DBWR make free requests 0 0.0 0.0
DBWR revisited being-written buff 0 0.0 0.0
DBWR summed scan depth 0 0.0 0.0
DBWR transaction table writes 270 0.1 0.0
DBWR undo block writes 1,519 0.8 0.1
PX local messages recv'd 0 0.0 0.0
PX local messages sent 0 0.0 0.0
SQL*Net roundtrips to/from client 442,057 225.2 15.4
SQL*Net roundtrips to/from dblink 0 0.0 0.0
background checkpoints completed 0 0.0 0.0
background checkpoints started 0 0.0 0.0
background timeouts 2,606 1.3 0.1
branch node splits 0 0.0 0.0
buffer is not pinned count 98,725,444 50,293.2 3,432.3
buffer is pinned count 7,995,563,564 4,073,134.8 277,971.2
bytes received via SQL*Net from c 25,528,948 13,005.1 887.5
bytes received via SQL*Net from d 0 0.0 0.0
bytes sent via SQL*Net to client 25,731,142 13,108.1 894.6
bytes sent via SQL*Net to dblink 0 0.0 0.0
calls to get snapshot scn: kcmgss 191,319 97.5 6.7
calls to kcmgas 30,029 15.3 1.0
calls to kcmgcs 331 0.2 0.0
change write time 339 0.2 0.0
cleanouts and rollbacks - consist 1 0.0 0.0
cleanouts only - consistent read 65 0.0 0.0
cluster key scan block gets 1,071 0.6 0.0
cluster key scans 145 0.1 0.0
commit cleanout failures: block l 0 0.0 0.0
commit cleanout failures: buffer 0 0.0 0.0
commit cleanout failures: callbac 65 0.0 0.0
commit cleanout failures: cannot 11 0.0 0.0
commit cleanouts 83,412 42.5 2.9
commit cleanouts successfully com 83,336 42.5 2.9
consistent changes 37,198 19.0 1.3
consistent gets 108,715,322 55,382.2 3,779.6
current blocks converted for CR
cursor authentications 1,870 1.0 0.1
data blocks consistent reads - un 37,198 19.0 1.3
db block changes 237,055 120.8 8.2
db block gets 463,984 236.4 16.1
deferred (CURRENT) block cleanout 43,292 22.1 1.5
dirty buffers inspected 0 0.0 0.0
enqueue conversions 0 0.0 0.0
enqueue releases 63,616 32.4 2.2
enqueue requests 63,616 32.4 2.2
enqueue timeouts 0 0.0 0.0
enqueue waits 0 0.0 0.0
Instance Activity Stats for DB: SOUTHDB Instance: southdb Snaps: 101 -102
Statistic Total per Second per Trans
--------------------------------- ---------------- ------------ ------------
exchange deadlocks 0 0.0 0.0
execute count 132,193 67.3 4.6
free buffer inspected 33 0.0 0.0
free buffer requested 494,552 251.9 17.2
hot buffers moved to head of LRU 3,742 1.9 0.1
immediate (CR) block cleanout app 66 0.0 0.0
immediate (CURRENT) block cleanou 4,779 2.4 0.2
index fast full scans (full) 0 0.0 0.0
leaf node splits 113 0.1 0.0
logons cumulative 2,145 1.1 0.1
messages received 30,415 15.5 1.1
messages sent 30,415 15.5 1.1
no buffer to keep pinned count 0 0.0 0.0
no work - consistent read gets 108,342,594 55,192.4 3,766.6
opened cursors cumulative 85,171 43.4 3.0
parse count (hard) 4,695 2.4 0.2
parse count (total) 86,268 44.0 3.0
parse time cpu 1,143 0.6 0.0
parse time elapsed 4,058 2.1 0.1
physical reads 504,936 257.2 17.6
physical reads direct 14,985 7.6 0.5
physical writes 27,348 13.9 1.0
physical writes direct 14,985 7.6 0.5
physical writes non checkpoint 18,124 9.2 0.6
pinned buffers inspected 1 0.0 0.0
prefetched blocks 424,062 216.0 14.7
prefetched blocks aged out before 6 0.0 0.0
process last non-idle time ################ ############ ############
recursive calls 186,004 94.8 6.5
recursive cpu usage 1,137 0.6 0.0
redo blocks written 91,225 46.5 3.2
redo buffer allocation retries 0 0.0 0.0
redo entries 121,363 61.8 4.2
redo log space requests 0 0.0 0.0
redo log space wait time 0 0.0 0.0
redo ordering marks 0 0.0 0.0
redo size 37,833,848 19,273.5 1,315.3
redo synch time 42,275 21.5 1.5
redo synch writes 28,814 14.7 1.0
redo wastage 7,584,600 3,863.8 263.7
redo write time 2,991 1.5 0.1
redo writer latching time 58 0.0 0.0
redo writes 29,565 15.1 1.0
rollback changes - undo records a 3 0.0 0.0
rollbacks only - consistent read 3,164 1.6 0.1
rows fetched via callback 41,489 21.1 1.4
session connect time ################ ############ ############
session logical reads 109,179,328 55,618.6 3,795.7
session pga memory 3,824,440,260 1,948,263.0 132,959.3
session pga memory max 5,552,887,956 2,828,776.3 193,049.9
session uga memory 11,064,308 5,636.4 384.7
session uga memory max 64,437,136 32,825.9 2,240.2
sorts (disk) 2 0.0 0.0
sorts (memory) 2,238 1.1 0.1
sorts (rows) 45,501,663 23,179.7 1,581.9
summed dirty queue length 0 0.0 0.0
Instance Activity Stats for DB: SOUTHDB Instance: southdb Snaps: 101 -102
Statistic Total per Second per Trans
--------------------------------- ---------------- ------------ ------------
switch current to new buffer
table fetch by rowid 4,044,994,648 2,060,618.8 140,627.0
table fetch continued row 0 0.0 0.0
table scan blocks gotten 4,213,177 2,146.3 146.5
table scan rows gotten 228,328,194 116,315.9 7,938.0
table scans (direct read) 0 0.0 0.0
table scans (long tables) 407 0.2 0.0
table scans (rowid ranges) 0 0.0 0.0
table scans (short tables) 42,975 21.9 1.5
total file opens 973 0.5 0.0
transaction rollbacks 1 0.0 0.0
transaction tables consistent rea 0 0.0 0.0
transaction tables consistent rea 0 0.0 0.0
user calls 447,773 228.1 15.6
user commits 28,764 14.7 1.0
user rollbacks 0 0.0 0.0
write clones created in backgroun 0 0.0 0.0
write clones created in foregroun 2 0.0 0.0
-------------------------------------------------------------
Tablespace IO Stats for DB: SOUTHDB Instance: southdb Snaps: 101 -102
->ordered by IOs (Reads + Writes) desc
Tablespace
------------------------------
Av Av Av Av Buffer Av Buf
Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits Wt(ms)
-------------- ------- ------ ------- ------------ -------- ---------- ------
BEIJING_DATA
64,620 33 0.4 7.5 10,122 5 23 70.9
TEMP
6,339 3 7.8 2.4 3,754 2 0 0.0
RBS
0 0 0.0 1,793 1 3 3.3
TVGAME_DATA
887 0 0.7 2.5 122 0 0 0.0
PERFSTAT_DATA
416 0 3.9 1.0 323 0 0 0.0
SYSTEM
4 0 7.5 1.0 52 0 0 0.0
USER_ENGINE_DATA
2 0 20.0 1.0 0 0 0 0.0
DATACENTER_DATA
1 0 220.0 1.0 0 0 0 0.0
-------------------------------------------------------------
File IO Stats for DB: SOUTHDB Instance: southdb Snaps: 101 -102
->ordered by Tablespace, File
Tablespace Filename
------------------------ ----------------------------------------------------
Av Av Av Av Buffer Av Buf
Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits Wt(ms)
-------------- ------- ------ ------- ------------ -------- ---------- ------
BEIJING_DATA /u01/app/oracle/oradata/southdb/beijing_data01.dbf
955 0 0.6 7.8 205 0 0
/u01/app/oracle/oradata/southdb/beijing_data02.dbf
954 0 0.6 7.8 200 0 2 0.0
/u01/app/oracle/oradata/southdb/beijing_data03.dbf
948 0 0.9 7.9 152 0 0
/u01/app/oracle/oradata/southdb/beijing_data04.dbf
940 0 0.4 7.9 138 0 0
/u01/app/oracle/oradata/southdb/beijing_data05.dbf
943 0 0.5 7.9 217 0 0
/u01/app/oracle/oradata/southdb/beijing_data06.dbf
956 0 0.9 7.8 114 0 0
/u01/app/oracle/oradata/southdb/beijing_data07.dbf
938 0 0.2 7.9 123 0 0
/u01/app/oracle/oradata/southdb/beijing_data08.dbf
947 0 1.4 7.9 217 0 0
/u01/app/oracle/oradata/southdb/beijing_data09.dbf
954 0 0.9 7.8 202 0 0
/u01/app/oracle/oradata/southdb/beijing_data10.dbf
949 0 0.6 7.8 401 0 1 130.0
/u01/app/oracle/oradata/southdb/beijing_data11.dbf
939 0 0.2 7.9 208 0 0
/u01/app/oracle/oradata/southdb/beijing_data12.dbf
968 0 0.5 7.9 183 0 0
/u01/app/oracle/oradata/southdb/beijing_data13.dbf
970 0 0.9 7.9 168 0 0
/u01/app/oracle/oradata/southdb/beijing_data14.dbf
965 0 0.5 7.9 96 0 0
/u01/app/oracle/oradata/southdb/beijing_data15.dbf
974 0 0.2 7.9 189 0 0
/u01/app/oracle/oradata/southdb/beijing_data16.dbf
970 0 0.5 7.9 167 0 0
/u01/app/oracle/oradata/southdb/beijing_data17.dbf
964 0 0.3 7.9 134 0 0
/u01/app/oracle/oradata/southdb/beijing_data18.dbf
969 0 0.3 7.9 147 0 0
/u01/app/oracle/oradata/southdb/beijing_data19.dbf
976 0 0.4 7.9 129 0 0
/u01/app/oracle/oradata/southdb/beijing_data20.dbf
4,219 2 0.1 8.0 97 0 0
/u01/app/oracle/oradata/southdb/beijing_data21.dbf
4,227 2 0.2 8.0 98 0 0
/u01/app/oracle/oradata/southdb/beijing_data22.dbf
4,237 2 0.3 7.9 148 0 0
/u01/app/oracle/oradata/southdb/beijing_data23.dbf
4,250 2 0.4 7.9 242 0 0
/u01/app/oracle/oradata/southdb/beijing_data24.dbf
4,214 2 0.3 7.9 240 0 0
/u01/app/oracle/oradata/southdb/beijing_data25.dbf
4,216 2 0.3 7.9 520 0 0
/u01/app/oracle/oradata/southdb/beijing_data26.dbf
4,261 2 0.4 7.9 487 0 0
File IO Stats for DB: SOUTHDB Instance: southdb Snaps: 101 -102
->ordered by Tablespace, File
Tablespace Filename
------------------------ ----------------------------------------------------
Av Av Av Av Buffer Av Buf
Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits Wt(ms)
-------------- ------- ------ ------- ------------ -------- ---------- ------
BEIJING_DATA /u01/app/oracle/oradata/southdb/beijing_data27.dbf
4,254 2 0.4 7.9 630 0 0
/u01/app/oracle/oradata/southdb/beijing_data28.dbf
4,931 3 0.4 3.1 978 0 19 78.9
/u01/app/oracle/oradata/southdb/beijing_data29.dbf
478 0 0.7 7.8 780 0 0
/u01/app/oracle/oradata/southdb/beijing_data30.dbf
477 0 0.5 7.8 260 0 0
/u01/app/oracle/oradata/southdb/beijing_data31.dbf
478 0 0.8 7.8 492 0 0
/u01/app/oracle/oradata/southdb/beijing_data32.dbf
467 0 0.6 8.0 171 0 0
/u01/app/oracle/oradata/southdb/beijing_data33.dbf
468 0 0.1 7.9 359 0 0
/u01/app/oracle/oradata/southdb/beijing_data34.dbf
464 0 0.1 8.0 185 0 0
/u01/app/oracle/oradata/southdb/beijing_data35.dbf
496 0 0.1 8.0 62 0 0
/u01/app/oracle/oradata/southdb/beijing_data36.dbf
500 0 0.2 7.9 90 0 0
/u01/app/oracle/oradata/southdb/beijing_data37.dbf
929 0 0.1 8.0 41 0 0
/u01/app/oracle/oradata/southdb/beijing_data38.dbf
942 0 0.4 7.9 50 0 0
/u01/app/oracle/oradata/southdb/beijing_data39.dbf
953 0 0.3 7.8 240 0 1 0.0
/u01/app/oracle/oradata/southdb/beijing_data40.dbf
980 0 0.7 7.6 562 0 0
DATACENTER_DATA /u01/app/oracle/oradata/southdb/datacenter_data01.db
f
1 0 220.0 1.0 0 0 0
PERFSTAT_DATA /u01/app/oracle/oradata/southdb/perfstat_data01.dbf
226 0 4.6 1.0 181 0 0
/u01/app/oracle/oradata/southdb/perfstat_data02.dbf
190 0 3.1 1.0 142 0 0
RBS /u01/app/oracle/oradata/southdb/rbs01.dbf
0 0 340 0 0
/u01/app/oracle/oradata/southdb/rbs02.dbf
0 0 225 0 1 10.0
/u01/app/oracle/oradata/southdb/rbs03.dbf
0 0 293 0 1 0.0
/u01/app/oracle/oradata/southdb/rbs04.dbf
0 0 107 0 0
/u01/app/oracle/oradata/southdb/rbs05.dbf
0 0 260 0 0
/u01/app/oracle/oradata/southdb/rbs06.dbf
0 0 134 0 0
/u01/app/oracle/oradata/southdb/rbs07.dbf
0 0 94 0 0
File IO Stats for DB: SOUTHDB Instance: southdb Snaps: 101 -102
->ordered by Tablespace, File
Tablespace Filename
------------------------ ----------------------------------------------------
Av Av Av Av Buffer Av Buf
Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits Wt(ms)
-------------- ------- ------ ------- ------------ -------- ---------- ------
RBS /u01/app/oracle/oradata/southdb/rbs08.dbf
0 0 65 0 0
/u01/app/oracle/oradata/southdb/rbs09.dbf
0 0 78 0 0
/u01/app/oracle/oradata/southdb/rbs10.dbf
0 0 122 0 0
/u01/app/oracle/oradata/southdb/rbs11.dbf
0 0 75 0 1 0.0
SYSTEM /u01/app/oracle/oradata/southdb/system01.dbf
4 0 7.5 1.0 52 0 0
TEMP /u01/app/oracle/oradata/southdb/temp01.dbf
6,339 3 7.8 2.4 3,754 2 0
TVGAME_DATA /u01/app/oracle/oradata/southdb/tvgame_data01.dbf
0 0 16 0 0
/u01/app/oracle/oradata/southdb/tvgame_data02.dbf
9 0 2.2 2.9 3 0 0
/u01/app/oracle/oradata/southdb/tvgame_data03.dbf
5 0 4.0 1.2 11 0 0
/u01/app/oracle/oradata/southdb/tvgame_data04.dbf
10 0 6.0 1.6 14 0 0
/u01/app/oracle/oradata/southdb/tvgame_data05.dbf
2 0 5.0 4.5 3 0 0
/u01/app/oracle/oradata/southdb/tvgame_data06.dbf
1 0 10.0 2.0 0 0 0
/u01/app/oracle/oradata/southdb/tvgame_data08.dbf
11 0 3.6 7.3 0 0 0
/u01/app/oracle/oradata/southdb/tvgame_data09.dbf
6 0 6.7 8.0 15 0 0
/u01/app/oracle/oradata/southdb/tvgame_data10.dbf
18 0 3.9 8.0 14 0 0
/u01/app/oracle/oradata/southdb/tvgame_data11.dbf
17 0 2.4 8.0 22 0 0
/u01/app/oracle/oradata/southdb/tvgame_data12.dbf
21 0 3.3 8.0 0 0 0
/u01/app/oracle/oradata/southdb/tvgame_data13.dbf
16 0 2.5 8.0 3 0 0
/u01/app/oracle/oradata/southdb/tvgame_data14.dbf
16 0 3.8 8.0 0 0 0
/u01/app/oracle/oradata/southdb/tvgame_data15.dbf
736 0 0.1 1.6 0 0 0
/u01/app/oracle/oradata/southdb/tvgame_data16.dbf
16 0 3.8 8.0 11 0 0
/u01/app/oracle/oradata/southdb/tvgame_data17.dbf
1 0 20.0 5.0 0 0 0
/u01/app/oracle/oradata/southdb/tvgame_data18.dbf
0 0 2 0 0
/u01/app/oracle/oradata/southdb/tvgame_data19.dbf
1 0 0.0 1.0 7 0 0
/u01/app/oracle/oradata/southdb/tvgame_data20.dbf
File IO Stats for DB: SOUTHDB Instance: southdb Snaps: 101 -102
->ordered by Tablespace, File
Tablespace Filename
------------------------ ----------------------------------------------------
Av Av Av Av Buffer Av Buf
Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits Wt(ms)
-------------- ------- ------ ------- ------------ -------- ---------- ------
1 0 0.0 1.0 1 0 0
USER_ENGINE_DATA /u01/app/oracle/oradata/southdb/user_engine_data01.d
bf
2 0 20.0 1.0 0 0 0
-------------------------------------------------------------
Buffer Pool Statistics for DB: SOUTHDB Instance: southdb Snaps: 101 -102
-> Pools D: default pool, K: keep pool, R: recycle pool
Free Write Buffer
Buffer Consistent Physical Physical Buffer Complete Busy
P Gets Gets Reads Writes Waits Waits Waits
- ----------- ------------- ----------- ---------- ------- -------- ----------
D 494,579 108,370,283 489,978 12,363 0 0 26
------------------------------------------------------------- Buffer wait Statistics for DB: SOUTHDB Instance: southdb Snaps: 101 -102
-> ordered by wait time desc, waits desc
&