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：已经运行的时间CPU Time:cpu时间
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/15720542/viewspace-629861/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/15720542/viewspace-629861/
556
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
