ORACLE TOP SQL
1 buffer cache
select substr(sql_text,1,500) "SQL",sql_fulltext,
(cpu_time/1000000) "CPU_Seconds",
(cpu_time/1000000)/(nvl(replace(executions,0,1),1)) "CPU_Seconds/executions",
disk_reads "Disk_Reads",
buffer_gets "Buffer_Gets",
executions "Executions",
case when rows_processed = 0 then null
else (buffer_gets/nvl(replace(rows_processed,0,1),1))
end "Buffer_gets/rows_proc",
(buffer_gets/nvl(replace(executions,0,1),1)) "Buffer_gets/executions",
(elapsed_time/1000000) "Elapsed_Seconds",
(elapsed_time/1000000)/(nvl(replace(executions,0,1),1)) "Elapsed_Seconds/executions",
module "Module",
s.PARSING_SCHEMA_NAME
from v$sql s
order by buffer_gets desc nulls last
2 buffer cache（每次)
select substr(sql_text,1,500) "SQL",sql_fulltext,
(cpu_time/1000000) "CPU_Seconds",
(cpu_time/1000000)/(nvl(replace(executions,0,1),1)) "CPU_Seconds/executions",
disk_reads "Disk_Reads",
buffer_gets "Buffer_Gets",
executions "Executions",
case when rows_processed = 0 then null
else (buffer_gets/nvl(replace(rows_processed,0,1),1))
end "Buffer_gets/rows_proc",
(buffer_gets/nvl(replace(executions,0,1),1)) "Buffer_gets/executions",
(elapsed_time/1000000) "Elapsed_Seconds",
(elapsed_time/1000000)/(nvl(replace(executions,0,1),1)) "Elapsed_Seconds/executions",
module "Module",
s.PARSING_SCHEMA_NAME
from v$sql s
order by buffer_gets/nvl(replace(executions,0,1),1) desc nulls last
3 执行时间(每次)
select substr(sql_text,1,500) "SQL",sql_fulltext,
(cpu_time/1000000) "CPU_Seconds",
(cpu_time/1000000)/(nvl(replace(executions,0,1),1)) "CPU_Seconds/executions",
disk_reads "Disk_Reads",
buffer_gets "Buffer_Gets",
executions "Executions",
case when rows_processed = 0 then null
else (buffer_gets/nvl(replace(rows_processed,0,1),1))
end "Buffer_gets/rows_proc",
(buffer_gets/nvl(replace(executions,0,1),1)) "Buffer_gets/executions",
(elapsed_time/1000000) "Elapsed_Seconds",
(elapsed_time/1000000)/(nvl(replace(executions,0,1),1)) "Elapsed_Seconds/executions",
module "Module"
from v$sql s
order by (elapsed_time/1000000)/(nvl(replace(executions,0,1),1)) desc nulls last
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/15747463/viewspace-751400/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
[装载]oracle 无归档的不完全恢复
下一篇：
ORACLE移动数据文件
请登录后发表评论
登录
全部评论
最新文章
- 【kylin】Failed to build cube
- Greenplum 数据分布黄金法则 - 论分布列与分区的选择
- oracle 11g_调度程序维护任务或 Autotasks
- How to Find which Session is Holding a Particular Library Cache Lock
- 修改系统时间对oracle数据库的影响
- oracle控制文件复制、移动方式
- How To Move Datafiles On AIX Using Raw Logical Volumes To A New Location?
- ASMM/AMM开启时动态调整内存组件(shared_pool_size, db_cache_size等)大小
- RAC中通过nkmod解决各个节点盘符不一致问题
- full backup 与 level 0 incremental backup
