jobs$ 引起 buffer busy wait

oracle_bin

Load Profile

	Per Second	Per Transaction
Redo size:	189,871.95	171,088.64
Logical reads:	1,197.45	1,078.99
Block changes:	545.66	491.68
Physical reads:	12.12	10.92
Physical writes:	10.47	9.43
User calls:	3.47	3.13
Parses:	2.27	2.04
Hard parses:	0.14	0.13
Sorts:	1.12	1.01
Logons:	0.04	0.04
Executes:	291.10	262.30
Transactions:	1.11

% Blocks changed per Read:	45.57	Recursive Call %:	98.89
Rollback per transaction %:	0.59	Rows per Sort:	45.50

Instance Efficiency Percentages (Target 100%)

Buffer Nowait %:	99.85	Redo NoWait %:	100.00
Buffer Hit %:	100.00	In-memory Sort %:	100.00
Library Hit %:	99.76	Soft Parse %:	93.80
Execute to Parse %:	99.22	Latch Hit %:	99.99
Parse CPU to Parse Elapsd %:	96.79	% Non-Parse CPU:	98.15

Shared Pool Statistics

	Begin	End
Memory Usage %:	81.30	82.35
% SQL with executions>1:	98.56	97.46
% Memory for SQL w/exec>1:	89.71	87.57

Top 5 Timed Events

Event	Waits	Time(s)	Avg Wait(ms)	% Total Call Time	Wait Class
buffer busy waits	8,000	7,995	999	68.2	Concurrency
log buffer space	4,191	4,190	1,000	35.8	Configuration
log file parallel write	5,585	1,928	345	16.5	System I/O
row cache lock	375	1,114	2,970	9.5	Concurrency
CPU time		130		1.1

oracle_bin

Owner Tablespace Name Object Name Subobject Name Obj. Type Buffer Busy Waits % of Capture
SYS SYSTEM JOB$   TABLE 3,690 91.97
SYS SYSTEM USER$   TABLE 322 8.03

oracle_bin

哪些情况会导致这种情况发生

oracle_bin

/* OracleOEM */ SELECT SUM(broken), SUM(failed) FROM (SELECT DECODE(broken, 'N', 0, 1) broken, DECODE(NVL(failures, 0), 0, 0, 1) failed FROM dba_jobs )

这条sql  执行了255.90秒 ，等待事件 就是 buffer busy wait 。 select 操作怎么也会有这个等待事件？

Maclean Liu(刘相兵

1.

请上传完整的AWR报告


2. 执行以下SQL 并贴出结果


select count(*) from sys.job$;

select table_name,last_analyzed from dba_tables where table_name='JOB$';

select index_name,status from dba_indexes where table_name='JOB$';


set linesize 200 pagesize 2000

explain plan for /* OracleOEM */ SELECT SUM(broken), SUM(failed) FROM (SELECT DECODE(broken, 'N', 0, 1) broken, DECODE(NVL(failures, 0), 0, 0, 1) failed FROM dba_jobs ) ;

@?/rdbms/admin/utlxplp

oracle_bin

---------------------------awr报告------------------------

oracle_bin

就一个job:  EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS();   
执行计划：

oracle_bin

select count(*) from sys.job$;   1
select table_name,last_analyzed from dba_tables where table_name='JOB$';
TABLE_NAME                     LAST_ANALYZED
------------------------------ -------------
JOB$                           07-四月-12     
1 row selected.
select index_name,status from dba_indexes where table_name='JOB$';
INDEX_NAME                     STATUS  
------------------------------ --------
I_JOB_JOB                      VALID   
I_JOB_NEXT                     VALID   
2 rows selected.
set linesize 200 pagesize 2000
explain plan for /* OracleOEM */ SELECT SUM(broken), SUM(failed) FROM (SELECT DECODE(broken, 'N', 0, 1) broken, DECODE(NVL(failures, 0), 0, 0, 1) failed FROM dba_jobs ) ;
Plan
SELECT STATEMENT  ALL_ROWSCost: 2  Bytes: 4  Cardinality: 1   
2 SORT AGGREGATE  Bytes: 4  Cardinality: 1   
  1 TABLE ACCESS FULL TABLE SYS.JOB$ Cost: 2  Bytes: 4  Cardinality: 1

Maclean Liu(刘相兵

TOP 5

Event        Waits        Time(s)        Avg Wait(ms)        % Total Call Time        Wait Class
buffer busy waits        8,000        7,995        999        68.2        Concurrency
log buffer space        4,191        4,190        1,000        35.8        Configuration
log file parallel write        5,585        1,928        345        16.5        System I/O
row cache lock        375        1,114        2,970        9.5        Concurrency


log buffer space + log file parallel write

log file parallel write  avg wait = 345 ms 这个 日志写的 I/O 等待异常缓慢


job$ => busy buffer wait  999ms       

可能因为后台log file parallel write 等待而造成server process pin住某个 buffer后而长期不释放， 造成其他 server process异常慢的busy buffer wait


建议

1. 搞清楚为什么 日志写 log file parallel write这样慢，考虑使用异步I/O

2. 确认JOB$表的段信息：


set linesize 200 pagesize 1400
select * from dba_segments where owner='SYS' and segment_name='JOB$';
select * from dba_tables  where owner='SYS' and table_name='JOB$';
select * from dba_hist_seg_stat where dataobj#=(select data_object_id from dba_objects where owner='SYS' and  object_name='JOB$');