最近公司在搞SQL优化,想了解一下SQL执行的效率。
请问一下如何查看已经执行过的SQL时间以及等待时间?
不要用SET TIMING ON,这种用法哦,这种方式是在已知SQL的情况下执行的,不适合我的回答!
请问一下如何查看已经执行过的SQL时间以及等待时间?
不要用SET TIMING ON,这种用法哦,这种方式是在已知SQL的情况下执行的,不适合我的回答!
解决方案 »
- 不显示删除回复显示所有回复显示星级回复显示得分回复 RedHat 5 安装 Oracle10g,运行runinstaller 出错(带乱码
- 如何在function中传递not in 参数
- 关于存储过程传值的问题
- 老大们救救我吧,oracle客户端打包问题
- 救救我呀。我不小心把一个表中数据删除了。
- 初学者的三个郁闷的问题?
- 在WINXP_PRO上能安装ORACLE????
- 帮忙看两个SQL语句
- 新人问两个题目 求大神解答下 救急
- oracle触发器,一张表在录入时,一个字段的值如何直接取另一个表字段的值
- Oracle 在什么情况下,OMS自动重建物化视图
- server2005链接服务器访问oracle,数据查询
这个视图是只查看正在执行的会话,历史的SQL有记录么?
呵呵,您知道的确实很多。
V$SQL lists statistics on shared SQL area without the GROUP BY clause and contains one row for each child of the original SQL text entered. Statistics displayed in V$SQL are normally updated at the end of query execution. However, for long running queries, they are updated every 5 seconds. This makes it easy to see the impact of long running SQL statements while they are still in progress.
我问的是查看SQL的执行时间哦
set pagesize 100
SELECT * FROM
(SELECT substr(sql_text,1,40) sql,
buffer_gets, executions, buffer_gets/executions "Gets/Exec",
hash_value,address
FROM V$SQLAREA
WHERE buffer_gets > 10000
ORDER BY buffer_gets DESC)
WHERE rownum <= 10
;Top 10 by Physical Reads:set linesize 100
set pagesize 100
SELECT * FROM
(SELECT substr(sql_text,1,40) sql,
disk_reads, executions, disk_reads/executions "Reads/Exec",
hash_value,address
FROM V$SQLAREA
WHERE disk_reads > 1000
ORDER BY disk_reads DESC)
WHERE rownum <= 10
;Top 10 by Executions:set linesize 100
set pagesize 100
SELECT * FROM
(SELECT substr(sql_text,1,40) sql,
executions, rows_processed, rows_processed/executions "Rows/Exec",
hash_value,address
FROM V$SQLAREA
WHERE executions > 100
ORDER BY executions DESC)
WHERE rownum <= 10
;Top 10 by Parse Calls:set linesize 100
set pagesize 100
SELECT * FROM
(SELECT substr(sql_text,1,40) sql,
parse_calls, executions, hash_value,address
FROM V$SQLAREA
WHERE parse_calls > 1000
ORDER BY parse_calls DESC)
WHERE rownum <= 10
;Top 10 by Sharable Memory:set linesize 100
set pagesize 100
SELECT * FROM
(SELECT substr(sql_text,1,40) sql,
sharable_mem, executions, hash_value,address
FROM V$SQLAREA
WHERE sharable_mem > 1048576
ORDER BY sharable_mem DESC)
WHERE rownum <= 10
;Top 10 by Version Count:set linesize 100
set pagesize 100
SELECT * FROM
(SELECT substr(sql_text,1,40) sql,
version_count, executions, hash_value,address
FROM V$SQLAREA
WHERE version_count > 20
ORDER BY version_count DESC)
WHERE rownum <= 10
;
SQL优化的话,建议去下载些相关的书还看下!一贴很难讲清!