Class perf_oci8
Description
Located in /adodb5/perf/perf-oci8.inc.php (line 18)
ADODB_perf | --perf_oci8
Variable Summary
Method Summary
Variables
mixed
$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
)" (line 24)
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
)" (line 24)
mixed
$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) (line 33)
'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) (line 33)
mixed
$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" (line 20)
group by segment_name,tablespace_name" (line 20)
mixed
$version
(line 23)
Methods
Documentation generated on Sun, 09 Mar 2008 23:52:50 -0300 by phpDocumentor 1.4.0
