oracle认证辅导:oracle优化和管理sql1

oracle认证辅导oracle优化和管理sql

查看当前正在执行的等待情况

SELECT TAsid

TAseq#

TBUsername

TBTerminal

TBProgram

Decode（TBCommand ［ ］ NoCommand

［ ］ CreateTable

［ ］ Insert

［ ］ Select

［ ］ Update

［ ］ Delete

［ ］ CreateIndex

［］ AlterTable

［］ CreateView

［］ ValidateIndex

［］ AlterDatabase

［］ CreateTablespace

［］ DropTablespace

［］ AlterTablespace

［］ DropUser

［］ AnalyzeTable

［］ AnalyzeIndex

TBCommand || ther） Command

DECODE（TAeventdb file scattered read通表扫描

db file sequential read索引扫描

latch freelatch contention

free buffer waits等待DBWR 清除弄髒块

log file syncLGWR写COMMIT或ROLLBACK数据

write complete waits等待DBWR写

buffer busy wait可能是FreeList竞争

TAevent） Event

TAptextTAp TApraw

TAptextTAp TApraw

TAptextTAp TApraw

TAwait_time

TAseconds_in_wait

TAstate

TBsql_address

TBsql_hash_value

FROM v$session_wait TA

v$session TB

WHERE TBterminal=FUTUREMGET AND

TASID = TBSID AND

TAevent NOT LIKE % timer AND

TAevent NOT LIKE rdbms ipc message AND

TAevent NOT LIKE SQL*Net %

查看连接等待事件

SELECT Sid || || Event || || Total_Waits || || Average_Wait

FROM V$session_Event

WHERE Sid = &上面的SID

每个用户命中率（命中率应该超过%）

SELECT TASid 连接ID

username 用户名

consistent_gets 读一致性

block_gets 缓沖区读

physical_reads 物理读

*（consistent_gets+block_getsphysical_reads）/（consistent_gets+block_gets） hiratio

TAsql_address

TAsql_hash_value

FROM v$session TA

v$sess_io TB

WHERE TAsid=TBsid

AND （consistent_gets+block_gets）》

AND username IS NOT NULL

ORDER BY ASC;

查询耗资源的SQL

SELECT ADDRESSHASH_VALUE

SUBSTR（SQL_TEXT ） TEXT

BUFFER_GETS

EXECUTIONS

BUFFER_GETS / EXECUTIONS AVG

FROM V$SQLAREA

WHERE EXECUTIONS 》

AND BUFFER_GETS 》

ORDER BY ;

查询耗CPU资源的SQL

SELECT SSSID

Decode（SECommand ［ ］ NoCommand

［ ］ CreateTable

［ ］ Insert

［ ］ Select

［ ］ Update

［ ］ Delete

［ ］ CreateIndex

［］ AlterTable

［］ CreateView

［］ ValidateIndex

［］ AlterDatabase

［］ CreateTablespace

［］ DropTablespace

［］ AlterTablespace

［］ DropUser

［］ AnalyzeTable

［］ AnalyzeIndex

SECommand || ther）

SSVALUE CPU

SEUSERNAME

SEPROGRAM

SEsql_address

SEsql_hash_value

FROM V$SESSTAT SS V$SESSION SE

WHERE SSSTATISTIC# IN

（SELECT STATISTIC#

FROM V$STATNAME

WHERE NAME = CPU used by this session）

AND SESID = SSSID

AND SSSID 》

ORDER BY SSSID;