oracle认证辅导:oracle优化和管理sql1
1.查看当前正在执行的等待情况
SELECT TA.sid ,
TA.seq# ,
TB.Username ,
TB.Terminal ,
TB.Program ,
Decode(TB.Command,0 ,‘[ 0] NoCommand’,
1 ,‘[ 1] CreateTable’,
2 ,‘[ 2] Insert’,
3 ,‘[ 3] Select’,
6 ,‘[ 6] Update’,
7 ,‘[ 7] Delete’,
9 ,‘[ 9] CreateIndex’,
15,‘[15] AlterTable’,
21,‘[21] CreateView’,
23,‘[23] ValidateIndex’,
35,‘[35] AlterDatabase’,
39,‘[39] CreateTablespace’,
41,‘[41] DropTablespace’,
40,‘[40] AlterTablespace’,
53,‘[53] DropUser’,
62,‘[62] AnalyzeTable’,
63,‘[63] AnalyzeIndex’,
TB.Command || ‘ther’) Command,
DECODE(TA.event,‘db file scattered read’,‘通表扫描’,
‘db file sequential read’,‘索引扫描’,
‘latch free’,‘latch contention’,
‘free buffer waits’,‘等待DBWR 清除弄脏块’,
‘log file sync’,‘LGWR写COMMIT或ROLLBACK数据’,
‘write complete waits’,‘等待DBWR写’,
‘buffer busy wait’,‘可能是FreeList竞争’,
TA.event) Event,
TA.p1text,TA.p1 ,TA.p1raw ,
TA.p2text,TA.p2 ,TA.p2raw ,
TA.p3text,TA.p3 ,TA.p3raw ,
TA.wait_time ,
TA.seconds_in_wait,
TA.state,
TB.sql_address,
TB.sql_hash_value
FROM v$session_wait TA,
v$session TB
WHERE --TB.terminal=‘FUTURE-MGET’ AND
TA.SID = TB.SID AND
TA.event NOT LIKE ‘% timer’ AND
TA.event NOT LIKE ‘rdbms ipc message’ AND
TA.event NOT LIKE ‘SQL*Net %’
2.查看连接等待事件
SELECT Sid || ‘ ’ || Event || ‘ ’ || Total_Waits || ‘ ’ || Average_Wait
FROM V$session_Event
WHERE Sid = &上面的SID
3.每个用户命中率(命中率应该超过90%)
SELECT TA.Sid “连接ID”,
username “用户名”,
consistent_gets “读一致性”,
block_gets “缓冲区读”,
physical_reads “物理读”,
100*(consistent_gets+block_gets-physical_reads)/(consistent_gets+block_gets) hiratio,
TA.sql_address,
TA.sql_hash_value
FROM v$session TA,
v$sess_io TB
WHERE TA.sid=TB.sid
AND (consistent_gets+block_gets)》0
AND username IS NOT NULL
ORDER BY 6 ASC;
4.查询耗资源的SQL
SELECT ADDRESS,HASH_VALUE,
SUBSTR(SQL_TEXT, 1, 20) TEXT,
BUFFER_GETS,
EXECUTIONS,
BUFFER_GETS / EXECUTIONS AVG
FROM V$SQLAREA
WHERE EXECUTIONS 》 0
AND BUFFER_GETS 》 100000
ORDER BY 6;
5.查询耗CPU资源的SQL
SELECT SS.SID,
Decode(SE.Command,0 ,‘[ 0] NoCommand’,
1 ,‘[ 1] CreateTable’,
2 ,‘[ 2] Insert’,
3 ,‘[ 3] Select’,
6 ,‘[ 6] Update’,
7 ,‘[ 7] Delete’,
9 ,‘[ 9] CreateIndex’,
15,‘[15] AlterTable’,
21,‘[21] CreateView’,
23,‘[23] ValidateIndex’,
35,‘[35] AlterDatabase’,
39,‘[39] CreateTablespace’,
41,‘[41] DropTablespace’,
40,‘[40] AlterTablespace’,
53,‘[53] DropUser’,
62,‘[62] AnalyzeTable’,
63,‘[63] AnalyzeIndex’,
SE.Command || ‘ther’),
SS.VALUE CPU,
SE.USERNAME,
SE.PROGRAM,
SE.sql_address,
SE.sql_hash_value
FROM V$SESSTAT SS, V$SESSION SE
WHERE SS.STATISTIC# IN
(SELECT STATISTIC#
FROM V$STATNAME
WHERE NAME = ‘CPU used by this session’)
AND SE.SID = SS.SID
AND SS.SID 》 6
ORDER BY SS.SID; |
|