oracle sql tuning 8--常用的檢視

oracle_db發表於2010-03-18
OracleSQL

 V$SQLAREA, V$SQL, V$SQLTEXT, and V$SQL_PLAN這4個檢視很重要

通過這些檢視我們能發現很多系統中的問題

v$sqlarea:

列出shared SQL area 中相關SQL的統計資訊，對於每個SQL都有一行來描述.這個檢視中重要的幾列

sql_text:列出SQL語句中的前1000個字元

sorts:排序總數

fetches:Number of fetches associated with the SQL statement

executions:總的執行次數

loads:物件被load,unload的次數

PARSE_CALLS:語句解析總次數

DISK_READS:磁碟讀的總次數

BUFFER_GETS:Sum of buffer gets over all child cursors

ROWS_PROCESSED:SQL語句已經處理過的總的行數

V$SQL

經常關注的列:

OPTIMIZER_COST:優化器提供的關於當前查詢所要消費的COST值

CPU_TIME:解析，執行，讀取當前SQL所需要的CPU消費值[單位：微秒]

ELAPSED_TIME:解析，執行，讀取當前SQL已經消耗時間[單位：微秒]

v$sqltext

包含完整的SQL指令碼

v$sql_plan

包含SQL語句的執行計劃

儲存近期執行的SQL執行計劃，EXPLAIN PLAN 命令生成的執行計劃是潛在的在plan_table中可以查詢.相反，v$sql_plan包含的是近期執行的SQL語句的執行計劃的一個真實情況或者說是實際情況.

以下是一些應用示例:

 

查詢指定使用者的SQL語句：

SELECT * FROM v$sqlarea WHERE parsing_user_id =
      (SELECT user_id FROM dba_users WHERE username =
         SYS);

找出指定使用者下指定多次的SQL

SELECT executions, disk_reads, buffer_gets, rows_processed,
   sorts, sql_text
FROM v$sqlarea WHERE parsing_user_id =
         (SELECT user_id FROM dba_users WHERE username =
            SYS)
ORDER BY executions DESC;

優化器的COST值,按降序排列

SELECT sa.rows_processed,
       s.optimizer_cost,
       s.cpu_time,
       s.elapsed_time,
       sa.sql_text
  FROM v$sqlarea sa, v$sql s
 WHERE sa.parsing_user_id =
       (SELECT user_id FROM dba_users WHERE username = 'SYS')   
   AND s.sql_text = sa.sql_text
 ORDER BY s.optimizer_cost DESC;

定位10條執行次數最多的語句:

SQL> SELECT *
  2    FROM (SELECT executions "Execs", rows_processed "Rows", sql_text
  3            FROM v$sqlarea
  4           WHERE parsing_user_id =
  5                 (SELECT user_id FROM dba_users WHERE username = 'SYS')
  6           ORDER BY executions DESC)
  7   WHERE ROWNUM <= 10;

     Execs       Rows SQL_TEXT
---------- ---------- --------------------------------------------------------------------------------
      4720       3437 select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, sample_
      2240          0 select job, nvl2(last_date, 1, 0) from sys.job$ where (((:1 <= next_date) and (n
      2238       2238 select count(*) from sys.job$ where (next_date > sysdate) and (next_date < (sysd
      2072       2072 SELECT COUNT(UNQ) UNQ, COUNT(PFX) PFX FROM (SELECT /*+ first_rows(1) leading(cc)
      1332      18954 select /*+ rule */ bucket, endpoint, col#, epvalue from histgrm$ where obj#=:1 a
      1331       2279 select pos#,intcol#,col#,spare1,bo#,spare2 from icol$ where obj#=:1
      1186       1181 select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.subname,o.dataobj#
      1133       1133 select value(p$) from "XDB"."XDB$RESOURCE"                 as of snapshot(:2) p$
      1133       1133 select sys_nc_oid$ from xdb.xdb$resource where rowid = :1
      1046       1010 update sys.col_usage$ set   equality_preds    = equality_preds    + decode(bitan

10 rows selected

SQL>

定位資料訪問最高的查詢[DISK+BUFFER READS PER ROW]

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as SYS

SQL>
SQL> SELECT *
  2    FROM (SELECT ROUND((disk_reads + buffer_gets) / rows_processed) "Reads/Row",
  3                 disk_reads + buffer_gets "Reads",
  4                 rows_processed "Rows",
  5                 sql_text
  6            FROM v$sqlarea
  7           WHERE parsing_user_id =
  8                 (SELECT user_id FROM dba_users WHERE username = 'SYS')
  9             AND rows_processed > 0
 10             AND (disk_reads + buffer_gets) > 0
 11           ORDER BY ROUND((disk_reads + buffer_gets) / rows_processed) DESC)
 12   WHERE ROWNUM <= 10;

 Reads/Row      Reads       Rows SQL_TEXT
---------- ---------- ---------- --------------------------------------------------------------------------------
      3337     106799         32 SELECT OWNER, SEGMENT_NAME, PARTITION_NAME, SEGMENT_TYPE, TABLESPACE_NAME, TABLE
      2208       6625          3 begin prvt_hdm.auto_execute( :db_id, :inst_id, :end_snap ); end;
      1479      35506         24  select count(*)  from user$ u, xdb.xdb$schema s  where u.user# = :1  and   u.na
      1334       1334          1 select value(p$) from "XDB"."XDB$CONFIG"                 as of snapshot(:2) p$ w
       758        758          1 SELECT S.TABLE_OWNER, S.TABLE_NAME FROM SYS.ALL_SYNONYMS S WHERE S.SYNONYM_NAME
       730        730          1 SELECT /*+ first_rows(1) */ '"'||OI.NAME||'"' FROM SYS.USER$ U, SYS.OBJ$ OT, SYS
       664        664          1 select /*+ no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_exact u
       664        664          1  select /*+ no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_exact
       624       1248          2 SELECT A.ID,A.TYPE FROM SYS.WRI$_ADV_DEFINITIONS A WHERE A.NAME = :B1
       496        496          1 select /*+ no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_exact u

10 rows selected

SQL>

定位排序最高的語句:

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as SYS

SQL>
SQL> SELECT *
  2    FROM (SELECT ROUND(rows_processed / sorts) "Rows/Sort",
  3                 rows_processed "Rows",
  4                 Sorts "Sorts",
  5                 sql_text
  6            FROM v$sqlarea
  7           WHERE parsing_user_id =
  8                 (SELECT user_id FROM dba_users WHERE username = 'SYS')
  9             AND rows_processed > 0
 10             AND sorts > 0
 11           ORDER BY ROUND(rows_processed / sorts) ASC)
 12   WHERE ROWNUM <= 10;

 Rows/Sort       Rows      Sorts SQL_TEXT
---------- ---------- ---------- --------------------------------------------------------------------------------
         0          1          6 SELECT S.TABLE_OWNER, S.TABLE_NAME FROM SYS.ALL_SYNONYMS S WHERE S.SYNONYM_NAME
         0          1         73 select col#,intcol#,ntab# from ntab$ where obj#=:1 order by intcol# asc
         0         38        125 select /*+ index(idl_char$ i_idl_char1) +*/ piece#,length,piece from idl_char$ w
         0         49        233 select grantee#,privilege#,nvl(col#,0),max(mod(nvl(option$,0),2))from objauth$ w
         0          1          3 SELECT INSTANCE_NAME, HOST_NAME, NVL(GVI_STARTUP_TIME, SYSTIMESTAMP) - INTERVAL
         0         34         73 select intcol#, toid, version#, intcols, intcol#s, flags, synobj# from subcoltyp
         0          3          8 select privilege#,nvl(col#,0),max(mod(nvl(option$,0),2))from objauth$ where obj#
         0          1         29 select procedure#,entrypoint# from procedurec$ where obj#=:1 order by procedure#
         0         20         73 select intcol#,type,flags,lobcol,objcol,extracol,schemaoid,  elemnum from opqtyp
         1          1          1 select substrb(dump(val,16,0,32),1,120) ep, cnt from (select /*+ no_parallel(t)

10 rows selected

SQL>

 

Rows per Fetch:

SQL>
SQL> SELECT *
  2    FROM (SELECT ROUND(rows_processed / fetches) "Rows/Fetch",
  3                 rows_processed "Rows",
  4                 fetches "Fetches",
  5                 sql_text
  6            FROM v$sqlarea
  7           WHERE parsing_user_id =
  8                 (SELECT user_id FROM dba_users WHERE username = 'SYS')
  9             AND rows_processed > 0
 10             AND fetches > 0
 11           ORDER BY ROUND(rows_processed / fetches) ASC)
 12   WHERE ROWNUM <= 10;

Rows/Fetch       Rows    Fetches SQL_TEXT
---------- ---------- ---------- --------------------------------------------------------------------------------
         0         37        110 select col#,intcol#,reftyp,stabid,expctoid from refcon$ where obj#=:1 order by i
         0         20         93 select intcol#,type,flags,lobcol,objcol,extracol,schemaoid,  elemnum from opqtyp
         0        101        207 select timestamp, flags from fixed_obj$ where obj#=:1
         0          1          6 WITH UNQIDX AS (SELECT /*+ index(cc) */ CD.CON#,CC.INTCOL# FROM SYS.CCOL$ CC, SY
         0          1        191 select con#,obj#,rcon#,enabled,nvl(defer,0) from cdef$ where robj#=:1
         0          1         74 select col#,intcol#,ntab# from ntab$ where obj#=:1 order by intcol# asc
         0          1          3 select grantee#, privilege#, max(nvl(option$,0)) from objauth$ where obj#=:1 gro
         0          2          9 SELECT O.OBJECT_TYPE, O.OWNER, O.OBJECT_NAME FROM SYS.ALL_OBJECTS O WHERE O.OWNE
         0        123        496 select parttype, partcnt, partkeycols, flags, defts#, defpctfree, defpctused, de
         0         38        163 select /*+ index(idl_char$ i_idl_char1) +*/ piece#,length,piece from idl_char$ w

10 rows selected

解析次數多的語句：多一般暗指缺少繫結變數

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as SYS

SQL>
SQL> SELECT *
  2    FROM (SELECT ROUND(parse_calls / executions) "Parses/Exec",
  3                 parse_calls "Parses",
  4                 executions "Execs",
  5                 sql_text
  6            FROM v$sqlarea
  7           WHERE parsing_user_id =
  8                 (SELECT user_id FROM dba_users WHERE username = 'SYS')
  9             AND parse_calls > 0
 10             AND executions > 0
 11           ORDER BY ROUND(parse_calls / executions) ASC)
 12   WHERE ROWNUM <= 10;

Parses/Exec     Parses      Execs SQL_TEXT
----------- ---------- ---------- --------------------------------------------------------------------------------
          0         54        491 select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.flags,i.property,i.pctf
          0          1         32 INSERT INTO SYS.WRI$_SEGADV_OBJLIST (OBJN, OBJD, REASON, REASON_VALUE, CREATION_
          0          2         11 SELECT COUNT(*) FROM SYS.WRI$_ADV_TASKS A WHERE A.NAME = :B3 AND BITAND(A.PROPER
          0          2          8 select privilege#,nvl(col#,0),max(mod(nvl(option$,0),2))from objauth$ where obj#
          0          2          9 UPDATE SYS.WRI$_ADV_TASKS SET MTIME = SYSDATE WHERE ID = :B1
          0          1         71 SELECT /*+ use_nl(u,o,t) */ NVL(T.DEGREE,1) FROM USER$ U,OBJ$ O,TAB$ T WHERE U.N
          0         87        231 select col#, grantee#, privilege#,max(mod(nvl(option$,0),2)) from objauth$ where
          0          3         12 select con# from con$ where owner#=:1 and name=:2
          0          2         70 INSERT INTO SYS.WRI$_ADV_PARAMETERS (TASK_ID,NAME,DATATYPE,VALUE,FLAGS,DESCRIPTI
          0         10         82 select bo#, intcol# from icoldep$ where obj#=:1

10 rows selected

Elapsed Time：已經執行的時間 
SELECT * FROM(
SELECT s.elapsed_time "Time", sa.rows_processed "Rows",
   sa.sql_text
FROM v$sqlarea sa, v$sql s
WHERE sa.parsing_user_id =
      (SELECT user_id FROM dba_users WHERE username =
         SYS)
AND s.sql_text = sa.sql_text
ORDER BY s.elapsed_time DESC
) WHERE ROWNUM <= 10;
CPU Time:cpu時間
SELECT * FROM(
SELECT s.cpu_time "CPU", sa.rows_processed "Rows",
   sa.sql_text
FROM v$sqlarea sa, v$sql s
WHERE sa.parsing_user_id =
      (SELECT user_id FROM dba_users WHERE username =
         'ACCOUNTS')
AND s.sql_text = sa.sql_text
ORDER BY s.cpu_time DESC
) WHERE ROWNUM <= 10;



 

                                                            

                    

                                                    來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/15720542/viewspace-629861/，如需轉載，請註明出處，否則將追究法律責任。
                                            


                




相關文章