--1、消耗时间最多的前10条SQL语句;
SELECT round(elapsed_time / 1000, 2) "Elapsed Time (ms)",
executions,
round(elapsed_time / executions / 1000, 2) "Elapsed Time per Exec (ms)",
parsing_schema_name "username",
sql_id,
sql_fulltext
FROM (SELECT parsing_schema_name,
sql_id,
sql_fulltext,
executions,
elapsed_time,
rank() over(ORDER BY elapsed_time DESC NULLS LAST) elapsed_time_rank
FROM v$sql
WHERE parsing_schema_name NOT IN
('SYS',
'SYSTEM',
'SYSMAN',
'ORACLE_OCM',
'EXFSYS',
'DBSNMP',
'MDSYS',
'SINO_DBA',
'ADMIN',
'APEX_030200'))
WHERE elapsed_time_rank <= 10;
--2、消耗内存资源最多的前10条SQL语句;
SELECT buffer_gets,
executions,
round(buffer_gets / decode(executions, 0, 1, executions), 0) "Gets per Exec",
round(elapsed_time / 1000, 2) "elapsed TIME(ms)",
parsing_schema_name "username",
sql_fulltext,
sql_id
FROM (SELECT parsing_schema_name,
sql_id,
sql_fulltext,
executions,
buffer_gets,
elapsed_time,
dense_rank() over(ORDER BY buffer_gets DESC NULLS LAST) buffer_gets_rank
FROM v$sql t
WHERE parsing_schema_name NOT IN
('SYS',
'SYSTEM',
'SYSMAN',
'ORACLE_OCM',
'EXFSYS',
'DBSNMP',
'MDSYS',
'SINO_DBA',
'ADMIN',
'APEX_030200'))
WHERE buffer_gets_rank <= 10;
--3、消耗I/O资源最多的前10条SQL语句;
SELECT disk_reads,
executions,
round(disk_reads / decode(executions, 0, 1, executions), 0) "Reads per Exec",
round(elapsed_time / 1000, 2) "elapsed TIME(ms)",
parsing_schema_name "username",
sql_fulltext,
sql_id
FROM (SELECT parsing_schema_name,
sql_id,
sql_fulltext,
executions,
disk_reads,
elapsed_time,
dense_rank() over(ORDER BY disk_reads DESC NULLS LAST) disk_reads_rank
FROM v$sql t
WHERE parsing_schema_name NOT IN
('SYS',
'SYSTEM',
'SYSMAN',
'ORACLE_OCM',
'EXFSYS',
'DBSNMP',
'MDSYS',
'SINO_DBA',
'ADMIN',
'APEX_030200'))
WHERE disk_reads_rank <= 10;
--4、执行频率最多的前10条SQL语句;
SELECT executions,
rows_processed,
round(rows_processed / decode(executions, 0, 1, executions), 0) "Rows per Exec",
round(elapsed_time / 1000, 2) "Elapsed Time (ms)",
parsing_schema_name "username",
sql_fulltext,
sql_id
FROM (SELECT sql_id,
sql_fulltext,
executions,
rows_processed,
elapsed_time,
parsing_schema_name,
rank() over(ORDER BY executions DESC NULLS LAST) exec_rank
FROM v$sql
WHERE parsing_schema_name NOT IN
('SYS',
'SYSTEM',
'SYSMAN',
'ORACLE_OCM',
'EXFSYS',
'DBSNMP',
'MDSYS',
'SINO_DBA',
'ADMIN',
'APEX_030200'))
WHERE exec_rank <= 10;
---5、解析比最高的前10条SQL语句;
SELECT parse_calls,
executions,
round(elapsed_time / 1000, 2) "Elapsed Time (ms)",
parsing_schema_name "username",
sql_fulltext,
sql_id
FROM (SELECT sql_id,
sql_fulltext,
executions,
parse_calls,
elapsed_time,
parsing_schema_name,
rank() over(ORDER BY parse_calls DESC NULLS LAST) parse_calls_rank
FROM v$sql
WHERE parsing_schema_name NOT IN
('SYS',
'SYSTEM',
'SYSMAN',
'ORACLE_OCM',
'EXFSYS',
'DBSNMP',
'MDSYS',
'SINO_DBA',
'ADMIN',
'APEX_030200'))
WHERE parse_calls_rank <= 10;
--6、排序次数最多的前10条SQL语句;
SELECT sorts,
executions,
round(elapsed_time / 1000, 2) "Elapsed Time (ms)",
parsing_schema_name "username",
sql_fulltext,
sql_id
FROM (SELECT sql_id,
sql_fulltext,
executions,
sorts,
elapsed_time,
parsing_schema_name,
rank() over(ORDER BY sorts DESC NULLS LAST) sorts_rank
FROM v$sql
WHERE parsing_schema_name NOT IN
('SYS',
'SYSTEM',
'SYSMAN',
'ORACLE_OCM',
'EXFSYS',
'DBSNMP',
'MDSYS',
'SINO_DBA',
'ADMIN',
'APEX_030200'))
WHERE sorts_rank <= 10;
--7、Cost成本值最大的前10条SQL语句;
SELECT optimizer_cost,
executions,
round(elapsed_time / 1000, 2) "Elapsed Time (ms)",
parsing_schema_name "username",
sql_fulltext,
sql_id
FROM (SELECT sql_id,
sql_fulltext,
executions,
optimizer_cost,
elapsed_time,
parsing_schema_name,
rank() over(ORDER BY optimizer_cost DESC NULLS LAST) optimizer_cost_rank
FROM v$sql
WHERE parsing_schema_name NOT IN
('SYS',
'SYSTEM',
'SYSMAN',
'ORACLE_OCM',
'EXFSYS',
'DBSNMP',
'MDSYS',
'SINO_DBA',
'ADMIN',
'APEX_030200'))
WHERE optimizer_cost_rank <= 10;
--8、查看表空间分配与空闲空间情况;
SELECT a.tablespace_name,
a.file_name,
round(a.bytes / 1024 / 1024, 0) allocated_mbytes,
b.free_mbytes
FROM dba_data_files a,
(SELECT file_id, round(SUM(bytes) / 1024 / 1024, 0) free_mbytes
FROM dba_free_space b
GROUP BY file_id) b
WHERE a.file_id = b.file_id
ORDER BY a.tablespace_name;
--9、显示表对象锁的会话信息;
SELECT o.owner,
o.object_name,
o.object_type,
s.sid,
s.serial#,
s.machine,
s.program,
s.osuser
FROM v$session s, v$locked_object l, dba_objects o
WHERE l.session_id = s.sid
AND l.object_id = o.object_id(+);
--10、显示数据库归档日志定时备份情况信息;
SELECT btype,
btype_key,
sequence#,
first_change#,
next_change#,
filesize,
filesize_display
FROM v$backup_archivelog_details t
ORDER BY sequence#;
--11、显示数据库数据文件定时备份情况信息;
SELECT btype,
btype_key,
file#,
incremental_level,
checkpoint_time,
filesize,
tsname,
filesize_display
FROM v$backup_datafile_details
ORDER BY btype_key,file#;
SELECT round(elapsed_time / 1000, 2) "Elapsed Time (ms)",
executions,
round(elapsed_time / executions / 1000, 2) "Elapsed Time per Exec (ms)",
parsing_schema_name "username",
sql_id,
sql_fulltext
FROM (SELECT parsing_schema_name,
sql_id,
sql_fulltext,
executions,
elapsed_time,
rank() over(ORDER BY elapsed_time DESC NULLS LAST) elapsed_time_rank
FROM v$sql
WHERE parsing_schema_name NOT IN
('SYS',
'SYSTEM',
'SYSMAN',
'ORACLE_OCM',
'EXFSYS',
'DBSNMP',
'MDSYS',
'SINO_DBA',
'ADMIN',
'APEX_030200'))
WHERE elapsed_time_rank <= 10;
--2、消耗内存资源最多的前10条SQL语句;
SELECT buffer_gets,
executions,
round(buffer_gets / decode(executions, 0, 1, executions), 0) "Gets per Exec",
round(elapsed_time / 1000, 2) "elapsed TIME(ms)",
parsing_schema_name "username",
sql_fulltext,
sql_id
FROM (SELECT parsing_schema_name,
sql_id,
sql_fulltext,
executions,
buffer_gets,
elapsed_time,
dense_rank() over(ORDER BY buffer_gets DESC NULLS LAST) buffer_gets_rank
FROM v$sql t
WHERE parsing_schema_name NOT IN
('SYS',
'SYSTEM',
'SYSMAN',
'ORACLE_OCM',
'EXFSYS',
'DBSNMP',
'MDSYS',
'SINO_DBA',
'ADMIN',
'APEX_030200'))
WHERE buffer_gets_rank <= 10;
--3、消耗I/O资源最多的前10条SQL语句;
SELECT disk_reads,
executions,
round(disk_reads / decode(executions, 0, 1, executions), 0) "Reads per Exec",
round(elapsed_time / 1000, 2) "elapsed TIME(ms)",
parsing_schema_name "username",
sql_fulltext,
sql_id
FROM (SELECT parsing_schema_name,
sql_id,
sql_fulltext,
executions,
disk_reads,
elapsed_time,
dense_rank() over(ORDER BY disk_reads DESC NULLS LAST) disk_reads_rank
FROM v$sql t
WHERE parsing_schema_name NOT IN
('SYS',
'SYSTEM',
'SYSMAN',
'ORACLE_OCM',
'EXFSYS',
'DBSNMP',
'MDSYS',
'SINO_DBA',
'ADMIN',
'APEX_030200'))
WHERE disk_reads_rank <= 10;
--4、执行频率最多的前10条SQL语句;
SELECT executions,
rows_processed,
round(rows_processed / decode(executions, 0, 1, executions), 0) "Rows per Exec",
round(elapsed_time / 1000, 2) "Elapsed Time (ms)",
parsing_schema_name "username",
sql_fulltext,
sql_id
FROM (SELECT sql_id,
sql_fulltext,
executions,
rows_processed,
elapsed_time,
parsing_schema_name,
rank() over(ORDER BY executions DESC NULLS LAST) exec_rank
FROM v$sql
WHERE parsing_schema_name NOT IN
('SYS',
'SYSTEM',
'SYSMAN',
'ORACLE_OCM',
'EXFSYS',
'DBSNMP',
'MDSYS',
'SINO_DBA',
'ADMIN',
'APEX_030200'))
WHERE exec_rank <= 10;
---5、解析比最高的前10条SQL语句;
SELECT parse_calls,
executions,
round(elapsed_time / 1000, 2) "Elapsed Time (ms)",
parsing_schema_name "username",
sql_fulltext,
sql_id
FROM (SELECT sql_id,
sql_fulltext,
executions,
parse_calls,
elapsed_time,
parsing_schema_name,
rank() over(ORDER BY parse_calls DESC NULLS LAST) parse_calls_rank
FROM v$sql
WHERE parsing_schema_name NOT IN
('SYS',
'SYSTEM',
'SYSMAN',
'ORACLE_OCM',
'EXFSYS',
'DBSNMP',
'MDSYS',
'SINO_DBA',
'ADMIN',
'APEX_030200'))
WHERE parse_calls_rank <= 10;
--6、排序次数最多的前10条SQL语句;
SELECT sorts,
executions,
round(elapsed_time / 1000, 2) "Elapsed Time (ms)",
parsing_schema_name "username",
sql_fulltext,
sql_id
FROM (SELECT sql_id,
sql_fulltext,
executions,
sorts,
elapsed_time,
parsing_schema_name,
rank() over(ORDER BY sorts DESC NULLS LAST) sorts_rank
FROM v$sql
WHERE parsing_schema_name NOT IN
('SYS',
'SYSTEM',
'SYSMAN',
'ORACLE_OCM',
'EXFSYS',
'DBSNMP',
'MDSYS',
'SINO_DBA',
'ADMIN',
'APEX_030200'))
WHERE sorts_rank <= 10;
--7、Cost成本值最大的前10条SQL语句;
SELECT optimizer_cost,
executions,
round(elapsed_time / 1000, 2) "Elapsed Time (ms)",
parsing_schema_name "username",
sql_fulltext,
sql_id
FROM (SELECT sql_id,
sql_fulltext,
executions,
optimizer_cost,
elapsed_time,
parsing_schema_name,
rank() over(ORDER BY optimizer_cost DESC NULLS LAST) optimizer_cost_rank
FROM v$sql
WHERE parsing_schema_name NOT IN
('SYS',
'SYSTEM',
'SYSMAN',
'ORACLE_OCM',
'EXFSYS',
'DBSNMP',
'MDSYS',
'SINO_DBA',
'ADMIN',
'APEX_030200'))
WHERE optimizer_cost_rank <= 10;
--8、查看表空间分配与空闲空间情况;
SELECT a.tablespace_name,
a.file_name,
round(a.bytes / 1024 / 1024, 0) allocated_mbytes,
b.free_mbytes
FROM dba_data_files a,
(SELECT file_id, round(SUM(bytes) / 1024 / 1024, 0) free_mbytes
FROM dba_free_space b
GROUP BY file_id) b
WHERE a.file_id = b.file_id
ORDER BY a.tablespace_name;
--9、显示表对象锁的会话信息;
SELECT o.owner,
o.object_name,
o.object_type,
s.sid,
s.serial#,
s.machine,
s.program,
s.osuser
FROM v$session s, v$locked_object l, dba_objects o
WHERE l.session_id = s.sid
AND l.object_id = o.object_id(+);
--10、显示数据库归档日志定时备份情况信息;
SELECT btype,
btype_key,
sequence#,
first_change#,
next_change#,
filesize,
filesize_display
FROM v$backup_archivelog_details t
ORDER BY sequence#;
--11、显示数据库数据文件定时备份情况信息;
SELECT btype,
btype_key,
file#,
incremental_level,
checkpoint_time,
filesize,
tsname,
filesize_display
FROM v$backup_datafile_details
ORDER BY btype_key,file#;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29439655/viewspace-1378538/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29439655/viewspace-1378538/