查看oracle数据库里哪些语句耗时最长或者效率最低
CPU：
select *
from (select v.sql_id,
v.child_number,
v.sql_text,
v.elapsed_time,
v.cpu_time,
v.disk_reads,
rank() over(order by v.cpu_time desc) elapsed_rank
from v$sql v) a
where elapsed_rank <= 10;
磁盘：
select *
from (select v.sql_id,
v.child_number,
v.sql_text,
v.elapsed_time,
v.cpu_time,
v.disk_reads,
rank() over(order by v.disk_reads desc) elapsed_rank
from v$sql v) a
where elapsed_rank <= 10;
数据库管理员可以执行下述语句来查看SQL语句的解析情况：
SELECT * FROM V$SYSSTAT WHERE NAME IN ('parse_time_cpu','parse_time_elapsed','parse_count_ hard');
这里：
①parse_time_cpu：是系统服务时间。
②parse_time_elapsed：是响应时间。
而用户等待时间为：
waite_time = parse_time_elapsed – parse_time_cpu
（2）
数据库管理员还可以通过下述语句，查看低效率的SQL语句：
SELECT BUFFER_GETS,EXECUTIONS,SQL_TEXT FROM V$SQLAREA;
优化这些低效率的SQL语句也有助于提高CPU的利用率。
--- 查询每天执行慢的SQL：
SELECT S.SQL_TEXT,
S.SQL_FULLTEXT,
S.SQL_ID,
ROUND(ELAPSED_TIME / 1000000 / (CASE
WHEN (EXECUTIONS = 0 OR NVL(EXECUTIONS, 1 ) = 1) THEN
1
ELSE
EXECUTIONS
END),
2) "执行时间'S'",
S.EXECUTIONS "执行次数",
S.OPTIMIZER_COST "COST",
S.SORTS,
S.MODULE, --连接模式（JDBC THIN CLIENT：程序）
-- S.LOCKED_TOTAL,
S.PHYSICAL_READ_BYTES "物理读",
-- S.PHYSICAL_READ_REQUESTS "物理读请求",
S.PHYSICAL_WRITE_REQUESTS "物理写",
-- S.PHYSICAL_WRITE_BYTES "物理写请求",
S.ROWS_PROCESSED "返回行数",
S.DISK_READS "磁盘读",
S.DIRECT_WRITES "直接路径写",
S.PARSING_SCHEMA_NAME,
S.LAST_ACTIVE_TIME
FROM GV$SQLAREA S
WHERE ROUND(ELAPSED_TIME / 1000000 / (CASE
WHEN (EXECUTIONS = 0 OR NVL(EXECUTIONS, 1 ) = 1) THEN
1
ELSE
EXECUTIONS
END),
2) > 5 --100 0000微秒=1S
AND S.PARSING_SCHEMA_NAME = USER
AND TO_CHAR(S.LAST_LOAD_TIME, 'YYYY-MM-DD') =
TO_CHAR( SYSDATE, 'YYYY-MM-DD' )
AND S.COMMAND_TYPE IN (2 , 3, 5, 6 , 189)
ORDER BY "执行时间'S'" DESC;
/* SQL中 COMMAND_TYPE意义:
2：INSERT
3：SELECT
6：UPDATE
7：DELETE
189：MERGE
详情可通过查找V$SQLCOMMAND视图 */
V$SQLAREA 官网解释：http://docs.oracle.com/cd/E11882_01/server.112/e40402/dynviews_3064.htm#REFRN30259
V$SQLCOMMAND 官网解释：http://docs.oracle.com/cd/E11882_01/server.112/e40402/dynviews_3066.htm#REFRN30632