| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Help!!!!Peformance Tuning
I have problems about CPU loading and Oracle performance.
The loading average is always higher than 1(which is abnormal).
I have looked up v$seesion_wait, jobq slave waits obviously hold
resources, that cause CPU loading is always high.
Plz help to tune my system.
Here is my machine load average.
load averages: 2.19, 2.21, 2.21
duck
10:55:52
90 processes: 86 sleeping, 1 stopped, 3 on cpu
CPU states: % idle, % user, % kernel, % iowait, %
swap
PID USERNAME THR PR NCE SIZE RES STATE TIME FLTS CPU COMMAND
2155 root 1 10 0 45.5M 10.4M cpu01 656.7H 0 24.49% Xsun
1932 oracle 1 0 0 1.3G 1.3G cpu03 228.4H 0 16.40% oracle
1938 oracle 1 58 0 1.3G 1.3G sleep 151.8H 0 6.50% oracle
1936 oracle 1 58 0 1.3G 1.3G sleep 227.0H 0 0.09% oracle
13322 oracle 1 58 0 1744K 1144K cpu00 0:00 0 0.02% top
19 root 1 58 0 8504K 6976K sleep 9:42 0 0.02%
vxconfigd
13051 oracle 1 58 0 5480K 1832K sleep 0:00 0 0.01% sshd
1920 oracle 1 58 0 1.3G 1.3G sleep 25:42 0 0.01% oracle
1910 oracle 12 58 0 1.3G 1.3G sleep 10:34 0 0.01% oracle
664 root 8 59 0 15.0M 7272K sleep 1:05 0 0.00% opcmona
13295 oracle 1 58 0 1.3G 1.2G sleep 0:00 0 0.00% oracle
9705 oracle 1 58 0 1.3G 1.3G sleep 276:00 0 0.00% oracle
765 root 16 59 0 4512K 3912K sleep 6:43 0 0.00%
OracleAgent.cur
676 root 1 99 -20 10.0M 9192K sleep 5:17 0 0.00% had
1908 oracle 62 59 0 1.3G 1.3G sleep 2:17 0 0.00% oracle
Here is my STATSPACK report:
STATSPACK report for
DB Name DB Id Instance Inst Num Release Cluster Host
------------ ----------- ------------ -------- ----------- -------
Snap Id Snap Time Sessions Curs/Sess Comment
--------- ------------------ -------- ---------
-------------------
Begin Snap: 978 17-1ды -06 11:00:03 15 38,039.3
End Snap: 998 18-1ды -06 07:00:03 15 39,212.8
Elapsed: 1,200.00 (mins)
Cache Sizes (end)
Buffer Cache: 512M Std Block Size: 8K
Shared Pool Size: 512M Log Buffer: 512K
Load Profile
~~~~~~~~~~~~ Per Second Per
Transaction
---------------
---------------
Redo size: 6,461.12
113,685.35
Logical reads: 12,127.64
213,389.63
Block changes: 49.83
876.85
Physical reads: 0.00
0.01
Physical writes: 0.59
10.30
User calls: 0.72
12.70
Parses: 1.45
25.48
Hard parses: 0.00
0.00
Sorts: 0.35
6.08
Logons: 0.24
4.23
Executes: 56.73
998.24
Transactions: 0.06
% Blocks changed per Read: 0.41 Recursive Call %: 99.23
Rollback per transaction %: 0.00 Rows per Sort: 3158.02
Instance Efficiency Percentages (Target 100%)
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 100.00 In-memory Sort %: 100.00
Library Hit %: 100.00 Soft Parse %: 100.00
Execute to Parse %: 97.45 Latch Hit %: 100.00
Parse CPU to Parse Elapsd %: 95.28 % Non-Parse CPU: 100.00
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 6.90 6.90
% SQL with executions>1: 71.11 71.11
% Memory for SQL w/exec>1: 78.22 78.22
Top 5 Timed Events
Event Waits Time (s)Ela Time
CPU time 69,061
99.69
log file parallel write 207,545 81
.12
process startup 925 36
.05
control file parallel write 23,984 35
.05
db file parallel write 17,378 32
.05
-------------------------------------------------------------
Wait Events for DB: CMSMETA Instance: CMSmeta Snaps: 978 -998
-> 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
---------------------------- ------------ ---------- ---------- ------
50.7
process startup 925 0 36 39
0.2
control file parallel write 23,984 0 35 1
5.9
db file parallel write 17,378 0 32 2
4.2
async disk IO 1,164 0 13 11
0.3
log file sequential read 412 0 13 32
0.1
log file sync 1,387 0 2 1
0.3
control file sequential read 17,992 0 1 0
4.4
LGWR wait for redo copy 9,257 0 0 0
2.3
log file switch completion 9 0 0 13
0.0
latch free 35 21 0 3
0.0
direct path write 60 0 0 0
0.0
buffer busy waits 74 0 0 0
0.0
slave TJ process wait 1 1 0 11
0.0
log file single write 8 0 0 1
0.0
direct path read 60 0 0 0
0.0
jobq slave wait 109,859 65,662 279,212 2542
26.8
virtual circuit status 2,399 2,399 70,079 29212
0.6
wakeup time manager 2,394 2,393 67,648 28257
0.6
-------------------------------------------------------------
Background Wait Events for DB: CMSMETA Instance: CMSmeta Snaps: 978
-998
Avg
Total Wait wait
Waits
Event Waits Timeouts Time (s) (ms)
/txn
---------------------------- ------------ ---------- ---------- ------
50.7
control file parallel write 23,984 0 35 1
5.9
db file parallel write 17,378 0 32 2
4.2
async disk IO 1,164 0 13 11
0.3
log file sequential read 412 0 13 32
0.1
control file sequential read 16,952 0 1 0
4.1
LGWR wait for redo copy 9,257 0 0 0
2.3
rdbms ipc reply 31 0 0 0
0.0
direct path write 60 0 0 0
0.0
log file single write 8 0 0 1
0.0
direct path read 60 0 0 0
0.0
buffer busy waits 2 0 0 0
0.0
rdbms ipc message 96,753 73,218 403,455 4170
23.6
pmon timer 23,921 23,921 70,270 2938
5.8
smon timer 249 232 68,009 ######
0.1
-------------------------------------------------------------
SQL ordered by Gets for DB: CMSMETA Instance: CMSmeta Snaps: 978
-998 -> End Buffer Gets Threshold: 10000 -> Note that resources reported for PL/SQL includes the resources usedby
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
871,255,435 897 971,299.3 99.8 ######## 67645.54
1924067401
DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;
broken BOOLEAN := FALSE; BEGIN DECLARE v_ID hitlog.id%TYPE;
v_TYPE hitlog.type%TYPE; v_Count NUMBER; CURSOR cur_produ
ct IS select unique ID,TYPE from hitlog t; BE
GIN OPEN cur_product; LOOP FETCH cur_product
866,505,092 1,987,397 436.0 99.2 ######## 66697.27
581084807
3,934,407 1,488,393 2.6 0.5 265.10 252.08
3882047173
UPDATE FATEPRODUCT T SET T.HITRATE = :B2 WHERE T.ID = :B1
1,000,735 464,884 2.2 0.1 110.27 106.13
936504761
UPDATE FATETHEME T SET T.HITRATE = :B2 WHERE T.ID = :B1
916,222 2,393 382.9 0.1 45.82 52.06
2614951860
select t.schema, t.name, t.flags, q.name from system.aq$_queue_t
ables t, sys.aq$_queue_table_affinities aft, system.aq$_que
ues q where aft.table_objno = t.objno and aft.owner_instance = :
1 and q.table_objno = t.objno and q.usage = 0 and b
itand(t.flags, 4+16+32+64+128+256) = 0 and NOT ( t.n
391,092 897 436.0 0.0 192.56 182.13
19327550
187,789 1,119 167.8 0.0 10.19 6.21
2699514414
DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;
broken BOOLEAN := FALSE; BEGIN updateoutpath; :mydate := next_da
te; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;
94,420 1,119 84.4 0.0 0.08 3.44
1524889499
UPDATE RSS_CHANNLES T SET T.OUTPATH = '/www/RSS/' || TO_CHAR(SYS
DATE,'YYYYMMDD') || '/' WHERE T.JOBID = 16
93,369 1,119 83.4 0.0 9.25 2.15
1383221079
UPDATE RSS_CHANNLES T SET T.OUTPATH = '/home/hinews/rss/' || TO_
CHAR(SYSDATE,'YYYYMMDD') || '/' WHERE T.JOBID = 14
59,370 16,396 3.6 0.0 0.00 8.31
2594425492
select u1.user#, u2.user#, u3.user#, failures, flag, interval#,
what, nlsenv, env, field1 from sys.job$ j, sys.user$ u1, sys .user$ u2, sys.user$ u3 where job=:1 and (next_date < sysdate o SQL ordered by Gets for DB: CMSMETA Instance: CMSmeta Snaps: 978
-998 -> End Buffer Gets Threshold: 10000 -> Note that resources reported for PL/SQL includes the resources usedby
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
55,279 34,121 1.6 0.0 6.22 6.06
622204029
UPDATE FATEAD T SET T.HITRATE = :B2 WHERE T.ID = :B1
44,310 14,123 3.1 0.0 0.00 6.23
1316169839
select job, nvl2(last_date, 1, 0) from sys.job$ where (((:1 <= n
ext_date) and (next_date < :2)) or ((last_date is null) and
(next_date < :3))) and (field1 = :4 or (field1 = 0 and 'Y' = :5)
) and (this_date is null) order by next_date, job
21,612 20 1,080.6 0.0 6.70 6.53
333476733
DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;
broken BOOLEAN := FALSE; BEGIN statspack.snap; :mydate := next_d
ate; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;
15,932 2,036 7.8 0.0 0.00 1.29
1356713530
select privilege#,level from sysauth$ connect by grantee#=prior
privilege# and privilege#>0 start with (grantee#=:1 or grantee#=
1) and privilege#>0
12,526 2,036 6.2 0.0 0.56 2.09
4075357577
update sys.job$ set failures=0, this_date=null, flag=:1, last_da
te=:2, next_date = greatest(:3, sysdate), total=total+(sysdate
-nvl(this_date,sysdate)) where job=:4
11,200 20 560.0 0.0 0.09 0.11
4115672663
select u.name, o.name, t.spare1, t.pctfree$ from sys.obj$ o,
sys.user$ u, sys.tab$ t where (bitand(t.trigflag, 1048576) =
6,155 2,036 3.0 0.0 0.00 2.00
297937389
update sys.job$ set this_date=:1 where job=:2
6,076 2,016 3.0 0.0 0.01 0.46
3210829694
select SYSDATE + 1/1440 from dual
4,176 232 18.0 0.0 0.00 0.08
2095543314
select f.file#, f.block#, f.ts#, f.length from fet$ f, ts$ t whe
re t.ts#=f.ts# and t.dflextpct!=0 and t.bitmapped=0
3,887 20 194.4 0.0 0.03 0.04
3170171180
INSERT INTO STATS$SQLTEXT ( HASH_VALUE , TEXT_SUBSET , PIECE , S
QL_TEXT , ADDRESS , COMMAND_TYPE , LAST_SNAP_ID ) SELECT /*+ ord
ered use_nl(vst) */ VST.HASH_VALUE , NEW_SQL.TEXT_SUBSET , VST.P
IECE , VST.SQL_TEXT , VST.ADDRESS , VST.COMMAND_TYPE , NEW_SQL.S
NAP_ID FROM (SELECT HASH_VALUE , ADDRESS , TEXT_SUBSET , SNAP_ID
3,654 2 1,827.0 0.0 0.06 0.07
3629250371
SQL ordered by Gets for DB: CMSMETA Instance: CMSmeta Snaps: 978
-998 -> End Buffer Gets Threshold: 10000 -> Note that resources reported for PL/SQL includes the resources usedby
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
3,600 20 180.0 0.0 0.61 0.59
2697077327
INSERT INTO STATS$LATCH ( SNAP_ID , DBID , INSTANCE_NUMBER , NAM
E , LATCH# , LEVEL# , GETS , MISSES , SLEEPS , IMMEDIATE_GETS ,
CPU Elapsd
Physical Reads Executions Reads per Exec %Total Time (s) Time (s)
Hash Value
0 897 0.0 0.0 192.56 182.13
19327550
SELECT UNIQUE ID,TYPE FROM HITLOG T
0 20 0.0 0.0 0.03 0.05
41415173
INSERT INTO STATS$INSTANCE_RECOVERY ( SNAP_ID , DBID , INSTANCE_
NUMBER , RECOVERY_ESTIMATED_IOS , ACTUAL_REDO_BLKS , TARGET_REDO
_BLKS , LOG_FILE_SIZE_REDO_BLKS , LOG_CHKPT_TIMEOUT_REDO_BLKS ,
LOG_CHKPT_INTERVAL_REDO_BLKS , FAST_START_IO_TARGET_REDO_BLKS ,
TARGET_MTTR , ESTIMATED_MTTR , CKPT_BLOCK_WRITES ) SELECT :B3 ,
0 239 0.0 0.0 0.00 0.07
130926350
0 20 0.0 0.0 0.00 0.01
173271802
INSERT INTO STATS$ROLLSTAT ( SNAP_ID , DBID , INSTANCE_NUMBER ,
USN , EXTENTS , RSSIZE , WRITES , XACTS , GETS , WAITS , OPTSIZE
, HWMSIZE , SHRINKS , WRAPS , EXTENDS , AVESHRINK , AVEACTIVE )
SELECT :B3 , :B2 , :B1 , USN , EXTENTS , RSSIZE , WRITES , XACT
S , GETS , WAITS , OPTSIZE , HWMSIZE , SHRINKS , WRAPS , EXTENDS
0 20 0.0 0.0 0.02 0.00
176460321
SELECT 1 FROM STATS$DATABASE_INSTANCE WHERE STARTUP_TIME = :B3 A
ND DBID = :B2 AND INSTANCE_NUMBER = :B1
0 20 0.0 0.0 0.01 0.02
237564565
INSERT INTO STATS$WAITSTAT ( SNAP_ID , DBID , INSTANCE_NUMBER ,
CLASS , WAIT_COUNT , TIME ) SELECT :B3 , :B2 , :B1 , CLASS , "CO
UNT" , TIME FROM V$WAITSTAT
0 20 0.0 0.0 0.02 0.01
282785072
INSERT INTO STATS$SHARED_POOL_ADVICE ( SNAP_ID , DBID , INSTANCE
_NUMBER , SHARED_POOL_SIZE_FOR_ESTIMATE , SHARED_POOL_SIZE_FACTO
R , ESTD_LC_SIZE , ESTD_LC_MEMORY_OBJECTS , ESTD_LC_TIME_SAVED ,
ESTD_LC_TIME_SAVED_FACTOR , ESTD_LC_MEMORY_OBJECT_HITS ) SELECT
:B3 , :B2 , :B1 , SHARED_POOL_SIZE_FOR_ESTIMATE , SHARED_POOL_S
0 2,036 0.0 0.0 0.00 2.00
297937389
update sys.job$ set this_date=:1 where job=:2
0 20 0.0 0.0 6.70 6.53
333476733
DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;
broken BOOLEAN := FALSE; BEGIN statspack.snap; :mydate := next_d
ate; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;
0 20 0.0 0.0 1.94 1.89
486588686
INSERT INTO STATS$BG_EVENT_SUMMARY ( SNAP_ID , DBID , INSTANCE_N
UMBER , EVENT , TOTAL_WAITS , TOTAL_TIMEOUTS , TIME_WAITED_MICRO
) SELECT :B3 , :B2 , :B1 , E.EVENT , SUM(E.TOTAL_WAITS) , SUM(E
.TOTAL_TIMEOUTS) , SUM(E.TIME_WAITED_MICRO) FROM V$SESSION_EVENT
E WHERE E.SID IN (SELECT S.SID FROM V$SESSION S WHERE S.TYPE =
0 20 0.0 0.0 0.00 0.14
536839608
SELECT NVL(MAX(BEGIN_TIME), TO_DATE('01011900','DDMMYYYY')) FROM
SQL ordered by Reads for DB: CMSMETA Instance: CMSmeta Snaps: 978
-998
-> End Disk Reads Threshold: 1000
CPU Elapsd
Physical Reads Executions Reads per Exec %Total Time (s) Time (s)
Hash Value
0 1,987,397 0.0 0.0 ######## 66697.27
581084807
SELECT COUNT(*) FROM HITLOG WHERE HITLOG.ID = :B2 AND HITLOG.TYP
E = :B1
0 20 0.0 0.0 0.00 0.02
615869461
INSERT INTO STATS$SNAPSHOT ( SNAP_ID, DBID, INSTANCE_NUMBER , SN
AP_TIME, STARTUP_TIME , SESSION_ID, SNAP_LEVEL, UCOMMENT , EXECU
TIONS_TH, PARSE_CALLS_TH, DISK_READS_TH , BUFFER_GETS_TH, SHARAB
LE_MEM_TH , VERSION_COUNT_TH, SEG_PHY_READS_TH , SEG_LOG_READS_T
H, SEG_BUFF_BUSY_TH, SEG_ROWLOCK_W_TH , SEG_ITL_WAITS_TH, SEG_CR
0 34,121 0.0 0.0 6.22 6.06
622204029
UPDATE FATEAD T SET T.HITRATE = :B2 WHERE T.ID = :B1
0 2,393 0.0 0.0 0.00 0.62
633914867
select q_name, state, delay, expiration, rowid, msgid, dequeu
e_msgid, chain_no, local_order_no, enq_time, enq_tid, step_no,
priority, exception_qschema, exception_queue, retry_count, corr
id, time_manager_info from SYS.AQ_EVENT_TABLE where time
_manager_info <= :1 and state != :2 for update skip locked
0 20 0.0 0.0 0.20 0.12 642932756
INSERT INTO STATS$TEMPSTATXS ( SNAP_ID , DBID , INSTANCE_NUMBER , TSNAME , FILENAME , PHYRDS , PHYWRTS , SINGLEBLKRDS , READTIM , WRITETIM , SINGLEBLKRDTIM , PHYBLKRD , PHYBLKWRT , WAIT_COUNT, TIME ) SELECT :B3 , :B2 , :B1 , TSNAME , FILENAME , PHYRDS , P HYWRTS , SINGLEBLKRDS , READTIM , WRITETIM , SINGLEBLKRDTIM , PH 0 20 0.0 0.0 0.05 0.03 663322427
CPU per Elap per
Executions Rows Processed Rows per Exec Exec (s) Exec (s)
Hash Value
1,987,397 1,987,397 1.0 0.03 0.03
581084807
SELECT COUNT(*) FROM HITLOG WHERE HITLOG.ID = :B2 AND HITLOG.TYP
E = :B1
1,488,393 935,527 0.6 0.00 0.00
3882047173
UPDATE FATEPRODUCT T SET T.HITRATE = :B2 WHERE T.ID = :B1
464,884 458,600 1.0 0.00 0.00
936504761
UPDATE FATETHEME T SET T.HITRATE = :B2 WHERE T.ID = :B1
47,860 47,860 1.0 0.00 0.00
3371479671
select t.name, (select owner_instance from sys.aq$_queue_table_
affinities where table_objno = t.objno) from system.aq$_queue
_tables t where t.name = :1 and t.schema = :2 for update skip lo
cked
34,121 20,652 0.6 0.00 0.00
622204029
UPDATE FATEAD T SET T.HITRATE = :B2 WHERE T.ID = :B1
16,396 2,035 0.1 0.00 0.00
2594425492
select u1.user#, u2.user#, u3.user#, failures, flag, interval#,
what, nlsenv, env, field1 from sys.job$ j, sys.user$ u1, sys .user$ u2, sys.user$ u3 where job=:1 and (next_date < sysdate o r :2 != 0) and lowner = u1.name and powner = u2.name and cowner
14,123 16,144 1.1 0.00 0.00
1316169839
select job, nvl2(last_date, 1, 0) from sys.job$ where (((:1 <= n
ext_date) and (next_date < :2)) or ((last_date is null) and
(next_date < :3))) and (field1 = :4 or (field1 = 0 and 'Y' = :5)
) and (this_date is null) order by next_date, job
2,393 0 0.0 0.00 0.00
633914867
select q_name, state, delay, expiration, rowid, msgid, dequeu
e_msgid, chain_no, local_order_no, enq_time, enq_tid, step_no,
priority, exception_qschema, exception_queue, retry_count, corr
id, time_manager_info from SYS.AQ_EVENT_TABLE where time
_manager_info <= :1 and state != :2 for update skip locked
2,393 0 0.0 0.00 0.00
994140048
select q_name, state, delay, expiration, rowid, msgid, dequeue
_msgid, chain_no, local_order_no, enq_time, enq_tid, step_no,
priority, exception_qschema, exception_queue, retry_count, corri
d, time_manager_info, sender_name, sender_address, sender_prot
ocol from QS_CBADM.QS_CBADM_ORDERS_SQTAB where time_manager_
2,393 0 0.0 0.00 0.00
1824912791
select q_name, state, delay, expiration, rowid, msgid, dequeue
_msgid, chain_no, local_order_no, enq_time, enq_tid, step_no,
priority, exception_qschema, exception_queue, retry_count, corri
d, time_manager_info, sender_name, sender_address, sender_prot
ocol from ODM.DMS_QUEUE_TABLE where time_manager_info <= :1
2,393 47,860 20.0 0.02 0.02
2614951860
SQL ordered by Executions for DB: CMSMETA Instance: CMSmeta Snaps:
978 -998
-> End Executions Threshold: 100
CPU per Elap per
Executions Rows Processed Rows per Exec Exec (s) Exec (s)
Hash Value
1 and q.table_objno = t.objno and q.usage = 0 and b
itand(t.flags, 4+16+32+64+128+256) = 0 and NOT ( t.n
2,393 0 0.0 0.00 0.00
3637162254
2,393 0 0.0 0.00 0.00
3920324236
select q_name, state, delay, expiration, rowid, msgid, dequeue
_msgid, chain_no, local_order_no, enq_time, enq_tid, step_no,
priority, exception_qschema, exception_queue, retry_count, corri
d, time_manager_info, sender_name, sender_address, sender_prot
ocol from SYS.AQ_SRVNTFN_TABLE where time_manager_info <= :1
2,036 2,036 1.0 0.00 0.00
297937389
update sys.job$ set this_date=:1 where job=:2
2,036 3,536 1.7 0.00 0.00
1356713530
select privilege#,level from sysauth$ connect by grantee#=prior
privilege# and privilege#>0 start with (grantee#=:1 or grantee#=
1) and privilege#>0
2,036 0 0.0 0.00 0.00
3990697329
alter session set NLS_LANGUAGE='TRADITIONAL CHINESE' NLS_TERRITO
RY='TAIWAN' NLS_CURRENCY='NT$' NLS_ISO_CURRENCY='TAIWAN' NLS_NUM
ERIC_CHARACTERS='.,' NLS_DATE_FORMAT='DD-MON-RR' NLS_DATE_LANGUA
GE='TRADITIONAL CHINESE' NLS_SORT='BINARY'
2,036 2,036 1.0 0.00 0.00
4075357577
update sys.job$ set failures=0, this_date=null, flag=:1, last_da
te=:2, next_date = greatest(:3, sysdate), total=total+(sysdate
-nvl(this_date,sysdate)) where job=:4
2,016 2,016 1.0 0.00 0.00
3210829694
select SYSDATE + 1/1440 from dual
1,119 16,785 15.0 0.01 0.00
1383221079
UPDATE RSS_CHANNLES T SET T.OUTPATH = '/home/hinews/rss/' || TO_
CHAR(SYSDATE,'YYYYMMDD') || '/' WHERE T.JOBID = 14
1,119 16,785 15.0 0.00 0.00
1524889499
UPDATE RSS_CHANNLES T SET T.OUTPATH = '/www/RSS/' || TO_CHAR(SYS
DATE,'YYYYMMDD') || '/' WHERE T.JOBID = 16
% Total
16,398 2,036 15.73 297937389 update sys.job$ set this_date=:1 where job=:2
16,398 16,396 15.73 2594425492 select u1.user#, u2.user#, u3.user#, failures, flag, interval#,
what, nlsenv, env, field1 from sys.job$ j, sys.user$ u1, sys .user$ u2, sys.user$ u3 where job=:1 and (next_date < sysdate o r :2 != 0) and lowner = u1.name and powner = u2.name and cowner
16,398 2,036 15.73 4075357577 update sys.job$ set failures=0, this_date=null, flag=:1, last_da te=:2, next_date = greatest(:3, sysdate), total=total+(sysdate -nvl(this_date,sysdate)) where job=:4
2,393 0 2.30 163922476 select q_name, state, delay, expiration, rowid, msgid, dequeue _msgid, chain_no, local_order_no, enq_time, enq_tid, step_no, priority, exception_qschema, exception_queue, retry_count, corri d, time_manager_info, sender_name, sender_address, sender_prot ocol from QS.QS_ORDERS_PR_MQTAB where msgid = :1
2,393 0 2.30 378803933 select q_name, state, delay, expiration, rowid, msgid, dequeue _msgid, chain_no, local_order_no, enq_time, enq_tid, step_no, priority, exception_qschema, exception_queue, retry_count, corri d, time_manager_info, sender_name, sender_address, sender_prot ocol from QS_ES.QS_ES_ORDERS_MQTAB where msgid = :1
2,393 0 2.30 613578973 select q_name, state, delay, expiration, rowid, msgid, dequeue _msgid, chain_no, local_order_no, enq_time, enq_tid, step_no, priority, exception_qschema, exception_queue, retry_count, corri d, time_manager_info, sender_name, sender_address, sender_prot ocol from QS_ES.QS_ES_ORDERS_PR_MQTAB where msgid = :1
2,393 2,393 2.30 633914867 select q_name, state, delay, expiration, rowid, msgid, dequeu e_msgid, chain_no, local_order_no, enq_time, enq_tid, step_no, priority, exception_qschema, exception_queue, retry_count, corr id, time_manager_info from SYS.AQ_EVENT_TABLE where time _manager_info <= :1 and state != :2 for update skip locked
2,393 2,393 2.30 994140048 select q_name, state, delay, expiration, rowid, msgid, dequeue _msgid, chain_no, local_order_no, enq_time, enq_tid, step_no, priority, exception_qschema, exception_queue, retry_count, corri d, time_manager_info, sender_name, sender_address, sender_prot ocol from QS_CBADM.QS_CBADM_ORDERS_SQTAB where time_manager_
2,393 2,393 2.30 1824912791
select q_name, state, delay, expiration, rowid, msgid, dequeue
_msgid, chain_no, local_order_no, enq_time, enq_tid, step_no,
priority, exception_qschema, exception_queue, retry_count, corri
SQL ordered by Parse Calls for DB: CMSMETA Instance: CMSmeta Snaps:
978 -998
-> End Parse Calls Threshold: 1000
% Total
2,393 0 2.30 1937207903 select q_name, state, delay, expiration, rowid, msgid, dequeue _msgid, chain_no, local_order_no, enq_time, enq_tid, step_no, priority, exception_qschema, exception_queue, retry_count, corri d, time_manager_info, sender_name, sender_address, sender_prot ocol from QS_OS.QS_OS_ORDERS_PR_MQTAB where msgid = :1
2,393 0 2.30 2540375272 select q_name, state, delay, expiration, rowid, msgid, dequeue _msgid, chain_no, local_order_no, enq_time, enq_tid, step_no, priority, exception_qschema, exception_queue, retry_count, corri d, time_manager_info, sender_name, sender_address, sender_prot ocol from QS_OS.QS_OS_ORDERS_MQTAB where msgid = :1
2,393 2,393 2.30 2614951860 select t.schema, t.name, t.flags, q.name from system.aq$_queue_t ables t, sys.aq$_queue_table_affinities aft, system.aq$_que ues q where aft.table_objno = t.objno and aft.owner_instance = :
1 and q.table_objno = t.objno and q.usage = 0 and b
itand(t.flags, 4+16+32+64+128+256) = 0 and NOT ( t.n
2,393 0 2.30 2833257892
select q_name, state, delay, expiration, rowid, msgid, dequeue
_msgid, chain_no, local_order_no, enq_time, enq_tid, step_no,
priority, exception_qschema, exception_queue, retry_count, corri
d, time_manager_info, sender_name, sender_address, sender_prot
ocol from QS_CBADM.QS_CBADM_ORDERS_MQTAB where msgid = :1
2,393 0 2.30 3137402284 select q_name, state, delay, expiration, rowid, msgid, dequeue _msgid, chain_no, local_order_no, enq_time, enq_tid, step_no, priority, exception_qschema, exception_queue, retry_count, corri d, time_manager_info, sender_name, sender_address, sender_prot ocol from QS_WS.QS_WS_ORDERS_PR_MQTAB where msgid = :1
Statistic Total per Secondper Trans
CPU used by this session 6,906,146 95.9
1,687.7
CPU used when call started 19 0.0
0.0
CR blocks created 2,410 0.0
0.6
DBWR checkpoint buffers written 42,104 0.6
10.3
DBWR checkpoints 4 0.0
Received on Tue Jan 17 2006 - 21:03:20 CST
 |
 |