$createTableSQL = "CREATE TABLE adodb_logsql (
created date NOT NULL,
sql0 varchar(250) NOT NULL,
sql1 varchar(4000) NOT NULL,
params varchar(4000),
tracer varchar(4000),
timer decimal(16,6) NOT NULL
)"
$settings = array(
'Ratios',
'data cache hit ratio' => array('RATIOH',
"select round((1-(phy.value / (cur.value + con.value)))*100,2)
from v\$sysstat cur, v\$sysstat con, v\$sysstat phy
where cur.name = 'db block gets' and
con.name = 'consistent gets' and
phy.name = 'physical reads'",
'=WarnCacheRatio'),'sql cache hit ratio'=>array('RATIOH','select round(100*(sum(pins)-sum(reloads))/sum(pins),2) from v$librarycache','increase <i>shared_pool_size</i> if too ratio low'),'datadict cache hit ratio'=>array('RATIOH',"select
round((1 - (sum(getmisses) / (sum(gets) +
sum(getmisses))))*100,2)
from v\$rowcache",'increase <i>shared_pool_size</i> if too ratio low'),'memory sort ratio'=>array('RATIOH',"SELECT ROUND((100 * b.VALUE) /DECODE ((a.VALUE + b.VALUE),
0,1,(a.VALUE + b.VALUE)),2)
FROM v\$sysstat a,
v\$sysstat b
WHERE a.name = 'sorts (disk)'
AND b.name = 'sorts (memory)'","% of memory sorts compared to disk sorts - should be over 95%"),'IO','data reads'=>array('IO',"select value from v\$sysstat where name='physical reads'"),'data writes'=>array('IO',"select value from v\$sysstat where name='physical writes'"),'Data Cache','data cache buffers'=>array('DATAC',"select a.value/b.value from v\$parameter a, v\$parameter b
where a.name = 'db_cache_size' and b.name= 'db_block_size'",'Number of cache buffers. Tune <i>db_cache_size</i> if the <i>data cache hit ratio</i> is too low.'),'data cache blocksize'=>array('DATAC',"select value from v\$parameter where name='db_block_size'",''),'Memory Pools','data cache size'=>array('DATAC',"select value from v\$parameter where name = 'db_cache_size'",'db_cache_size'),'shared pool size'=>array('DATAC',"select value from v\$parameter where name = 'shared_pool_size'",'shared_pool_size, which holds shared sql, stored procedures, dict cache and similar shared structs'),'java pool size'=>array('DATAJ',"select value from v\$parameter where name = 'java_pool_size'",'java_pool_size'),'large pool buffer size'=>array('CACHE',"select value from v\$parameter where name='large_pool_size'",'this pool is for large mem allocations (not because it is larger than shared pool), for MTS sessions, parallel queries, io buffers (large_pool_size) '),'pga buffer size'=>array('CACHE',"select value from v\$parameter where name='pga_aggregate_target'",'program global area is private memory for sorting, and hash and bitmap merges - since oracle 9i (pga_aggregate_target)'),'Connections','current connections'=>array('SESS','select count(*) from sys.v_$session where username is not null',''),'max connections'=>array('SESS',"select value from v\$parameter where name='sessions'",''),'Memory Utilization','data cache utilization ratio'=>array('RATIOU',"select round((1-bytes/sgasize)*100, 2)
from (select sum(bytes) sgasize from sys.v_\$sgastat) s, sys.v_\$sgastat f
where name = 'free memory' and pool = 'shared pool'",'Percentage of data cache actually in use - should be over 85%'),'shared pool utilization ratio'=>array('RATIOU','select round((sga.bytes/case when p.value=0 then sga.bytes else to_number(p.value) end)*100,2)
from v$sgastat sga, v$parameter p
where sga.name = \'free memory\' and sga.pool = \'shared pool\'
and p.name = \'shared_pool_size\'','Percentage of shared pool actually used - too low is bad, too high is worse'),'large pool utilization ratio'=>array('RATIOU',"select round((1-bytes/sgasize)*100, 2)
from (select sum(bytes) sgasize from sys.v_\$sgastat) s, sys.v_\$sgastat f
where name = 'free memory' and pool = 'large pool'",'Percentage of large_pool actually in use - too low is bad, too high is worse'),'sort buffer size'=>array('CACHE',"select value from v\$parameter where name='sort_area_size'",'max in-mem sort_area_size (per query), uses memory in pga'),'pga usage at peak'=>array('RATIOU','=PGA','Mb utilization at peak transactions (requires Oracle 9i+)'),'Transactions','rollback segments'=>array('ROLLBACK',"select count(*) from sys.v_\$rollstat",''),'peak transactions'=>array('ROLLBACK',"select max_utilization tx_hwm
from sys.v_\$resource_limit
where resource_name = 'transactions'",'Taken from high-water-mark'),'max transactions'=>array('ROLLBACK',"select value from v\$parameter where name = 'transactions'",'max transactions / rollback segments < 3.5 (or transactions_per_rollback_segment)'),'Parameters','cursor sharing'=>array('CURSOR',"select value from v\$parameter where name = 'cursor_sharing'",'Cursor reuse strategy. Recommended is FORCE (8i+) or SIMILAR (9i+). See <a href=http://www.praetoriate.com/oracle_tips_cursor_sharing.htm>cursor_sharing</a>.'),/*
'cursor reuse' => array('CURSOR',
"select count(*) from (select sql_text_wo_constants, count(*)
from t1
group by sql_text_wo_constants
having count(*) > 100)",'These are sql statements that should be using bind variables'),*/'index cache cost'=>array('COST',"select value from v\$parameter where name = 'optimizer_index_caching'",'=WarnIndexCost'),'random page cost'=>array('COST',"select value from v\$parameter where name = 'optimizer_index_cost_adj'",'=WarnPageCost'),'Backup','Achivelog Mode'=>array('BACKUP','select log_mode from v$database','To turn on archivelog:<br>
<pre>
SQLPLUS> connect sys as sysdba;
SQLPLUS> shutdown immediate;
SQLPLUS> startup mount exclusive;
SQLPLUS> alter database archivelog;
SQLPLUS> archive log start;
SQLPLUS> alter database open;
</pre>'),'DBID'=>array('BACKUP','select dbid from v$database','Primary key of database, used for recovery with an RMAN Recovery Catalog'),'Archive Log Dest'=>array('BACKUP',"SELECT NVL(v1.value,v2.value)
FROM v\$parameter v1, v\$parameter v2 WHERE v1.name='log_archive_dest' AND v2.name='log_archive_dest_10'",''),'Flashback Area'=>array('BACKUP',"select nvl(value,'Flashback Area not used') from v\$parameter where name=lower('DB_RECOVERY_FILE_DEST')",'Flashback area is a folder where all backup data and logs can be stored and managed by Oracle. If Error: message displayed, then it is not in use.'),'Control File Keep Time'=>array('BACKUP',"select value from v\$parameter where name='control_file_record_keep_time'",'No of days to keep RMAN info in control file. I recommend it be set to x2 or x3 times the frequency of your full backup.'),false)
$tablesSQL = "select segment_name as \"tablename\", sum(bytes)/1024 as \"size_in_k\",tablespace_name as \"tablespace\",count(*) \"extents\" from sys.user_extents
group by segment_name,tablespace_name"
