STATSPACK report forDB Name DB Id Instance Inst Num Release Cluster Host ------------ ----------- ------------ -------- ----------- ------- ------------ SMSDB 4222284955 smsdb 1 9.2.0.6.0 NO smsds Snap Id Snap Time Sessions Curs/Sess Comment --------- ------------------ -------- --------- ------------------- Begin Snap: 7 24-Nov-07 11:05:32 103 2.0 End Snap: 9 24-Nov-07 12:00:01 82 2.0 Elapsed: 54.48 (mins)Cache Sizes (end) ~~~~~~~~~~~~~~~~~ Buffer Cache: 1,024M Std Block Size: 8K Shared Pool Size: 768M Log Buffer: 1,024KLoad Profile ~~~~~~~~~~~~ Per Second Per Transaction --------------- --------------- Redo size: 11,027.88 1,333.86 Logical reads: 15,701.64 1,899.16 Block changes: 56.75 6.86 Physical reads: 3,510.71 424.63 Physical writes: 12.00 1.45 User calls: 208.46 25.21 Parses: 63.83 7.72 Hard parses: 7.03 0.85 Sorts: 34.72 4.20 Logons: 0.02 0.00 Executes: 53.66 6.49 Transactions: 8.27 % Blocks changed per Read: 0.36 Recursive Call %: 12.53 Rollback per transaction %: 0.07 Rows per Sort: 46.02Instance Efficiency Percentages (Target 100%) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Buffer Nowait %: 99.47 Redo NoWait %: 100.00 Buffer Hit %: 77.71 In-memory Sort %: 99.99 Library Hit %: 95.32 Soft Parse %: 88.99 Execute to Parse %: -18.96 Latch Hit %: 99.90 Parse CPU to Parse Elapsd %: 34.20 % Non-Parse CPU: 99.67 Shared Pool Statistics Begin End ------ ------ Memory Usage %: 92.19 92.15 % SQL with executions>1: 20.40 20.30 % Memory for SQL w/exec>1: 20.50 21.30Top 5 Timed Events ~~~~~~~~~~~~~~~~~~ % Total Event Waits Time (s) Ela Time -------------------------------------------- ------------ ----------- -------- db file scattered read 1,172,898 8,759 53.76 CPU time 3,796 23.30 db file sequential read 395,171 1,948 11.96 buffer busy waits 272,977 1,738 10.67 log file sync 27,338 32 .20 ------------------------------------------------------------- Wait Events for DB: SMSDB Instance: smsdb Snaps: 7 -9 -> s - second -> cs - centisecond - 100th of a second -> ms - millisecond - 1000th of a second -> us - microsecond - 1000000th of a second -> ordered by wait time desc, waits desc (idle events last) Avg Total Wait wait Waits Event Waits Timeouts Time (s) (ms) /txn ---------------------------- ------------ ---------- ---------- ------ -------- db file scattered read 1,172,898 0 8,759 7 43.4 db file sequential read 395,171 0 1,948 5 14.6 buffer busy waits 272,977 0 1,738 6 10.1 log file sync 27,338 0 32 1 1.0 db file parallel write 788 0 8 10 0.0 direct path write 514 0 3 7 0.0 latch free 600 573 2 4 0.0 direct path read 777 0 2 3 0.0 control file parallel write 1,089 0 1 1 0.0 log file parallel write 27,704 0 1 0 1.0 db file parallel read 57 0 0 6 0.0 control file sequential read 292 0 0 1 0.0 library cache pin 2 0 0 62 0.0 process startup 3 0 0 24 0.0 reliable message 1 0 0 8 0.0 LGWR wait for redo copy 91 0 0 0 0.0 enqueue 2 0 0 1 0.0 SQL*Net more data to client 78 0 0 0 0.0 SQL*Net break/reset to clien 14 0 0 0 0.0 SQL*Net message from client 602,413 0 126,395 210 22.3 wakeup time manager 107 107 3,082 28804 0.0 jobq slave wait 6 3 15 2463 0.0 SQL*Net message to client 602,399 0 0 0 22.3 SQL*Net more data from clien 523 0 0 0 0.0 ------------------------------------------------------------- Background Wait Events for DB: SMSDB Instance: smsdb Snaps: 7 -9 -> ordered by wait time desc, waits desc (idle events last)
Avg Total Wait wait Waits Event Waits Timeouts Time (s) (ms) /txn ---------------------------- ------------ ---------- ---------- ------ -------- db file parallel write 788 0 8 10 0.0 control file parallel write 1,089 0 1 1 0.0 log file parallel write 27,700 0 1 0 1.0 db file scattered read 64 0 1 8 0.0 db file sequential read 47 0 0 10 0.0 LGWR wait for redo copy 91 0 0 0 0.0 latch free 1 0 0 7 0.0 rdbms ipc message 31,418 3,200 13,047 415 1.2 pmon timer 1,095 1,095 3,193 2916 0.0 smon timer 16 7 3,164 ###### 0.0 ------------------------------------------------------------- SQL ordered by Gets for DB: SMSDB Instance: smsdb Snaps: 7 -9 -> 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 CPU Elapsd Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value --------------- ------------ -------------- ------ -------- --------- ---------- 20,125,725 623 32,304.5 39.2 872.36 867.36 470985499 Module: CACMD.sms Select Count(*) iCount From "CMD_DVN_DL" Where IsSent='N' and Cm dDate<='20071124' 5,588,580 172 32,491.7 10.9 250.41 255.30 1246822303 Module: CACMD.sms Select "CMD_DVN_DL".*, "CMD_DVN_DL".ROWID from "CMD_DVN_DL" wh ere IsSent='N' and CmdDate<='20071124' and RowNum <= 1000 ord er by TransNum 4,572,199 372 12,290.9 8.9 331.12 4085.03 3571815486 select aa.* ,bb.stbid, bb.iccardid from (select * from charge_pa ybilldetail where smsbillno = :1) aa left join (select a.smsbi llno, c.stbid,c.iccardid from charge_paybilldetail a, sale_relat ion c where a.billid = concat(c.tradeid, '&1') and a.smsbillno = :2) bb on aa.smsbillno=bb.smsbillno 3,337,992 1,554 2,148.0 6.5 91.76 138.35 3523143076 Module: CACMD.sms Select Count(*) iCount From "CMD_DVN_KM" Where IsSent='N' and Cm dDate<='20071124' 3,144,670 2 1,572,335.0 6.1 407.79 1249.60 4022988168 select operatorid,decode(opernm,null,'合计',opernm) opernm,sum(i ccardcount) iccardcount,sum(totaliccardprice) totaliccardprice,s um(totalstbprice) totalstbprice,sum(totalprice) totalprice,sum(j ibenbao_num) jibenbao_num,sum(net_fee) net_fee,sum(base_fee) bas e_fee,sum(zhongshu_num) zhongshu_num,sum(zhongshu) zhongshu,sum( 2,055,756 409 5,026.3 4.0 79.40 1695.36 3422222463 Module: CACMD.sms Select Count(*) iCount From "CMD_DVN_DH" Where IsSent='N' and Cm dDate<='20071124' 1,188,627 4 297,156.8 2.3 303.41 1068.00 884743628 select areanm,decode(netnm, null, '合计', netnm) netnm,sum(iccar dcount) iccardcount,sum(totaliccardprice) totaliccardprice,sum(t otalstbprice) totalstbprice,sum(totalprice) totalprice,sum(jiben bao_num) jibenbao_num,sum(net_fee) net_fee,sum(base_fee) base_fe e,sum(zhongshu_num) zhongshu_num,sum(zhongshu) zhongshu,sum(weng 769,858 1 769,858.0 1.5 193.22 707.36 3859443273 select operatorid,decode(opernm,null,'合计',opernm) opernm,sum(i ccardcount) iccardcount,sum(totaliccardprice) totaliccardprice,s um(totalstbprice) totalstbprice,sum(totalprice) totalprice,sum(j ibenbao_num) jibenbao_num,sum(net_fee) net_fee,sum(base_fee) bas e_fee,sum(zhongshu_num) zhongshu_num,sum(zhongshu) zhongshu,sum( 517,382 18 28,743.4 1.0 92.72 97.64 1804476514 SQL ordered by Gets for DB: SMSDB Instance: smsdb Snaps: 7 -9 -> 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 CPU Elapsd Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value --------------- ------------ -------------- ------ -------- --------- ---------- Select count(*) from ( Select * from (SELECT * FROM "CMD_DVN_DL" WHERE to_date(to_char(RECORDDATE,'yyyy-mm-dd'),'yyyy-mm-dd')=t o_date('2007-11-24','YYYY-MM-DD') and ISSENT='Y' UNION SELECT * FROM "CMD_DVN_DL_HISTORY" WHERE to_date(to_char(RECORDDATE,'yyy y-mm-dd'),'yyyy-mm-dd')=to_date('2007-11-24','YYYY-MM-DD') and I 435,626 1 435,626.0 0.8 102.66 327.96 1364574476 select areanm,decode(netnm, null, '合计', netnm) netnm,sum(iccar dcount) iccardcount,sum(totaliccardprice) totaliccardprice,sum(t otalstbprice) totalstbprice,sum(totalprice) totalprice,sum(jiben bao_num) jibenbao_num,sum(net_fee) net_fee,sum(base_fee) base_fe e,sum(zhongshu_num) zhongshu_num,sum(zhongshu) zhongshu,sum(weng 418,682 34 12,314.2 0.8 16.48 498.92 2121944008 select stbid, iccardid from sale_relation where tradeid='TD00008 23550' 379,638 1 379,638.0 0.7 23.65 411.85 2698885710 select userid,decode(customnm,null,'合计',customnm) customnm,sum (iccardcount) iccardcount,sum(totaliccardprice) totaliccardprice ,sum(totalstbprice) totalstbprice,sum(totalprice) totalprice,rou nd(sum(net_fee),2) net_fee,round(sum(base_fee),2) base_fee,sum(z hongshu) zhongshu,sum(wenguang) wengguang,sum(huacheng) huacheng 322,920 1 322,920.0 0.6 11.54 165.67 743964133 select distinct productid, productname from ( select a.productid , a.productname from auth_service a, cmng_stbcustomvdo b where a .status='1' and a.stockid=b.stockid and b.stbid='057013029' and b.icid='0104536330162220' union all select a.productid, a.produc tname from auth_service a, cmng_stbcustomvdo b , cmng_custom c w
322,894 1 322,894.0 0.6 11.79 150.60 238094962 select distinct productid, productname from ( select a.productid , a.productname from auth_service a, cmng_stbcustomvdo b where a .status='1' and a.stockid=b.stockid and b.stbid='057011228' and b.icid='0104536330173526' union all select a.productid, a.produc tname from auth_service a, cmng_stbcustomvdo b , cmng_custom c w 215,283 1 215,283.0 0.4 7.73 103.75 2650839752 select distinct productid, productname from ( select a.productid , a.productname from auth_service a, cmng_stbcustomvdo b where a .status='1' and a.stockid=b.stockid and b.stbid='1111900901' and b.icid='01054D3437222047' union all select a.productid, a.produ ctname from auth_service a, cmng_stbcustomvdo b , cmng_custom c 151,006 22,011 6.9 0.3 0.06 5.52 3914117847 select * from PRODUCT_SERIES where SERIESSTATUS = '01' and PRODU CTID = :1 and areaid in (select areaid from reg_areacode start w ith areaid = :2 connect by prior parentid = areaid and AREAID != SQL ordered by Gets for DB: SMSDB Instance: smsdb Snaps: 7 -9 -> 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 CPU Elapsd Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value --------------- ------------ -------------- ------ -------- --------- ---------- ------------------------------------------------------------- SQL ordered by Reads for DB: SMSDB Instance: smsdb Snaps: 7 -9 -> End Disk Reads Threshold: 1000 CPU Elapsd Physical Reads Executions Reads per Exec %Total Time (s) Time (s) Hash Value --------------- ------------ -------------- ------ -------- --------- ---------- 3,369,353 372 9,057.4 29.4 331.12 4085.03 3571815486 select aa.* ,bb.stbid, bb.iccardid from (select * from charge_pa ybilldetail where smsbillno = :1) aa left join (select a.smsbi llno, c.stbid,c.iccardid from charge_paybilldetail a, sale_relat ion c where a.billid = concat(c.tradeid, '&1') and a.smsbillno = :2) bb on aa.smsbillno=bb.smsbillno 1,960,944 409 4,794.5 17.1 79.40 1695.36 3422222463 Module: CACMD.sms Select Count(*) iCount From "CMD_DVN_DH" Where IsSent='N' and Cm dDate<='20071124' 1,218,654 2 609,327.0 10.6 407.79 1249.60 4022988168 select operatorid,decode(opernm,null,'合计',opernm) opernm,sum(i ccardcount) iccardcount,sum(totaliccardprice) totaliccardprice,s um(totalstbprice) totalstbprice,sum(totalprice) totalprice,sum(j ibenbao_num) jibenbao_num,sum(net_fee) net_fee,sum(base_fee) bas e_fee,sum(zhongshu_num) zhongshu_num,sum(zhongshu) zhongshu,sum( 871,233 4 217,808.3 7.6 303.41 1068.00 884743628 select areanm,decode(netnm, null, '合计', netnm) netnm,sum(iccar dcount) iccardcount,sum(totaliccardprice) totaliccardprice,sum(t otalstbprice) totalstbprice,sum(totalprice) totalprice,sum(jiben bao_num) jibenbao_num,sum(net_fee) net_fee,sum(base_fee) base_fe e,sum(zhongshu_num) zhongshu_num,sum(zhongshu) zhongshu,sum(weng 545,926 1 545,926.0 4.8 193.22 707.36 3859443273 select operatorid,decode(opernm,null,'合计',opernm) opernm,sum(i ccardcount) iccardcount,sum(totaliccardprice) totaliccardprice,s um(totalstbprice) totalstbprice,sum(totalprice) totalprice,sum(j ibenbao_num) jibenbao_num,sum(net_fee) net_fee,sum(base_fee) bas e_fee,sum(zhongshu_num) zhongshu_num,sum(zhongshu) zhongshu,sum( 320,870 34 9,437.4 2.8 16.48 498.92 2121944008 select stbid, iccardid from sale_relation where tradeid='TD00008 23550' 314,619 1 314,619.0 2.7 23.65 411.85 2698885710 select userid,decode(customnm,null,'合计',customnm) customnm,sum (iccardcount) iccardcount,sum(totaliccardprice) totaliccardprice ,sum(totalstbprice) totalstbprice,sum(totalprice) totalprice,rou nd(sum(net_fee),2) net_fee,round(sum(base_fee),2) base_fee,sum(z hongshu) zhongshu,sum(wenguang) wengguang,sum(huacheng) huacheng 277,969 1 277,969.0 2.4 102.66 327.96 1364574476 select areanm,decode(netnm, null, '合计', netnm) netnm,sum(iccar dcount) iccardcount,sum(totaliccardprice) totaliccardprice,sum(t otalstbprice) totalstbprice,sum(totalprice) totalprice,sum(jiben bao_num) jibenbao_num,sum(net_fee) net_fee,sum(base_fee) base_fe e,sum(zhongshu_num) zhongshu_num,sum(zhongshu) zhongshu,sum(weng 151,754 1 151,754.0 1.3 11.54 165.67 743964133 select distinct productid, productname from ( select a.productid , a.productname from auth_service a, cmng_stbcustomvdo b where a SQL ordered by Reads for DB: SMSDB Instance: smsdb Snaps: 7 -9 -> End Disk Reads Threshold: 1000 CPU Elapsd Physical Reads Executions Reads per Exec %Total Time (s) Time (s) Hash Value --------------- ------------ -------------- ------ -------- --------- ---------- .status='1' and a.stockid=b.stockid and b.stbid='057013029' and b.icid='0104536330162220' union all select a.productid, a.produc tname from auth_service a, cmng_stbcustomvdo b , cmng_custom c w 149,824 1 149,824.0 1.3 11.79 150.60 238094962 select distinct productid, productname from ( select a.productid , a.productname from auth_service a, cmng_stbcustomvdo b where a .status='1' and a.stockid=b.stockid and b.stbid='057011228' and b.icid='0104536330173526' union all select a.productid, a.produc tname from auth_service a, cmng_stbcustomvdo b , cmng_custom c w 138,881 29 4,789.0 1.2 5.50 120.35 2609264385 Module: CACMD.sms Select "CMD_DVN_DH".*, "CMD_DVN_DH".ROWID from "CMD_DVN_DH" wh ere IsSent='N' and CmdDate<='20071124' and RowNum <= 1000 ord er by TransNum 99,615 1 99,615.0 0.9 7.73 103.75 2650839752 select distinct productid, productname from ( select a.productid , a.productname from auth_service a, cmng_stbcustomvdo b where a .status='1' and a.stockid=b.stockid and b.stbid='1111900901' and b.icid='01054D3437222047' union all select a.productid, a.produ ctname from auth_service a, cmng_stbcustomvdo b , cmng_custom c 96,236 6 16,039.3 0.8 11.98 160.33 2342562494 Select count(*) from ( select distinct cg.GROUPNM,cct.TEAMNM,c.* , ci.CustomNM, ci.ZipCode, ci.sortid, cs.sortnm, ci.zoneid, cz.z onenm, ci.MainTele, ci.MainFax, ci.MobilePhone, ci.Email, ci.Acc ountID, ci.Address, ci.IDCard, ci.Auid, ci.AddressConn, ci.BANK, ci.Bankassign, ci.BANKACCNAME, ci.BANKACCNUM, ci.DISTRICT, ci.S
24,265 2 12,132.5 0.2 3.85 68.71 2313732363 select * from (select rownum as my_rownum,table_a.* from(select distinct cg.GROUPNM,cct.TEAMNM,c.*, ci.CustomNM, ci.ZipCode, ci. sortid, cs.sortnm, ci.zoneid, cz.zonenm, ci.MainTele, ci.MainFax , ci.MobilePhone, ci.Email, ci.AccountID, ci.Address, ci.IDCard, ci.Auid, ci.AddressConn, ci.BANK, ci.Bankassign, ci.BANKACCNAME 21,549 3 7,183.0 0.2 2.42 37.38 494643122 select * from (select a.*,b.maker stbmaker,b.stbtype,c.maker icm aker,c.ictype from CMNG_STBCUSTOMVDO a,WARE_STBBASE b,WARE_CARDB ------------------------------------------------------------- SQL ordered by Executions for DB: SMSDB Instance: smsdb Snaps: 7 -9 -> End Executions Threshold: 100 CPU per Elap per Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value ------------ --------------- ---------------- ----------- ---------- ---------- 22,330 22,318 1.0 0.00 0.00 631736976 select count(*) from TESTCON 22,011 7,062 0.3 0.00 0.00 3914117847 select * from PRODUCT_SERIES where SERIESSTATUS = '01' and PRODU CTID = :1 and areaid in (select areaid from reg_areacode start w ith areaid = :2 connect by prior parentid = areaid and AREAID != '0000000001') And SERIESID not in ( Select SERIESID from SERIES_ DESELECT where AREAID = :3) Order by PERIODTYPE,PERIOD 4,371 4,371 1.0 0.00 0.00 3742653144 select sysdate from dual 3,237 3,237 1.0 0.00 0.00 2607682035 Module: JDBC Thin Client Select count(*) from TESTCON 2,773 0 0.0 0.00 0.00 3068452925 select * from CMNG_CUSTOMSORT where SORTID = '' 2,710 2,710 1.0 0.00 0.00 2298890112 select * from CMNG_CUSTOMINFO where CUSTOMID = :1 2,654 2,654 1.0 0.00 0.00 1955729435 select * from CMNG_CUSTOMDISCOUNT where DISCOUNTID = '01' 2,653 2,653 1.0 0.00 0.00 586656027 select * from CMNG_CUSTOMTYPE where TYPE = '01' 2,290 2,290 1.0 0.00 0.00 1338792403 select * from charge_Account Where AccountID=:1 2,235 2,235 1.0 0.00 0.00 157877130 select * from charge_PayBill where SMSBillNO = :1 1,756 1,756 1.0 0.00 0.00 345607699 update charge_paybill set SMSBILLNO = :1,SMSACCOUNTID = :2,CUSTO MID = :3,AGENCYID = :4,SMSAMOUNT = :5,SMSFREEAMOUNT = :6,SMSCARD AMOUNT = :7,TRADEAMOUNT = :8,HANDLEFEE = :9,SURCHARGE = :10,TRAN STYPE = :11,TRANSDATE = :12,BANKBILLNO = :13,SMSCANCLENO = :14,B ANKCANCLENO = :15,ISOVER = :16,ISVALID = :17,OPERATORID = :18,OP 1,659 0 0.0 0.00 0.00 1441427115 Module: JDBC Thin Client Select * from "CMD_TF_YN" where IsSent='N' and CmdDate<=to_char (sysdate, 'yyyymmdd') and RowNum <= 1000 and Transnum > '0001339 71' and Transnum <= ( select Max(C1) from ( select Max(Transnum) C1 from "CMD_TF_YN" where IsSent='N' and Recorddate < (sysdate -10/24/3600) Union select '000000000' C1 from dual )) order by T 1,632 1,632 1.0 0.00 0.00 2555072908 Module: CACMD.sms Update CA_RUNFLAG set FlagValue='Y' where FlagID='IsCMDSending' and CASID ='0009' SQL ordered by Executions for DB: SMSDB Instance: smsdb Snaps: 7 -9 -> End Executions Threshold: 100 CPU per Elap per Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value ------------ --------------- ---------------- ----------- ---------- ---------- 1,631 1,631 1.0 0.00 0.00 162071932 Module: CACMD.sms Select Count(*) iCount From "CMD_DVN_CX" Where IsSent='N' and Cm dDate<='20071124' 1,631 1,631 1.0 0.00 0.00 3808889251 Module: CACMD.sms Update CA_RUNFLAG set FlagValue='N' where FlagID='IsCMDSending' and CASID ='0009' 1,602 1,602 1.0 0.00 0.00 1738025616 Module: CACMD.sms Select Count(*) iCount From "CMD_DVN_YX" Where IsSent='N' and Cm dDate<='20071124' 1,602 1,602 1.0 0.00 0.00 2928636233 Module: CACMD.sms Update CA_RUNFLAG set FlagValue='N' where FlagID='IsCMDSending' and CASID ='02' 1,601 1,601 1.0 0.00 0.00 2853900259 Module: CACMD.sms Update CA_RUNFLAG set FlagValue='Y' where FlagID='IsCMDSending' and CASID ='02' 1,587 1,587 1.0 0.00 0.00 3977975804 SELECT AREAID, AREANM, PARENTID FROM REG_AREACODE WHERE AREAID = '9' and AREAID != '0000000001' 1,571 1,571 1.0 0.00 0.00 585178709 Module: CACMD.sms Update CA_RUNFLAG set FlagValue='Y' where FlagID='IsCMDSending' and CASID ='0011' 1,570 1,570 1.0 0.00 0.00 20490731 Module: CACMD.sms Update CA_RUNFLAG set FlagValue='N' where FlagID='IsCMDSending' and CASID ='0011' 1,570 1,570 1.0 0.00 0.00 684099634 Module: CACMD.sms Select Count(*) iCount From "CMD_DVN_BN" Where IsSent='N' and Cm dDate<='20071124' 1,554 1,554 1.0 0.00 0.00 1064936426 Module: CACMD.sms Update CA_RUNFLAG set FlagValue='Y' where FlagID='IsCMDSending' and CASID ='0002' 1,554 1,554 1.0 0.00 0.00 2385034839 Module: CACMD.sms Update CA_RUNFLAG set FlagValue='N' where FlagID='IsCMDSending' and CASID ='0002' SQL ordered by Executions for DB: SMSDB Instance: smsdb Snaps: 7 -9 -> End Executions Threshold: 100 CPU per Elap per Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value ------------ --------------- ---------------- ----------- ---------- ----------
1,554 1,554 1.0 0.06 0.09 3523143076 Module: CACMD.sms Select Count(*) iCount From "CMD_DVN_KM" Where IsSent='N' and Cm dDate<='20071124' 1,516 1,516 1.0 0.00 0.00 899019327 Module: CACMD.sms Update CA_RUNFLAG set FlagValue='Y' where FlagID='IsCMDSending' and CASID ='01' 1,516 1,516 1.0 0.00 0.00 1204413334 Module: CACMD.sms Select Count(*) iCount From "CMD_DVN_QJ" Where IsSent='N' and Cm dDate<='20071124' 1,516 1,516 1.0 0.00 0.00 2783056971 Module: CACMD.sms Update CA_RUNFLAG set FlagValue='N' where FlagID='IsCMDSending' and CASID ='01' 1,408 1,408 1.0 0.00 0.00 1983143151 select billtype from charge_PayBillDetail where SMSBillNO=:1 and rownum=1 1,369 1,369 1.0 0.00 0.00 2405511289 select DVN_XT_LOGID_SEQ.NEXTVAL SEQVALUE from dual 1,354 1,354 1.0 0.00 0.00 2247203405 insert into SYS_LOGINFO(LOGID,LOGTYPE,LOGMODULE,AREAID,NETID,LOG INFO,USERID,USERNM,LOGTIME,DESCRIPTION) values(:1,:2,:3,:4,:5,:6 ,:7,:8,:9,:10) 1,316 1,316 1.0 0.00 0.00 206664093 Module: CACMD.sms Update CA_RUNFLAG set FlagValue='Y' where FlagID='IsCMDSending' and CASID ='0012' ------------------------------------------------------------- SQL ordered by Parse Calls for DB: SMSDB Instance: smsdb Snaps: 7 -9 -> End Parse Calls Threshold: 1000 % Total Parse Calls Executions Parses Hash Value ------------ ------------ -------- ---------- 22,329 22,330 10.70 631736976 select count(*) from TESTCON 20,133 22,011 9.65 3914117847 select * from PRODUCT_SERIES where SERIESSTATUS = '01' and PRODU CTID = :1 and areaid in (select areaid from reg_areacode start w ith areaid = :2 connect by prior parentid = areaid and AREAID != '0000000001') And SERIESID not in ( Select SERIESID from SERIES_ DESELECT where AREAID = :3) Order by PERIODTYPE,PERIOD 4,371 4,371 2.09 3742653144 select sysdate from dual 3,264 1,632 1.56 2555072908 Module: CACMD.sms Update CA_RUNFLAG set FlagValue='Y' where FlagID='IsCMDSending' and CASID ='0009' 3,262 1,631 1.56 162071932 Module: CACMD.sms Select Count(*) iCount From "CMD_DVN_CX" Where IsSent='N' and Cm dDate<='20071124' 3,262 1,631 1.56 3808889251 Module: CACMD.sms Update CA_RUNFLAG set FlagValue='N' where FlagID='IsCMDSending' and CASID ='0009' 3,237 3,237 1.55 2607682035 Module: JDBC Thin Client Select count(*) from TESTCON 3,204 1,602 1.54 1738025616 Module: CACMD.sms Select Count(*) iCount From "CMD_DVN_YX" Where IsSent='N' and Cm dDate<='20071124' 3,204 1,602 1.54 2928636233 Module: CACMD.sms Update CA_RUNFLAG set FlagValue='N' where FlagID='IsCMDSending' and CASID ='02' 3,202 1,601 1.53 2853900259 Module: CACMD.sms Update CA_RUNFLAG set FlagValue='Y' where FlagID='IsCMDSending' and CASID ='02' 3,142 1,571 1.51 585178709 Module: CACMD.sms Update CA_RUNFLAG set FlagValue='Y' where FlagID='IsCMDSending' and CASID ='0011' 3,140 1,570 1.50 20490731 Module: CACMD.sms SQL ordered by Parse Calls for DB: SMSDB Instance: smsdb Snaps: 7 -9 -> End Parse Calls Threshold: 1000 % Total Parse Calls Executions Parses Hash Value ------------ ------------ -------- ---------- Update CA_RUNFLAG set FlagValue='N' where FlagID='IsCMDSending' and CASID ='0011' 3,140 1,570 1.50 684099634 Module: CACMD.sms Select Count(*) iCount From "CMD_DVN_BN" Where IsSent='N' and Cm dDate<='20071124' 3,108 1,554 1.49 1064936426 Module: CACMD.sms Update CA_RUNFLAG set FlagValue='Y' where FlagID='IsCMDSending' and CASID ='0002' 3,108 1,554 1.49 2385034839 Module: CACMD.sms Update CA_RUNFLAG set FlagValue='N' where FlagID='IsCMDSending' and CASID ='0002' 3,108 1,554 1.49 3523143076 Module: CACMD.sms Select Count(*) iCount From "CMD_DVN_KM" Where IsSent='N' and Cm dDate<='20071124' 3,032 1,516 1.45 899019327 Module: CACMD.sms Update CA_RUNFLAG set FlagValue='Y' where FlagID='IsCMDSending' and CASID ='01' 3,032 1,516 1.45 1204413334 Module: CACMD.sms Select Count(*) iCount From "CMD_DVN_QJ" Where IsSent='N' and Cm dDate<='20071124' 3,032 1,516 1.45 2783056971 Module: CACMD.sms Update CA_RUNFLAG set FlagValue='N' where FlagID='IsCMDSending' and CASID ='01' 2,772 2,773 1.33 3068452925 select * from CMNG_CUSTOMSORT where SORTID = '' 2,654 2,654 1.27 1955729435 select * from CMNG_CUSTOMDISCOUNT where DISCOUNTID = '01' 2,653 2,653 1.27 586656027 select * from CMNG_CUSTOMTYPE where TYPE = '01' 2,632 1,316 1.26 206664093 Module: CACMD.sms Update CA_RUNFLAG set FlagValue='Y' where FlagID='IsCMDSending' and CASID ='0012' 2,632 1,316 1.26 3247731699 Module: CACMD.sms SQL ordered by Parse Calls for DB: SMSDB Instance: smsdb Snaps: 7 -9 -> End Parse Calls Threshold: 1000 % Total Parse Calls Executions Parses Hash Value ------------ ------------ -------- ---------- Select Count(*) iCount From "CMD_DVN_HH" Where IsSent='N' and Cm dDate<='20071124' 2,632 1,316 1.26 3642256803 Module: CACMD.sms Update CA_RUNFLAG set FlagValue='N' where FlagID='IsCMDSending' and CASID ='0012'
1,659 1,659 0.80 1441427115 Module: JDBC Thin Client Select * from "CMD_TF_YN" where IsSent='N' and CmdDate<=to_char (sysdate, 'yyyymmdd') and RowNum <= 1000 and Transnum > '0001339 71' and Transnum <= ( select Max(C1) from ( select Max(Transnum) C1 from "CMD_TF_YN" where IsSent='N' and Recorddate < (sysdate -10/24/3600) Union select '000000000' C1 from dual )) order by T 1,639 2,710 0.79 2298890112 select * from CMNG_CUSTOMINFO where CUSTOMID = :1 1,632 0 0.78 66199152 Module: CACMD.sms SELECT "CMD_DVN_CX".*, "CMD_DVN_CX".ROWID FROM "CMD_DVN_CX" 1,601 0 0.77 3571144412 Module: CACMD.sms SELECT "CMD_DVN_YX".*, "CMD_DVN_YX".ROWID FROM "CMD_DVN_YX" 1,587 1,587 0.76 3977975804 SELECT AREAID, AREANM, PARENTID FROM REG_AREACODE WHERE AREAID = '9' and AREAID != '0000000001' 1,571 0 0.75 3834199895 Module: CACMD.sms SELECT "CMD_DVN_BN".*, "CMD_DVN_BN".ROWID FROM "CMD_DVN_BN" 1,554 0 0.74 3324058973 Module: CACMD.sms SELECT "CMD_DVN_KM".*, "CMD_DVN_KM".ROWID FROM "CMD_DVN_KM" 1,516 0 0.73 1936585825 Module: CACMD.sms SELECT "CMD_DVN_QJ".*, "CMD_DVN_QJ".ROWID FROM "CMD_DVN_QJ" 1,480 2,235 0.71 157877130 select * from charge_PayBill where SMSBillNO = :1 1,369 1,369 0.66 2405511289 select DVN_XT_LOGID_SEQ.NEXTVAL SEQVALUE from dual 1,359 2,290 0.65 1338792403 select * from charge_Account Where AccountID=:1 1,316 0 0.63 1805818967 Module: CACMD.sms SQL ordered by Parse Calls for DB: SMSDB Instance: smsdb Snaps: 7 -9 -> End Parse Calls Threshold: 1000 % Total Parse Calls Executions Parses Hash Value ------------ ------------ -------- ---------- SELECT "CMD_DVN_HH".*, "CMD_DVN_HH".ROWID FROM "CMD_DVN_HH" ------------------------------------------------------------- SQL ordered by Sharable Memory for DB: SMSDB Instance: smsdb Snaps: 7 -9 -> End Sharable Memory Threshold: 1048576Sharable Mem (b) Executions % Total Hash Value ---------------- ------------ ------- ------------ 2,159,994 1 0.2 2698885710 select userid,decode(customnm,null,'合计',customnm) customnm,sum (iccardcount) iccardcount,sum(totaliccardprice) totaliccardprice ,sum(totalstbprice) totalstbprice,sum(totalprice) totalprice,rou nd(sum(net_fee),2) net_fee,round(sum(base_fee),2) base_fee,sum(z hongshu) zhongshu,sum(wenguang) wengguang,sum(huacheng) huacheng 2,028,641 1 0.2 1487493856 select operatorid,decode(opernm,null,'合计',opernm) opernm,sum(i ccardcount) iccardcount,sum(totaliccardprice) totaliccardprice,s um(totalstbprice) totalstbprice,sum(totalprice) totalprice,sum(j ibenbao_num) jibenbao_num,sum(net_fee) net_fee,sum(base_fee) bas e_fee,sum(zhongshu_num) zhongshu_num,sum(zhongshu) zhongshu,sum( 2,000,273 1 0.2 3859443273 select operatorid,decode(opernm,null,'合计',opernm) opernm,sum(i ccardcount) iccardcount,sum(totaliccardprice) totaliccardprice,s um(totalstbprice) totalstbprice,sum(totalprice) totalprice,sum(j ibenbao_num) jibenbao_num,sum(net_fee) net_fee,sum(base_fee) bas e_fee,sum(zhongshu_num) zhongshu_num,sum(zhongshu) zhongshu,sum( 1,996,977 2 0.2 4022988168 select operatorid,decode(opernm,null,'合计',opernm) opernm,sum(i ccardcount) iccardcount,sum(totaliccardprice) totaliccardprice,s um(totalstbprice) totalstbprice,sum(totalprice) totalprice,sum(j ibenbao_num) jibenbao_num,sum(net_fee) net_fee,sum(base_fee) bas e_fee,sum(zhongshu_num) zhongshu_num,sum(zhongshu) zhongshu,sum( 1,210,524 1 0.1 1364574476 select areanm,decode(netnm, null, '合计', netnm) netnm,sum(iccar dcount) iccardcount,sum(totaliccardprice) totaliccardprice,sum(t otalstbprice) totalstbprice,sum(totalprice) totalprice,sum(jiben bao_num) jibenbao_num,sum(net_fee) net_fee,sum(base_fee) base_fe e,sum(zhongshu_num) zhongshu_num,sum(zhongshu) zhongshu,sum(weng 1,193,948 4 0.1 884743628 select areanm,decode(netnm, null, '合计', netnm) netnm,sum(iccar dcount) iccardcount,sum(totaliccardprice) totaliccardprice,sum(t otalstbprice) totalstbprice,sum(totalprice) totalprice,sum(jiben bao_num) jibenbao_num,sum(net_fee) net_fee,sum(base_fee) base_fe e,sum(zhongshu_num) zhongshu_num,sum(zhongshu) zhongshu,sum(weng ------------------------------------------------------------- Instance Activity Stats for DB: SMSDB Instance: smsdb Snaps: 7 -9
Statistic Total per Second per Trans --------------------------------- ------------------ -------------- ------------ CPU used by this session 379,643 116.1 14.1 CPU used when call started 379,500 116.1 14.0 CR blocks created 2,587 0.8 0.1 Cached Commit SCN referenced 0 0.0 0.0 Commit SCN cached 0 0.0 0.0 DBWR buffers scanned 51,429 15.7 1.9 DBWR checkpoint buffers written 2,668 0.8 0.1 DBWR checkpoints 0 0.0 0.0 DBWR free buffers found 48,702 14.9 1.8 DBWR lru scans 50 0.0 0.0 DBWR make free requests 55 0.0 0.0 DBWR summed scan depth 51,429 15.7 1.9 DBWR transaction table writes 17 0.0 0.0 DBWR undo block writes 1,996 0.6 0.1 SQL*Net roundtrips to/from client 601,974 184.2 22.3 active txn count during cleanout 3,545 1.1 0.1 background checkpoints completed 0 0.0 0.0 background checkpoints started 0 0.0 0.0 background timeouts 3,921 1.2 0.2 branch node splits 1 0.0 0.0 buffer is not pinned count 46,128,768 14,111.0 1,706.8 buffer is pinned count 2,030,890,821 621,257.5 75,143.0 bytes received via SQL*Net from c 45,630,142 13,958.4 1,688.3 bytes sent via SQL*Net to client 99,567,312 30,458.0 3,684.0 calls to get snapshot scn: kcmgss 378,095 115.7 14.0 calls to kcmgas 31,290 9.6 1.2 calls to kcmgcs 638 0.2 0.0 change write time 141 0.0 0.0 cleanout - number of ktugct calls 2,743 0.8 0.1 cluster key scan block gets 1,121 0.3 0.0 cluster key scans 887 0.3 0.0 commit cleanout failures: block l 2 0.0 0.0 commit cleanout failures: buffer 0 0.0 0.0 commit cleanout failures: callbac 2 0.0 0.0 commit cleanout failures: cannot 14 0.0 0.0 commit cleanouts 52,545 16.1 1.9 commit cleanouts successfully com 52,527 16.1 1.9 commit txn count during cleanout 1,664 0.5 0.1 consistent changes 37,854 11.6 1.4 consistent gets 51,153,979 15,648.2 1,892.7 consistent gets - examination 5,441,516 1,664.6 201.3 current blocks converted for CR 34 0.0 0.0 cursor authentications 4,778 1.5 0.2 data blocks consistent reads - un 37,844 11.6 1.4 db block changes 185,504 56.8 6.9 db block gets 174,664 53.4 6.5 deferred (CURRENT) block cleanout 21,535 6.6 0.8 dirty buffers inspected 193 0.1 0.0 enqueue conversions 842 0.3 0.0 enqueue deadlocks 0 0.0 0.0 enqueue releases 89,586 27.4 3.3 enqueue requests 89,575 27.4 3.3 enqueue waits 2 0.0 0.0 execute count 175,407 53.7 6.5 free buffer inspected 129,920 39.7 4.8 free buffer requested 11,448,724 3,502.2 423.6 Instance Activity Stats for DB: SMSDB Instance: smsdb Snaps: 7 -9
以上是stats report 的一部分，请高手指教
db file scattered read 1,172,898 8,759 53.76 全表扫描太厉害了.先把top sql中的sql语句给tuning了.
buffer这么小,多大的内存呀.把硬件配置也给说说.OS什么的.物理读相当高啊.
先检查一下这条语句吧:Select Count(*) iCount From "CMD_DVN_DL" Where IsSent='N' and Cm dDate <='20071124' 看看执行计划如何,再一个,如果可以,最好是在程序中使用绑定变量.你的hard parse太高.
------------ ----------- ------------ -------- ----------- ------- ------------
SMSDB 4222284955 smsdb 1 9.2.0.6.0 NO smsds Snap Id Snap Time Sessions Curs/Sess Comment
--------- ------------------ -------- --------- -------------------
Begin Snap: 7 24-Nov-07 11:05:32 103 2.0
End Snap: 9 24-Nov-07 12:00:01 82 2.0
Elapsed: 54.48 (mins)Cache Sizes (end)
~~~~~~~~~~~~~~~~~
Buffer Cache: 1,024M Std Block Size: 8K
Shared Pool Size: 768M Log Buffer: 1,024KLoad Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 11,027.88 1,333.86
Logical reads: 15,701.64 1,899.16
Block changes: 56.75 6.86
Physical reads: 3,510.71 424.63
Physical writes: 12.00 1.45
User calls: 208.46 25.21
Parses: 63.83 7.72
Hard parses: 7.03 0.85
Sorts: 34.72 4.20
Logons: 0.02 0.00
Executes: 53.66 6.49
Transactions: 8.27 % Blocks changed per Read: 0.36 Recursive Call %: 12.53
Rollback per transaction %: 0.07 Rows per Sort: 46.02Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 99.47 Redo NoWait %: 100.00
Buffer Hit %: 77.71 In-memory Sort %: 99.99
Library Hit %: 95.32 Soft Parse %: 88.99
Execute to Parse %: -18.96 Latch Hit %: 99.90
Parse CPU to Parse Elapsd %: 34.20 % Non-Parse CPU: 99.67 Shared Pool Statistics Begin End
------ ------
Memory Usage %: 92.19 92.15
% SQL with executions>1: 20.40 20.30
% Memory for SQL w/exec>1: 20.50 21.30Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
db file scattered read 1,172,898 8,759 53.76
CPU time 3,796 23.30
db file sequential read 395,171 1,948 11.96
buffer busy waits 272,977 1,738 10.67
log file sync 27,338 32 .20
-------------------------------------------------------------
Wait Events for DB: SMSDB Instance: smsdb Snaps: 7 -9
-> s - second
-> cs - centisecond - 100th of a second
-> ms - millisecond - 1000th of a second
-> us - microsecond - 1000000th of a second
-> ordered by wait time desc, waits desc (idle events last) Avg
Total Wait wait Waits
Event Waits Timeouts Time (s) (ms) /txn
---------------------------- ------------ ---------- ---------- ------ --------
db file scattered read 1,172,898 0 8,759 7 43.4
db file sequential read 395,171 0 1,948 5 14.6
buffer busy waits 272,977 0 1,738 6 10.1
log file sync 27,338 0 32 1 1.0
db file parallel write 788 0 8 10 0.0
direct path write 514 0 3 7 0.0
latch free 600 573 2 4 0.0
direct path read 777 0 2 3 0.0
control file parallel write 1,089 0 1 1 0.0
log file parallel write 27,704 0 1 0 1.0
db file parallel read 57 0 0 6 0.0
control file sequential read 292 0 0 1 0.0
library cache pin 2 0 0 62 0.0
process startup 3 0 0 24 0.0
reliable message 1 0 0 8 0.0
LGWR wait for redo copy 91 0 0 0 0.0
enqueue 2 0 0 1 0.0
SQL*Net more data to client 78 0 0 0 0.0
SQL*Net break/reset to clien 14 0 0 0 0.0
SQL*Net message from client 602,413 0 126,395 210 22.3
wakeup time manager 107 107 3,082 28804 0.0
jobq slave wait 6 3 15 2463 0.0
SQL*Net message to client 602,399 0 0 0 22.3
SQL*Net more data from clien 523 0 0 0 0.0
-------------------------------------------------------------
Background Wait Events for DB: SMSDB Instance: smsdb Snaps: 7 -9
-> ordered by wait time desc, waits desc (idle events last)
Total Wait wait Waits
Event Waits Timeouts Time (s) (ms) /txn
---------------------------- ------------ ---------- ---------- ------ --------
db file parallel write 788 0 8 10 0.0
control file parallel write 1,089 0 1 1 0.0
log file parallel write 27,700 0 1 0 1.0
db file scattered read 64 0 1 8 0.0
db file sequential read 47 0 0 10 0.0
LGWR wait for redo copy 91 0 0 0 0.0
latch free 1 0 0 7 0.0
rdbms ipc message 31,418 3,200 13,047 415 1.2
pmon timer 1,095 1,095 3,193 2916 0.0
smon timer 16 7 3,164 ###### 0.0
-------------------------------------------------------------
SQL ordered by Gets for DB: SMSDB Instance: smsdb Snaps: 7 -9
-> 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 CPU Elapsd
Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
20,125,725 623 32,304.5 39.2 872.36 867.36 470985499
Module: CACMD.sms
Select Count(*) iCount From "CMD_DVN_DL" Where IsSent='N' and Cm
dDate<='20071124' 5,588,580 172 32,491.7 10.9 250.41 255.30 1246822303
Module: CACMD.sms
Select "CMD_DVN_DL".*, "CMD_DVN_DL".ROWID from "CMD_DVN_DL" wh
ere IsSent='N' and CmdDate<='20071124' and RowNum <= 1000 ord
er by TransNum 4,572,199 372 12,290.9 8.9 331.12 4085.03 3571815486
select aa.* ,bb.stbid, bb.iccardid from (select * from charge_pa
ybilldetail where smsbillno = :1) aa left join (select a.smsbi
llno, c.stbid,c.iccardid from charge_paybilldetail a, sale_relat
ion c where a.billid = concat(c.tradeid, '&1') and a.smsbillno
= :2) bb on aa.smsbillno=bb.smsbillno 3,337,992 1,554 2,148.0 6.5 91.76 138.35 3523143076
Module: CACMD.sms
Select Count(*) iCount From "CMD_DVN_KM" Where IsSent='N' and Cm
dDate<='20071124' 3,144,670 2 1,572,335.0 6.1 407.79 1249.60 4022988168
select operatorid,decode(opernm,null,'合计',opernm) opernm,sum(i
ccardcount) iccardcount,sum(totaliccardprice) totaliccardprice,s
um(totalstbprice) totalstbprice,sum(totalprice) totalprice,sum(j
ibenbao_num) jibenbao_num,sum(net_fee) net_fee,sum(base_fee) bas
e_fee,sum(zhongshu_num) zhongshu_num,sum(zhongshu) zhongshu,sum( 2,055,756 409 5,026.3 4.0 79.40 1695.36 3422222463
Module: CACMD.sms
Select Count(*) iCount From "CMD_DVN_DH" Where IsSent='N' and Cm
dDate<='20071124' 1,188,627 4 297,156.8 2.3 303.41 1068.00 884743628
select areanm,decode(netnm, null, '合计', netnm) netnm,sum(iccar
dcount) iccardcount,sum(totaliccardprice) totaliccardprice,sum(t
otalstbprice) totalstbprice,sum(totalprice) totalprice,sum(jiben
bao_num) jibenbao_num,sum(net_fee) net_fee,sum(base_fee) base_fe
e,sum(zhongshu_num) zhongshu_num,sum(zhongshu) zhongshu,sum(weng 769,858 1 769,858.0 1.5 193.22 707.36 3859443273
select operatorid,decode(opernm,null,'合计',opernm) opernm,sum(i
ccardcount) iccardcount,sum(totaliccardprice) totaliccardprice,s
um(totalstbprice) totalstbprice,sum(totalprice) totalprice,sum(j
ibenbao_num) jibenbao_num,sum(net_fee) net_fee,sum(base_fee) bas
e_fee,sum(zhongshu_num) zhongshu_num,sum(zhongshu) zhongshu,sum( 517,382 18 28,743.4 1.0 92.72 97.64 1804476514
SQL ordered by Gets for DB: SMSDB Instance: smsdb Snaps: 7 -9
-> 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 CPU Elapsd
Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
Select count(*) from ( Select * from (SELECT * FROM "CMD_DVN_DL"
WHERE to_date(to_char(RECORDDATE,'yyyy-mm-dd'),'yyyy-mm-dd')=t
o_date('2007-11-24','YYYY-MM-DD') and ISSENT='Y' UNION SELECT *
FROM "CMD_DVN_DL_HISTORY" WHERE to_date(to_char(RECORDDATE,'yyy
y-mm-dd'),'yyyy-mm-dd')=to_date('2007-11-24','YYYY-MM-DD') and I 435,626 1 435,626.0 0.8 102.66 327.96 1364574476
select areanm,decode(netnm, null, '合计', netnm) netnm,sum(iccar
dcount) iccardcount,sum(totaliccardprice) totaliccardprice,sum(t
otalstbprice) totalstbprice,sum(totalprice) totalprice,sum(jiben
bao_num) jibenbao_num,sum(net_fee) net_fee,sum(base_fee) base_fe
e,sum(zhongshu_num) zhongshu_num,sum(zhongshu) zhongshu,sum(weng 418,682 34 12,314.2 0.8 16.48 498.92 2121944008
select stbid, iccardid from sale_relation where tradeid='TD00008
23550' 379,638 1 379,638.0 0.7 23.65 411.85 2698885710
select userid,decode(customnm,null,'合计',customnm) customnm,sum
(iccardcount) iccardcount,sum(totaliccardprice) totaliccardprice
,sum(totalstbprice) totalstbprice,sum(totalprice) totalprice,rou
nd(sum(net_fee),2) net_fee,round(sum(base_fee),2) base_fee,sum(z
hongshu) zhongshu,sum(wenguang) wengguang,sum(huacheng) huacheng 322,920 1 322,920.0 0.6 11.54 165.67 743964133
select distinct productid, productname from ( select a.productid
, a.productname from auth_service a, cmng_stbcustomvdo b where a
.status='1' and a.stockid=b.stockid and b.stbid='057013029' and
b.icid='0104536330162220' union all select a.productid, a.produc
tname from auth_service a, cmng_stbcustomvdo b , cmng_custom c w
322,894 1 322,894.0 0.6 11.79 150.60 238094962
select distinct productid, productname from ( select a.productid
, a.productname from auth_service a, cmng_stbcustomvdo b where a
.status='1' and a.stockid=b.stockid and b.stbid='057011228' and
b.icid='0104536330173526' union all select a.productid, a.produc
tname from auth_service a, cmng_stbcustomvdo b , cmng_custom c w 215,283 1 215,283.0 0.4 7.73 103.75 2650839752
select distinct productid, productname from ( select a.productid
, a.productname from auth_service a, cmng_stbcustomvdo b where a
.status='1' and a.stockid=b.stockid and b.stbid='1111900901' and
b.icid='01054D3437222047' union all select a.productid, a.produ
ctname from auth_service a, cmng_stbcustomvdo b , cmng_custom c 151,006 22,011 6.9 0.3 0.06 5.52 3914117847
select * from PRODUCT_SERIES where SERIESSTATUS = '01' and PRODU
CTID = :1 and areaid in (select areaid from reg_areacode start w
ith areaid = :2 connect by prior parentid = areaid and AREAID != SQL ordered by Gets for DB: SMSDB Instance: smsdb Snaps: 7 -9
-> 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 CPU Elapsd
Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
-------------------------------------------------------------
SQL ordered by Reads for DB: SMSDB Instance: smsdb Snaps: 7 -9
-> End Disk Reads Threshold: 1000 CPU Elapsd
Physical Reads Executions Reads per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
3,369,353 372 9,057.4 29.4 331.12 4085.03 3571815486
select aa.* ,bb.stbid, bb.iccardid from (select * from charge_pa
ybilldetail where smsbillno = :1) aa left join (select a.smsbi
llno, c.stbid,c.iccardid from charge_paybilldetail a, sale_relat
ion c where a.billid = concat(c.tradeid, '&1') and a.smsbillno
= :2) bb on aa.smsbillno=bb.smsbillno 1,960,944 409 4,794.5 17.1 79.40 1695.36 3422222463
Module: CACMD.sms
Select Count(*) iCount From "CMD_DVN_DH" Where IsSent='N' and Cm
dDate<='20071124' 1,218,654 2 609,327.0 10.6 407.79 1249.60 4022988168
select operatorid,decode(opernm,null,'合计',opernm) opernm,sum(i
ccardcount) iccardcount,sum(totaliccardprice) totaliccardprice,s
um(totalstbprice) totalstbprice,sum(totalprice) totalprice,sum(j
ibenbao_num) jibenbao_num,sum(net_fee) net_fee,sum(base_fee) bas
e_fee,sum(zhongshu_num) zhongshu_num,sum(zhongshu) zhongshu,sum( 871,233 4 217,808.3 7.6 303.41 1068.00 884743628
select areanm,decode(netnm, null, '合计', netnm) netnm,sum(iccar
dcount) iccardcount,sum(totaliccardprice) totaliccardprice,sum(t
otalstbprice) totalstbprice,sum(totalprice) totalprice,sum(jiben
bao_num) jibenbao_num,sum(net_fee) net_fee,sum(base_fee) base_fe
e,sum(zhongshu_num) zhongshu_num,sum(zhongshu) zhongshu,sum(weng 545,926 1 545,926.0 4.8 193.22 707.36 3859443273
select operatorid,decode(opernm,null,'合计',opernm) opernm,sum(i
ccardcount) iccardcount,sum(totaliccardprice) totaliccardprice,s
um(totalstbprice) totalstbprice,sum(totalprice) totalprice,sum(j
ibenbao_num) jibenbao_num,sum(net_fee) net_fee,sum(base_fee) bas
e_fee,sum(zhongshu_num) zhongshu_num,sum(zhongshu) zhongshu,sum( 320,870 34 9,437.4 2.8 16.48 498.92 2121944008
select stbid, iccardid from sale_relation where tradeid='TD00008
23550' 314,619 1 314,619.0 2.7 23.65 411.85 2698885710
select userid,decode(customnm,null,'合计',customnm) customnm,sum
(iccardcount) iccardcount,sum(totaliccardprice) totaliccardprice
,sum(totalstbprice) totalstbprice,sum(totalprice) totalprice,rou
nd(sum(net_fee),2) net_fee,round(sum(base_fee),2) base_fee,sum(z
hongshu) zhongshu,sum(wenguang) wengguang,sum(huacheng) huacheng 277,969 1 277,969.0 2.4 102.66 327.96 1364574476
select areanm,decode(netnm, null, '合计', netnm) netnm,sum(iccar
dcount) iccardcount,sum(totaliccardprice) totaliccardprice,sum(t
otalstbprice) totalstbprice,sum(totalprice) totalprice,sum(jiben
bao_num) jibenbao_num,sum(net_fee) net_fee,sum(base_fee) base_fe
e,sum(zhongshu_num) zhongshu_num,sum(zhongshu) zhongshu,sum(weng 151,754 1 151,754.0 1.3 11.54 165.67 743964133
select distinct productid, productname from ( select a.productid
, a.productname from auth_service a, cmng_stbcustomvdo b where a
SQL ordered by Reads for DB: SMSDB Instance: smsdb Snaps: 7 -9
-> End Disk Reads Threshold: 1000 CPU Elapsd
Physical Reads Executions Reads per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
.status='1' and a.stockid=b.stockid and b.stbid='057013029' and
b.icid='0104536330162220' union all select a.productid, a.produc
tname from auth_service a, cmng_stbcustomvdo b , cmng_custom c w 149,824 1 149,824.0 1.3 11.79 150.60 238094962
select distinct productid, productname from ( select a.productid
, a.productname from auth_service a, cmng_stbcustomvdo b where a
.status='1' and a.stockid=b.stockid and b.stbid='057011228' and
b.icid='0104536330173526' union all select a.productid, a.produc
tname from auth_service a, cmng_stbcustomvdo b , cmng_custom c w 138,881 29 4,789.0 1.2 5.50 120.35 2609264385
Module: CACMD.sms
Select "CMD_DVN_DH".*, "CMD_DVN_DH".ROWID from "CMD_DVN_DH" wh
ere IsSent='N' and CmdDate<='20071124' and RowNum <= 1000 ord
er by TransNum 99,615 1 99,615.0 0.9 7.73 103.75 2650839752
select distinct productid, productname from ( select a.productid
, a.productname from auth_service a, cmng_stbcustomvdo b where a
.status='1' and a.stockid=b.stockid and b.stbid='1111900901' and
b.icid='01054D3437222047' union all select a.productid, a.produ
ctname from auth_service a, cmng_stbcustomvdo b , cmng_custom c 96,236 6 16,039.3 0.8 11.98 160.33 2342562494
Select count(*) from ( select distinct cg.GROUPNM,cct.TEAMNM,c.*
, ci.CustomNM, ci.ZipCode, ci.sortid, cs.sortnm, ci.zoneid, cz.z
onenm, ci.MainTele, ci.MainFax, ci.MobilePhone, ci.Email, ci.Acc
ountID, ci.Address, ci.IDCard, ci.Auid, ci.AddressConn, ci.BANK,
ci.Bankassign, ci.BANKACCNAME, ci.BANKACCNUM, ci.DISTRICT, ci.S
24,265 2 12,132.5 0.2 3.85 68.71 2313732363
select * from (select rownum as my_rownum,table_a.* from(select
distinct cg.GROUPNM,cct.TEAMNM,c.*, ci.CustomNM, ci.ZipCode, ci.
sortid, cs.sortnm, ci.zoneid, cz.zonenm, ci.MainTele, ci.MainFax
, ci.MobilePhone, ci.Email, ci.AccountID, ci.Address, ci.IDCard,
ci.Auid, ci.AddressConn, ci.BANK, ci.Bankassign, ci.BANKACCNAME 21,549 3 7,183.0 0.2 2.42 37.38 494643122
select * from (select a.*,b.maker stbmaker,b.stbtype,c.maker icm
aker,c.ictype from CMNG_STBCUSTOMVDO a,WARE_STBBASE b,WARE_CARDB -------------------------------------------------------------
SQL ordered by Executions for DB: SMSDB Instance: smsdb Snaps: 7 -9
-> End Executions Threshold: 100 CPU per Elap per
Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value
------------ --------------- ---------------- ----------- ---------- ----------
22,330 22,318 1.0 0.00 0.00 631736976
select count(*) from TESTCON 22,011 7,062 0.3 0.00 0.00 3914117847
select * from PRODUCT_SERIES where SERIESSTATUS = '01' and PRODU
CTID = :1 and areaid in (select areaid from reg_areacode start w
ith areaid = :2 connect by prior parentid = areaid and AREAID !=
'0000000001') And SERIESID not in ( Select SERIESID from SERIES_
DESELECT where AREAID = :3) Order by PERIODTYPE,PERIOD 4,371 4,371 1.0 0.00 0.00 3742653144
select sysdate from dual 3,237 3,237 1.0 0.00 0.00 2607682035
Module: JDBC Thin Client
Select count(*) from TESTCON 2,773 0 0.0 0.00 0.00 3068452925
select * from CMNG_CUSTOMSORT where SORTID = '' 2,710 2,710 1.0 0.00 0.00 2298890112
select * from CMNG_CUSTOMINFO where CUSTOMID = :1 2,654 2,654 1.0 0.00 0.00 1955729435
select * from CMNG_CUSTOMDISCOUNT where DISCOUNTID = '01' 2,653 2,653 1.0 0.00 0.00 586656027
select * from CMNG_CUSTOMTYPE where TYPE = '01' 2,290 2,290 1.0 0.00 0.00 1338792403
select * from charge_Account Where AccountID=:1 2,235 2,235 1.0 0.00 0.00 157877130
select * from charge_PayBill where SMSBillNO = :1 1,756 1,756 1.0 0.00 0.00 345607699
update charge_paybill set SMSBILLNO = :1,SMSACCOUNTID = :2,CUSTO
MID = :3,AGENCYID = :4,SMSAMOUNT = :5,SMSFREEAMOUNT = :6,SMSCARD
AMOUNT = :7,TRADEAMOUNT = :8,HANDLEFEE = :9,SURCHARGE = :10,TRAN
STYPE = :11,TRANSDATE = :12,BANKBILLNO = :13,SMSCANCLENO = :14,B
ANKCANCLENO = :15,ISOVER = :16,ISVALID = :17,OPERATORID = :18,OP 1,659 0 0.0 0.00 0.00 1441427115
Module: JDBC Thin Client
Select * from "CMD_TF_YN" where IsSent='N' and CmdDate<=to_char
(sysdate, 'yyyymmdd') and RowNum <= 1000 and Transnum > '0001339
71' and Transnum <= ( select Max(C1) from ( select Max(Transnum)
C1 from "CMD_TF_YN" where IsSent='N' and Recorddate < (sysdate
-10/24/3600) Union select '000000000' C1 from dual )) order by T 1,632 1,632 1.0 0.00 0.00 2555072908
Module: CACMD.sms
Update CA_RUNFLAG set FlagValue='Y' where FlagID='IsCMDSending'
and CASID ='0009'
SQL ordered by Executions for DB: SMSDB Instance: smsdb Snaps: 7 -9
-> End Executions Threshold: 100 CPU per Elap per
Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value
------------ --------------- ---------------- ----------- ---------- ---------- 1,631 1,631 1.0 0.00 0.00 162071932
Module: CACMD.sms
Select Count(*) iCount From "CMD_DVN_CX" Where IsSent='N' and Cm
dDate<='20071124' 1,631 1,631 1.0 0.00 0.00 3808889251
Module: CACMD.sms
Update CA_RUNFLAG set FlagValue='N' where FlagID='IsCMDSending'
and CASID ='0009' 1,602 1,602 1.0 0.00 0.00 1738025616
Module: CACMD.sms
Select Count(*) iCount From "CMD_DVN_YX" Where IsSent='N' and Cm
dDate<='20071124' 1,602 1,602 1.0 0.00 0.00 2928636233
Module: CACMD.sms
Update CA_RUNFLAG set FlagValue='N' where FlagID='IsCMDSending'
and CASID ='02' 1,601 1,601 1.0 0.00 0.00 2853900259
Module: CACMD.sms
Update CA_RUNFLAG set FlagValue='Y' where FlagID='IsCMDSending'
and CASID ='02' 1,587 1,587 1.0 0.00 0.00 3977975804
SELECT AREAID, AREANM, PARENTID FROM REG_AREACODE WHERE AREAID =
'9' and AREAID != '0000000001' 1,571 1,571 1.0 0.00 0.00 585178709
Module: CACMD.sms
Update CA_RUNFLAG set FlagValue='Y' where FlagID='IsCMDSending'
and CASID ='0011' 1,570 1,570 1.0 0.00 0.00 20490731
Module: CACMD.sms
Update CA_RUNFLAG set FlagValue='N' where FlagID='IsCMDSending'
and CASID ='0011' 1,570 1,570 1.0 0.00 0.00 684099634
Module: CACMD.sms
Select Count(*) iCount From "CMD_DVN_BN" Where IsSent='N' and Cm
dDate<='20071124' 1,554 1,554 1.0 0.00 0.00 1064936426
Module: CACMD.sms
Update CA_RUNFLAG set FlagValue='Y' where FlagID='IsCMDSending'
and CASID ='0002' 1,554 1,554 1.0 0.00 0.00 2385034839
Module: CACMD.sms
Update CA_RUNFLAG set FlagValue='N' where FlagID='IsCMDSending'
and CASID ='0002'
SQL ordered by Executions for DB: SMSDB Instance: smsdb Snaps: 7 -9
-> End Executions Threshold: 100 CPU per Elap per
Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value
------------ --------------- ---------------- ----------- ---------- ----------
Module: CACMD.sms
Select Count(*) iCount From "CMD_DVN_KM" Where IsSent='N' and Cm
dDate<='20071124' 1,516 1,516 1.0 0.00 0.00 899019327
Module: CACMD.sms
Update CA_RUNFLAG set FlagValue='Y' where FlagID='IsCMDSending'
and CASID ='01' 1,516 1,516 1.0 0.00 0.00 1204413334
Module: CACMD.sms
Select Count(*) iCount From "CMD_DVN_QJ" Where IsSent='N' and Cm
dDate<='20071124' 1,516 1,516 1.0 0.00 0.00 2783056971
Module: CACMD.sms
Update CA_RUNFLAG set FlagValue='N' where FlagID='IsCMDSending'
and CASID ='01' 1,408 1,408 1.0 0.00 0.00 1983143151
select billtype from charge_PayBillDetail where SMSBillNO=:1 and
rownum=1 1,369 1,369 1.0 0.00 0.00 2405511289
select DVN_XT_LOGID_SEQ.NEXTVAL SEQVALUE from dual 1,354 1,354 1.0 0.00 0.00 2247203405
insert into SYS_LOGINFO(LOGID,LOGTYPE,LOGMODULE,AREAID,NETID,LOG
INFO,USERID,USERNM,LOGTIME,DESCRIPTION) values(:1,:2,:3,:4,:5,:6
,:7,:8,:9,:10) 1,316 1,316 1.0 0.00 0.00 206664093
Module: CACMD.sms
Update CA_RUNFLAG set FlagValue='Y' where FlagID='IsCMDSending'
and CASID ='0012' -------------------------------------------------------------
SQL ordered by Parse Calls for DB: SMSDB Instance: smsdb Snaps: 7 -9
-> End Parse Calls Threshold: 1000 % Total
Parse Calls Executions Parses Hash Value
------------ ------------ -------- ----------
22,329 22,330 10.70 631736976
select count(*) from TESTCON 20,133 22,011 9.65 3914117847
select * from PRODUCT_SERIES where SERIESSTATUS = '01' and PRODU
CTID = :1 and areaid in (select areaid from reg_areacode start w
ith areaid = :2 connect by prior parentid = areaid and AREAID !=
'0000000001') And SERIESID not in ( Select SERIESID from SERIES_
DESELECT where AREAID = :3) Order by PERIODTYPE,PERIOD 4,371 4,371 2.09 3742653144
select sysdate from dual 3,264 1,632 1.56 2555072908
Module: CACMD.sms
Update CA_RUNFLAG set FlagValue='Y' where FlagID='IsCMDSending'
and CASID ='0009' 3,262 1,631 1.56 162071932
Module: CACMD.sms
Select Count(*) iCount From "CMD_DVN_CX" Where IsSent='N' and Cm
dDate<='20071124' 3,262 1,631 1.56 3808889251
Module: CACMD.sms
Update CA_RUNFLAG set FlagValue='N' where FlagID='IsCMDSending'
and CASID ='0009' 3,237 3,237 1.55 2607682035
Module: JDBC Thin Client
Select count(*) from TESTCON 3,204 1,602 1.54 1738025616
Module: CACMD.sms
Select Count(*) iCount From "CMD_DVN_YX" Where IsSent='N' and Cm
dDate<='20071124' 3,204 1,602 1.54 2928636233
Module: CACMD.sms
Update CA_RUNFLAG set FlagValue='N' where FlagID='IsCMDSending'
and CASID ='02' 3,202 1,601 1.53 2853900259
Module: CACMD.sms
Update CA_RUNFLAG set FlagValue='Y' where FlagID='IsCMDSending'
and CASID ='02' 3,142 1,571 1.51 585178709
Module: CACMD.sms
Update CA_RUNFLAG set FlagValue='Y' where FlagID='IsCMDSending'
and CASID ='0011' 3,140 1,570 1.50 20490731
Module: CACMD.sms
SQL ordered by Parse Calls for DB: SMSDB Instance: smsdb Snaps: 7 -9
-> End Parse Calls Threshold: 1000 % Total
Parse Calls Executions Parses Hash Value
------------ ------------ -------- ----------
Update CA_RUNFLAG set FlagValue='N' where FlagID='IsCMDSending'
and CASID ='0011' 3,140 1,570 1.50 684099634
Module: CACMD.sms
Select Count(*) iCount From "CMD_DVN_BN" Where IsSent='N' and Cm
dDate<='20071124' 3,108 1,554 1.49 1064936426
Module: CACMD.sms
Update CA_RUNFLAG set FlagValue='Y' where FlagID='IsCMDSending'
and CASID ='0002' 3,108 1,554 1.49 2385034839
Module: CACMD.sms
Update CA_RUNFLAG set FlagValue='N' where FlagID='IsCMDSending'
and CASID ='0002' 3,108 1,554 1.49 3523143076
Module: CACMD.sms
Select Count(*) iCount From "CMD_DVN_KM" Where IsSent='N' and Cm
dDate<='20071124' 3,032 1,516 1.45 899019327
Module: CACMD.sms
Update CA_RUNFLAG set FlagValue='Y' where FlagID='IsCMDSending'
and CASID ='01' 3,032 1,516 1.45 1204413334
Module: CACMD.sms
Select Count(*) iCount From "CMD_DVN_QJ" Where IsSent='N' and Cm
dDate<='20071124' 3,032 1,516 1.45 2783056971
Module: CACMD.sms
Update CA_RUNFLAG set FlagValue='N' where FlagID='IsCMDSending'
and CASID ='01' 2,772 2,773 1.33 3068452925
select * from CMNG_CUSTOMSORT where SORTID = '' 2,654 2,654 1.27 1955729435
select * from CMNG_CUSTOMDISCOUNT where DISCOUNTID = '01' 2,653 2,653 1.27 586656027
select * from CMNG_CUSTOMTYPE where TYPE = '01' 2,632 1,316 1.26 206664093
Module: CACMD.sms
Update CA_RUNFLAG set FlagValue='Y' where FlagID='IsCMDSending'
and CASID ='0012' 2,632 1,316 1.26 3247731699
Module: CACMD.sms
SQL ordered by Parse Calls for DB: SMSDB Instance: smsdb Snaps: 7 -9
-> End Parse Calls Threshold: 1000 % Total
Parse Calls Executions Parses Hash Value
------------ ------------ -------- ----------
Select Count(*) iCount From "CMD_DVN_HH" Where IsSent='N' and Cm
dDate<='20071124' 2,632 1,316 1.26 3642256803
Module: CACMD.sms
Update CA_RUNFLAG set FlagValue='N' where FlagID='IsCMDSending'
and CASID ='0012'
1,659 1,659 0.80 1441427115
Module: JDBC Thin Client
Select * from "CMD_TF_YN" where IsSent='N' and CmdDate<=to_char
(sysdate, 'yyyymmdd') and RowNum <= 1000 and Transnum > '0001339
71' and Transnum <= ( select Max(C1) from ( select Max(Transnum)
C1 from "CMD_TF_YN" where IsSent='N' and Recorddate < (sysdate
-10/24/3600) Union select '000000000' C1 from dual )) order by T 1,639 2,710 0.79 2298890112
select * from CMNG_CUSTOMINFO where CUSTOMID = :1 1,632 0 0.78 66199152
Module: CACMD.sms
SELECT "CMD_DVN_CX".*, "CMD_DVN_CX".ROWID FROM "CMD_DVN_CX" 1,601 0 0.77 3571144412
Module: CACMD.sms
SELECT "CMD_DVN_YX".*, "CMD_DVN_YX".ROWID FROM "CMD_DVN_YX" 1,587 1,587 0.76 3977975804
SELECT AREAID, AREANM, PARENTID FROM REG_AREACODE WHERE AREAID =
'9' and AREAID != '0000000001' 1,571 0 0.75 3834199895
Module: CACMD.sms
SELECT "CMD_DVN_BN".*, "CMD_DVN_BN".ROWID FROM "CMD_DVN_BN" 1,554 0 0.74 3324058973
Module: CACMD.sms
SELECT "CMD_DVN_KM".*, "CMD_DVN_KM".ROWID FROM "CMD_DVN_KM" 1,516 0 0.73 1936585825
Module: CACMD.sms
SELECT "CMD_DVN_QJ".*, "CMD_DVN_QJ".ROWID FROM "CMD_DVN_QJ" 1,480 2,235 0.71 157877130
select * from charge_PayBill where SMSBillNO = :1 1,369 1,369 0.66 2405511289
select DVN_XT_LOGID_SEQ.NEXTVAL SEQVALUE from dual 1,359 2,290 0.65 1338792403
select * from charge_Account Where AccountID=:1 1,316 0 0.63 1805818967
Module: CACMD.sms
SQL ordered by Parse Calls for DB: SMSDB Instance: smsdb Snaps: 7 -9
-> End Parse Calls Threshold: 1000 % Total
Parse Calls Executions Parses Hash Value
------------ ------------ -------- ----------
SELECT "CMD_DVN_HH".*, "CMD_DVN_HH".ROWID FROM "CMD_DVN_HH" -------------------------------------------------------------
SQL ordered by Sharable Memory for DB: SMSDB Instance: smsdb Snaps: 7 -9
-> End Sharable Memory Threshold: 1048576Sharable Mem (b) Executions % Total Hash Value
---------------- ------------ ------- ------------
2,159,994 1 0.2 2698885710
select userid,decode(customnm,null,'合计',customnm) customnm,sum
(iccardcount) iccardcount,sum(totaliccardprice) totaliccardprice
,sum(totalstbprice) totalstbprice,sum(totalprice) totalprice,rou
nd(sum(net_fee),2) net_fee,round(sum(base_fee),2) base_fee,sum(z
hongshu) zhongshu,sum(wenguang) wengguang,sum(huacheng) huacheng 2,028,641 1 0.2 1487493856
select operatorid,decode(opernm,null,'合计',opernm) opernm,sum(i
ccardcount) iccardcount,sum(totaliccardprice) totaliccardprice,s
um(totalstbprice) totalstbprice,sum(totalprice) totalprice,sum(j
ibenbao_num) jibenbao_num,sum(net_fee) net_fee,sum(base_fee) bas
e_fee,sum(zhongshu_num) zhongshu_num,sum(zhongshu) zhongshu,sum( 2,000,273 1 0.2 3859443273
select operatorid,decode(opernm,null,'合计',opernm) opernm,sum(i
ccardcount) iccardcount,sum(totaliccardprice) totaliccardprice,s
um(totalstbprice) totalstbprice,sum(totalprice) totalprice,sum(j
ibenbao_num) jibenbao_num,sum(net_fee) net_fee,sum(base_fee) bas
e_fee,sum(zhongshu_num) zhongshu_num,sum(zhongshu) zhongshu,sum( 1,996,977 2 0.2 4022988168
select operatorid,decode(opernm,null,'合计',opernm) opernm,sum(i
ccardcount) iccardcount,sum(totaliccardprice) totaliccardprice,s
um(totalstbprice) totalstbprice,sum(totalprice) totalprice,sum(j
ibenbao_num) jibenbao_num,sum(net_fee) net_fee,sum(base_fee) bas
e_fee,sum(zhongshu_num) zhongshu_num,sum(zhongshu) zhongshu,sum( 1,210,524 1 0.1 1364574476
select areanm,decode(netnm, null, '合计', netnm) netnm,sum(iccar
dcount) iccardcount,sum(totaliccardprice) totaliccardprice,sum(t
otalstbprice) totalstbprice,sum(totalprice) totalprice,sum(jiben
bao_num) jibenbao_num,sum(net_fee) net_fee,sum(base_fee) base_fe
e,sum(zhongshu_num) zhongshu_num,sum(zhongshu) zhongshu,sum(weng 1,193,948 4 0.1 884743628
select areanm,decode(netnm, null, '合计', netnm) netnm,sum(iccar
dcount) iccardcount,sum(totaliccardprice) totaliccardprice,sum(t
otalstbprice) totalstbprice,sum(totalprice) totalprice,sum(jiben
bao_num) jibenbao_num,sum(net_fee) net_fee,sum(base_fee) base_fe
e,sum(zhongshu_num) zhongshu_num,sum(zhongshu) zhongshu,sum(weng -------------------------------------------------------------
Instance Activity Stats for DB: SMSDB Instance: smsdb Snaps: 7 -9
--------------------------------- ------------------ -------------- ------------
CPU used by this session 379,643 116.1 14.1
CPU used when call started 379,500 116.1 14.0
CR blocks created 2,587 0.8 0.1
Cached Commit SCN referenced 0 0.0 0.0
Commit SCN cached 0 0.0 0.0
DBWR buffers scanned 51,429 15.7 1.9
DBWR checkpoint buffers written 2,668 0.8 0.1
DBWR checkpoints 0 0.0 0.0
DBWR free buffers found 48,702 14.9 1.8
DBWR lru scans 50 0.0 0.0
DBWR make free requests 55 0.0 0.0
DBWR summed scan depth 51,429 15.7 1.9
DBWR transaction table writes 17 0.0 0.0
DBWR undo block writes 1,996 0.6 0.1
SQL*Net roundtrips to/from client 601,974 184.2 22.3
active txn count during cleanout 3,545 1.1 0.1
background checkpoints completed 0 0.0 0.0
background checkpoints started 0 0.0 0.0
background timeouts 3,921 1.2 0.2
branch node splits 1 0.0 0.0
buffer is not pinned count 46,128,768 14,111.0 1,706.8
buffer is pinned count 2,030,890,821 621,257.5 75,143.0
bytes received via SQL*Net from c 45,630,142 13,958.4 1,688.3
bytes sent via SQL*Net to client 99,567,312 30,458.0 3,684.0
calls to get snapshot scn: kcmgss 378,095 115.7 14.0
calls to kcmgas 31,290 9.6 1.2
calls to kcmgcs 638 0.2 0.0
change write time 141 0.0 0.0
cleanout - number of ktugct calls 2,743 0.8 0.1
cluster key scan block gets 1,121 0.3 0.0
cluster key scans 887 0.3 0.0
commit cleanout failures: block l 2 0.0 0.0
commit cleanout failures: buffer 0 0.0 0.0
commit cleanout failures: callbac 2 0.0 0.0
commit cleanout failures: cannot 14 0.0 0.0
commit cleanouts 52,545 16.1 1.9
commit cleanouts successfully com 52,527 16.1 1.9
commit txn count during cleanout 1,664 0.5 0.1
consistent changes 37,854 11.6 1.4
consistent gets 51,153,979 15,648.2 1,892.7
consistent gets - examination 5,441,516 1,664.6 201.3
current blocks converted for CR 34 0.0 0.0
cursor authentications 4,778 1.5 0.2
data blocks consistent reads - un 37,844 11.6 1.4
db block changes 185,504 56.8 6.9
db block gets 174,664 53.4 6.5
deferred (CURRENT) block cleanout 21,535 6.6 0.8
dirty buffers inspected 193 0.1 0.0
enqueue conversions 842 0.3 0.0
enqueue deadlocks 0 0.0 0.0
enqueue releases 89,586 27.4 3.3
enqueue requests 89,575 27.4 3.3
enqueue waits 2 0.0 0.0
execute count 175,407 53.7 6.5
free buffer inspected 129,920 39.7 4.8
free buffer requested 11,448,724 3,502.2 423.6
Instance Activity Stats for DB: SMSDB Instance: smsdb Snaps: 7 -9
全表扫描太厉害了.先把top sql中的sql语句给tuning了.
dDate <='20071124'
看看执行计划如何,再一个,如果可以,最好是在程序中使用绑定变量.你的hard parse太高.