AWR报表

呵呵，不错，作statspack简单多了，只需要运行awrrpt.sql 这个脚本，然后按照提示一步一步来就可以了。awrrpti.sql这个脚本会被awrrpt.sql自动调用。如果不想进入互动模式，那么需要手工编辑一下awrrpti.sql脚本。

下面是测试：

SQL> @awrrpt.sql

Current Instance
~~~~~~~~~~~~~~~~

   DB Id    DB Name      Inst Num Instance
----------- ------------ -------- ------------
3725863330 ORCL10              1 orcl10


Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
Enter value for report_type: html

Type Specified:  html 输入html表示要生成HTML格式的报表


Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   DB Id     Inst Num DB Name      Instance     Host
------------ -------- ------------ ------------ ------------
* 3725863330        1 ORCL10       orcl10       as.rhel.com

Using 3725863330 for database Id
Using          1 for instance number


Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed.  Pressing <return> without
specifying a number lists all completed snapshots.



Listing the last 3 days of Completed Snapshots

                                                        Snap
Instance     DB Name        Snap Id    Snap Started    Level
------------ ------------ --------- ------------------ -----
orcl10       ORCL10             425 08 Mar 2004 12:53      1

                                426 08 Mar 2004 14:40      1
                                427 08 Mar 2004 15:00      1
                                428 08 Mar 2004 16:00      1
                                429 08 Mar 2004 17:00      1
                                430 08 Mar 2004 18:00      1

列出了所有存在的SNAPID

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 428
Begin Snapshot Id specified: 428

Enter value for end_snap: 430
End   Snapshot Id specified: 430

输入起始和结束的SNAPID

Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_428_430.html.  To use this name,
press <return> to continue, otherwise enter an alternative.

Enter value for report_name:

输入文件名，或者直接回车使用默认的文件名


下面就是生成一大段的HTML，不贴出来了。有兴趣的看一下附件吧，是生成的报表。很漂亮哦。

WORKLOAD REPOSITORY report for

DB Name	DB Id	Instance	Inst num	Release	Cluster	Host
ORCL10	3725863330	orcl10	1	10.1.0.2.0	NO	as.rhel.com

	Snap Id	Snap Time	Sessions	Cursors/Session
Begin Snap:	429	08-Mar-04 17:00:58	18	2.8
End Snap:	430	08-Mar-04 18:00:31	18	2.8
Elapsed:		59.56 (mins)
DB Time:		0.07 (mins)

 

Report Summary

Cache Sizes (end)

Buffer Cache:	108M	Std Block Size:	8K
Shared Pool Size:	68M	Log Buffer:	256K

Load Profile

	Per Second	Per Transaction
Redo size:	584.09	6,605.20
Logical reads:	11.16	126.23
Block changes:	3.53	39.95
Physical reads:	0.02	0.18
Physical writes:	0.19	2.16
User calls:	0.07	0.83
Parses:	0.93	10.52
Hard parses:	0.00	0.01
Sorts:	0.69	7.82
Logons:	0.02	0.28
Executes:	1.72	19.47
Transactions:	0.09

% Blocks changed per Read:	31.65	Recursive Call %:	99.16
Rollback per transaction %:	2.53	Rows per Sort:	5.92

Instance Efficiency Percentages (Target 100%)

Buffer Nowait %:	100.00	Redo NoWait %:	99.96
Buffer Hit %:	99.86	In-memory Sort %:	100.00
Library Hit %:	99.96	Soft Parse %:	99.88
Execute to Parse %:	45.96	Latch Hit %:	100.00
Parse CPU to Parse Elapsd %:	97.87	% Non-Parse CPU:	82.58

Shared Pool Statistics

	Begin	End
Memory Usage %:	86.03	86.57
% SQL with executions>1:	82.61	84.58
% Memory for SQL w/exec>1:	77.11	79.21

Top 5 Timed Events

Event	Waits	Time(s)	Percent Total DB Time	Wait Class
control file parallel write	1,161	3	74.45	System I/O
CPU time		3	67.05
log file sync	75	1	28.18	Commit
process startup	15	1	19.17	Other
db file scattered read	14	0	8.77	User I/O

Main Report

• Report Summary
• Wait Events Statistics
• SQL Statistics
• Instance Activity Statistics
• IO Stats
• Buffer Pool Statistics
• Advisory Statistics
• Wait Statistics
• Undo Statistics
• Latch Statistics
• Segment Statistics
• Dictionary Cache Statistics
• Library Cache Statistics
• SGA Statistics
• Resource Limit Statistics
• init.ora Parameters

Back to Top

Wait Events Statistics

• Wait Events
• Background Wait Events
• Time Model Statistics
• Operating System Statistics
• Service Statistics
• Service Wait Class Stats

Back to Top

Wait Events

• 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)

Event	Waits	Timeouts	Total Wait Time (s)	Avg wait (ms)	Waits /txn
control file parallel write	1,161	0	3	3	3.67
log file sync	75	0	1	15	0.24
process startup	15	0	1	50	0.05
db file scattered read	14	0	0	25	0.04
db file sequential read	24	0	0	13	0.08
latch free	1	0	0	221	0.00
control file sequential read	441	0	0	0	1.40
db file parallel write	434	0	0	0	1.37
log file parallel write	269	0	0	0	0.85
rdbms ipc reply	7	0	0	0	0.02
Queue Monitor Task Wait	11	0	0	0	0.03
buffer busy waits	1	0	0	0	0.00
latch: redo allocation	1	0	0	0	0.00
direct path write	4	0	0	0	0.01
jobq slave wait	2,343	2,272	7,012	2,993	7.41
virtual circuit status	119	119	3,477	29,216	0.38
Queue Monitor Wait	116	116	3,455	29,788	0.37
wakeup time manager	15	3	3,125	208,329	0.05

Back to Wait Events Statistics
Back to Top

Background Wait Events

• ordered by wait time desc, waits desc (idle events last)

Event	Waits	Timeouts	Total Wait Time (s)	Avg wait (ms)	Waits /txn
control file parallel write	1,175	0	3	3	3.72
process startup	15	0	1	50	0.05
log file switch completion	2	0	0	88	0.01
log file sequential read	16	0	0	7	0.05
control file sequential read	433	0	0	0	1.37
log file single write	4	0	0	4	0.01
db file parallel write	434	0	0	0	1.37
log file parallel write	270	0	0	0	0.85
control file single write	3	0	0	1	0.01
rdbms ipc reply	7	0	0	0	0.02
Queue Monitor Task Wait	11	0	0	0	0.03
buffer busy waits	1	0	0	0	0.00
latch: redo allocation	1	0	0	0	0.00
rdbms ipc message	10,253	10,039	34,929	3,407	32.45
smon timer	13	12	3,484	268,010	0.04
Queue Monitor Wait	116	116	3,455	29,788	0.37

Back to Wait Events Statistics
Back to Top

Time Model Statistics

• ordered by Time (seconds) desc

Statistic Name	Time (seconds)	% Total DB Time
background elapsed time	19.00	482.65
background cpu time	9.49	241.05
DB time	3.94	100.00
DB CPU	2.74	69.50
sql execute elapsed time	2.33	59.27
PL/SQL execution elapsed time	0.41	10.42
parse time elapsed	0.27	6.87
Java execution elapsed time	0.05	1.28
hard parse (bind mismatch) elapsed time	0.00	0.00
sequence load elapsed time	0.00	0.00
failed parse (out of shared memory) elapsed t	0.00	0.00
hard parse (sharing criteria) elapsed time	0.00	0.00
inbound PL/SQL rpc elapsed time	0.00	0.00
connection management call elapsed time	0.00	0.00
failed parse elapsed time	0.00	0.00
PL/SQL compilation elapsed time	0.00	0.00
hard parse elapsed time	0.00	0.00

Back to Wait Events Statistics
Back to Top

Operating System Statistics

Statistic Name	Value
AVG_BUSY_TICKS	1,628
AVG_IDLE_TICKS	353,947
AVG_IN_BYTES	265,568,256
AVG_NICE_TICKS	0
AVG_OUT_BYTES	336,723,968
AVG_SYS_TICKS	266
AVG_USER_TICKS	1,362
BUSY_TICKS	1,628
IDLE_TICKS	353,947
IN_BYTES	265,568,256
NICE_TICKS	0
OUT_BYTES	336,723,968
RSRC_MGR_CPU_WAIT_TIME	0
SYS_TICKS	266
USER_TICKS	1,362

Back to Wait Events Statistics
Back to Top

Service Statistics

• ordered by DB Time
• us - microsecond - 1000000th of a second

Service Name	DB Time (s)	DB CPU (s)	Physical Reads	Logical Reads
SYS$USERS	3.90	2.70	15	14,815
SYS$BACKGROUND	0.00	0.00	87	25,167
orcl10.as.rhel.com	0.00	0.00	0	0
orcl10XDB	0.00	0.00	0	0

Back to Wait Events Statistics
Back to Top

Service Wait Class Stats

• Wait Class info for services in the Service Statistics section.
• Total Waits and Time Waited displayed for the following wait classes: User I/O, Concurrency, Administrative, Network
• Time Waited (Wt Time) in centisecond (100th of a second)

Service Name	User I/O Total Wts	User I/O Wt Time	Concurcy Total Wts	Concurcy Wt Time	Admin Total Wts	Admin Wt Time	Network Total Wts	Network Wt Time
SYS$USERS	3	13	0	0	0	0	0	0
SYS$BACKGROUND	220,703	69	1	0	0	0	1	0
orcl10.as.rhel.com	0	0	0	0	0	0	0	0

Back to Wait Events Statistics
Back to Top

 

SQL Statistics

• SQL ordered by Elapsed Time
• SQL ordered by CPU Time
• SQL ordered by Gets
• SQL ordered by Reads
• SQL ordered by Executions
• SQL ordered by Parse Calls
• SQL ordered by Sharable Memory
• SQL ordered by Version Count
• Complete List of SQL Text

Back to Top

SQL ordered by Elapsed Time

• Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.
• % Total DB Time is the Elapsed Time of the SQL statement divided into the Total Database Time multiplied by 100

Elapsed Time (s)	CPU Time (s)	Executions	Elap per Exec (s)	% Total DB Time	SQL Id	SQL Module	SQL Text
2	2	58	0.03	43.49	6gvch1xu9ca3g		DECLARE job BINARY_INTEGER := ...
1	0	1	0.89	22.57	5k7qrrrjxcd6w		insert into wrh$_enqueue_stat ...
1	1	2	0.30	15.28	6q766vsk5290x		BEGIN dbms_stats_internal.flus...
0	0	43	0.01	10.45	abtp0uqvdb1d3		CALL MGMT_ADMIN_DATA.EVALUATE_...
0	0	1	0.34	8.57	584ypkst2k8p7		insert into wrh$_bg_event_summ...
0	0	545	0.00	8.18	0h6b2sajwb74n		select privilege#, level from ...
0	0	1	0.30	7.65	5b1whcs76xtg4		MERGE INTO cache_stats_1$ D US...
0	0	1	0.30	7.53	fctq4u7ajf8c6		MERGE INTO cache_stats_1$ D US...
0	0	1	0.22	5.63	agpd044zj368m		insert into wrh$_system_event ...
0	0	698	0.00	5.02	803b7z0t84sq7		select job, nvl2(last_date, ...
0	0	107	0.00	3.92	18naypzfmabd6	EM_PING	INSERT INTO MGMT_SYSTEM_PERFOR...
0	0	66	0.00	3.52	6d64jpfzqc9rv		INSERT INTO MGMT_METRICS_RAW (...
0	0	629	0.00	3.11	g2wr3u7s1gtf3		select count(*) from sys.job$ ...
0	0	1	0.12	3.02	84qubbrsr0kfn		insert into wrh$_latch (snap...
0	0	1	0.12	2.94	d92h3rjp0y217		begin prvt_hdm.auto_execute( :...
0	0	1	0.11	2.88	fztumj649upth		insert into wrh$_sgastat (sn...
0	0	1	0.09	2.39	8x7f6g15hbrw1		insert into wrh$_sqlbind (sn...
0	0	278	0.00	2.31	8ggw94h7mvxd7	EM_PING	COMMIT
0	0	2	0.04	2.11	4ynyjw64rp857		(SELECT "A16"."DATAOBJ#" "O",...
0	0	84	0.00	2.07	2txg6sm725mn6		INSERT INTO MGMT_METRICS_RAW (...
0	0	1	0.07	1.88	g6wf9na8zs5hb		insert into wrh$_sysmetric_sum...
0	0	186	0.00	1.86	by0qb19b2n9u6		select t.schema, t.name, t.f...
0	0	1	0.07	1.85	586b2udq6dbng		insert into wrh$_sysstat (sn...
0	0	1	0.07	1.82	00pmrvsjpawc8		DECLARE job BINARY_INTEGER := ...
0	0	71	0.00	1.81	47a50dvdgnxc2		update sys.job$ set failures=0...
0	0	1	0.07	1.78	4m62qhfc4936p		insert into wrh$_parameter (...
0	0	105	0.00	1.65	g00cj285jmgsw		update sys.mon_mods$ set inser...
0	0	186	0.00	1.64	4cvqvs489pd6k		select min(next_date) from "SY...
0	0	150	0.00	1.58	91h2x42zqagcm		UPDATE MGMT_CURRENT_METRICS SE...
0	0	71	0.00	1.53	0k8522rmdzg4k		select privilege# from sysauth...
0	0	150	0.00	1.11	5kyb5bvnu3s04		SELECT DISTINCT METRIC_GUID FR...
0	0	71	0.00	1.05	7s7a79nsaf4j7		select u1.user#, u2.user#, u...
0	0	71	0.00	1.01	aq8yqxyyb40nn		update sys.job$ set this_date=...

Back to SQL Statistics
Back to Top

SQL ordered by CPU Time

• Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.
• % Total DB Time is the Elapsed Time of the SQL statement divided into the Total Database Time multiplied by 100

CPU Time (s)	Elapsed Time (s)	Executions	CPU per Exec (s)	% Total DB Time	SQL Id	SQL Module	SQL Text
2	2	58	0.03	43.49	6gvch1xu9ca3g		DECLARE job BINARY_INTEGER := ...
1	1	2	0.30	15.28	6q766vsk5290x		BEGIN dbms_stats_internal.flus...
0	0	545	0.00	8.18	0h6b2sajwb74n		select privilege#, level from ...
0	0	43	0.01	10.45	abtp0uqvdb1d3		CALL MGMT_ADMIN_DATA.EVALUATE_...
0	0	1	0.29	7.65	5b1whcs76xtg4		MERGE INTO cache_stats_1$ D US...
0	0	1	0.29	7.53	fctq4u7ajf8c6		MERGE INTO cache_stats_1$ D US...
0	0	1	0.25	8.57	584ypkst2k8p7		insert into wrh$_bg_event_summ...
0	0	698	0.00	5.02	803b7z0t84sq7		select job, nvl2(last_date, ...
0	0	629	0.00	3.11	g2wr3u7s1gtf3		select count(*) from sys.job$ ...
0	0	1	0.10	2.94	d92h3rjp0y217		begin prvt_hdm.auto_execute( :...
0	0	1	0.10	3.02	84qubbrsr0kfn		insert into wrh$_latch (snap...
0	0	66	0.00	3.52	6d64jpfzqc9rv		INSERT INTO MGMT_METRICS_RAW (...
0	0	278	0.00	2.31	8ggw94h7mvxd7	EM_PING	COMMIT
0	0	2	0.04	2.11	4ynyjw64rp857		(SELECT "A16"."DATAOBJ#" "O",...
0	0	84	0.00	2.07	2txg6sm725mn6		INSERT INTO MGMT_METRICS_RAW (...
0	0	1	0.08	2.39	8x7f6g15hbrw1		insert into wrh$_sqlbind (sn...
0	0	186	0.00	1.86	by0qb19b2n9u6		select t.schema, t.name, t.f...
0	0	1	0.07	1.82	00pmrvsjpawc8		DECLARE job BINARY_INTEGER := ...
0	0	71	0.00	1.81	47a50dvdgnxc2		update sys.job$ set failures=0...
0	0	1	0.07	1.85	586b2udq6dbng		insert into wrh$_sysstat (sn...
0	0	1	0.07	1.78	4m62qhfc4936p		insert into wrh$_parameter (...
0	0	105	0.00	1.65	g00cj285jmgsw		update sys.mon_mods$ set inser...
0	0	186	0.00	1.64	4cvqvs489pd6k		select min(next_date) from "SY...
0	0	107	0.00	3.92	18naypzfmabd6	EM_PING	INSERT INTO MGMT_SYSTEM_PERFOR...
0	0	150	0.00	1.58	91h2x42zqagcm		UPDATE MGMT_CURRENT_METRICS SE...
0	0	71	0.00	1.53	0k8522rmdzg4k		select privilege# from sysauth...
0	0	1	0.05	5.63	agpd044zj368m		insert into wrh$_system_event ...
0	0	150	0.00	1.11	5kyb5bvnu3s04		SELECT DISTINCT METRIC_GUID FR...
0	0	71	0.00	1.05	7s7a79nsaf4j7		select u1.user#, u2.user#, u...
0	0	71	0.00	1.01	aq8yqxyyb40nn		update sys.job$ set this_date=...
0	1	1	0.03	22.57	5k7qrrrjxcd6w		insert into wrh$_enqueue_stat ...
0	0	1	0.01	2.88	fztumj649upth		insert into wrh$_sgastat (sn...
0	0	1	0.01	1.88	g6wf9na8zs5hb		insert into wrh$_sysmetric_sum...

Back to SQL Statistics
Back to Top

SQL ordered by Gets

• Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.
• Total Buffer Gets: 39,888
• Captured SQL accounts for 70.7% of Total Buffer Gets

Buffer Gets	Executions	Gets per Exec	%Total	CPU Time (s)	Elapsed Time (s)	SQL Id	SQL Module	SQL Text
8,568	58	147.72	21.48	1.54	1.71	6gvch1xu9ca3g		DECLARE job BINARY_INTEGER := ...
3,664	545	6.72	9.19	0.32	0.32	0h6b2sajwb74n		select privilege#, level from ...
2,790	186	15.00	6.99	0.06	0.06	4cvqvs489pd6k		select min(next_date) from "SY...
2,104	43	48.93	5.27	0.30	0.41	abtp0uqvdb1d3		CALL MGMT_ADMIN_DATA.EVALUATE_...
2,094	698	3.00	5.25	0.20	0.20	803b7z0t84sq7		select job, nvl2(last_date, ...
1,717	2	858.50	4.30	0.60	0.60	6q766vsk5290x		BEGIN dbms_stats_internal.flus...
1,114	1	1,114.00	2.79	0.07	0.07	586b2udq6dbng		insert into wrh$_sysstat (sn...
1,095	1	1,095.00	2.75	0.10	0.12	84qubbrsr0kfn		insert into wrh$_latch (snap...
933	1	933.00	2.34	0.07	0.07	4m62qhfc4936p		insert into wrh$_parameter (...
930	186	5.00	2.33	0.07	0.07	by0qb19b2n9u6		select t.schema, t.name, t.f...
860	1	860.00	2.16	0.29	0.30	5b1whcs76xtg4		MERGE INTO cache_stats_1$ D US...
855	1	855.00	2.14	0.29	0.30	fctq4u7ajf8c6		MERGE INTO cache_stats_1$ D US...
829	71	11.68	2.08	0.06	0.06	0k8522rmdzg4k		select privilege# from sysauth...
819	107	7.65	2.05	0.06	0.15	18naypzfmabd6	EM_PING	INSERT INTO MGMT_SYSTEM_PERFOR...
793	66	12.02	1.99	0.10	0.14	6d64jpfzqc9rv		INSERT INTO MGMT_METRICS_RAW (...
677	1	677.00	1.70	0.08	0.09	8x7f6g15hbrw1		insert into wrh$_sqlbind (sn...
629	629	1.00	1.58	0.12	0.12	g2wr3u7s1gtf3		select count(*) from sys.job$ ...
607	1	607.00	1.52	0.10	0.12	d92h3rjp0y217		begin prvt_hdm.auto_execute( :...
570	71	8.03	1.43	0.07	0.07	47a50dvdgnxc2		update sys.job$ set failures=0...
568	71	8.00	1.42	0.04	0.04	7s7a79nsaf4j7		select u1.user#, u2.user#, u...
522	150	3.48	1.31	0.06	0.06	91h2x42zqagcm		UPDATE MGMT_CURRENT_METRICS SE...
506	84	6.02	1.27	0.08	0.08	2txg6sm725mn6		INSERT INTO MGMT_METRICS_RAW (...
485	58	8.36	1.22	0.04	0.04	19v5guvsgcd1v		SELECT C.TARGET_GUID, C.METRI...
464	58	8.00	1.16	0.03	0.03	3z6cvmt9w5292	EM_PING	SELECT /*+ RULE */ STEP_ID FRO...
456	150	3.04	1.14	0.04	0.04	5kyb5bvnu3s04		SELECT DISTINCT METRIC_GUID FR...
418	1	418.00	1.05	0.01	0.01	auy2nsgaw1pzg		insert into wrh$_seg_stat_obj ...
406	58	7.00	1.02	0.02	0.02	6t7007dkc1abh	EM_PING	DELETE FROM MGMT_JOB_EMD_STATU...
406	58	7.00	1.02	0.02	0.02	cchx5kcx6tgmz	EM_PING	SELECT COUNT(*) FROM MGMT_FAIL...
406	58	7.00	1.02	0.02	0.02	d59tgwpksgs94	EM_PING	SELECT EMD_URL, EVENT_TYPE, ...

Back to SQL Statistics
Back to Top

SQL ordered by Reads

• Total Disk Reads: 56
• Captured SQL accounts for 71.4% of Total Disk Reads

Physical Reads	Executions	Reads per Exec	%Total	CPU Time (s)	Elapsed Time (s)	SQL Id	SQL Module	SQL Text
15	58	0.26	26.79	1.54	1.71	6gvch1xu9ca3g		DECLARE job BINARY_INTEGER := ...
12	1	12.00	21.43	0.03	0.89	5k7qrrrjxcd6w		insert into wrh$_enqueue_stat ...
8	107	0.07	14.29	0.06	0.15	18naypzfmabd6	EM_PING	INSERT INTO MGMT_SYSTEM_PERFOR...
8	43	0.19	14.29	0.30	0.41	abtp0uqvdb1d3		CALL MGMT_ADMIN_DATA.EVALUATE_...
7	66	0.11	12.50	0.10	0.14	6d64jpfzqc9rv		INSERT INTO MGMT_METRICS_RAW (...
3	1	3.00	5.36	0.01	0.11	fztumj649upth		insert into wrh$_sgastat (sn...
2	1	2.00	3.57	0.01	0.07	g6wf9na8zs5hb		insert into wrh$_sysmetric_sum...
0	1	0.00	0.00	0.07	0.07	00pmrvsjpawc8		DECLARE job BINARY_INTEGER := ...
0	3	0.00	0.00	0.00	0.00	032pzujmgvyhj		select OBJOID, CLSOID, RUNT...
0	58	0.00	0.00	0.01	0.01	089dbukv1aanh	EM_PING	SELECT SYS_EXTRACT_UTC(SYSTIME...

Back to SQL Statistics
Back to Top

SQL ordered by Executions

• Total Executions: 6,153
• Captured SQL accounts for 76.1% of Total Executions

Executions	Rows Processed	Rows per Exec	CPU per Exec (s)	Elap per Exec (s)	SQL Id	SQL Module	SQL Text
698	71	0.10	0.00	0.00	803b7z0t84sq7		select job, nvl2(last_date, ...
629	629	1.00	0.00	0.00	g2wr3u7s1gtf3		select count(*) from sys.job$ ...
545	2,113	3.88	0.00	0.00	0h6b2sajwb74n		select privilege#, level from ...
278	0	0.00	0.00	0.00	8ggw94h7mvxd7	EM_PING	COMMIT
186	186	1.00	0.00	0.00	4cvqvs489pd6k		select min(next_date) from "SY...
186	186	1.00	0.00	0.00	by0qb19b2n9u6		select t.schema, t.name, t.f...
150	150	1.00	0.00	0.00	5kyb5bvnu3s04		SELECT DISTINCT METRIC_GUID FR...
150	150	1.00	0.00	0.00	91h2x42zqagcm		UPDATE MGMT_CURRENT_METRICS SE...
127	127	1.00	0.00	0.00	dm39phkdf04ug		SELECT DECODE(PARAMETER_VALUE,...
107	107	1.00	0.00	0.00	18naypzfmabd6	EM_PING	INSERT INTO MGMT_SYSTEM_PERFOR...
105	0	0.00	0.00	0.00	350f5yrnnmshs		lock table sys.mon_mods$ in ex...
105	105	1.00	0.00	0.00	g00cj285jmgsw		update sys.mon_mods$ set inser...
84	84	1.00	0.00	0.00	2txg6sm725mn6		INSERT INTO MGMT_METRICS_RAW (...
71	545	7.68	0.00	0.00	0k8522rmdzg4k		select privilege# from sysauth...
71	71	1.00	0.00	0.00	47a50dvdgnxc2		update sys.job$ set failures=0...
71	71	1.00	0.00	0.00	7s7a79nsaf4j7		select u1.user#, u2.user#, u...
71	0	0.00	0.00	0.00	813qds7fub8x4		alter session set NLS_LANGUAGE...
71	71	1.00	0.00	0.00	aq8yqxyyb40nn		update sys.job$ set this_date=...
66	66	1.00	0.00	0.00	6d64jpfzqc9rv		INSERT INTO MGMT_METRICS_RAW (...

Back to SQL Statistics
Back to Top

SQL ordered by Parse Calls

• Total Parse Calls: 3,325
• Captured SQL accounts for 84.5% of Total Parse Calls

Parse Calls	Executions	% Total Parses	SQL Id	SQL Module	SQL Text
545	545	16.39	0h6b2sajwb74n		select privilege#, level from ...
187	278	5.62	8ggw94h7mvxd7	EM_PING	COMMIT
186	4	5.59	0sfgqjz5cs52w		select q_name, state, delay,...
186	186	5.59	4cvqvs489pd6k		select min(next_date) from "SY...
186	186	5.59	by0qb19b2n9u6		select t.schema, t.name, t.f...
105	105	3.16	350f5yrnnmshs		lock table sys.mon_mods$ in ex...
105	105	3.16	g00cj285jmgsw		update sys.mon_mods$ set inser...
71	71	2.14	0k8522rmdzg4k		select privilege# from sysauth...
71	71	2.14	47a50dvdgnxc2		update sys.job$ set failures=0...
71	71	2.14	7s7a79nsaf4j7		select u1.user#, u2.user#, u...
71	71	2.14	813qds7fub8x4		alter session set NLS_LANGUAGE...
71	71	2.14	aq8yqxyyb40nn		update sys.job$ set this_date=...
58	58	1.74	089dbukv1aanh	EM_PING	SELECT SYS_EXTRACT_UTC(SYSTIME...
58	107	1.74	18naypzfmabd6	EM_PING	INSERT INTO MGMT_SYSTEM_PERFOR...
58	58	1.74	19v5guvsgcd1v		SELECT C.TARGET_GUID, C.METRI...
58	58	1.74	24dkx03u3rj6k		SELECT COUNT(*) FROM MGMT_PARA...
58	58	1.74	3z6cvmt9w5292	EM_PING	SELECT /*+ RULE */ STEP_ID FRO...
58	58	1.74	6b3nm6z1xz9yp	EM_PING	SELECT COUNT(FAILOVER_ID) FROM...
58	58	1.74	6gvch1xu9ca3g		DECLARE job BINARY_INTEGER := ...
58	58	1.74	6nf6bgxvghzq9	EM_PING	SELECT /*+ RULE */ STATUS, EX...
58	58	1.74	6t7007dkc1abh	EM_PING	DELETE FROM MGMT_JOB_EMD_STATU...
58	58	1.74	7gnakyxnvaadf	SEVERITY EVALUATION	SELECT UNIQUE(TARGET_GUID) FRO...
58	58	1.74	cchx5kcx6tgmz	EM_PING	SELECT COUNT(*) FROM MGMT_FAIL...
58	58	1.74	d59tgwpksgs94	EM_PING	SELECT EMD_URL, EVENT_TYPE, ...
58	58	1.74	dwssdqx28tzf5		select sysdate + 1 / (24 * 60)...
40	40	1.20	41ytarfvyaxm8		declare vsn va...

Back to SQL Statistics
Back to Top

 

SQL ordered by Sharable Memory

No data exists for this section of the report.

Back to SQL Statistics
Back to Top

 

SQL ordered by Version Count

No data exists for this section of the report.

Back to SQL Statistics
Back to Top

 

Complete List of SQL Text

SQL Id	SQL Text
00pmrvsjpawc8	DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate; broken BOOLEAN := FALSE; BEGIN wksys.wk_job.invoke(1, 1); :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;
032pzujmgvyhj	select OBJOID, CLSOID, RUNTIME, PRI, JOBTYPE, SCHLIM, WT, INST, RUNNOW from ( select a.obj# OBJOID, a.class_oid CLSOID, decode(bitand(a.flags, 16384), 0, a.next_run_date, a.last_enabled_time) RUNTIME, (2*a.priority + decode(bitand(a.job_status, 4), 0, 0, decode(a.running_instance, :1, -1, 1))) PRI, 1 JOBTYPE, a.schedule_limit SCHLIM, a.job_weight WT, decode(a.running_instance, NULL, 0, a.running_instance) INST, decode(bitand(a.flags, 16384), 0, 0, 1) RUNNOW from sys.scheduler$_job a where bitand(a.job_status, 515) = 1 and bitand(a.flags, 4096) = 0 and (a.next_run_date <= :2 or bitand(a.flags, 16384) <> 0) and ( (a.schedule_limit is null or bitand(a.flags, 16384) <> 0) or (a.schedule_limit is not null and a.next_run_date + a.schedule_limit > :3)) and (a.class_oid is null or (a.class_oid is not null and a.class_oid in (select b.obj# from sys.scheduler$_class b where b.affinity is null))) union all select c.obj#, 0, c.next_start_date, 0, 2, c.duration, 1, 0, 0 from sys.scheduler$_window c where bitand(c.flags, 1) <> 0 and c.next_start_date <= :4 and c.next_start_date + c.duration > :5 union all select d.obj#, 0, d.next_start_date + d.duration, 0, 3, numtodsinterval(0, 'minute'), 1, 0, 0 from sys.scheduler$_window d where bitand(d.flags, 1) <> 0 and d.next_start_date <= :6 and d.next_start_date + d.du ration > :7 union all select e.obj#, 0, e.manual_open_time, 0, 4, e.manual_duration, 1, 0, 0 from sys.scheduler$_window e where bitand(e.flags, 64) <> 0 and e.manual_open_time <= :8 and e.manual_open_time + e.manual_duration > :9 union all select f.obj#, 0, f.manual_open_time + f.manual_duration, 0, 5, numtodsinterval(0, 'minute'), 1, 0, 0 from sys.scheduler$_window f where bitand(f.flags, 64) <> 0 and f.manual_open_time <= :10 and f.manual_open_time + f.manual_duration > :11) order by RUNTIME, CLSOID, PRI, WT DESC, OBJOID
089dbukv1aanh	SELECT SYS_EXTRACT_UTC(SYSTIMESTAMP) FROM DUAL
0h6b2sajwb74n	select privilege#, level from sysauth$ connect by grantee#=prior privilege# and privilege#>0 start with grantee#=:1 and privilege#>0
0k8522rmdzg4k	select privilege# from sysauth$ where (grantee#=:1 or grantee#=1) and privilege#>0
0sfgqjz5cs52w	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, corrid, time_manager_info, sender_name, sender_address, sender_protocol from "SYS"."ALERT_QT" where msgid = :1
18naypzfmabd6	INSERT INTO MGMT_SYSTEM_PERFORMANCE_LOG (JOB_NAME, TIME, DURATION, MODULE, ACTION, IS_TOTAL, NAME, VALUE, CLIENT_DATA, HOST_URL) VALUES (:B9 , SYSDATE, :B8 , SUBSTR(:B7 , 1, 512), SUBSTR(:B6 , 1, 32), :B5 , SUBSTR(:B4 , 1, 128), SUBSTR(:B3 , 1, 128), SUBSTR(:B2 , 1, 128), SUBSTR(:B1 , 1, 256))
19v5guvsgcd1v	SELECT C.TARGET_GUID, C.METRIC_GUID, C.STORE_METRIC, C.SCHEDULE, C.COLL_NAME, M.METRIC_NAME, M.EVAL_FUNC FROM MGMT_METRIC_COLLECTIONS_REP R, MGMT_METRIC_COLLECTIONS C, MGMT_METRICS M WHERE C.SUSPENDED = 0 AND C.IS_REPOSITORY = 1 AND (C.LAST_COLLECTED_TIMESTAMP IS NULL OR C.LAST_COLLECTED_TIMESTAMP + C.SCHEDULE / 1440 < SYSDATE) AND C.METRIC_GUID = M.METRIC_GUID AND R.TARGET_GUID = C.TARGET_GUID AND R.METRIC_GUID = C.METRIC_GUID AND R.COLL_NAME = C.COLL_NAME
24dkx03u3rj6k	SELECT COUNT(*) FROM MGMT_PARAMETERS WHERE PARAMETER_NAME=:B1 AND UPPER(PARAMETER_VALUE)='TRUE'
2txg6sm725mn6	INSERT INTO MGMT_METRICS_RAW (TARGET_GUID, COLLECTION_TIMESTAMP, METRIC_GUID, KEY_VALUE, VALUE, STRING_VALUE) VALUES (:B5 , :B4 , :B3 , :B2 , :B1 , NULL)
350f5yrnnmshs	lock table sys.mon_mods$ in exclusive mode nowait
3z6cvmt9w5292	SELECT /*+ RULE */ STEP_ID FROM MGMT_JOB_EXECUTION E, MGMT_JOB J WHERE E.JOB_ID=J.JOB_ID AND STEP_STATUS IN (:B6 , :B5 , :B4 , :B3 , :B2 ) AND STEP_TYPE=:B1 AND J.EXECUTION_TIMEOUT > 0 AND (CAST(SYS_EXTRACT_UTC(SYSTIMESTAMP) AS DATE)-E.START_TIME) > (J.EXECUTION_TIMEOUT/24)
41ytarfvyaxm8	declare vsn varchar2(20); begin vsn := dbms_rcvman.getPackageVersion; :pkg_vsn:pkg_vsn_i := vsn; if vsn is not null then :pkg_vsnub4 := to_number(substr(vsn, 1, 2) || substr(vsn, 4, 2) || substr(vsn, 7, 2)); end if; end;
47a50dvdgnxc2	update sys.job$ set failures=0, this_date=null, flag=:1, last_date=:2, next_date = greatest(:3, sysdate), total=total+(sysdate-nvl(this_date, sysdate)) where job=:4
4cvqvs489pd6k	select min(next_date) from "SYS"."AQ$_ALERT_QT_T"
4m62qhfc4936p	insert into wrh$_parameter (snap_id, dbid, instance_number, parameter_hash, value, isdefault, ismodified) select :snap_id, :dbid, :instance_number, hash, value, isdefault, ismodified from v$system_parameter order by hash
4ynyjw64rp857	(SELECT "A16"."DATAOBJ#" "O", "A16"."STATISTIC#" "STAT", NVL("A16"."VALUE", 0) "VAL" FROM "GV$SEGSTAT" "A16" WHERE ("A16"."STATISTIC#"=0 OR "A16"."STATISTIC#"=3 OR "A16"."STATISTIC#"=5) AND "A16"."OBJ#">0 AND "A16"."INST_ID"=1) UNION ALL (SELECT "A15"."OBJ#" "O", 7 "STAT", NVL(SUM("A15"."NUM_BUF"), 0) "VAL" FROM "X$KCBOQH" "A15" WHERE "A15"."INST_ID"=1 GROUP BY "A15"."OBJ#") ORDER BY 1
584ypkst2k8p7	insert into wrh$_bg_event_summary (snap_id, dbid, instance_number, event_id, total_waits, total_timeouts, time_waited_micro) select :snap_id, :dbid, :instance_number, e.event_id, 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 = 'BACKGROUND') group by e.event_id
586b2udq6dbng	insert into wrh$_sysstat (snap_id, dbid, instance_number, stat_id, value) select :snap_id, :dbid, :instance_number, stat_id, value from v$sysstat order by stat_id
5b1whcs76xtg4	MERGE INTO cache_stats_1$ D USING (select * from table(dbms_stats_internal.format_cache_rows(CURSOR((select dataobj# o, statistic# stat, nvl(value, 0) val from gv$segstat where statistic# in (0, 3, 5) and obj# > 0 and inst_id = 1) union all (select obj# o, 7 stat, nvl(sum(num_buf), 0) val from x$kcboqh x where inst_id = 1 group by obj#) order by o))) where cachedblk>10) S ON (D.dataobj# = S.obj# AND D.inst_id = S.inst_id) WHEN MATCHED THEN UPDATE SET cached_avg = CASE WHEN (S.cachedblk>0 AND cached_sqr_avg <= cached_avg*cached_avg* 5) THEN 2/(cached_no+1)*((cached_no-1)/2*cached_avg+S.cachedblk) WHEN (S.cachedblk>0 AND cached_sqr_avg > cached_avg*cached_avg* 5) THEN S.cachedblk ELSE cached_avg END, cached_sqr_avg = CASE WHEN (S.cachedblk>0 AND cached_sqr_avg <= cached_avg*cached_avg* 5) THEN 2/(cached_no+1)*((cached_no-1)/2*cached_sqr_avg+ S.cachedblk*S.cachedblk) WHEN (S.cachedblk>0 AND cached_sqr_avg > cached_avg*cached_avg* 5) THEN S.cachedblk*S.cachedblk ELSE cached_sqr_avg END, cached_no = CASE WHEN (S.cachedblk>0 AND cached_sqr_avg <= cached_avg*cached_avg* 5) THEN cached_no+1 WHEN (S.cachedblk>0 AND cached_sqr_avg > cached_avg*cached_avg* 5) THEN 1 ELSE cached_no END, cached_seq_no = CASE WHEN S.cachedblk>0 THEN 2314 ELSE cached_seq_no END, chr_avg = CASE WHEN (S.logrds>lgr_last AND chr_sqr_avg <= chr_avg*chr_avg* 5) THEN 2/(chr_no+1)*((chr_no-1)/2*chr_avg+ (1 - (S.phrds-phr_last)/(S.logrds-lgr_last)) ) WHEN (S.logrds>lgr_last AND (chr_no=0 OR chr_sqr_avg > chr_avg*chr_avg* 5)) THEN (1 - (S.phrds-phr_last)/(S.logrds-lgr_last)) ELSE chr_avg END, chr_sqr_avg = CASE WHEN (S.logrds>lgr_last AND chr_sqr_avg <= chr_avg*chr_avg* 5) THEN 2/(chr_no+1)*((chr_no-1)/2*chr_sqr_avg+ (1 - (S.phrds-phr_last)/(S.logrds-lgr_last)) *(1 - (S.phrds-phr_last)/(S.logrds-lgr_last)) ) WHEN (S.logrds>lgr_last AND (chr_no=0 OR chr_sqr_avg > chr_avg*chr_avg* 5)) THEN (1 - (S.phrds-phr_last)/(S.logrds-lgr_last)) *(1 - (S.phrds-phr_last)/(S.logrds-lgr_last)) ELSE chr_sqr_avg E ND, chr_no = CASE WHEN (S.logrds>lgr_last AND chr_sqr_avg <= chr_avg*chr_avg* 5) THEN chr_no+1 WHEN (S.logrds>lgr_last AND (chr_no=0 OR chr_sqr_avg > chr_avg*chr_avg* 5)) THEN 1 ELSE chr_no END, chr_seq_no = CASE WHEN S.logrds>lgr_last THEN 2314 ELSE chr_seq_no END, lgr_sum = CASE WHEN (S.logrds>lgr_last AND chr_sqr_avg <= chr_avg*chr_avg* 5) THEN S.logrds-lgr_last+lgr_sum WHEN (S.logrds>lgr_last AND (chr_no=0 OR chr_sqr_avg > chr_avg*chr_avg* 5)) THEN S.logrds-lgr_last ELSE lgr_sum END, lgr _last = S.logrds, phr_last = S.phrds WHERE dataobj#=S.obj# WHEN NOT MATCHED THEN INSERT (dataobj#, inst_id, cached_avg, cached_sqr_avg, cached_no, cached_seq_no, chr_avg, chr_sqr_avg, chr_no, chr_seq_no, lgr_sum, lgr_last, phr_last) VALUES (S.obj#, 1, S.cachedblk, S.cachedblk*S.cachedblk, CASE WHEN (S.cachedblk>0) THEN 1 ELSE 0 END, 2313, 0, 0, 0, 2313, 0, S.logrds, S.phrds)
5k7qrrrjxcd6w	insert into wrh$_enqueue_stat (snap_id, dbid, instance_number, eq_type, req_reason, total_req#, total_wait#, succ_req#, failed_req#, cum_wait_time, event#) select :snap_id, :dbid, :instance_number, eq_type, req_reason, sum(total_req#), sum(total_wait#), sum(succ_req#), sum(failed_req#), sum(cum_wait_time), max(event#) from v$enqueue_statistics where total_req# != 0 group by eq_type, req_reason
5kyb5bvnu3s04	SELECT DISTINCT METRIC_GUID FROM MGMT_METRICS WHERE TARGET_TYPE = :B3 AND METRIC_NAME = :B2 AND METRIC_COLUMN = :B1
6b3nm6z1xz9yp	SELECT COUNT(FAILOVER_ID) FROM MGMT_FAILOVER_TABLE
6d64jpfzqc9rv	INSERT INTO MGMT_METRICS_RAW (TARGET_GUID, COLLECTION_TIMESTAMP, METRIC_GUID, KEY_VALUE, VALUE, STRING_VALUE) VALUES (:B5 , :B4 , :B3 , :B2 , NULL, :B1 )
6gvch1xu9ca3g	DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate; broken BOOLEAN := FALSE; BEGIN EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS(); :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;
6nf6bgxvghzq9	SELECT /*+ RULE */ STATUS, EXECUTION_ID FROM MGMT_JOB_EXEC_SUMMARY E WHERE STATUS IN (:B2 , :B1 ) AND SUSPEND_TIMEOUT > 0 AND (CAST(SYS_EXTRACT_UTC(SYSTIMESTAMP) AS DATE)-SUSPEND_TIME) > (SUSPEND_TIMEOUT/(24*60))
6q766vsk5290x	BEGIN dbms_stats_internal.flush_cache_stats; END;
6t7007dkc1abh	DELETE FROM MGMT_JOB_EMD_STATUS_QUEUE
7gnakyxnvaadf	SELECT UNIQUE(TARGET_GUID) FROM MGMT_METRIC_DEPENDENCY WHERE CAN_CALCULATE = 1 AND DISABLED = 0
7s7a79nsaf4j7	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 or :2 != 0) and lowner = u1.name and powner = u2.name and cowner = u3.name
803b7z0t84sq7	select job, nvl2(last_date, 1, 0) from sys.job$ where (((:1 <= next_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
813qds7fub8x4	alter session set NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$' NLS_ISO_CURRENCY='AMERICA' NLS_NUMERIC_CHARACTERS='., ' NLS_DATE_FORMAT='DD-MON-RR' NLS_DATE_LANGUAGE='AMERICAN' NLS_SORT='BINARY'
84qubbrsr0kfn	insert into wrh$_latch (snap_id, dbid, instance_number, latch_hash, level#, gets, misses, sleeps, immediate_gets, immediate_misses, spin_gets, sleep1, sleep2, sleep3, sleep4, wait_time) select :snap_id, :dbid, :instance_number, hash, level#, gets, misses, sleeps, immediate_gets, immediate_misses, spin_gets, sleep1, sleep2, sleep3, sleep4, wait_time from v$latch order by hash
8ggw94h7mvxd7	COMMIT
8x7f6g15hbrw1	insert into wrh$_sqlbind (snap_id, dbid, instance_number, sql_id, child_number, name, position, dup_position, datatype, datatype_string, character_sid, precision, scale, max_length, was_captured, last_captured, value_string, value_anydata) SELECT /*+ ordered use_nl(b) index(b sql_id) */ :snap_id, :dbid, :instance_number, x.sqlid_kewrsie, b.child_number, b.name, b.position, b.dup_position, b.datatype, b.datatype_string, b.character_sid , b.precision, b.scale, b.max_length, b.was_captured, b.last_captured, b.value_string, b.value_anydata FROM x$kewrsqlidtab x, v$sql_bind_capture b where x.sqlid_kewrsie = b.sql_id AND b.child_number = 0
91h2x42zqagcm	UPDATE MGMT_CURRENT_METRICS SET COLLECTION_TIMESTAMP = :B3 , VALUE = :B2 , STRING_VALUE = :B1 WHERE TARGET_GUID = :B6 AND METRIC_GUID = :B5 AND KEY_VALUE = :B4 AND COLLECTION_TIMESTAMP < :B3
abtp0uqvdb1d3	CALL MGMT_ADMIN_DATA.EVALUATE_MGMT_METRICS(:tguid, :mguid, :result)
agpd044zj368m	insert into wrh$_system_event (snap_id, dbid, instance_number, event_id, total_waits, total_timeouts, time_waited_micro) select :snap_id, :dbid, :instance_number, event_id, total_waits, total_timeouts, time_waited_micro from v$system_event order by event_id
aq8yqxyyb40nn	update sys.job$ set this_date=:1 where job=:2
auy2nsgaw1pzg	insert into wrh$_seg_stat_obj ( snap_id , dbid , ts# , obj# , dataobj# , owner , object_name , subobject_name , partition_type , object_type , tablespace_name) select :lah_snap_id , :dbid , ss1.tsn_kewrseg , ss1.objn_kewrseg , ss1.objd_kewrseg , ss1.ownername_kewrseg , ss1.objname_kewrseg , ss1.subobjname_kewrseg , decode(po.parttype, 1, 'RANGE', 2, 'HASH', 3, 'SYSTEM', 4, 'LIST', NULL, 'NONE', 'UNKNOWN') , decode(ss1.objtype_kewrseg, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER', 4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE', 7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE', 11, 'PACKAGE BODY', 12, 'TRIGGER', 13, 'TYPE', 14, 'TYPE BODY', 19, 'TABLE PARTITION', 20, 'INDEX PARTITION', 21, 'LOB', 22, 'LIBRARY', 23, 'DIRECTORY', 24, 'QUEUE', 28, 'JAVA SOURCE', 29, 'JAVA CLASS', 30, 'JAVA RESOURCE', 32, 'INDEXTYPE', 33, 'OPERATOR', 34, 'TABLE SUBPARTITION', 35, 'INDEX SUBPARTITION', 40, 'LOB PARTITION', 41, 'LOB SUBPARTITION', 42, 'MATERIALIZED VIEW', 43, 'DIMENSION', 44, 'CONTEXT', 47, 'RESOURCE PLAN', 48, 'CONSUMER GROUP', 51, 'SUBSCRIPTION', 52, 'LOCATION', 55, 'XML SCHEMA', 56, 'JAVA DATA', 57, 'SECURITY PROFILE', 'UNDEFINED') , ss1.tsname_kewrseg from x$kewrtsegstat ss1, (select o.obj#, p.parttype from sys.obj$ o, sys.partobj$ p where o.obj# = p.obj#) po where ss1.objn_kewrseg = po.obj#(+) and (ss1.objtype_kewrseg not in (1 /* INDEX - handled below */, 10 /* NON-EXISTENT */) or (ss1.objtype_kewrseg = 1 and 1 = (select 1 from ind$ i where i.obj# = ss1.objn_kewrseg and i.type# in (1, 2, 3, 4, 6, 7, 9)))) and ss1.objname_kewrseg != '_NEXT_OBJECT' and ss1.objname_kewrseg != '_default_auditing_options_' and not exists (select 1 from wrh$_seg_stat_obj ss2 where ss2.dataobj# = ss1.objd_kewrseg and ss2.obj# = ss1.ob jn_kewrseg and ss2.ts# = ss1.tsn_kewrseg and ss2.dbid = :dbid2)
by0qb19b2n9u6	select t.schema, t.name, t.flags, q.name, t.timezone from system.aq$_queue_tables t, system.aq$_queues q where t.objno = :1 and q.table_objno = t.objno and q.usage = 0
cchx5kcx6tgmz	SELECT COUNT(*) FROM MGMT_FAILOVER_TABLE WHERE SYSDATE-LAST_TIME_STAMP > (HEARTBEAT_INTERVAL*4)/(24*60*60)
d59tgwpksgs94	SELECT EMD_URL, EVENT_TYPE, OCCUR_TIME FROM MGMT_JOB_EMD_STATUS_QUEUE ORDER BY OCCUR_TIME
d92h3rjp0y217	begin prvt_hdm.auto_execute( :db_id, :inst_id, :end_snap ); end;
dm39phkdf04ug	SELECT DECODE(PARAMETER_VALUE, :B3 , 3, :B2 , 2, :B1 , 1, 0) FROM MGMT_PARAMETERS WHERE PARAMETER_NAME = 'system_error_log_level'
dwssdqx28tzf5	select sysdate + 1 / (24 * 60) from dual
fctq4u7ajf8c6	MERGE INTO cache_stats_1$ D USING (select * from table(dbms_stats_internal.format_cache_rows(CURSOR((select dataobj# o, statistic# stat, nvl(value, 0) val from gv$segstat where statistic# in (0, 3, 5) and obj# > 0 and inst_id = 1) union all (select obj# o, 7 stat, nvl(sum(num_buf), 0) val from x$kcboqh x where inst_id = 1 group by obj#) order by o))) where cachedblk>10) S ON (D.dataobj# = S.obj# AND D.inst_id = S.inst_id) WHEN MATCHED THEN UPDATE SET cached_avg = CASE WHEN (S.cachedblk>0 AND cached_sqr_avg <= cached_avg*cached_avg* 5) THEN 2/(cached_no+1)*((cached_no-1)/2*cached_avg+S.cachedblk) WHEN (S.cachedblk>0 AND cached_sqr_avg > cached_avg*cached_avg* 5) THEN S.cachedblk ELSE cached_avg END, cached_sqr_avg = CASE WHEN (S.cachedblk>0 AND cached_sqr_avg <= cached_avg*cached_avg* 5) THEN 2/(cached_no+1)*((cached_no-1)/2*cached_sqr_avg+ S.cachedblk*S.cachedblk) WHEN (S.cachedblk>0 AND cached_sqr_avg > cached_avg*cached_avg* 5) THEN S.cachedblk*S.cachedblk ELSE cached_sqr_avg END, cached_no = CASE WHEN (S.cachedblk>0 AND cached_sqr_avg <= cached_avg*cached_avg* 5) THEN cached_no+1 WHEN (S.cachedblk>0 AND cached_sqr_avg > cached_avg*cached_avg* 5) THEN 1 ELSE cached_no END, cached_seq_no = CASE WHEN S.cachedblk>0 THEN 2316 ELSE cached_seq_no END, chr_avg = CASE WHEN (S.logrds>lgr_last AND chr_sqr_avg <= chr_avg*chr_avg* 5) THEN 2/(chr_no+1)*((chr_no-1)/2*chr_avg+ (1 - (S.phrds-phr_last)/(S.logrds-lgr_last)) ) WHEN (S.logrds>lgr_last AND (chr_no=0 OR chr_sqr_avg > chr_avg*chr_avg* 5)) THEN (1 - (S.phrds-phr_last)/(S.logrds-lgr_last)) ELSE chr_avg END, chr_sqr_avg = CASE WHEN (S.logrds>lgr_last AND chr_sqr_avg <= chr_avg*chr_avg* 5) THEN 2/(chr_no+1)*((chr_no-1)/2*chr_sqr_avg+ (1 - (S.phrds-phr_last)/(S.logrds-lgr_last)) *(1 - (S.phrds-phr_last)/(S.logrds-lgr_last)) ) WHEN (S.logrds>lgr_last AND (chr_no=0 OR chr_sqr_avg > chr_avg*chr_avg* 5)) THEN (1 - (S.phrds-phr_last)/(S.logrds-lgr_last)) *(1 - (S.phrds-phr_last)/(S.logrds-lgr_last)) ELSE chr_sqr_avg E ND, chr_no = CASE WHEN (S.logrds>lgr_last AND chr_sqr_avg <= chr_avg*chr_avg* 5) THEN chr_no+1 WHEN (S.logrds>lgr_last AND (chr_no=0 OR chr_sqr_avg > chr_avg*chr_avg* 5)) THEN 1 ELSE chr_no END, chr_seq_no = CASE WHEN S.logrds>lgr_last THEN 2316 ELSE chr_seq_no END, lgr_sum = CASE WHEN (S.logrds>lgr_last AND chr_sqr_avg <= chr_avg*chr_avg* 5) THEN S.logrds-lgr_last+lgr_sum WHEN (S.logrds>lgr_last AND (chr_no=0 OR chr_sqr_avg > chr_avg*chr_avg* 5)) THEN S.logrds-lgr_last ELSE lgr_sum END, lgr _last = S.logrds, phr_last = S.phrds WHERE dataobj#=S.obj# WHEN NOT MATCHED THEN INSERT (dataobj#, inst_id, cached_avg, cached_sqr_avg, cached_no, cached_seq_no, chr_avg, chr_sqr_avg, chr_no, chr_seq_no, lgr_sum, lgr_last, phr_last) VALUES (S.obj#, 1, S.cachedblk, S.cachedblk*S.cachedblk, CASE WHEN (S.cachedblk>0) THEN 1 ELSE 0 END, 2315, 0, 0, 0, 2315, 0, S.logrds, S.phrds)
fztumj649upth	insert into wrh$_sgastat (snap_id, dbid, instance_number, pool, name, bytes) select :snap_id, :dbid, :instance_number, pool, name, bytes from v$sgastat order by name, pool
g00cj285jmgsw	update sys.mon_mods$ set inserts = inserts + :ins, updates = updates + :upd, deletes = deletes + :del, flags = (decode(bitand(flags, :flag), :flag, flags, flags + :flag)), drop_segments = drop_segments + :dropseg, timestamp = :time where obj# = :objn
g2wr3u7s1gtf3	select count(*) from sys.job$ where (next_date > sysdate) and (next_date < (sysdate+5/86400))
g6wf9na8zs5hb	insert into wrh$_sysmetric_summary (snap_id, dbid, instance_number, begin_time, end_time, intsize, group_id, metric_id, num_interval, maxval, minval, average, standard_deviation) select :snap_id, :dbid, :instance_number, begtime, endtime, intsize_csec, groupid, metricid, numintv, max, min, avg, std FROM x$kewmsmdv WHERE groupid = 2

Back to SQL Statistics
Back to Top

 

Instance Activity Statistics

• Instance Activity Stats
• Instance Activity Stats - Absolute Values
• Instance Activity Stats - Thread Activity

Back to Top

Instance Activity Stats

Statistic	Total	per Second	per Trans
CPU used by this session	264	0.07	0.84
CPU used when call started	264	0.07	0.84
CR blocks created	13	0.00	0.04
DB time	723,525	202.47	2,289.64
DBWR checkpoint buffers written	679	0.19	2.15
DBWR checkpoints	1	0.00	0.00
DBWR transaction table writes	28	0.01	0.09
DBWR undo block writes	127	0.04	0.40
IMU CR rollbacks	4	0.00	0.01
IMU Flushes	11	0.00	0.03
IMU Redo allocation size	177,396	49.64	561.38
IMU commits	301	0.08	0.95
IMU undo allocation size	750,820	210.11	2,376.01
SQL*Net roundtrips to/from client	0	0.00	0.00
active txn count during cleanout	24	0.01	0.08
background checkpoints started	1	0.00	0.00
background timeouts	10,043	2.81	31.78
buffer is not pinned count	9,835	2.75	31.12
buffer is pinned count	4,652	1.30	14.72
bytes received via SQL*Net from client	0	0.00	0.00
bytes sent via SQL*Net to client	0	0.00	0.00
calls to get snapshot scn: kcmgss	9,394	2.63	29.73
calls to kcmgas	505	0.14	1.60
calls to kcmgcs	37	0.01	0.12
change write time	26	0.01	0.08
cleanout - number of ktugct calls	28	0.01	0.09
cleanouts and rollbacks - consistent read gets	2	0.00	0.01
cleanouts only - consistent read gets	0	0.00	0.00
cluster key scan block gets	2,921	0.82	9.24
cluster key scans	48	0.01	0.15
commit cleanout failures: callback failure	1	0.00	0.00
commit cleanouts	1,146	0.32	3.63
commit cleanouts successfully completed	1,145	0.32	3.62
commit txn count during cleanout	13	0.00	0.04
concurrency wait time	0	0.00	0.00
consistent changes	15	0.00	0.05
consistent gets	29,115	8.15	92.14
consistent gets - examination	6,221	1.74	19.69
consistent gets direct	2	0.00	0.01
consistent gets from cache	29,113	8.15	92.13
cursor authentications	23	0.01	0.07
data blocks consistent reads - undo records applied	15	0.00	0.05
db block changes	12,625	3.53	39.95
db block gets	10,773	3.01	34.09
db block gets direct	2	0.00	0.01
db block gets from cache	10,771	3.01	34.09
deferred (CURRENT) block cleanout applications	783	0.22	2.48
enqueue conversions	706	0.20	2.23
enqueue releases	30,738	8.60	97.27
enqueue requests	30,738	8.60	97.27
enqueue waits	0	0.00	0.00
execute count	6,153	1.72	19.47
free buffer requested	224	0.06	0.71
heap block compress	25	0.01	0.08
immediate (CR) block cleanout applications	2	0.00	0.01
immediate (CURRENT) block cleanout applications	146	0.04	0.46
index fast full scans (full)	0	0.00	0.00
index fetch by key	2,752	0.77	8.71
index scans kdiixs1	5,179	1.45	16.39
leaf node 90-10 splits	5	0.00	0.02
leaf node splits	9	0.00	0.03
logons cumulative	87	0.02	0.28
messages received	657	0.18	2.08
messages sent	658	0.18	2.08
no buffer to keep pinned count	0	0.00	0.00
no work - consistent read gets	14,691	4.11	46.49
opened cursors cumulative	3,320	0.93	10.51
parse count (failures)	2	0.00	0.01
parse count (hard)	4	0.00	0.01
parse count (total)	3,325	0.93	10.52
parse time cpu	46	0.01	0.15
parse time elapsed	47	0.01	0.15
physical read IO requests	23	0.01	0.07
physical reads	56	0.02	0.18
physical reads cache	56	0.02	0.18
physical reads cache prefetch	33	0.01	0.10
physical reads direct	0	0.00	0.00
physical reads direct temporary tablespace	0	0.00	0.00
physical reads prefetch warmup	33	0.01	0.10
physical write IO requests	436	0.12	1.38
physical writes	681	0.19	2.16
physical writes direct	2	0.00	0.01
physical writes direct (lob)	2	0.00	0.01
physical writes direct temporary tablespace	0	0.00	0.00
physical writes from cache	679	0.19	2.15
physical writes non checkpoint	181	0.05	0.57
process last non-idle time	76,594,507,684	21,434,000.42	242,387,682.54
recursive calls	30,923	8.65	97.86
recursive cpu usage	410	0.11	1.30
redo blocks written	4,053	1.13	12.83
redo buffer allocation retries	3	0.00	0.01
redo entries	5,591	1.56	17.69
redo log space requests	2	0.00	0.01
redo log space wait time	18	0.01	0.06
redo size	2,087,244	584.09	6,605.20
redo synch time	101	0.03	0.32
redo synch writes	76	0.02	0.24
redo wastage	75,104	21.02	237.67
redo write time	307	0.09	0.97
redo writes	266	0.07	0.84
rollbacks only - consistent read gets	11	0.00	0.03
rows fetched via callback	1,535	0.43	4.86
session connect time	76,594,507,684	21,434,000.42	242,387,682.54
session logical reads	39,888	11.16	126.23
session pga memory	0	0.00	0.00
session uga memory	1,915,432	536.01	6,061.49
session uga memory max	34,128,592	9,550.45	108,001.87
shared hash latch upgrades - no wait	5,341	1.49	16.90
sorts (memory)	2,471	0.69	7.82
sorts (rows)	14,624	4.09	46.28
switch current to new buffer	19	0.01	0.06
table fetch by rowid	3,872	1.08	12.25
table fetch continued row	48	0.01	0.15
table scan blocks gotten	4,052	1.13	12.82
table scan rows gotten	128,795	36.04	407.58
table scans (long tables)	0	0.00	0.00
table scans (short tables)	1,382	0.39	4.37
undo change vector size	648,240	181.40	2,051.39
user I/O wait time	132	0.04	0.42
user calls	261	0.07	0.83
user commits	308	0.09	0.97
user rollbacks	8	0.00	0.03
workarea executions - onepass	0	0.00	0.00
workarea executions - optimal	1,213	0.34	3.84

Back to Instance Activity Statistics
Back to Top

Instance Activity Stats - Absolute Values

• Statistics with absolute values (should not be diffed)
• Statistics identified by '(derived)' come from sources other than SYSSTAT

Statistic	Begin Value	End Value
logons current	18	18
open threads (derived)	1	1
opened cursors current	50	50

Back to Instance Activity Statistics
Back to Top

Instance Activity Stats - Thread Activity

Statistic	Total	per Hour
log switches (derived)	1	1.01

Back to Instance Activity Statistics
Back to Top

 

IO Stats

• Tablespace IO Stats
• File IO Stats

Back to Top

Tablespace IO Stats

• ordered by IOs (Reads + Writes) desc

Tablespace	Reads	Av Reads/s	Av Rd(ms)	Av Blks/Rd	Writes	Av Writes/s	Buffer Waits	Av Buf Wt(ms)
SYSAUX	38	0	17.37	2.84	295	0	0	0.00
SYSTEM	0	0	0.00	0.00	78	0	0	0.00
UNDOTBS1	0	0	0.00	0.00	63	0	0	0.00

Back to IO Stats
Back to Top

File IO Stats

• ordered by Tablespace, File

Tablespace	Filename	Reads	Av Reads/s	Av Rd(ms)	Av Blks/Rd	Writes	Av Writes/s	Buffer Waits	Av Buf Wt(ms)
SYSAUX	/oracle/oradata/ORCL10/datafile/o1_mf_sysaux_02p09kn	38	0	17.37	2.84	295	0	0	0.00
SYSTEM	/oracle/oradata/ORCL10/datafile/o1_mf_system_02p09kn	0	0			78	0	0	0.00
UNDOTBS1	/oracle/oradata/ORCL10/datafile/o1_mf_undotbs1_02p09	0	0			63	0	0	0.00

Back to IO Stats
Back to Top

 

Buffer Pool Statistics

• Standard block size Pools D: default, K: keep, R: recycle
• Default Pools for other block sizes: 2k, 4k, 8k, 16k, 32k

P	Number of Buffers	Pool Hit%	Buffer Gets	Physical Reads	Physical Writes	Free Buff Wait	Writ Comp Wait	Buffer Busy Waits
D	13,527	100	39,891	88	679	0	0	1

Back to Top

 

Advisory Statistics

• Instance Recovery Stats
• Buffer Pool Advisory
• PGA Aggr Summary
• PGA Aggr Target Stats
• PGA Aggr Target Histogram
• PGA Memory Advisory
• Shared Pool Advisory
• Java Pool Advisory

Back to Top

Instance Recovery Stats

• B: Begin snapshot, E: End snapshot

	Targt MTTR (s)	Estd MTTR (s)	Recovery Estd IOs	Actual Redo Blks	Target Redo Blks	Log File Size Redo Blks	Log Ckpt Timeout Redo Blks	Log Ckpt Interval Redo Blks
B	0	27	197	1,596	1,466	18,432	1,466
E	0	27	191	1,394	1,257	18,432	1,257

Back to Advisory Statistics
Back to Top

Buffer Pool Advisory

• Only rows with estimated physical reads >0 are displayed
• ordered by Block Size, Buffers For Estimate

P	Size for Estimate (M)	Size Factr	Buffers for Estimate	Est Physical Read Factor	Estimated Physical Reads
D	8	0.07	1,002	3.31	15,625
D	16	0.15	2,004	2.32	10,971
D	24	0.22	3,006	1.32	6,233
D	32	0.30	4,008	1.21	5,695
D	40	0.37	5,010	1.20	5,645
D	48	0.44	6,012	1.00	4,721
D	56	0.52	7,014	1.00	4,721
D	64	0.59	8,016	1.00	4,721
D	72	0.67	9,018	1.00	4,721
D	80	0.74	10,020	1.00	4,721
D	88	0.81	11,022	1.00	4,721
D	96	0.89	12,024	1.00	4,721
D	104	0.96	13,026	1.00	4,721
D	108	1.00	13,527	1.00	4,721
D	112	1.04	14,028	1.00	4,721
D	120	1.11	15,030	1.00	4,721
D	128	1.19	16,032	1.00	4,721
D	136	1.26	17,034	1.00	4,721
D	144	1.33	18,036	1.00	4,721
D	152	1.41	19,038	1.00	4,721
D	160	1.48	20,040	1.00	4,721

Back to Advisory Statistics
Back to Top

PGA Aggr Summary

• PGA cache hit % - percentage of W/A (WorkArea) data processed only in-memory

PGA Cache Hit %	W/A MB Processed	Extra W/A MB Read/Written
100.00	9	0

Back to Advisory Statistics
Back to Top

PGA Aggr Target Stats

• B: Begin snap E: End snap (rows dentified with B or E contain data which is absolute i.e. not diffed over the interval)
• Auto PGA Target - actual workarea memory target
• W/A PGA Used - amount of memory used for all Workareas (manual + auto)
• %PGA W/A Mem - percentage of PGA memory allocated to workareas
• %Auto W/A Mem - percentage of workarea memory controlled by Auto Mem Mgmt
• %Man W/A Mem - percentage of workarea memory under manual control

	PGA Aggr Target(M)	Auto PGA Target(M)	PGA Mem Alloc(M)	W/A PGA Used(M)	%PGA W/A Mem	%Auto W/A Mem	%Man W/A Mem	Global Mem Bound(K)
B	24	4	82.40	0.00	0.00	0.00	0.00	1,228
E	24	4	81.15	0.00	0.00	0.00	0.00	1,228

Back to Advisory Statistics
Back to Top

PGA Aggr Target Histogram

• Optimal Executions are purely in-memory operations

Low Optimal	High Optimal	Total Execs	Optimal Execs	1-Pass Execs	M-Pass Execs
2K	4K	1,204	1,204	0	0
512K	1024K	9	9	0	0

Back to Advisory Statistics
Back to Top

PGA Memory Advisory

• When using Auto Memory Mgmt, minimally choose a pga_aggregate_target value where Estd PGA Overalloc Count is 0

PGA Target Est (MB)	Size Factr	W/A MB Processed	Estd Extra W/A MB Read/ Written to Disk	Estd PGA Cache Hit %	Estd PGA Overalloc Count
12	0.50	55.71	16.05	78.00	1
18	0.75	55.71	16.05	78.00	1
24	1.00	55.71	3.15	95.00	1
29	1.20	55.71	3.15	95.00	1
34	1.40	55.71	3.15	95.00	1
38	1.60	55.71	3.15	95.00	1
43	1.80	55.71	3.15	95.00	0
48	2.00	55.71	3.15	95.00	0
72	3.00	55.71	0.00	100.00	0
96	4.00	55.71	0.00	100.00	0
144	6.00	55.71	0.00	100.00	0
192	8.00	55.71	0.00	100.00	0

Back to Advisory Statistics
Back to Top

Shared Pool Advisory

• SP: Shared Pool Est LC: Estimated Library Cache Factr: Factor
• Note there is often a 1:Many correlation between a single logical object in the Library Cache, and the physical number of memory objects associated with it. Therefore comparing the number of Lib Cache objects (e.g. in v$librarycache), with the number of Lib Cache Memory Objects is invalid.

Shared Pool Size(M)	SP Size Factr	Est LC Size (M)	Est LC Mem Obj	Est LC Time Saved (s)	Est LC Time Saved Factr	Est LC Load Time (s)	Est LC Load Time Factr	Est LC Mem Obj Hits
36	0.53	8	1,508	8	1.00	8	1.00	10,716
44	0.65	11	1,809	8	1.00	8	1.00	10,719
52	0.76	11	1,809	8	1.00	8	1.00	10,719
60	0.88	11	1,809	8	1.00	8	1.00	10,719
68	1.00	11	1,809	8	1.00	8	1.00	10,719
76	1.12	11	1,809	8	1.00	8	1.00	10,719
84	1.24	11	1,809	8	1.00	8	1.00	10,719
92	1.35	11	1,809	8	1.00	8	1.00	10,719
100	1.47	11	1,809	8	1.00	8	1.00	10,719
108	1.59	11	1,809	8	1.00	8	1.00	10,719
116	1.71	11	1,809	8	1.00	8	1.00	10,719
124	1.82	11	1,809	8	1.00	8	1.00	10,719
132	1.94	11	1,809	8	1.00	8	1.00	10,719
140	2.06	11	1,809	8	1.00	8	1.00	10,719

Back to Advisory Statistics
Back to Top

Java Pool Advisory

Java Pool Size(M)	JP Size Factr	Est LC Size (M)	Est LC Mem Obj	Est LC Time Saved (s)	Est LC Time Saved Factr	Est LC Load Time (s)	Est LC Load Time Factr	Est LC Mem Obj Hits
4	0.25	4	124	0	0.00	5	1.00	0
8	0.50	4	124	0	0.00	5	1.00	0
12	0.75	4	124	0	0.00	5	1.00	0
16	1.00	4	124	0	0.00	5	1.00	0
20	1.25	4	124	0	0.00	5	1.00	0
24	1.50	4	124	0	0.00	5	1.00	0
28	1.75	4	124	0	0.00	5	1.00	0
32	2.00	4	124	0	0.00	5	1.00	0

Back to Advisory Statistics
Back to Top

 

Wait Statistics

• Buffer Wait Statistics
• Enqueue Activity

Back to Top

Buffer Wait Statistics

• ordered by wait time desc, waits desc

Class	Waits	Total Wait Time (s)	Avg Time (ms)
undo header	1	0	0

Back to Wait Statistics
Back to Top

 

Enqueue Activity

No data exists for this section of the report.

Back to Wait Statistics
Back to Top

 

Undo Statistics

• Undo Segment Summary
• Undo Segment Stats

Back to Top

Undo Segment Summary

• Undo segment block stats:
• uS - unexpired Stolen, uR - unexpired Released, uU - unexpired reUsed
• eS - expired Stolen, eR - expired Released, eU - expired reUsed

Undo TS#	Undo Blocks	Num Trans	Max Qry Len (s)	Max Tx Concurcy	Snap TooOld	OutOf Space	uS/uR/uU/ eS/eR/eU
1	95	430	0	3	0	0	0/0/0/0/0/0

Back to Undo Statistics
Back to Top

Undo Segment Stats

• ordered by Time desc

End Time	Undo Blocks	Num Trans	Max Qry Len (s)	Max Tx Concy	Snap TooOld	OutOf Space	uS/uR/uU/ eS/eR/eU
08-Mar 17:49	5	55	0	1	0	0	0/0/0/0/0/0
08-Mar 17:39	4	61	0	1	0	0	0/0/0/0/0/0
08-Mar 17:29	4	69	0	1	0	0	0/0/0/0/0/0
08-Mar 17:19	7	56	0	1	0	0	0/0/0/0/0/0
08-Mar 17:09	75	189	0	3	0	0	0/0/0/0/0/0

Back to Undo Statistics
Back to Top

 

Latch Statistics

• Latch Activity
• Latch Sleep Breakdown
• Latch Miss Sources
• Parent Latch Statistics
• Child Latch Statistics

Back to Top

Latch Activity

• "Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for willing-to-wait latch get requests
• "NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests
• "Pct Misses" for both should be very close to 0.0

Latch Name	Get Requests	Pct Get Miss	Avg Slps /Miss	Wait Time (s)	NoWait Requests	Pct NoWait Miss
Consistent RBA	271	0.00		0	0
FAL request queue	58	0.00		0	0
FIB s.o chain latch	10	0.00		0	0
FOB s.o list latch	32	0.00		0	0
In memory undo latch	2,858	0.00		0	780	0.00
JOX SGA heap latch	24	0.00		0	0
JS queue state obj latch	25,164	0.00		0	0
JS slv state obj latch	6	0.00		0	0
KTF sga enqueue	12	0.00		0	1,100	0.00
KWQMN job cache list latch	392	0.00		0	0
MQL Tracking Latch	0			0	70	0.00
Memory Management Latch	17,400	0.00		0	1,160	0.00
PL/SQL warning settings	87	0.00		0	0
Reserved Space Latch	2	0.00		0	0
SQL memory manager latch	1	0.00		0	1,161	0.00
SQL memory manager workarea list latch	77,904	0.00		0	0
SWRF Alerted Metric Element list	11,609	0.00		0	0
active checkpoint queue latch	1,596	0.00		0	0
active service list	5,942	0.00		0	0
alert log latch	5	0.00		0	0
archive control	69	0.00		0	0
archive process latch	1,233	0.00		0	0
begin backup scn array	2	0.00		0	0
cache buffer handles	35	0.00		0	0
cache buffers chains	104,513	0.00		0	381	0.00
cache buffers lru chain	1,718	0.00		0	55	0.00
channel handle pool latch	38	0.00		0	0
channel operations parent latch	11,768	0.00		0	0
checkpoint queue latch	25,471	0.00		0	481	0.00
child cursor hash table	26	0.00		0	0
client/application info	234	0.00		0	0
commit callback allocation	18	0.00		0	0
compile environment latch	387	0.00		0	0
cursor bind value capture	36	0.00		0	223	0.00
dml lock allocation	3,233	0.00		0	0
dummy allocation	174	0.00		0	0
enqueue hash chains	62,266	0.00		0	0
enqueues	57,771	0.00		0	0
event group latch	16	0.00		0	0
file cache latch	86	0.00		0	0
hash table column usage latch	20	0.00		0	32	0.00
hash table modification latch	60	0.00		0	0
job workq parent latch	0			0	142	0.00
job_queue_processes parameter latch	260	0.00		0	0
ksuosstats global area	241	0.00		0	0
ktm global data	15	0.00		0	0
lgwr LWN SCN	1,281	0.00		0	0
library cache	52,801	0.00		0	0
library cache lock	18,535	0.00		0	0
library cache lock allocation	1,423	0.00		0	0
library cache pin	30,196	0.00		0	0
library cache pin allocation	1,411	0.00		0	0
list of block allocation	161	0.00		0	0
loader state object freelist	12	0.00		0	0
messages	21,699	0.00		0	0
mostly latch-free SCN	1,281	0.00		0	0
multiblock read objects	30	0.00		0	0
ncodef allocation latch	59	0.00		0	0
object queue header operation	19,939	0.00		0	0
object stats modification	119	0.00		0	0
parallel query alloc buffer	464	0.00		0	0
parameter table allocation management	174	0.00		0	0
post/wait queue	160	0.00		0	77	0.00
process allocation	16	0.00		0	16	0.00
process group creation	32	0.00		0	0
qmn state object latch	24	0.00		0	0
qmn task queue latch	36	0.00		0	0
redo allocation	3,200	0.03	1.00	0	5,671	0.00
redo copy	0			0	5,671	0.00
redo writing	4,685	0.00		0	0
reservation so alloc latch	1	0.00		0	0
row cache objects	16,760	0.00		0	0
sequence cache	30	0.00		0	0
session allocation	11,646	0.00		0	0
session idle bit	822	0.00		0	0
session switching	59	0.00		0	0
session timer	1,247	0.00		0	0
shared pool	23,423	0.00		0	0
simulator hash latch	1,924	0.00		0	0
simulator lru latch	17	0.00		0	14	0.00
slave class	14	0.00		0	0
slave class create	56	1.79	1.00	0	0
sort extent pool	90	0.00		0	0
state object free list	2	0.00		0	0
statistics aggregation	140	0.00		0	0
threshold alerts latch	147	0.00		0	0
transaction allocation	159	0.00		0	0
transaction branch allocation	59	0.00		0	0
undo global data	2,104	0.00		0	0
user lock	142	0.00		0	0

Back to Latch Statistics
Back to Top

Latch Sleep Breakdown

• ordered by misses desc

Latch Name	Get Requests	Misses	Sleeps	Spin Gets	Sleep1	Sleep2	Sleep3
slave class create	56	1	1	0	1	0	0
redo allocation	3,200	1	1	0	1	0	0

Back to Latch Statistics
Back to Top

Latch Miss Sources

• only latches with sleeps are shown
• ordered by name, sleeps desc

Latch Name	Where	NoWait Misses	Sleeps	Waiter Sleeps
redo allocation	kcrfw_redo_gen: redo allocation 1	0	1	0
slave class create	ksvcreate	0	1	0

Back to Latch Statistics
Back to Top

 

Parent Latch Statistics

No data exists for this section of the report.

Back to Latch Statistics
Back to Top

 

Child Latch Statistics

No data exists for this section of the report.

Back to Latch Statistics
Back to Top

 

Segment Statistics

• Segments by Logical Reads
• Segments by Physical Reads
• Segments by Buffer Busy Waits
• Segments by Row Lock Waits
• Segments by ITL Waits

Back to Top

Segments by Logical Reads

• % Total shows % of logical reads for each top segment compared with total logical reads for all segments captured by the Snapshot

Owner	Tablespace Name	Object Name	Subobject Name	Obj. Type	Logical Reads	%Total
SYS	SYSAUX	SYS_IOT_TOP_8540		INDEX	5,648	15.36
SYS	SYSTEM	I_SYSAUTH1		INDEX	4,240	11.53
SYS	SYSTEM	SMON_SCN_TIME		TABLE	2,928	7.96
SYS	SYSTEM	JOB$		TABLE	2,240	6.09
SYS	SYSTEM	I_JOB_NEXT		INDEX	1,040	2.83

Back to Segment Statistics
Back to Top

Segments by Physical Reads

Owner	Tablespace Name	Object Name	Subobject Name	Obj. Type	Physical Reads	%Total
SYS	SYSAUX	WRH$_PGASTAT_PK		INDEX	11	16.67
SYS	SYSAUX	WRH$_SQLTEXT		TABLE	8	12.12
SYSMAN	SYSAUX	MGMT_SYSTEM_PERFORMANCE_LOG		TABLE	8	12.12
SYS	SYSAUX	WRH$_BG_EVENT_SUMMARY		TABLE	7	10.61
SYSMAN	SYSAUX	SYS_IOT_OVER_47853		TABLE	7	10.61

Back to Segment Statistics
Back to Top

 

Segments by Buffer Busy Waits

No data exists for this section of the report.

Back to Segment Statistics
Back to Top

 

Segments by Row Lock Waits

No data exists for this section of the report.

Back to Segment Statistics
Back to Top

 

Segments by ITL Waits

No data exists for this section of the report.

Back to Segment Statistics
Back to Top

 

Dictionary Cache Stats

• "Pct Misses" should be very low (< 2% in most cases)
• "Final Usage" is the number of cache entries being used

Cache	Get Requests	Pct Miss	Scan Reqs	Pct Miss	Mod Reqs	Final Usage
dc_awr_control	63	0.00	0		2	1
dc_global_oids	1,026	0.00	0		0	29
dc_histogram_data	4	0.00	0		0	504
dc_histogram_defs	110	0.00	0		0	2,324
dc_object_ids	1,209	0.58	0		0	647
dc_objects	310	1.94	0		0	1,393
dc_profiles	71	0.00	0		0	1
dc_rollback_segments	825	0.00	0		0	35
dc_segments	189	0.00	0		3	604
dc_tablespaces	206	0.00	0		0	6
dc_usernames	152	0.00	0		0	12
dc_users	1,488	0.00	0		0	24
outstanding_alerts	47	4.26	0		5	11

Back to Top

 

Library Cache Activity

• "Pct Misses" should be very low

Namespace	Get Requests	Pct Miss	Pin Requests	Pct Miss	Reloads	Invali- dations
BODY	653	0.00	818	0.00	0	0
JAVA DATA	1	0.00	0		0	0
SQL AREA	2,176	0.09	11,304	0.05	2	0
TABLE/PROCEDURE	147	0.00	1,697	0.00	0	0
TRIGGER	13	0.00	193	0.00	0	0

Back to Top

 

SGA Statistics

• SGA Memory Summary
• SGA breakdown difference

Back to Top

SGA Memory Summary

SGA regions	Size in Bytes
Database Buffers	113,246,208
Fixed Size	778,136
Redo Buffers	262,144
Variable Size	95,428,712

Back to SGA Statistics
Back to Top

SGA breakdown difference

Pool	Name	Begin value	End value	% Diff
java	free memory	11,003,392	11,003,392	0.00
java	joxlod exec hp	5,439,168	5,439,168	0.00
java	joxs heap	334,656	334,656	0.00
large	PX msg pool	206,208	206,208	0.00
large	free memory	3,988,096	3,988,096	0.00
shared	ASH buffers	2,097,152	2,097,152	0.00
shared	FileOpenBlock	1,213,284	1,213,284	0.00
shared	KGLS heap	2,529,504	2,529,504	0.00
shared	KQR L SO	71,680	71,680	0.00
shared	KQR M PO	2,242,652	2,249,308	0.30
shared	KQR M SO	222,208	222,208	0.00
shared	KQR S PO	177,724	177,724	0.00
shared	KQR S SO	2,304	2,304	0.00
shared	KTI-UNDO	2,058,840	2,058,840	0.00
shared	PL/SQL DIANA	1,325,060	1,325,060	0.00
shared	PL/SQL MPCODE	2,132,300	2,132,300	0.00
shared	PLS non-lib hp	10,964	10,964	0.00
shared	PX subheap	65,548	65,548	0.00
shared	alert threshol	740	740	0.00
shared	dbwriter coalesce buffer	1,049,088	1,049,088	0.00
shared	event statistics per sess	6,334,720	6,334,720	0.00
shared	fixed allocation callback	256	256	0.00
shared	free memory	9,961,516	9,573,080	-3.90
shared	joxlod exec hp	316,576	316,576	0.00
shared	joxlod pcod hp	64,808	64,808	0.00
shared	joxs heap	4,220	4,220	0.00
shared	library cache	7,530,384	7,545,124	0.20
shared	miscellaneous	17,046,696	17,071,820	0.15
shared	parameters	12,092	12,092	0.00
shared	partitioning d	178,764	178,764	0.00
shared	pl/sql source	17,956	17,956	0.00
shared	private strands	1,996,800	1,996,800	0.00
shared	repository	79,532	79,532	0.00
shared	row cache	3,707,272	3,707,272	0.00
shared	sessions	1,284,644	1,284,644	0.00
shared	sql area	7,211,648	7,553,564	4.74
shared	table definiti	2,392	2,392	0.00
shared	trigger defini	4,080	4,080	0.00
shared	trigger inform	1,956	1,956	0.00
shared	trigger source	640	640	0.00
shared	type object de	347,168	347,168	0.00
	buffer_cache	113,246,208	113,246,208	0.00
	fixed_sga	778,136	778,136	0.00
	log_buffer	262,144	262,144	0.00

Back to SGA Statistics
Back to Top

 

 

Resource Limit Stats

No data exists for this section of the report.

Back to Top

 

init.ora Parameters

Parameter Name	Begin value	End value (if different)
__db_cache_size	113246208
__java_pool_size	16777216
__large_pool_size	4194304
__shared_pool_size	71303168
background_dump_dest	/oracle/admin/orcl10/bdump
compatible	10.1.0.2.0
control_files	/oracle/oradata/ORCL10/controlfil
core_dump_dest	/oracle/admin/orcl10/cdump
db_block_size	8192
db_create_file_dest	/oracle/oradata
db_domain	as.rhel.com
db_file_multiblock_read_count	16
db_name	orcl10
db_recovery_file_dest	/oracle/flash_recovery_area
db_recovery_file_dest_size	2147483648
dispatchers	(PROTOCOL=TCP) (SERVICE=orcl10XDB
job_queue_processes	10
local_listener
log_archive_format	%t_%s_%r.dbf
open_cursors	300
pga_aggregate_target	25165824
processes	250
remote_login_passwordfile	EXCLUSIVE
sga_target	209715200
sort_area_size	65536
undo_management	AUTO
undo_tablespace	UNDOTBS1
user_dump_dest	/oracle/admin/orcl10/udump

Back to Top

 

 

 

 

 

End of Report