mirror of https://github.com/sipwise/collectd.git
You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
56 lines
1.5 KiB
56 lines
1.5 KiB
-- Table sizes
|
|
SELECT owner,
|
|
TABLE_NAME,
|
|
bytes
|
|
FROM collectdu.c_tbl_size;
|
|
|
|
-- Tablespace sizes
|
|
SELECT tablespace_name,
|
|
bytes_free,
|
|
bytes_used
|
|
FROM collectdu.c_tbs_usage;
|
|
|
|
-- IO per Tablespace
|
|
SELECT SUM(vf.phyblkrd) *8192 AS
|
|
phy_blk_r,
|
|
SUM(vf.phyblkwrt) *8192 AS
|
|
phy_blk_w,
|
|
'tablespace' AS
|
|
i_prefix,
|
|
dt.tablespace_name
|
|
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)
|
|
GROUP BY dt.tablespace_name;
|
|
|
|
-- Buffer Pool Hit Ratio:
|
|
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
|
|
VALUE,
|
|
'BUFFER_CACHE_HIT_RATIO' AS
|
|
buffer_cache_hit_ratio
|
|
FROM v$sysstat;
|
|
|
|
-- Shared Pool Hit Ratio:
|
|
SELECT
|
|
100.0 * sum(PINHITS) / sum(pins) as VALUE,
|
|
'SHAREDPOOL_HIT_RATIO' AS SHAREDPOOL_HIT_RATIO
|
|
FROM V$LIBRARYCACHE;
|
|
|
|
-- PGA Hit Ratio:
|
|
SELECT VALUE,
|
|
'PGA_HIT_RATIO' AS
|
|
pga_hit_ratio
|
|
FROM v$pgastat
|
|
WHERE name = 'cache hit percentage';
|
|
|
|
-- DB Efficiency
|
|
SELECT ROUND(SUM(decode(metric_name, 'Database Wait Time Ratio', VALUE)), 2) AS
|
|
database_wait_time_ratio,
|
|
ROUND(SUM(decode(metric_name, 'Database CPU Time Ratio', VALUE)), 2) AS
|
|
database_cpu_time_ratio,
|
|
'DB_EFFICIENCY' AS
|
|
db_efficiency
|
|
FROM sys.v_$sysmetric
|
|
WHERE metric_name IN('Database CPU Time Ratio', 'Database Wait Time Ratio')
|
|
AND intsize_csec =
|
|
(SELECT MAX(intsize_csec)
|
|
FROM sys.v_$sysmetric);
|