oracle wri$_adv_objects 用途,Oracle Database Healthy Report For Hongkong-2

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)

%Timeouts: value of 0 indicates value was <

.5%. Value of null is truly 0

Event

Waits

%Time -outs

Total Wait Time (s)

Avg wait (ms)

Waits /txn

control file parallel write

185,090

4,545

25

0.70

log file parallel write

131,759

2,452

19

0.50

log file sync

11,578

1

312

27

0.04

rdbms ipc reply

2,804

78

28

0.01

control file sequential read

13,119,070

75

0

49.70

direct path sync

7,346

67

9

0.03

os thread startup

3,254

58

18

0.01

SQL*Net more data from client

443,465

41

0

1.68

Streams AQ: qmn coordinator waiting for slave to start

8

100

39

4884

0.00

enq: RO - fast object reuse

2,284

37

16

0.01

enq: KO - fast object checkpoint

835

17

20

0.00

latch free

1,237

12

10

0.00

SQL*Net break/reset to client

78,572

12

0

0.30

db file single write

924

10

10

0.00

log file switch completion

64

7

113

0.00

library cache lock

16

13

6

367

0.00

RMAN backup & recovery I/O

9,681

6

1

0.04

enq: UL - contention

14

4

307

0.00

Datapump dump file I/O

21,593

3

0

0.08

utl_file I/O

192,555

3

0

0.73

SQL*Net message to client

3,594,107

2

0

13.61

SQL*Net more data to client

179,520

2

0

0.68

Log archive I/O

1,107

2

2

0.00

reliable message

3,131

2

0

0.01

log buffer space

6

1

245

0.00

undo segment extension

18

78

1

77

0.00

log file single write

112

1

8

0.00

db file sequential read

106,815

1

0

0.40

switch logfile command

6

1

117

0.00

kupp process wait

7

100

1

98

0.00

log file sequential read

1,337

0

0

0.01

enq: CF - contention

8

0

31

0.00

cursor: pin S wait on X

20

95

0

10

0.00

recovery area: computing obsolete files

3

0

39

0.00

enq: TC - contention

1

0

79

0.00

db file scattered read

262

0

0

0.00

direct path read

44,371

0

0

0.17

latch: shared pool

25

0

2

0.00

latch: library cache

38

0

1

0.00

direct path write

7,289

0

0

0.03

control file single write

55

0

0

0.00

LGWR wait for redo copy

771

0

0

0.00

enq: TQ - DDL contention

2

0

5

0.00

latch: session allocation

237

0

0

0.00

direct path write temp

488

0

0

0.00

latch: redo writing

16

0

0

0.00

buffer busy waits

52

0

0

0.00

latch: cache buffers chains

57

0

0

0.00

direct path read temp

314

0

0

0.00

latch: row cache objects

5

0

0

0.00

enq: TX - contention

1

0

0

0.00

recovery area: computing dropped files

3

0

0

0.00

recovery area: computing backed up files

3

0

0

0.00

latch: library cache lock

3

0

0

0.00

latch: object queue header operation

3

0

0

0.00

cursor: pin S

3

0

0

0.00

latch: messages

3

0

0

0.00

latch: object queue header heap

2

0

0

0.00

enq: TQ - TM contention

1

0

0

0.00

SQL*Net message from client

3,767,826

25,499,204

6768

14.27

Streams AQ: qmn slave idle wait

59,923

0

1,079,521

18015

0.23

jobq slave wait

185,790

96

543,077

2923

0.70

virtual circuit status

18,481

100

539,599

29197

0.07

Streams AQ: qmn coordinator idle wait

55,630

36

539,301

9694

0.21

Streams AQ: waiting for time management or cleanup tasks

143

94

260,762

1823512

0.00

wait for unread message on broadcast channel

10,363

9

1,267

122

0.04

PL/SQL lock timer

77

99

12

154

0.00

class slave wait

1,265

0

0

0.00

针对control file parallel

write等待事件,在很多情况下我们会发现由两个进程,LGWR和CKPT引起,日志切换和检查点是导致control

file parallel

write等待事件最主要的原因,相应的解决方法了就是增加日志组和日志大小,对controlfile分开存储到磁盘等。

在一个繁忙的生产系统中我发现引起control file paralle

write等待事件的都是后台进程ckpt和lgwr,出现此等待事件很正常,但是如果影响了db性能就需要酌情调整。

针对log file parallel

write等待事件,该事件的等待表示重做日志所处的磁盘设备缓慢或存在争用。

SELECT s.event, s.time_waited, s.average_wait FROM

v$system_event s WHERE s.event IN('log file parallel write','log

file sync');注:'log file parallel

write'事件的平均等待时间大于10ms(1cs),这通常表示存在缓慢的IO吞吐量。

SQL Statistics

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

Total DB Time (s): 2,889

Captured SQL account for 112.1% of Total

Elapsed Time (s)

CPU Time (s)

Executions

Elap per Exec (s)

% Total DB Time

SQL Id

SQL Module

SQL Text

642

575

7

91.73

22.23

BEGIN SYS.KUPW$WORKER.MAIN('S...

257

138

38

6.76

8.89

OGG-EXTLC-GLOPEN_DATA_SOURCE

declare extract_name varchar2(...

252

137

76

3.32

8.72

OGG-EXTLC-GLOPEN_DATA_SOURCE

DECLARE opstring_in VARCHAR2(3...

211

212

22,431

0.01

7.29

Ips.GateWay.exe

Begin PKG_UTIL.SP_TASK_GETTASK...

136

139

1,921

0.07

4.69

Ips.GateWay.exe

with orderby as (select rowid ...

107

104

552,487

0.00

3.70

select privilege#, level from ...

77

16

258,250

0.00

2.68

OGG-EXTLC-OCI_META_THREAD

SELECT 1 FROM V$LOGFILE WHERE(...

76

78

147

0.52

2.63

insert into wrh$_sga_target_ad...

70

71

1,017

0.07

2.43

OGG-EXTLC-GLOPEN_DATA_SOURCE

DECLARE sql_text ora_name_list...

69

66

183,678

0.00

2.39

select privilege# from sysauth...

69

15

258,251

0.00

2.38

OGG-EXTLC-OCI_META_THREAD

SELECT MAX(sequence#) FROM v$l...

69

65

5

13.77

2.38

DBMS_SCHEDULER

call dbms_stats.gather_databas...

62

64

7

8.91

2.16

Data Pump Worker

SELECT SYS_XMLGEN(VA...

56

57

8,817

0.01

1.95

DECLARE job BINARY_INTEGER := ...

51

52

2,810

0.02

1.75

OGG-EXTLC-GLOPEN_DATA_SOURCE

SELECT T.BIGFILE FROM DBA_TABL...

50

0

258,251

0.00

1.72

OGG-EXTLC-OCI_META_THREAD

SELECT DECODE(archived, 'YES',...

46

44

173,759

0.00

1.58

select SYS_CONTEXT('USERENV', ...

45

38

63,756

0.00

1.56

Ips.GateWay.exe

Begin sp_exceptionhandlingtime...

37

38

126

0.29

1.27

Data Pump Worker

BEGIN "SYS"."DBMS_SCHED_EXPORT...

36

37

152,271

0.00

1.26

Ips.GateWay.exe

begin dbms_output.get_lines(:o...

34

35

7

4.87

1.18

Data Pump Worker

SELECT SYS_XMLGEN(VA...

33

39

258,251

0.00

1.14

OGG-EXTLC-OCI_META_THREAD

SELECT DECODE(status, 'STALE',...

32

31

63,756

0.00

1.10

Ips.GateWay.exe

Begin pkg_util.sp_exceptionhan...

31

32

319

0.10

1.09

OGG-EXTLC-GLOPEN_DATA_SOURCE

SELECT TS.BIGFILE FROM DBA_TAB...

29

28

173,759

0.00

1.02

select value$ from props$ wher...

SQL ordered by CPU Time

Resources reported for PL/SQL code includes the resources

used by all SQL statements called by the code.

% Total is the CPU Time divided into the Total CPU Time

times 100

Total CPU Time (s): 5,736

Captured SQL account for 48.0% of Total

CPU Time (s)

Elapsed Time (s)

Executions

CPU per Exec (s)

% Total

% Total DB Time

SQL Id

SQL Module

SQL Text

575

642

7

82.09

10.02

22.23

BEGIN SYS.KUPW$WORKER.MAIN('S...

212

211

22,431

0.01

3.69

7.29

Ips.GateWay.exe

Begin PKG_UTIL.SP_TASK_GETTASK...

139

136

1,921

0.07

2.42

4.69

Ips.GateWay.exe

with orderby as (select rowid ...

138

257

38

3.63

2.40

8.89

OGG-EXTLC-GLOPEN_DATA_SOURCE

declare extract_name varchar2(...

137

252

76

1.80

2.38

8.72

OGG-EXTLC-GLOPEN_DATA_SOURCE

DECLARE opstring_in VARCHAR2(3...

104

107

552,487

0.00

1.82

3.70

select privilege#, level from ...

78

76

147

0.53

1.36

2.63

insert into wrh$_sga_target_ad...

71

70

1,017

0.07

1.24

2.43

OGG-EXTLC-GLOPEN_DATA_SOURCE

DECLARE sql_text ora_name_list...

66

69

183,678

0.00

1.14

2.39

select privilege# from sysauth...

65

69

5

12.98

1.13

2.38

DBMS_SCHEDULER

call dbms_stats.gather_databas...

64

62

7

9.13

1.11

2.16

Data Pump Worker

SELECT SYS_XMLGEN(VA...

57

56

8,817

0.01

1.00

1.95

DECLARE job BINARY_INTEGER := ...

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: 112,462,772

Captured SQL account for 82.4% of Total

Buffer Gets

Executions

Gets per Exec

%Total

CPU Time (s)

Elapsed Time (s)

SQL Id

SQL Module

SQL Text

54,135,650

7

7,733,664.29

48.14

574.66

642.12

BEGIN SYS.KUPW$WORKER.MAIN('S...

26,741,315

22,431

1,192.16

23.78

211.69

210.66

Ips.GateWay.exe

Begin PKG_UTIL.SP_TASK_GETTASK...

24,750,966

1,921

12,884.42

22.01

138.88

135.61

Ips.GateWay.exe

with orderby as (select rowid ...

22,834,440

1,017

22,452.74

20.30

71.32

70.06

OGG-EXTLC-GLOPEN_DATA_SOURCE

DECLARE sql_text ora_name_list...

21,264,254

126

168,763.92

18.91

37.52

36.66

Data Pump Worker

BEGIN "SYS"."DBMS_SCHED_EXPORT...

21,187,592

319

66,418.78

18.84

32.20

31.45

OGG-EXTLC-GLOPEN_DATA_SOURCE

SELECT TS.BIGFILE FROM DBA_TAB...

7,810,646

38

205,543.32

6.95

137.77

256.97

OGG-EXTLC-GLOPEN_DATA_SOURCE

declare extract_name varchar2(...

7,785,809

76

102,444.86

6.92

136.58

251.99

OGG-EXTLC-GLOPEN_DATA_SOURCE

DECLARE opstring_in VARCHAR2(3...

6,071,579

552,487

10.99

5.40

104.39

106.84

select privilege#, level from ...

5,386,317

2,810

1,916.84

4.79

51.76

50.55

OGG-EXTLC-GLOPEN_DATA_SOURCE

SELECT T.BIGFILE FROM DBA_TABL...

5,115,803

7

730,829.00

4.55

63.91

62.40

Data Pump Worker

SELECT SYS_XMLGEN(VA...

3,114,570

5

622,914.00

2.77

64.89

68.84

DBMS_SCHEDULER

call dbms_stats.gather_databas...

2,465,535

7

352,219.29

2.19

21.35

20.87

Data Pump Worker

SELECT SYS_XMLGE...

2,089,493

681,534

3.07

1.86

11.54

11.38

Data Pump Worker

SELECT C.DEFAULT$, C.PROPERTY ...

1,545,645

7

220,806.43

1.37

20.32

19.86

Data Pump Worker

SELECT SYS_XMLGE...

1,287,202

183,678

7.01

1.14

65.58

69.15

select privilege# from sysauth...

1,279,736

7

182,819.43

1.14

34.93

34.07

Data Pump Worker

SELECT SYS_XMLGEN(VA...

1,209,676

8,817

137.20

1.08

57.38

56.41

DECLARE job BINARY_INTEGER := ...

SQL ordered by Reads

Total Disk Reads: 477,142

Captured SQL account for 23.3% of Total

Physical Reads

Executions

Reads per Exec

%Total

CPU Time (s)

Elapsed Time (s)

SQL Id

SQL Module

SQL Text

434,476

7

62,068.00

91.06

574.66

642.12

BEGIN SYS.KUPW$WORKER.MAIN('S...

36,381

5

7,276.20

7.62

5.84

5.95

DBMS_SCHEDULER

call dbms_space.auto_space_adv...

36,375

62

586.69

7.62

1.64

1.80

DBMS_SCHEDULER

insert into wri$_adv_objspace_...

35,692

7

5,098.86

7.48

21.35

20.87

Data Pump Worker

SELECT SYS_XMLGE...

2,243

38

59.03

0.47

137.77

256.97

OGG-EXTLC-GLOPEN_DATA_SOURCE

declare extract_name varchar2(...

2,243

76

29.51

0.47

136.58

251.99

OGG-EXTLC-GLOPEN_DATA_SOURCE

DECLARE opstring_in VARCHAR2(3...

2,223

5

444.60

0.47

64.89

68.84

DBMS_SCHEDULER

call dbms_stats.gather_databas...

168

1

168.00

0.04

2.29

2.97

BEGIN SYS.KUPM$MCP.MAIN('SYS_...

168

1

168.00

0.04

2.27

3.01

BEGIN SYS.KUPM$MCP.MAIN('SYS_...

168

1

168.00

0.04

2.53

3.13

BEGIN SYS.KUPM$MCP.MAIN('SYS_...

SQL ordered by Reads: 记录了执行占总磁盘物理读(物理IO)的TOP

SQL(请注意是监控范围内该SQL的执行占磁盘物理读总和,而不是单次SQL执行所占的磁盘物理读)。

磁盘物理读对SQL性能的影响非常大,

所以当对SQL进行调优时,第一步就是找磁盘物理读比较高的SQL语句。原因很简单,因为通常磁盘物理读通常比BUFFER读慢10000倍。

此数据库中,此部分占前几位的都是数据库内部自带的SQL语句,这也说明当前系统loading比较低。

SQL ordered by Executions

Total Executions: 6,058,865

Captured SQL account for 73.6% of Total

Executions

Rows Processed

Rows per Exec

CPU per Exec (s)

Elap per Exec (s)

SQL Id

SQL Module

SQL Text

681,534

681,534

1.00

0.00

0.00

Data Pump Worker

SELECT C.DEFAULT$, C.PROPERTY ...

552,487

4,774,328

8.64

0.00

0.00

select privilege#, level from ...

258,251

258,251

1.00

0.00

0.00

OGG-EXTLC-OCI_META_THREAD

SELECT MAX(sequence#) FROM v$l...

258,251

258,251

1.00

0.00

0.00

OGG-EXTLC-OCI_META_THREAD

SELECT DECODE(status, 'STALE',...

258,251

258,251

1.00

0.00

0.00

OGG-EXTLC-OCI_META_THREAD

SELECT DECODE(archived, 'YES',...

258,250

258,250

1.00

0.00

0.00

OGG-EXTLC-OCI_META_THREAD

SELECT 1 FROM V$LOGFILE WHERE(...

183,678

552,489

3.01

0.00

0.00

select privilege# from sysauth...

173,759

173,758

1.00

0.00

0.00

select SYS_CONTEXT('USERENV', ...

173,759

173,759

1.00

0.00

0.00

select value$ from props$ wher...

173,748

173,748

1.00

0.00

0.00

select decode(failover_method,...

173,271

346,542

2.00

0.00

0.00

Ips.GateWay.exe

SELECT PARAMETER, VALUE FROM S...

152,271

152,268

1.00

0.00

0.00

Ips.GateWay.exe

begin dbms_output.get_lines(:o...

142,101

142,100

1.00

0.00

0.00

Ips.GateWay.exe

begin dbms_output.enable(60000...

98,533

98,491

1.00

0.00

0.00

select type#, blocks, extents,...

74,837

6,236

0.08

0.00

0.00

select job, nvl2(last_date, 1,...

63,756

63,755

1.00

0.00

0.00

Ips.GateWay.exe

Begin sp_exceptionhandlingtime...

63,756

63,754

1.00

0.00

0.00

Ips.GateWay.exe

Begin pkg_util.sp_exceptionhan...

61,272

61,272

1.00

0.00

0.00

Ips.GateWay.exe

SELECT COUNT(*) FROM TSYS_EXCE...

61,272

61,272

1.00

0.00

0.00

Ips.GateWay.exe

SELECT EXCEPTIONTIMESTAMP FROM...

SQL ordered by Parse Calls

Total Parse Calls: 3,155,771

Captured SQL account for 74.5% of Total

Parse Calls

Executions

% Total Parses

SQL Id

SQL Module

SQL Text

552,489

552,487

17.51

select privilege#, level from ...

183,678

183,678

5.82

select privilege# from sysauth...

173,759

173,759

5.51

select SYS_CONTEXT('USERENV', ...

173,759

173,759

5.51

select value$ from props$ wher...

173,748

173,748

5.51

select decode(failover_method,...

173,271

173,271

5.49

Ips.GateWay.exe

SELECT PARAMETER, VALUE FROM S...

152,270

152,271

4.83

Ips.GateWay.exe

begin dbms_output.get_lines(:o...

142,101

142,101

4.50

Ips.GateWay.exe

begin dbms_output.enable(60000...

98,533

98,533

3.12

select type#, blocks, extents,...

63,756

63,756

2.02

Ips.GateWay.exe

Begin sp_exceptionhandlingtime...

63,756

63,756

2.02

Ips.GateWay.exe

Begin pkg_util.sp_exceptionhan...

SQL ordered by Sharable Memory

Only Statements with Sharable Memory greater than 1048576

are displayed

Sharable Mem (b)

Executions

% Total

SQL Id

SQL Module

SQL Text

4,994,128

308

0.29

SELECT dbin.db_name, dbin.inst...

3,667,540

154

0.21

insert into wrh$_sess_time_sta...

2,671,866

154

0.16

insert into wrh$_instance_reco...

2,578,964

154

0.15

select 1, max(id) from sys.wri...

1,600,750

5,338

0.09

SELECT C.TARGET_GUID, C.METRIC...

1,600,638

74,837

0.09

select job, nvl2(last_date, 1,...

1,506,656

154

0.09

SELECT advisor_id FROM sys.wri...

1,506,620

154

0.09

SELECT status FROM sys.wri$_ad...

1,426,210

147

0.08

insert into wrh$_sga_target_ad...

1,384,773

19,946

0.08

INSERT INTO MGMT_SYSTEM_PERFOR...

1,310,421

154

0.08

SELECT owner# FROM sys.wri$_ad...

1,238,010

241

0.07

update seq$ set increment$=:2,...

1,154,387

17,497

0.07

update sys.mon_mods$ set inser...

SQL ordered by Version Count

Only Statements with Version Count greater than 20 are

displayed

Version Count

Executions

SQL Id

SQL Module

SQL Text

110

308

SELECT dbin.db_name, dbin.inst...

92

154

SELECT owner# FROM sys.wri$_ad...

92

154

SELECT owner# FROM sys.wri$_ad...

92

154

SELECT status FROM sys.wri$_ad...

92

154

SELECT advisor_id FROM sys.wri...

91

19,946

INSERT INTO MGMT_SYSTEM_PERFOR...

90

154

select 1, max(id) from sys.wri...

59

241

update seq$ set increment$=:2,...

56

154

SELECT name, datatype, value, ...

55

5,338

SELECT C.TARGET_GUID, C.METRIC...

55

17,497

lock table sys.mon_mods$ in ex...

55

74,837

select job, nvl2(last_date, 1,...

55

3,478

update sys.job$ set this_date=...

55

17,497

update sys.mon_mods$ set inser...

49

154

insert into wrh$_instance_reco...

49

154

insert into wrh$_sess_time_sta...

49

147

insert into wrh$_sga_target_ad...

33

61,272

Ips.GateWay.exe

SELECT EXCEPTIONTIMESTAMP FROM...

31

61,272

Ips.GateWay.exe

SELECT COUNT(*) FROM TSYS_EXCE...

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值