1 -- Table sizes
2 SELECT owner,
3 TABLE_NAME,
4 bytes
5 FROM collectdu.c_tbl_size;
7 -- Tablespace sizes
8 SELECT tablespace_name,
9 bytes_free,
10 bytes_used
11 FROM collectdu.c_tbs_usage;
13 -- IO per Tablespace
14 SELECT SUM(vf.phyblkrd) *8192 AS
15 phy_blk_r,
16 SUM(vf.phyblkwrt) *8192 AS
17 phy_blk_w,
18 'tablespace' AS
19 i_prefix,
20 dt.tablespace_name
21 FROM((dba_data_files dd JOIN v$filestat vf ON dd.file_id = vf.file#) JOIN dba_tablespaces dt ON dd.tablespace_name = dt.tablespace_name)
22 GROUP BY dt.tablespace_name;
24 -- Buffer Pool Hit Ratio:
25 SELECT DISTINCT 100 *ROUND(1 -((MAX(decode(name, 'physical reads cache', VALUE))) /(MAX(decode(name, 'db block gets from cache', VALUE)) + MAX(decode(name, 'consistent gets from cache', VALUE)))), 4) AS
26 VALUE,
27 'BUFFER_CACHE_HIT_RATIO' AS
28 buffer_cache_hit_ratio
29 FROM v$sysstat;
31 -- Shared Pool Hit Ratio:
32 SELECT
33 100.0 * sum(PINHITS) / sum(pins) as VALUE,
34 'SHAREDPOOL_HIT_RATIO' AS SHAREDPOOL_HIT_RATIO
35 FROM V$LIBRARYCACHE;
37 -- PGA Hit Ratio:
38 SELECT VALUE,
39 'PGA_HIT_RATIO' AS
40 pga_hit_ratio
41 FROM v$pgastat
42 WHERE name = 'cache hit percentage';
44 -- DB Efficiency
45 SELECT ROUND(SUM(decode(metric_name, 'Database Wait Time Ratio', VALUE)), 2) AS
46 database_wait_time_ratio,
47 ROUND(SUM(decode(metric_name, 'Database CPU Time Ratio', VALUE)), 2) AS
48 database_cpu_time_ratio,
49 'DB_EFFICIENCY' AS
50 db_efficiency
51 FROM sys.v_$sysmetric
52 WHERE metric_name IN('Database CPU Time Ratio', 'Database Wait Time Ratio')
53 AND intsize_csec =
54 (SELECT MAX(intsize_csec)
55 FROM sys.v_$sysmetric);