Oralce Database Health Check (Performance)
List Contents
1. 数据库概要
2. 参数文件(是spfile还是pfile)
3. 非默认的参数
4. 控制文件及其状态
5. 表空间及数据文件
6. 重做日志文件信息
7. 内存分配概况
8. Library Cache Reload Ratio(<1%)
9. Data Dictionary Miss Ratio(<15%)
10. 共享池使用概况
11. 共享池建议
12. DB Buffer Cache(Default) Hit Ratio(>90%)
13. DB Buffer Cache Advice
14. 磁盘排序(<5%)
15. Log Buffer latch Contention(<1%)
16. 表空间状态及其大小使用情况
17. 数据文件状态及其大小使用情况
18. 不使用临时文件的临时表空间
19. 无效的数据文件(offline)
20. 处于恢复模式的文件
21. 含有50个以上的Extent且30%以上碎片的表空间
22. 表空间上的I/O分布
23. 数据文件上的I/O分布
24. Next Extent 相对于段当前已分配字节过大(>=2倍)或过小(<10%)的Segments
25. Max Extents(>1)已经有90%被使用了的Segments
26. 已经分配超过100 Extents的Segments
27. 因表空间空间不够将导致不能扩展的Objects
28. 没有主键的非系统表
29. 没有索引的外键
30. 建有6个以上索引的非系统表
31. 指向对象不存在的Public同义词
32. 指向对象不存在的非Public同义词
33. 没有授予给任何角色和用户的角色
34. 将System表空间作为临时表空间的用户(除Sys外)
35. 将System表空间作为默认表空间的用户(除Sys外)
36. 没有授予给任何用户的profiles
37. 没有和Package相关联的Package Body
38. 被Disabled的约束
39. 被Disabled的触发器
40. Invalid Objects
41. 执行失败或中断的Jobs
42. 当前未执行且下一执行日期已经过去的Jobs
43. 含有未分析的非系统表的Schemas
44. 含有未分析的非系统分区表的Schemas
45. 含有未分析的非系统索引的Schemas
46. 含有未分析的非系统分区索引的Schemas
47. 死锁检测
48. top I/O Wait
49. top 10 wait
50. Top 10 bad SQL
51. Top most expensive SQL (Buffer Gets by Executions)
52. Top most expensive SQL (Physical Reads by Executions)
53. Top most expensive SQL (Rows Processed by Executions)
54. Top most expensive SQL (Buffer Gets vs Rows Processed)
1. 数据库概要
| DB Name | Global Name | Host Name | Instance Name | Restricted Mode | Archive Log Mode |
|---|---|---|---|---|---|
| ORCL10G | ORCL10G | CHINA-1257BBDF2 | orcl10g | NO | NOARCHIVELOG |
2. 参数文件(是spfile还是pfile)
| Parameter_File |
|---|
| F:\ORACLE\10.1.0\DATABASE\SPFILEORCL10G.ORA |
3. 非默认的参数
| NAME | pvalue |
|---|---|
| background_dump_dest | F:\ORACLE\ADMIN\ORCL10G\BDUMP |
| compatible | 10.1.0.2.0 |
| control_files | F:\ORACLE\ORADATA\ORCL10G\CONTROL01.CTL, F:\ORACLE\ORADATA\ORCL10G\CONTROL02.CTL, F:\ORACLE\ORADATA\ORCL10G\CONTROL03.CTL |
| core_dump_dest | F:\ORACLE\ADMIN\ORCL10G\CDUMP |
| db_block_size | 8192 |
| db_cache_size | 4194304 |
| db_domain | |
| db_file_multiblock_read_count | 8 |
| db_name | orcl10g |
| java_pool_size | 0 |
| job_queue_processes | 10 |
| large_pool_size | 4194304 |
| nls_language | SIMPLIFIED CHINESE |
| nls_territory | CHINA |
| open_cursors | 50 |
| pga_aggregate_target | 10485760 |
| processes | 20 |
| remote_login_passwordfile | EXCLUSIVE |
| shared_pool_size | 33554432 |
| sort_area_size | 65536 |
| undo_management | AUTO |
| undo_tablespace | UNDOTBS1 |
| user_dump_dest | F:\ORACLE\ADMIN\ORCL10G\UDUMP |
4. 控制文件及其状态
| NAME | STATUS |
|---|---|
| F:\ORACLE\ORADATA\ORCL10G\CONTROL01.CTL | |
| F:\ORACLE\ORADATA\ORCL10G\CONTROL02.CTL | |
| F:\ORACLE\ORADATA\ORCL10G\CONTROL03.CTL |
5. 表空间及数据文件
| TABLESPACE_NAME | FILE_NAME | Total Size(MB) | Auto |
|---|---|---|---|
| SYSAUX | F:\ORACLE\ORADATA\ORCL10G\SYSAUX01.DBF | 50 | YES |
| SYSTEM | F:\ORACLE\ORADATA\ORCL10G\SYSTEM01.DBF | 250 | YES |
| UNDOTBS1 | F:\ORACLE\ORADATA\ORCL10G\UNDOTBS01.DBF | 100 | YES |
| USERS | F:\ORACLE\ORADATA\ORCL10G\USERS01.DBF | 5 | YES |
6. 重做日志文件信息
| GROUP# | Redo File | TYPE | STATUS | Size(MB) |
|---|---|---|---|---|
| 1 | F:\ORACLE\ORADATA\ORCL10G\REDO01.LOG | ONLINE | CURRENT | 10 |
| 2 | F:\ORACLE\ORADATA\ORCL10G\REDO02.LOG | ONLINE | INACTIVE | 10 |
| 3 | F:\ORACLE\ORADATA\ORCL10G\REDO03.LOG | ONLINE | INACTIVE | 10 |
7. 内存分配概况
| NAME | value(Byte) |
|---|---|
| Fixed Size | 787388 |
| Variable Size | 40893508 |
| Database Buffers | 4194304 |
| Redo Buffers | 262144 |
| lock_sga | FALSE |
| large_pool_size | 4194304 |
| java_pool_size | 0 |
8. Library Cache Reload Ratio(<1%)
| LC_Reload_Ratio% |
|---|
| 1.0126 |
9. Data Dictionary Miss Ratio(<15%)
| DC_Miss_Ratio% |
|---|
| 23.667 |
10. 共享池使用概况
| Used(MB) | Size(MB) | Avail(MB) | Used(%) |
|---|---|---|---|
| 6.37 | 32 | 25.63 | 19.91 |
11. 共享池建议
| Shared Pool Size(estimate) | Factor | Libarary Cache Size | time Saved |
|---|---|---|---|
| 20 | 0.625 | 4 | 7 |
| 24 | 0.75 | 5 | 7 |
| 28 | 0.875 | 5 | 7 |
| 32 | 1 | 5 | 7 |
| 36 | 1.125 | 5 | 7 |
| 40 | 1.25 | 5 | 7 |
| 44 | 1.375 | 5 | 7 |
| 48 | 1.5 | 5 | 7 |
| 52 | 1.625 | 5 | 7 |
| 56 | 1.75 | 5 | 7 |
| 60 | 1.875 | 5 | 7 |
| 64 | 2 | 5 | 7 |
12. DB Buffer Cache(Default) Hit Ratio(>90%)
| BC_Hit _Ratio |
|---|
| 87.6955 |
13. DB Buffer Cache Advice
| Pool Name | BLOCK_SIZE | Buffer Size | Factor | Phy_Read_Factor | ESTD_PHY_READS |
|---|---|---|---|---|---|
| DEFAULT | 8192 | 4 | 1 | 1 | 1820 |
| DEFAULT | 8192 | 8 | 2 | 0.6078 | 1106 |
14. 磁盘排序(<5%)
| Sort(Disk) | Sort(Memory) | Disk_Sort_Ratio％ |
|---|---|---|
| 0 | 1357 | 0 |
15. Log Buffer latch Contention(<1%)
| Redo Name | GETS | MISSES | IMMEDIATE_GETS | IMMEDIATE_MISSES | Miss_Ratio％ | Immediate Misses Ratio% |
|---|---|---|---|---|---|---|
| redo copy | 10 | 0 | 160 | 0 | 0 | 0 |
| redo allocation | 260 | 0 | 160 | 0 | 0 | 0 |
16. 表空间状态及其大小使用情况
| Name | Status | Type | Size (MB) | Used (MB) | Used％ |
|---|---|---|---|---|---|
| SYSAUX | ONLINE | PERMANENT | 50.000 | 47.750 | 95.50 |
| SYSTEM | ONLINE | PERMANENT | 250.000 | 206.625 | 82.65 |
| UNDOTBS1 | ONLINE | UNDO | 100.000 | 4.750 | 4.75 |
| USERS | ONLINE | PERMANENT | 5.000 | 0.063 | 1.25 |
| TEMP | ONLINE | TEMPORARY | 0.000 | 0.000 | 0.00 |
17. 数据文件状态及其大小使用情况
| TableSpace Name | File Name | Status | Auto | Size (MB) | Used (MB) | Used % |
|---|---|---|---|---|---|---|
| SYSAUX | F:\ORACLE\ORADATA\ORCL10G\SYSAUX01.DBF | AVAILABLE | YES | 50.000 | 47.750 | 95.50 |
| SYSTEM | F:\ORACLE\ORADATA\ORCL10G\SYSTEM01.DBF | AVAILABLE | YES | 250.000 | 206.625 | 82.65 |
| UNDOTBS1 | F:\ORACLE\ORADATA\ORCL10G\UNDOTBS01.DBF | AVAILABLE | YES | 100.000 | 4.750 | 4.75 |
| USERS | F:\ORACLE\ORADATA\ORCL10G\USERS01.DBF | AVAILABLE | YES | 5.000 | 0.063 | 1.25 |
18. 不使用临时文件的临时表空间
| TABLESPACE_NAME | CONTENTS |
|---|
19. 无效的数据文件(offline)
| TABLESPACE_NAME | FILE_NAME | STATUS |
|---|
20. 处于恢复模式的文件
| TABLESPACE_NAME | FILE_NAME |
|---|
21. 含有50个以上的Extent且30%以上碎片的表空间
| TABLESPACE_NAME | PCT_FRAGMENTED | SEGMENTS | HOLES |
|---|
22. 表空间上的I/O分布
| TS_NAME | FILE_NAME | PHY_READS | PHY_BLOCKREADS | PHY_WRITES | PHY_BLOCKWRITES |
|---|---|---|---|---|---|
| SYSTEM | F:\ORACLE\ORADATA\ORCL10G\SYSTEM01.DBF | 1530 | 4011 | 69 | 77 |
| SYSAUX | F:\ORACLE\ORADATA\ORCL10G\SYSAUX01.DBF | 62 | 98 | 22 | 23 |
| UNDOTBS1 | F:\ORACLE\ORADATA\ORCL10G\UNDOTBS01.DBF | 43 | 43 | 35 | 36 |
| USERS | F:\ORACLE\ORADATA\ORCL10G\USERS01.DBF | 7 | 7 | 2 | 2 |
23. 数据文件上的I/O分布
| Table Space | File Name | Phys Rds | % Phys Rds | Phys Wrts | % Phys Wrts |
|---|---|---|---|---|---|
| SYSTEM | F:\ORACLE\ORADATA\ORCL10G\SYSTEM01.DBF | 1532 | 93.19 | 69 | 53.91 |
| UNDOTBS1 | F:\ORACLE\ORADATA\ORCL10G\UNDOTBS01.DBF | 43 | 2.62 | 35 | 27.34 |
| SYSAUX | F:\ORACLE\ORADATA\ORCL10G\SYSAUX01.DBF | 62 | 3.77 | 22 | 17.19 |
| USERS | F:\ORACLE\ORADATA\ORCL10G\USERS01.DBF | 7 | 0.43 | 2 | 1.56 |
24. Next Extent 相对于段当前已分配字节过大(>=2倍)或过小(<10%)的Segments
| Type | OWNER | SEGMENT_NAME | BYTES | NEXT_EXTENT | Percent(Next/Bytes) |
|---|
25. Max Extents(>1)已经有90%被使用了的Segments
| SEGMENT_TYPE | OWNER | SEGMENT_NAME | TABLESPACE_NAME | PARTITION_NAME | Size(MB) | EXTENTS | MAX_EXTENTS |
|---|
26. 已经分配超过100 Extents的Segments
| SEGMENT_TYPE | OWNER | SEGMENT_NAME | EXTENTS | PARTITION_NAME |
|---|
27. 因表空间空间不够将导致不能扩展的Objects
| TABLESPACE_NAME | OWNER | Segment Name | EXTENTS | NEXT_EXTENT_KB | TS_FREE_KB | TS_GROWTH_MB |
|---|
28. 没有主键的非系统表
| OWNER | TABLE_NAME |
|---|---|
| WMSYS | SYS_IOT_OVER_9255 |
| WMSYS | WM$ADT_FUNC_TABLE |
| WMSYS | WM$BATCH_COMPRESSIBLE_TABLES |
| WMSYS | WM$CONS_COLUMNS |
| WMSYS | WM$LOCKROWS_INFO |
| WMSYS | WM$MW_TABLE |
| WMSYS | WM$NEXTVER_TABLE |
| WMSYS | WM$REPLICATION_DETAILS_TABLE |
| WMSYS | WM$RIC_LOCKING_TABLE |
| WMSYS | WM$TMP_DBA_CONSTRAINTS |
| WMSYS | WM$WORKSPACE_PRIV_TABLE |
29. 没有索引的外键
| OWNER | TABLE_NAME | CONSTRAINT_NAME | COLUMN_NAME |
|---|
30. 建有6个以上索引的非系统表
| TABLE_OWNER | TABLE_NAME | INDEX_COUNT |
|---|
31. 指向对象不存在的Public同义词
| SYNONYM_NAME | TABLE_OWNER | TABLE_NAME |
|---|---|---|
| GV$TRANSPORTABLE_PLATFORM | SYS | GV$_TRANSPORTABLE_PLATFORM |
32. 指向对象不存在的非Public同义词
| OWNER | SYNONYM_NAME | TABLE_OWNER | TABLE_NAME |
|---|---|---|---|
| SYS | DEF$_SCHEDULE | SYSTEM | DEF$_SCHEDULE |
33. 没有授予给任何角色和用户的角色
| ROLE |
|---|
34. 将System表空间作为临时表空间的用户(除Sys外)
| USERNAME |
|---|
35. 将System表空间作为默认表空间的用户(除Sys外)
| USERNAME |
|---|
| SYSTEM |
| OUTLN |
36. 没有授予给任何用户的profiles
| PROFILE |
|---|
37. 没有和Package相关联的Package Body
| OWNER | OBJECT_NAME |
|---|
38. 被Disabled的约束
| OWNER | TABLE_NAME | CONSTRAINT_NAME | CONSTRAINT_TYPE |
|---|---|---|---|
| SYSTEM | LOGMNR_ATTRCOL$ | LOGMNR_ATTRCOL$_PK | P |
| SYSTEM | LOGMNR_ATTRIBUTE$ | LOGMNR_ATTRIBUTE$_PK | P |
| SYSTEM | LOGMNR_CCOL$ | LOGMNR_CCOL$_PK | P |
| SYSTEM | LOGMNR_CDEF$ | LOGMNR_CDEF$_PK | P |
| SYSTEM | LOGMNR_COL$ | LOGMNR_COL$_PK | P |
| SYSTEM | LOGMNR_COLTYPE$ | LOGMNR_COLTYPE$_PK | P |
| SYSTEM | LOGMNR_DICTIONARY$ | LOGMNR_DICTIONARY$_PK | P |
| SYSTEM | LOGMNR_DICTSTATE$ | LOGMNR_DICTSTATE$_PK | P |
| SYSTEM | LOGMNR_ICOL$ | LOGMNR_ICOL$_PK | P |
| SYSTEM | LOGMNR_IND$ | LOGMNR_IND$_PK | P |
| SYSTEM | LOGMNR_INDCOMPART$ | LOGMNR_INDCOMPART$_PK | P |
| SYSTEM | LOGMNR_INDPART$ | LOGMNR_INDPART$_PK | P |
| SYSTEM | LOGMNR_INDSUBPART$ | LOGMNR_INDSUBPART$_PK | P |
| SYSTEM | LOGMNR_LOB$ | LOGMNR_LOB$_PK | P |
| SYSTEM | LOGMNR_LOBFRAG$ | LOGMNR_LOBFRAG$_PK | P |
| SYSTEM | LOGMNR_OBJ$ | LOGMNR_OBJ$_PK | P |
| SYSTEM | LOGMNR_TAB$ | LOGMNR_TAB$_PK | P |
| SYSTEM | LOGMNR_TABCOMPART$ | LOGMNR_TABCOMPART$_PK | P |
| SYSTEM | LOGMNR_TABPART$ | LOGMNR_TABPART$_PK | P |
| SYSTEM | LOGMNR_TABSUBPART$ | LOGMNR_TABSUBPART$_PK | P |
| SYSTEM | LOGMNR_TS$ | LOGMNR_TS$_PK | P |
| SYSTEM | LOGMNR_TYPE$ | LOGMNR_TYPE$_PK | P |
| SYSTEM | LOGMNR_USER$ | LOGMNR_USER$_PK | P |
39. 被Disabled的触发器
| OWNER | TABLE_NAME | TRIGGER_NAME |
|---|---|---|
| SYS | NO_VM_ALTER | |
| SYS | NO_VM_CREATE | |
| SYS | NO_VM_DROP |
40. Invalid Objects
| OWNER | OBJECT_NAME | OBJECT_TYPE |
|---|
41. 执行失败或中断的Jobs
| JOB | Last Date | This Date | BROKEN | FAILURES | SCHEMA_USER | WHAT |
|---|
42. 当前未执行且下一执行日期已经过去的Jobs
| JOB | Last Date | This Date | BROKEN | FAILURES | SCHEMA_USER | WHAT |
|---|
43. 含有未分析的非系统表的Schemas
| Schema |
|---|
| WMSYS |
44. 含有未分析的非系统分区表的Schemas
| Schema |
|---|
45. 含有未分析的非系统索引的Schemas
| Schema |
|---|
| WMSYS |
46. 含有未分析的非系统分区索引的Schemas
| Schema |
|---|
47. 死锁检测
| TABLE_NAME | SESSION_ID | SERIAL# | ACTION | OSUSER | AP_PROCESS_ID | DB_PROCESS_ID |
|---|
48. top I/O Wait
| EVENT | SEGMENT_TYPE | SEGMENT_NAME | FILE_ID | BLOCK_ID | BLOCKS |
|---|
49. top 10 wait
| EVENT | Prev | Curr | Total |
|---|---|---|---|
| rdbms ipc message | 0 | 8 | 8 |
| Queue Monitor Wait | 0 | 1 | 1 |
| SQL*Net message to client | 1 | 0 | 1 |
| wakeup time manager | 0 | 1 | 1 |
| smon timer | 0 | 1 | 1 |
| pmon timer | 0 | 1 | 1 |
50. Top 10 bad SQL
| EXECUTIONS | SORTS | COMMAND_TYPE | DISK_READS | SQL_TEXT |
|---|---|---|---|---|
| 5 | 2 | 3 | 7779 | Select a.tablespace_name, a.owner, decode(a.partition_name, null, a.segment_name, a.segment_name || '.' || a.partition_name) "Segment Name", a.extents, round(next_extent/1024) next_extent_kb, round(b.free / 1024) ts_free_kb, round(c.morebytes / 1024 / 1024) ts_growth_mb from dba_segments a, (Select df.tablespace_name, nvl(max(fs.bytes), 0) free from dba_data_files df, dba_free_space fs where df.file_id = fs.file_id (+) group by df.tablespace_name) b, (Select tablespace_name, max(maxbytes - bytes) morebytes, sum(decode(AUTOEXTENSIBLE, 'YES', 1, 0)) autoextensible from dba_data_files group by tablespace_name) c where a.tablespace_name = b.tablespace_name and a.tablespace_name = c.tablespace_name and ((c.autoextensible = 0) or ((c.autoextensible > 0) and (a.next_extent > c.morebytes))) and a.next_extent > b.free order |
| 5 | 3 | 3 | 2025 | select s.tablespace_name, round(100 * f.hole_count / (f.hole_count + s.seg_count)) pct_fragmented, s.seg_count segments, f.hole_count holes from (Select tablespace_name, count(*) seg_count from dba_segments group by tablespace_name) s, (Select tablespace_name, count(*) hole_count from dba_free_space group by tablespace_name) f where s.tablespace_name = f.tablespace_name and s.tablespace_name in (Select tablespace_name from dba_tablespaces where contents = 'PERMANENT') And s.tablespace_name not in ('SYSTEM') and 100 * f.hole_count / (f.hole_count + s.seg_count) > 30 and s.seg_count > 50 |
| 5 | 1 | 3 | 2015 | Select InitCap(SEGMENT_TYPE) "Type", OWNER, SEGMENT_NAME, BYTES, NEXT_EXTENT, ROUND(100 * NEXT_EXTENT / BYTES) "Percent(Next/Bytes)" FROM DBA_SEGMENTS WHERE ((ROUND(100 * NEXT_EXTENT / BYTES) < 10) OR (ROUND(100 * NEXT_EXTENT / BYTES) >= 200)) AND SEGMENT_TYPE NOT IN ('ROLLBACK', 'TEMPORARY', 'CACHE', 'TYPE2 UNDO') order by 2,3,1 |
| 5 | 1 | 3 | 1939 | Select segment_type, owner, Segment_name, Tablespace_name, partition_name, round(bytes /1024/1024) "Size(MB)", extents, max_extents From dba_segments where segment_type not in ('ROLLBACK', 'TEMPORARY', 'CACHE', 'TYPE2 UNDO') and extents >= (1 - ( 10 / 100)) * max_extents and max_extents > 1 order by bytes / max_extents desc |
| 5 | 0 | 3 | 1899 | Select segment_type, owner, segment_name, extents, partition_name from dba_segments where segment_type not in ('ROLLBACK', 'TEMPORARY', 'CACHE', 'TYPE2 UNDO') and owner not in ('SYS', 'SYSTEM', 'OUTLN', 'DBSNMP', 'ORDSYS', 'ORDPLUGINS', 'MDSYS', 'CTXSYS', 'AURORA$ORB$UNAUTHENTICATED', 'XDB') and extents > 100 |
| 5 | 3 | 3 | 1066 | SELECT acc.owner,acc.table_name,acc.constraint_name,acc.column_name FROM all_cons_columns acc, all_constraints ac WHERE ac.constraint_name = acc.constraint_name AND ac.constraint_type = 'R' and acc.owner not in ('SYS','SYSTEM') AND (acc.owner, acc.table_name, acc.column_name, acc.position) IN (SELECT acc.owner, acc.table_name, acc.column_name, acc.position FROM all_cons_columns acc, all_constraints ac WHERE ac.constraint_name = acc.constraint_name AND ac.constraint_type = 'R' MINUS SELECT table_owner, table_name, column_name, column_position FROM all_ind_columns) ORDER BY acc.owner,acc.table_name, acc.constraint_name,acc.column_name |
| 5 | 3 | 3 | 549 | Select owner, table_name from dba_tables where owner not in ('SYS', 'SYSTEM', 'OUTLN', 'DBSNMP', 'ORDSYS', 'ORDPLUGINS', 'MDSYS', 'CTXSYS', 'AURORA$ORB$UNAUTHENTICATED', 'XDB') minus Select owner, table_name from dba_constraints where constraint_type = 'P' and owner not in ('SYS', 'SYSTEM', 'OUTLN', 'DBSNMP', 'ORDSYS', 'ORDPLUGINS', 'MDSYS', 'CTXSYS', 'AURORA$ORB$UNAUTHENTICATED', 'XDB') |
| 5 | 2 | 3 | 485 | Select table_owner, table_name, count(*) index_count from dba_indexes where table_owner not in ('SYS', 'SYSTEM', 'OUTLN', 'DBSNMP', 'ORDSYS', 'ORDPLUGINS', 'MDSYS', 'CTXSYS', 'AURORA$ORB$UNAUTHENTICATED', 'XDB') having count(*) > 6 group by table_owner, table_name order by 3 desc |
| 5 | 1 | 3 | 398 | Select distinct owner "Schema" from DBA_indexes where leaf_blocks is null and owner not in ('SYS', 'SYSTEM', 'OUTLN', 'DBSNMP', 'ORDSYS', 'ORDPLUGINS', 'MDSYS', 'CTXSYS', 'AURORA$ORB$UNAUTHENTICATED', 'XDB') order by 1 |
51. Top most expensive SQL (Buffer Gets by Executions)
| BUFFER_GETS | EXECUTIONS | GETS_PER_EXEC | HASH_VALUE | SQL_TEXT |
|---|---|---|---|---|
| 1117445 | 1 | 1117445 | 2096533749 | Select s.synonym_name, s.table_owner, s.table_name from sys.DBA_synonyms s where not exists (Select 'x' from sys.DBA_objects o where o.owner = s.table_owner and o.object_name = s.table_name) and db_link is null and s.owner = 'PUBLIC' order by 1 |
| 216328 | 1 | 216328 | 2308518879 | Select pb.owner, pb.object_name from dba_objects pb where pb.object_type = 'PACKAGE BODY' and not exists (Select 1 from dba_objects p where p.object_type = 'PACKAGE' and p.owner = pb.owner and p.object_name = pb.object_name) order by 1,2 |
52. Top most expensive SQL (Physical Reads by Executions)
| DISK_READS | EXECUTIONS | READS_PER_EXEC | HASH_VALUE | SQL_TEXT |
|---|
53. Top most expensive SQL (Rows Processed by Executions)
| ROWS_PROCESSED | EXECUTIONS | ROWS_PER_EXEC | HASH_VALUE | SQL_TEXT |
|---|
54. Top most expensive SQL (Buffer Gets vs Rows Processed)
| BUFFER_GETS | rows_processed | EXECUTIONS | LOADS | AVG_COST | SQL_TEXT |
|---|---|---|---|---|---|
| 1117445 | 1 | 1 | 1 | 1117445 | Select s.synonym_name, s.table_owner, s.table_name from sys.DBA_synonyms s where not exists (Select 'x' from sys.DBA_objects o where o.owner = s.table_owner and o.object_name = s.table_name) and db_link is null and s.owner = 'PUBLIC' order by 1 |
| 216328 | 0 | 1 | 1 | 216328 | Select pb.owner, pb.object_name from dba_objects pb where pb.object_type = 'PACKAGE BODY' and not exists (Select 1 from dba_objects p where p.object_type = 'PACKAGE' and p.owner = pb.owner and p.object_name = pb.object_name) order by 1,2 |
| 17191 | 0 | 1 | 1 | 17191 | Select InitCap(SEGMENT_TYPE) "Type", OWNER, SEGMENT_NAME, BYTES, NEXT_EXTENT, ROUND(100 * NEXT_EXTENT / BYTES) "Percent(Next/Bytes)" FROM DBA_SEGMENTS WHERE ((ROUND(100 * NEXT_EXTENT / BYTES) < 10) OR (ROUND(100 * NEXT_EXTENT / BYTES) >= 200)) AND SEGMENT_TYPE NOT IN ('ROLLBACK', 'TEMPORARY', 'CACHE', 'TYPE2 UNDO') order by 2,3,1 |
| 15335 | 0 | 1 | 1 | 15335 | Select segment_type, owner, Segment_name, Tablespace_name, partition_name, round(bytes /1024/1024) "Size(MB)", extents, max_extents From dba_segments where segment_type not in ('ROLLBACK', 'TEMPORARY', 'CACHE', 'TYPE2 UNDO') and extents >= (1 - ( 10 / 100)) * max_extents and max_extents > 1 order by bytes / max_extents desc |
| 11712 | 0 | 1 | 1 | 11712 | Select segment_type, owner, segment_name, extents, partition_name from dba_segments where segment_type not in ('ROLLBACK', 'TEMPORARY', 'CACHE', 'TYPE2 UNDO') and owner not in ('SYS', 'SYSTEM', 'OUTLN', 'DBSNMP', 'ORDSYS', 'ORDPLUGINS', 'MDSYS', 'CTXSYS', 'AURORA$ORB$UNAUTHENTICATED', 'XDB') and extents > 100 |