9. Oracle深度学习笔记——性能调优汇总

创建时间：2016-02-01 投稿人：浏览次数：34081

9. Oracle深度学习笔记——性能调优汇总

欢迎转载，转载请标明出处：http://blog.csdn.net/notbaron/article/details/50621026

数据字典高速缓存命中率

数据字典高速缓存命中率如下：

select (sum(gets-getmisses-fixed))/sum(gets)"data dictionary hit ratio" from v$rowcache;

data dictionary hit ratio

-------------------------

.975728791

参考95~99%

alter session set sql_trace=true;

alter system flush shared_pool;

select table_name from user_tables whererownum<=5;

打开TRC文件如下：

PARSING IN CURSOR #140195096140104 len=50dep=0 uid=0 oct=3 lid=0 tim=700865302 hv=17921204 ad="97a9d708"sqlid="83dx9sc0j2x5n"

select table_name from user_tables whererownum<=5

END OF STMT

PARSE#140195096140104:c=106000,e=131031,p=2,cr=727,cu=0,mis=1,r=0,dep=0,og=1,plh=5472353,tim=700865301

….

再执行相同的命令如下，查看TRC文件如下：

*** 2016-01-30 17:55:48.379

CLOSE#140195096140104:c=0,e=100,dep=0,type=0,tim=1039278193

=====================

PARSING IN CURSOR #140195096140104 len=50dep=0 uid=0 oct=3 lid=0 tim=1039279623 hv=17921204 ad="97a9d708"sqlid="83dx9sc0j2x5n"

select table_name from user_tables whererownum<=5

END OF STMT

PARSE #140195096140104:c=1000,e=937,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=5472353,tim=1039279622

……

发现命中。

使用tkprof处理一下：

具体使用，可以直接输入tkprof

打开结果文件如下：

select table_name

from

user_tables where rownum<=5

call count cpu elapsed disk query current rows

------- ------ -------- ---------- ---------- -------------------- ----------

Parse 2 0.05 0.06 0 5 0 0

Execute 2 0.00 0.00 0 0 0 0

Fetch 4 0.00 0.00 0 140 0 10

------- ------ -------- ---------- ---------- -------------------- ----------

total 8 0.06 0.07 0 145 0 10

Misses in library cacheduring parse: 1

select sum(pinhits)/sum(pins) Library_cache_hit_ratiofrom v$librarycache;

LIBRARY_CACHE_HIT_RATIO

-----------------------

.927807473

参考大于99%

确定库高速缓存的效率

select namespace,pins,pinhits,reloads fromv$librarycache order by namespace;

NAMESPACE PINS PINHITS RELOADS

-------------------------------------------------------------------------- ---------- ----------

AUDIT POLICY 66 56 0

BODY 1301 1097 16

CLUSTER 1318 1298 0

CONTEXT POLICY 2 0 0

DBINSTANCE 0 0 0

DBLINK 0 0 0

DIRECTORY 51 41 0

EDITION 309 296 0

INDEX 4516 3082 72

OBJECT ID 0 0 0

PDB 0 0 0

QUEUE 10 6 0

RULESET 3 2 0

SCHEMA 0 0 0

SQL AREA 528694 508982 641

SQL AREA BUILD 0 0 0

SQL AREA STATS 6406 262 0

TABLE/PROCEDURE 57487 44691 3245

TEMPORARY INDEX 481 0 45

TEMPORARY TABLE 3361 0 1962

TRIGGER 528 405 0

USER PRIVILEGE 1 0 0

22 rows selected.

其中RELOAD表示SQL语句在老化后又被重新装入库。可以使用DBMS_SHARED_POOL程序强迫将SQL语句保留在共享池的库高速缓存组中。

V$LIBRARY_CACHE_MEMORY视图确定库高速缓存内存对象的数目

V$SHARED_POOL_ADVICE视图提供各种尺寸的共享池预期可节省的分析时间的信息

l CUSOR_SHARING

设置CURSOR_SHARING=FORCE来减轻非绑定变量的问题，强制进行绑定变量。

显示实例启动以来，硬分析数与执行数比较结果，如下：

select s.sid,s.value "Hard Parses", t.value"Executions Count" from v$sesstat s,v$sesstat t where s.sid=t.sid ands.statistic#=(select statistic# from v$statname where name="parse count(hard)") and t.statistic#=(select statistic# from v$statname where name="executecount") and s.value >0;

SID Hard Parses Executions Count

---------- ----------- ----------------

1 576 3895

9 15 77

15 11 82

16 8 51

21 325 4506

22 3 15

34 1 19

37 1 1

39 78 1680

45 12 116

47 1 2

67 6 80

12 rows selected.

l 参数CURSOR_SPACE_FOR_TIME已废弃

l SESSION_CACHED_CURSORS

一个session可以缓存多少个cursor，让后续相同的SQL语句不再打开游标,从而避免软解析的过程来提高性能。(绑定变量是解决硬解析的问题)，软解析同硬解析一样,比较消耗资源.所以这个参数非常重要。

session_cached_cursors这个参数是控制sessioncursor cache的大小的。session_cached_cursors定义了session cursor cache中存储的cursor的个数。这个值越大，则会消耗的内存越多。设置在OPEN_CURSORS和会话中使用的游标的数目之间。

查看游标命中次数

select name,value from v$sysstat where name like"%cursor%";

NAME VALUE

--------------------------------------------------------------------------

opened cursors cumulative 456915

opened cursors current 33

pinned cursors current 15

session cursor cache hits 151457

session cursor cache count 6346

cursor reload failures 14

cursor authentications 1219

7 rows selected.

查看总的分析次数：

select name,value from v$sysstat where name like"%parse%";

NAME VALUE

--------------------------------------------------------------------------

ADG parselock X get attempts 0

ADG parselock X get successes 0

parse time cpu 1571

parse time elapsed 4723

parse count (total) 108089

parse count (hard) 8458

parse count (failures) 377

parse count (describe) 21

8 rows selected.

select type,count(*) objects,sum(decode(kept,"YES",1,0))kept, sum(loads)-count(*) reloads from v$db_object_cache group by type order byobjects desc;

TYPE OBJECTS KEPT RELOADS

-------------------------------------------------------------------------- ---------- ----------

CURSOR 6419 0 499

CURSOR STATS 1909 1114 0

TABLE 691 30 884

PACKAGE 225 0 -120

TYPE 163 0 -48

INDEX 82 7 83

MULTI-VERSIONED OBJECT 64 62 3

PACKAGE BODY 43 0 3

Optimizer Finding 43 0 0

SEQUENCE 28 0 -18

SYNONYM 28 0 -21

LIBRARY 23 0 -19

TRIGGER 21 0 0

Optimizer Directive Owner 16 0 0

FUNCTION 13 0 3

NONE 13 0 -13

CLUSTER 7 6 0

TYPE BODY 4 0 0

PROCEDURE 4 0 -2

SCHEDULER CLASS 3 0 0

SCHEDULER JOB 3 0 0

SCHEDULER GLOBAL ATTRIBUTE 2 0 0

QUEUE 2 0 0

PUB SUB INTERNAL INFORMATION 2 0 1

OPERATOR 2 0 0

AUDIT POLICY 2 0 0

SCHEDULER PROGRAM 1 0 0

SCHEDULER WINDOW 1 0 0

RULE EVALUATION CONTEXT 1 0 -1

SCHEDULER GROUP 1 0 0

RESOURCE MANAGER CONSUMER GROUP 1 0 0

EDITION 1 0 0

RULESET 1 0 0

34 rows selected.

共享池中钉住程序包，命令如下：

EXECUTE SYS.DBMS_SHARED_POOL.KEEP（object_name,object_type）;

查看数据库中程序包的数量如下：

select count(*) from v$db_object_cache wheretype="PACKAGE";

COUNT(*)

----------

223

共享池中钉住的内存量：

select sum(sharable_mem) from v$db_object_cache wheretype="PACKAGE";

SUM(SHARABLE_MEM)

-----------------

2349720

缓冲区高速缓存的命中率：

selectname,physical_reads,db_block_gets,consistent_gets,1-(physical_reads/(db_block_gets+consistent_gets))"HitRatio" from v$buffer_pool_statistics;

NAME PHYSICAL_READS DB_BLOCK_GETSCONSISTENT_GETS HitRatio

-------------------- --------------------------- --------------- ----------

DEFAULT 18216 159266 2316657 .992642744

可以使用v$db_cache_advice,v$sga_cache_advice 来优化缓冲区。

当然，事物都存在两极，如果存在大量全表扫描或数据仓库，命中率可能远低于100%，但并不一定是坏事。

使用多个池，保留缓冲池（keep）、回收缓冲池(recycle)、默认缓冲池(default)。

确定保留缓冲区高速缓存的备选

select obj object,count(1) buffers,avg(tch)average_touch_count from x$bh where lru_flag=8 group by obj havingavg(tch)>5 and count(1)>25;

将某个表放入到保留缓冲区中，命令如下：

alter table test1 storage (buffer_poolkeep);

查看数据库中PGA使用情况：

select s.value,s.sid,a.username from v$sesstats,v$statname n,v$session a where n.statistic#=s.statistic# and name="sessionpga memory" and s.sid=a.sid order by s.value;

VALUE SID USERNAME

---------- ----------------------------------------

766056 40

766056 17

766056 3

766056 4

766056 47

766056 18

766056 20

766056 6

766056 7

766056 28

766056 10

766056 24

774456 19

774456 8

774456 5

790912 2

962664 46

1421416 22

1486952 36

1535432 30

1618024 16

1618024 31

1749096 15

2093792 14

2330264 13

2330264 11

2338920 44

2609464 9

3395896 38

4911624 1 SYS

8827016 12

12636472 21

62238824 23

33 rows selected.

查看PGA状态:

select * from v$pgastat;

NAME VALUE UNIT CON_ID

-------------------------------------------------------------------------- ------------ ----------

aggregate PGA target parameter 377487360 bytes 0

aggregate PGA auto target 232538112 bytes 0

global memory bound 75497472 bytes 0

total PGA inuse 118634496 bytes 0

total PGA allocated 154334208 bytes 0

maximum PGA allocated 342854656 bytes 0

total freeable PGA memory 14745600 bytes 0

process count 42 0

max processes count 63 0

PGA memory freed back to OS 511049728 bytes 0

total PGA used for auto workareas 0 bytes 0

maximum PGA used for auto workareas 5123072 bytes 0

total PGA used for manual workareas 0 bytes 0

maximum PGA used for manual workareas 0 bytes 0

over allocation count 0 0

bytes processed 1479985152 bytes 0

extra bytes read/written 0 bytes 0

cache hit percentage 100percent 0

recompute count (total) 1436 0

19 rows selected.

查看PGA效能

select low_optimal_size/1024 "Low (K)",(high_optimal_size+1)/1024 "High (K)",optimal_executions"Optimal",onepass_executions "1-pass",multipasses_executions ">1 Pass" from v$sql_workarea_histogram wheretotal_executions <> 0;

Low (K) High (K) Optimal 1-pass >1 Pass

---------- ---------- ---------- --------------------

2 4 13821 0 0

64 128 124 0 0

128 256 35 0 0

256 512 14 0 0

512 1024 473 0 0

1024 2048 728 0 0

2048 4096 6 0 0

7 rows selected.

最佳方法来执行排序。

查看每个进程的PGA使用情况：

select pid,category,allocated,used fromv$process_memory;

PID CATEGORY ALLOCATED USED

---------- --------------- --------------------

2 PL/SQL 1976 224

2 Other 972532

3 PL/SQL 1976 224

3 Other 947740

4 PL/SQL 1976 224

4 Other 947740

5 PL/SQL 1976 224

5 Other 956108

6 PL/SQL 1976 224

6 Other 947740

7 SQL 43936 8440

7 PL/SQL 26128 21608

7 Freeable 917504 0

7 Other 3976580

8 PL/SQL 1976 224

8 Other 956108

9 SQL 0 0

9 PL/SQL 1976 224

9 Freeable 65536 0

9 Other 2791116

10PL/SQL 1976 224

10Other 947740

11PL/SQL 1976 224

11Freeable 131072 0

11Other 2511916

12PL/SQL 1976 224

12Freeable 786432 0

12Other 9008508

13PL/SQL 1976 224

13Freeable 393216 0

13Other 2511916

14PL/SQL 1976 224

14Freeable 393216 0

14Other 2275412

15SQL 2000 296

15PL/SQL 1976 224

15Freeable 262144 0

15Other 1928780

16SQL 0 0

16PL/SQL 1976 224

16Freeable 131072 0

16Other 1799708

17PL/SQL 1976 224

17Other 947740

18PL/SQL 1976 224

18Other 947740

19PL/SQL 1976 224

19Other 956108

20PL/SQL 1976 224

20Other 947740

21SQL 36576 0

21PL/SQL 312848 294744

21Freeable 3276800 0

21Other 12470676

22SQL 0 0

22PL/SQL 1976 224

22Other 1603100

23Other 1408468

24PL/SQL 1976 224

24Other 62420508

25Other 687572

26PL/SQL 1976 224

26Other 947740

27PL/SQL 1976 224

27Other 947740

28PL/SQL 1976 224

28Other 947740

29PL/SQL 1976 224

29Other 947740

30SQL 3024 1256

30PL/SQL 1976 224

30Other 1796684

31SQL 7096 800

31PL/SQL 1976 224

31Freeable 131072 0

31Other 1710020

33SQL 0 0

33PL/SQL 1976 224

33Freeable 131072 0

33Other 1668636

34SQL 11904 2728

34PL/SQL 20704 16816

34Freeable 7340032 0

34Other 3546916

35Other 687572

36Other 687572

37Other 687572

38Other 687572

39SQL 0 0

39PL/SQL 1976 224

39Other 947740

41SQL 3000 0

41PL/SQL 264056 257616

41Freeable 589824 0

41Other 2255524

42SQL 5760 3552

42PL/SQL 1976 224

42Other 1138588

98 rows selected.

评估实例启动以来，使用最佳、1遍、多遍PGA内存尺寸的工作区比例：

select name profile,cntcount,decode(total,0,0,round(cnt*100/total)) percentage from (select name,valuecnt,(sum(value) over()) total from v$sysstat where name like "workarea exec%");

PROFILE COUNT PERCENTAGE

-------------------------------------------------------------------------- ----------

workarea executions - optimal 15231 100

workarea executions - onepass 0 0

workarea executions - multipass 0 0

CPU

使用SAR命令显示CPU使用情况

#sar –u 10 5

查看CPU使用高的用户

select name profile,cntcount,decode(total,0,0,round(cnt*100/total)) percentage from (select name,valuecnt,(sum(value) over()) total from v$sysstat where name like "workarea exec%");

PROFILE COUNT PERCENTAGE

-------------------------------------------------------------------------- ----------

workarea executions - optimal 15231 100

workarea executions - onepass 0 0

workarea executions - multipass 0 0

select n.username,s.sid,s.value from v$sesstats,v$statname t,v$session n where s.statistic#=t.statistic# and n.sid=s.sid andt.name="CPU used by this session" order by s.value desc;

USERNAME SID VALUE

------------------------------ --------------------

22 276

21 238

38 163

SYS 1 150

44 13

15 11

30 9

31 7

16 4

9 3

13 0

14 0

17 0

18 0

19 0

20 0

23 0

24 0

28 0

36 0

40 0

46 0

12 0

11 0

10 0

8 0

7 0

6 0

5 0

4 0

3 0

47 0

2 0

33 rows selected.

总的CPU使用分解：

select name,value from v$sysstat where name in ("CPUused by this session","recursive cpu usage","parse time cpu");

NAME VALUE

--------------------------------------------------------------------------

recursive cpu usage 4546

CPU used by this session 5435

parse time cpu 800

select name,value from v$sysstat where name like"%CPU%";

NAME VALUE

--------------------------------------------------------------------------

OS CPU Qt wait time 0

CPU used when call started 359

CPU used by this session 5686

IPC CPU used by this session 0

global enqueue CPU used by this session 0

gc CPU used by this session 0

cell physical IO bytes sent directly to DBnode to balance CPU 0

7 rows selected.

其中CPU used by this session表示实例中CPU的总使用。

显示CPU的分析占用的时间

select name,value from v$sysstat where name like"%parse%";

NAME VALUE

--------------------------------------------------------------------------

ADG parselock X get attempts 0

ADG parselock X get successes 0

parse time cpu 802

parse time elapsed 4067

parse count (total) 40606

parse count (hard) 4261

parse count (failures) 182

parse count (describe) 15

8 rows selected.

确定分析时的CPU使用：

select a.value"Tot_CPU_Used_This_Session",b.value"Total_Parse_Count",c.value "Hard_Parse_Count",d.value"Parse_Time_COU" from v$sysstat a,v$sysstat b,v$sysstat c,v$sysstat dwhere a.name="CPU used by this session" and b.name="parse count (total)" andc.name="parse count (hard)" and d.name="parse time cpu";

Tot_CPU_Used_This_Session Total_Parse_CountHard_Parse_Count Parse_Time_COU

------------------------- --------------------------------- --------------

5715 40725 4262 803

如果分析过高可以考虑一下几点：使用绑定变量；保证不分配过多的共享池内存；保证库高速缓存上没有闩争用；确保表最近被分析过

查看递归使用

select name,value from v$sysstat where name in ("CPUused by this session","recursive cpu usage");

NAME VALUE

--------------------------------------------------------------------------

recursive cpu usage 5403

CPU used by this session 6759

内存

使用VMSTAT命令检查内存相关

IO

Iostate和sar提供了很流行的度量磁盘性能的工具。

# sar -d 10 5

# iostat -kx 2

确定数据库中的IO分布

Col name format a35

select d.name,f.phyrds reads,f.phywrts wrts,(f.readtim/ decode(f.phyrds,0,-1,f.phyrds)) readtime,

(f.writetim/ decode(f.phywrts,0,-1,phywrts)) writetime from v$datafile d,v$filestat fwhere d.file#=f.file# order by d.name;

NAME

----------------------------------------------------------------------------------------------------

READS WRTS READTIME WRITETIME

---------- ---------- ---------- ----------

+DATA/toaddb/example01.dbf

19 31.36842105 .666666667

+DATA/toaddb/sysaux01.dbf

4046 1650 .431290163.178787879

+DATA/toaddb/system01.dbf

9455 675 1.17059757 .088888889

+DATA/toaddb/undotbs01.dbf

28 7671.92857143 .10821382

+DATA/toaddb/users01.dbf

12 31.41666667 0

+DATA/tpcc1.dbf

7 3 1.57142857 0

6 rows selected.

减少磁盘几条：

l 增加磁盘数量

l 分散数据库文件和重做日志文件

l 大表使用分区

l 条带化数据

l 高端存储

l 使用ASM等

主要以下几点：

AWR收集必须的所有性能数据

ADDM通过分析AWR数据诊断数据库性能

Automatic SQL Tuning Advisor提供SQL调优建议

统计数据收集

Segment Advisor 指出哪些段需要缩短，哪些段需要重新组织

SQL Access Advisor提供创建理想的索引和实体化视图的建议

Memory Advisor, MTTR Advisor 和Undo Advisor帮助调优内存、REDO和UNDO。

统计数据可以分为两组：

数据库命中率统计数据和数据库等待统计数据。

V$SYSTEM_EVENT视图和V$SESSION_EVENT视图中的等待事件说明等待是什么。

等待事件仅仅是问题的表象，根源可能出在应用代码上。

等待事件

实例等待所占比例：

select metric_name,value from v$sysmetric wheremetric_name in ("Database CPU Time Ratio","Database Wait Time Ratio") andINTSIZE_CSEC=(select max(INTSIZE_CSEC) from v$sysmetric);

METRIC_NAME VALUE

--------------------------------------------------------------------------

Database Wait Time Ratio 0

Database CPU Time Ratio 135.577

查看等待事件：

select event,time_waited,average_wait fromv$system_event group by event,time_waited,average_wait order by time_waiteddesc;

EVENT TIME_WAITED AVERAGE_WAIT

--------------------------------------------------------------------------- ------------

rdbms ipc message 26569372 250.73

PX Idle Wait 7127241 1781810.2

Space Manager: slave idle wait 4446128 490.09

DIAG idle wait 3566269 100

SQL*Net message from client 1783990 501.4

lreg timer 1783618 300.07

shared server idle wait 1783466 2997.42

pmon timer 1783456 299.89

wait for unread message on broadcastchannel 1783414 299.99

ASM background timer 1782999 482.67

heartbeat redo informer 1782715 100.03

dispatcher timer 1782248 6000.83

AQPC idle 1780225 2991.98

Streams AQ: qmn coordinator idle wait 1778439 1399.24

Streams AQ: qmn slave idle wait 1778353 2800.56

smon timer 1771958 10804.62

class slave wait 1542860 1203.48

jobq slave wait 743351 49.71

Streams AQ: load balancer idle 12000 12000.19

db file sequential read 11679 1.11

library cache lock 9000 818.16

VKRM Idle 2321 2321.39

oracle thread bootstrap 2026 2.04

external table read 1581 1580.86

library cache load lock 1478 86.95

control file parallel write 1340 .21

log file parallel write 1160 .24

db file scattered read 855 1.18

os thread creation 812 .82

control file sequential read 689 .02

ASM background starting 594 59.4

db file parallel write 548 .14

resmgr:cpu quantum 462 7.57

不同用户面临的等待事件及SQL语句如下：

select s.username,t.sql_text,s.event from v$sessions,v$sqltext t where s.sql_hash_value = t.hash_value and s.sql_address=t.addressand s.type <> "BACKGROUD" order by s.sid,t.hash_value,t.piece;

USERNAME SQL_TEXT

----------------------------------------------------------------------------------------------

EVENT

----------------------------------------------------------------

SYS select s.username,t.sql_text,s.eventfrom v$session s,v$sqltext

SQL*Net message to client

SYS twhere s.sql_hash_value = t.hash_value and s.sql_address=t.addr

SQL*Net message to client

SYS ess and s.type <> "BACKGROUD"order by s.sid,t.hash_value,t.piec

SQL*Net message to client

SYS e

SQL*Net message to client

select /*+ FIRST_ROWS(1) */ x.C1, x.C2,x.C3 from (select a.obj

rdbms ipc message

# C1, decode(bitand(a.flags, 64), 0,a.next_start_date,

rdbms ipc message

按总等待事件排序

select event,total_waits,time_waited fromv$system_event where event not in ("pmon timer","smon timer","rdbms ipcreply","parallel deque wait","virtual circuit","%SQL*Net%","clientmessage","NULL event") order by time_waited desc;

EVENT TOTAL_WAITS TIME_WAITED

--------------------------------------------------------------------------- -----------

rdbms ipc message 110835 27931997

PX Idle Wait 4 7465641

Space Manager: slave idle wait 9414 4615126

DIAG idle wait 37355 3735362

SQL*Net message from client 3722 1868578

wait for unread message on broadcastchannel 6227 1868014

lreg timer 6225 1867954

ASM background timer 3863 1867499

shared server idle wait 623 1867488

heartbeat redo informer 18666 1867246

dispatcher timer 311 1866257

等待事件先查看V$SYSTEM_EVENT视图，找到时间总量排在前面的等待事件。（AWR TOP 5也有）

然后找出具体的等待事件。查看视图V$WAITSTAT

最后使用V$SESSION找出可能问题根源的具体对象

查找具体等待事件的会话如下：

select sid,sql_address,sql_hash_value from v$sessionwhere event="db file scattered read";

查找具体会话的等待事件如下：

select sid,state,event,wait_time,seconds_in_wait fromv$session where sid=1418;

查看会话的最后10个等待事件，视图V$SESSION_WAIT_HISTORY保存每个活动会话的last ten wait events

select seq#,event,wait_time,p1,p2,p3 fromv$session_wait_history where sid=988 order by seq#;

如果WAIT_TIME=0 说明会话正在等待一个特定的等待事件。

通过ASH用活动会话历史来分析等待，通过V$ACTIVE_SESSION_HISTORY查看ASH会话统计数据。ASH是内存中一个滚动的缓冲区。

确定导致最多等待的对象以及这些对象在最后15分钟内等待的事件类型

Col object_name format a15

select o.object_name,o.object_type,a.event, sum(a.wait_time + a.time_waited) total_wait_time fromv$active_session_history a,dba_objects o where a.sample_time betweensysdate-30/2880 and sysdate and a.current_obj# = o.object_id group byo.object_name,o.object_type,a.event order by total_wait_time;

最后15分钟内，最重要的等待事件

select a.event,sum(a.wait_time+a.time_waited)total_wait_time from v$active_session_history a where a.sample_time betweensysdate - 30/2880 and sysdate group by a.event order by total_wait_time desc;

EVENT TOTAL_WAIT_TIME

-------------------------------------------------------------------------------

138775866

oracle thread bootstrap 34136

os thread creation 19857

control file parallel write 6072

db file parallel write 2643

最后15分钟等待最多的用户

selects.sid,s.username,sum(a.wait_time+a.time_waited) total_wait_time fromv$active_session_history a,v$session s where a.sample_time betweensysdate-30/2880 and sysdate and a.session_id=s.sid group by s.sid,s.usernameorder by total_wait_time desc;

SID USERNAME TOTAL_WAIT_TIME

---------- ---------------------------------------------

声明：该文观点仅代表作者本人，牛骨文系教育信息发布平台，牛骨文仅提供信息存储空间服务。

上一篇： oracle参数open_cursors和session_cached_cursor详解！
下一篇： oracle参数open_cursors和session_cached_cursor详解

热门文章: CTF writeup 2_南邮网络攻防训...; SSM框架——详细整合教程（...; Linux Shell脚本编程－－curl命...; HttpClient使用详解; Java面试题全集（上）; JAVA设计模式之单例模式; java.lang.OutOfMemoryError: PermGen ...; TCP协议中的三次握手和四次...; form表单的两种提交方式，su...; String,StringBuffer与StringBuilder...

最新文章: Java之品优购课程讲义_day20（7）; 剑指 Offer - 8：跳台阶; Netty权威指南_札记02_NIO编程; mysql时间属性之时间戳和datetime之...; 虚拟现实或许可以拯救古埃及的“...; spring cloud服务注册中心eureka---集群...; Java SE 第六章; HTTP请求+数据库; HIDL学习笔记之HIDL C++（第二天）; ubuntu系统下指定tomcat运行时为JDK1.8...