oracle优化与管理SQL

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;
6.v$session中的列部分说明
0 - WAITING (当前等待的 Session)
-2 - WAITED UNKNOWN TIME (最后等待持续时间未知)
-1 - WAITED SHORT TIME (最后的等待 <1/100 秒)
>0 - WAITED KNOWN TIME (WAIT_TIME = 最后等待持续时间)

STATUS VARCHAR2(8) Status of the session:
ACTIVE - Session currently executing SQL
INACTIVE - sql及其session没有释放或正常退出......
KILLED - Session marked to be killed
CACHED - Session temporarily cached for use by Oracle*XA
SNIPED - Session inactive, waiting on the client
7.根据上面的地址找对应的SQL
SELECT *
  FROM v$sqltext
WHERE address=HexToRaw('0700000036D20268')
   AND hash_value=2348072240
ORDER BY address,hash_value,piece;
--或者
SELECT *
  FROM v$sqlarea
WHERE address=HexToRaw('0700000036D20268')
   AND hash_value=2348072240
ORDER BY address,hash_value;

HexToRaw('0700000036D20268')和hash_value=2348072240
对应的是v$session中的sql_address,sql_hash_value,
这两个值分别在楼上的SQL中有取出
8.根据ID找SQL
SELECT tb.command_type,
       Tb.Piece,
       Tb.Sql_Text
  FROM V$open_Cursor Ta,
       V$sqltext     Tb
WHERE Ta.Sid = &SID
   AND Ta.Address = Tb.Address
   AND Ta.Hash_Value = Tb.Hash_Value
ORDER BY Tb.Address,
          Tb.Hash_Value,
          Tb.Piece ASC;
9.寻找没有使用绑定变量的sql语句
SELECT Plan_Hash_Value,
       COUNT(*)
  FROM V$sql
WHERE Plan_Hash_Value <> 0
GROUP BY Plan_Hash_Value
ORDER BY 2 DESC;
10.查询正打开的游标
SELECT User_Name,
       Sql_Text 　　
  FROM V$open_Cursor 　　
WHERE Sid IN (SELECT Sid
                 FROM (SELECT Sid,
                              Serial#,
                              Username,
                              Program 　　
                         FROM V$session 　　
                        WHERE Status = 'ACTIVE'))

子查询
SELECT Sid,
       Serial#,
       Username,
       Program 　　
  FROM V$session 　　
WHERE Status = 'ACTIVE'
查出的是不活动的session的sid
10.锁表检查
锁与等待，如果发生了锁等待，我们可能更想知道是谁锁了表而引起谁的等待以下的语句可以
查询到谁锁了表，而谁在等待。
SELECT /*+ rule */
       LPAD(' ', DECODE(L.XIDUSN, 0, 3, 0)) || L.ORACLE_USERNAME USER_NAME,
       O.OWNER,
       O.OBJECT_NAME,
       O.OBJECT_TYPE,
       --DECODE(L.TYPE, 'TM', 'TABLE LOCK', 'TX', 'ROW LOCK', NULL) LOCK_LEVEL,
       Decode(L.Locked_Mode,0,'[0] none',
                            1,'[1] null 空',
                            2,'[2] Row-S 行共享(RS)：共享表锁，sub share ',
                            3,'[3] Row-X 行独占(RX)：用于行的修改，sub exclusive ',
                            4,'[4] Share 共享锁(S)：阻止其他DML操作，share',
                            5,'[5] S/Row-X 共享行独占(SRX)：阻止其他事务操作，share/sub exclusive ',
                            6,'[6] exclusive 独占(X)：独立访问使用，exclusive ',
                            '['||L.Locked_Mode||'] Other Lock') LockMode,
       S.SID,
       S.SERIAL#,
       S.sql_address,
       S.sql_hash_value
  FROM V$LOCKED_OBJECT L, DBA_OBJECTS O, V$SESSION S
WHERE L.OBJECT_ID = O.OBJECT_ID
   AND L.SESSION_ID = S.SID
ORDER BY O.OBJECT_ID, XIDUSN DESC;
11.锁类型检查
用户锁，数据库的锁有的时候是比较耗费资源的，特别是发生锁等待的时候，我们必须找到发生
等待的锁，有可能的话，杀掉该进程。
　　
　 这个语句将查找到数据库中所有的DML语句产生的锁，还可以发现，任何DML语句其实产生了两个锁，
一个是表锁，一个是行锁。
　　
　 可以通过《删除回话及进程》来杀掉会话
　　
SELECT /*+ rule */
       S.USERNAME,
       DECODE(L.TYPE, 'TM', 'TABLE LOCK', 'TX', 'ROW LOCK', NULL) LOCK_LEVEL,
       Decode(L.LMode,0,'[0] none',
                      1,'[1] null 空',
                      2,'[2] Row-S 行共享(RS)：共享表锁，sub share ',
                      3,'[3] Row-X 行独占(RX)：用于行的修改，sub exclusive ',
                      4,'[4] Share 共享锁(S)：阻止其他DML操作，share',
                      5,'[5] S/Row-X 共享行独占(SRX)：阻止其他事务操作，share/sub exclusive ',
                      6,'[6] exclusive 独占(X)：独立访问使用，exclusive ',
                      '['||L.LMode||'] Other Lock') LockMode,
       O.OWNER,
       O.OBJECT_NAME,
       O.OBJECT_TYPE,
       S.SID,
       S.SERIAL#,
       S.TERMINAL,
       S.MACHINE,
       S.PROGRAM,
       S.OSUSER,
       S.sql_address,
       S.sql_hash_value
  FROM V$SESSION S, V$LOCK L, DBA_OBJECTS O
WHERE L.SID = S.SID
   AND L.ID1 = O.OBJECT_ID(+)
   AND S.USERNAME IS NOT NULL
12.当前用户排序情况
SELECT s.sid    "连接ID",
       machine  "对方机器",username "系统用户",
       program  "应用程序",osuser   "登陆系统用户",
       sd.VALUE "磁盘排序",sm.VALUE "内存排序",
       sr.VALUE "排序行数",
       Round(sm.VALUE/Decode(Sign(sm.VALUE+sr.VALUE),0,NULL,(sm.VALUE+sr.VALUE)*100),4) "排序效率(%)",
       s.sql_address,
       s.sql_hash_value
  FROM v$session s,
       v$sesstat sd,
       v$sesstat sm,
       v$sesstat sr
WHERE s.sid = sd.sid AND
       s.sid = sm.sid AND
       s.sid = sr.sid AND
       sd.statistic# = 101 AND
       sm.statistic# = 100 AND
       sr.statistic# = 102 AND
       s.TYPE != 'BACKGROUND'
ORDER BY 6 Desc;
13.检查文件物理IO读写
SELECT name,phyrds,phywrts
         FROM v$datafile TA,
              v$filestat TB
        WHERE TA.file#=TB.File#
将读写比较多的文件放到速度比较快的硬盘上
或者将集中读写的文件分布到不同的硬盘上
14.优化日志缓存
日志高速缓存：
=================================================================================
SELECT Latch                          "Latch",
       Gets                           "成功",
       misses                         "失败",
       immediate_gets                 "立即成功",
       immediate_misses               "立即失败",
       IGetsHitRatio                  "IGets Hit Ratio (%)",
       GetsHitRatio                   "Gets Hit Ratio (%)",
       DECODE(Sign(GetsHitRatio-99),1,'正常','请增加log_buffer') "争用",
       DECODE(Sign(GetsHitRatio-99),1,'正常','请增加log_buffer') "立即争用"
  FROM (SELECT Substr(name,1,20)              Latch,
               gets                           Gets,
               misses                         misses,
               immediate_gets                 immediate_gets,
               immediate_misses               immediate_misses,
               Round(100*(1-immediate_misses/Decode(immediate_misses+immediate_gets,0,NULL,immediate_misses+immediate_gets)),2) IGetsHitRatio,
               Round(100*(1-misses/Decode(misses+gets,0,NULL,misses+gets)),2) GetsHitRatio
          FROM v$Latch
         WHERE name like '%redo%')
如果失败超过成功的1%，就可能出现Redo allocation和/或Redo Copy Latch争用，减少或消除
此争用是内存调整进程的一部分。


--查看日志缓冲区大小【三个值可能不一样V$parameter是初始化值】
SELECT Substr(NAME, 1, 10) NAME,
       Substr(VALUE, 1, 10) VALUE
  FROM V$parameter
WHERE NAME = 'log_buffer';

SELECT * FROM v$sgastat WHERE pool IS NULL

SELECT * FROM v$sga;