I used to read all of documentations and blogs on Oracle Application Server 10g installation.
Out of all I fond best for the installation of Oracle Application Server are follows:
1) Installing Oracle 10g Application Server On Linux
This Includes:
-> Pre-installation Tasks
-> Installing the Oracle Application Server 10g Infrastructure
-> Installing the Oracle Application Server 10g Mid Tier (J2EE and Web Cache)
-> Installing the Oracle BPEL Process Manager 10g
-> Installing the Oracle Application Server Toplink 10g
-> Installing the Oracle Application Server 10g Business Intelligence and Forms
-> Installing the Oracle Application Server 10g Portal and Wireless
2) Installing Oracle 10g Application Server on SLES9
I hope these links will help all my friends who want to install Oracle Application server.
Oct 13, 2006
Few Important Oracle Queries
I found the one more excellent query which can help the oracle dba in doing performance tuning.
--> Find the Large Table Scans:
SELECT table_owner,
table_name,
table_type,
size_kb,
statement_count,
reference_count,
executions,
executions * reference_count total_scans
FROM (SELECT a.object_owner table_owner,
a.object_name table_name,
b.segment_type table_type,
b.bytes / 1024 size_kb,
SUM(c.executions ) executions,
COUNT( DISTINCT a.hash_value ) statement_count,
COUNT( * ) reference_count
FROM sys.v_$sql_plan a,
sys.dba_segments b,
sys.v_$sql c
WHERE a.object_owner (+) = b.owner
AND a.object_name (+) = b.segment_name
AND b.segment_type IN ('TABLE', 'TABLE PARTITION')
AND a.operation LIKE '%TABLE%'
AND a.options = 'FULL'
AND a.hash_value = c.hash_value
AND b.bytes / 1024 > 1024
GROUP BY a.object_owner, a.object_name, a.operation, b.bytes /
1024, b.segment_type
ORDER BY 4 DESC, 1, 2 )
--> Full Table Scans:
select ss.username||'('||se.sid||') ' "USER_PROCESS",
sum(decode(name,'table scans (short tables)',value)) "SHORT_SCANS",
sum(decode(name,'table scans (long tables)', value)) "LONG_SCANS",
sum(decode(name,'table scan rows gotten',value)) "ROWS_RETRIEVED"
from v$session ss, v$sesstat se, v$statname sn
where se.statistic# = sn.statistic#
and (name like '%table scans (short tables)%'
OR name like '%table scans (long tables)%'
OR name like '%table scan rows gotten%' )
and se.sid = ss.sid
and ss.username is not null
group by ss.username||'('||se.sid||') ';
--> Top 10 Sessions by Physical Reads:
select distinct c.osuser,c.username,c.sid, b.name, a.value
from v$sesstat a,
v$statname b,
v$session c
where a.statistic# = b.statistic#
and a.sid = c.sid
and nvl(a.value,0) > 0
and c.username not in ('SYS','SYSTEM')
and b.name = 'physical reads'
and c.type <> 'BACKGROUND'
and rownum <= 10
order by a.value desc;
--> Top 10 Sessions by Physical Writes:
select distinct c.osuser,c.username,c.sid, b.name, a.value
from v$sesstat a,
v$statname b,
v$session c
where a.statistic# = b.statistic#
and a.sid = c.sid
and nvl(a.value,0) > 0
and c.username not in ('SYS','SYSTEM')
and b.name = 'physical writes'
and c.type <> 'BACKGROUND'
and rownum <= 10
order by a.value desc;
TABLESPACE USAGE:
select a.TABLESPACE_NAME,
a.BYTES bytes_used,
b.BYTES bytes_free,
b.largest,
round(((a.BYTES-b.BYTES)/a.BYTES)*100,2) percent_used
from
(
select TABLESPACE_NAME,
sum(BYTES) BYTES
from dba_data_files
group by TABLESPACE_NAME
)
a,
(
select TABLESPACE_NAME,
sum(BYTES) BYTES ,
max(BYTES) largest
from dba_free_space
group by TABLESPACE_NAME
)
b
where a.TABLESPACE_NAME=b.TABLESPACE_NAME
order by ((a.BYTES-b.BYTES)/a.BYTES) desc
Based on the few of these queries who can plan according and do the performance tuning.
--> Find the Large Table Scans:
SELECT table_owner,
table_name,
table_type,
size_kb,
statement_count,
reference_count,
executions,
executions * reference_count total_scans
FROM (SELECT a.object_owner table_owner,
a.object_name table_name,
b.segment_type table_type,
b.bytes / 1024 size_kb,
SUM(c.executions ) executions,
COUNT( DISTINCT a.hash_value ) statement_count,
COUNT( * ) reference_count
FROM sys.v_$sql_plan a,
sys.dba_segments b,
sys.v_$sql c
WHERE a.object_owner (+) = b.owner
AND a.object_name (+) = b.segment_name
AND b.segment_type IN ('TABLE', 'TABLE PARTITION')
AND a.operation LIKE '%TABLE%'
AND a.options = 'FULL'
AND a.hash_value = c.hash_value
AND b.bytes / 1024 > 1024
GROUP BY a.object_owner, a.object_name, a.operation, b.bytes /
1024, b.segment_type
ORDER BY 4 DESC, 1, 2 )
--> Full Table Scans:
select ss.username||'('||se.sid||') ' "USER_PROCESS",
sum(decode(name,'table scans (short tables)',value)) "SHORT_SCANS",
sum(decode(name,'table scans (long tables)', value)) "LONG_SCANS",
sum(decode(name,'table scan rows gotten',value)) "ROWS_RETRIEVED"
from v$session ss, v$sesstat se, v$statname sn
where se.statistic# = sn.statistic#
and (name like '%table scans (short tables)%'
OR name like '%table scans (long tables)%'
OR name like '%table scan rows gotten%' )
and se.sid = ss.sid
and ss.username is not null
group by ss.username||'('||se.sid||') ';
--> Top 10 Sessions by Physical Reads:
select distinct c.osuser,c.username,c.sid, b.name, a.value
from v$sesstat a,
v$statname b,
v$session c
where a.statistic# = b.statistic#
and a.sid = c.sid
and nvl(a.value,0) > 0
and c.username not in ('SYS','SYSTEM')
and b.name = 'physical reads'
and c.type <> 'BACKGROUND'
and rownum <= 10
order by a.value desc;
--> Top 10 Sessions by Physical Writes:
select distinct c.osuser,c.username,c.sid, b.name, a.value
from v$sesstat a,
v$statname b,
v$session c
where a.statistic# = b.statistic#
and a.sid = c.sid
and nvl(a.value,0) > 0
and c.username not in ('SYS','SYSTEM')
and b.name = 'physical writes'
and c.type <> 'BACKGROUND'
and rownum <= 10
order by a.value desc;
TABLESPACE USAGE:
select a.TABLESPACE_NAME,
a.BYTES bytes_used,
b.BYTES bytes_free,
b.largest,
round(((a.BYTES-b.BYTES)/a.BYTES)*100,2) percent_used
from
(
select TABLESPACE_NAME,
sum(BYTES) BYTES
from dba_data_files
group by TABLESPACE_NAME
)
a,
(
select TABLESPACE_NAME,
sum(BYTES) BYTES ,
max(BYTES) largest
from dba_free_space
group by TABLESPACE_NAME
)
b
where a.TABLESPACE_NAME=b.TABLESPACE_NAME
order by ((a.BYTES-b.BYTES)/a.BYTES) desc
Based on the few of these queries who can plan according and do the performance tuning.
Oct 11, 2006
Oracle Performance Tuning Tips
---------------------------------
--> Tuning Multi-Threaded Server
=================================
If SERVERS_HIGHWATER exceeds the MTS_SERVERS parameter, increase the number of
MTS_SERVERS in the init&SID.ora file.
set heading off
select 'Current MTS_SERVERS number is ' || value from v$parameter where name = 'mts_servers';
set heading on
select * from v$mts;
-----------------------------
--> Tuning The Library Cache
==============================
Library cache get/hit ratio for SQL AREA should be in high 90's. If not, there
is room to improve the efficiency of your application.
select namespace, gets, gethits, gethitratio from v$librarycache;
---------------------------
--> Reloads-to-pins ratio
===========================
If reloads-to-pins ratio is greater than .01, increase SHARED_POOL_SIZE in the init&SID.ora file.
set heading off
select 'Current SHARED_POOL_SIZE value is ' || value from v$parameter where name = 'shared_pool_size';
set heading on
select sum(pins) "Executions", sum(reloads) "LC Misses",
sum(reloads)/sum(pins) "Ratio" from v$librarycache;
---------------------------
--> Data Dictionary Cache
===========================
If ratio is greater than .15, consider increasing prompt SHARED_POOL_SIZE in the init&SID.ora file.
set heading off
select 'Current SHARED_POOL_SIZE value is ' || value from v$parameter where name = 'shared_pool_size';
set heading on
select sum(gets) "Total Gets", sum(getmisses) "Total Get Misses",
sum(getmisses)/sum(gets) "Ratio" from v$rowcache;
--------------------------------------------------------------------
--> Packages you might want to consider pinning into the shared pool
=====================================================================
column owner format a12
column name format a25
column type format a15
set feedback on
select owner, name, type, loads, executions, sharable_mem
from v$db_object_cache
where kept = 'NO'
and loads > 1 and executions > 50 and sharable_mem > 10000
and type in ('PACKAGE', 'PACKAGE BODY', 'FUNCTION', 'PROCEDURE')
order by loads desc;
--------------------------------
--> Shared Pool Reserved space
================================
The goal is to have zero REQUEST_MISSES and REQUEST_FAILURES, so increase SHARED_POOL_RESERVED_SIZE in the init&SID.ora file.
If either of them are greater than 0.
set heading off
select 'Current SHARED_POOL_RESERVED_SIZE value is ' || value from v$parameter where name = 'shared_pool_reserved_size';
set heading on
select request_misses, request_failures, last_failure_size
from v$shared_pool_reserved;
--------------------------------------
--> Tuning the Data Dictionary Cache
======================================
If the gethitratio is greater than .15 -- increase the SHARED_POOL_SIZE parameter in the init&SID.ora file.
set heading off
select 'Current SHARED_POOL_SIZE value is ' || value from v$parameter where name = 'shared_pool_size';
set heading on
select sum(gets) "total_gets", sum(getmisses) "total_get_misses",
sum(getmisses)/sum(gets) * 100 "gethitratio"
from v$rowcache;
----------------------------------
--> Tuning The Data Buffer Cache
==================================
Goal is to have a Cache Hit Ratio greater than 90% -- if lower, increase value for DB_BLOCK_BUFFERS in the init&SID.ora file.
set heading off
select 'Current DB_BLOCK_BUFFERS value is ' || value from v$parameter where name = 'db_block_buffers';
set heading on
select name, value from v$sysstat where
name in ('db block gets', 'consistent gets', 'physical reads');
select 1 - (phy.value / (cur.value + con.value)) "Cache Hit Ratio"
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';
------------------------------------------------------------------
--> If the number of free buffers inspected is high or increasing, consider increasing the DB_BLOCK_BUFFERS parameter in the init&SID.ora file.
set heading off
select 'Current DB_BLOCK_BUFFERS value is ' || value from v$parameter where name = 'db_block_buffers';
set heading on
select name, value from v$sysstat where name = 'free buffer inspected';
---------------------------------------------------------------------------
--> A high or increasing number of waits indicates that the db writer cannot keep up writing dirty buffers. Consider increasing the number of writers using the DB_WRITER_PROCESSES parameter in the init&SID.ora file.
set heading off
select 'Current DB_WRITER_PROCESSES value is ' || value from v$parameter where name = 'db_writer_processes';
set heading on
select event, total_waits from v$system_event where event in
('free buffer waits', 'buffer busy waits');
--------------------------
--> LRU Hit percentage
==========================
If the LRU Hit percentage is less than 99%, consider adding more DB_WRITER_PROCESSES and increasing the DB_BLOCK_LRU_LATCHES parameter in the init&SID.ora file.
set heading off
select 'Current DB_WRITER_PROCESSES value is ' || v1.value || chr(10) ||
'Current DB_BLOCK_LRU_LATCHES value is ' || v2.value
from v$parameter v1,v$parameter v2
where v1.name = 'db_writer_processes' and v2.name = 'db_block_lru_latches';
set heading on
select name, 100 - (sleeps/gets * 100) "LRU Hit%" from v$latch
where name = 'cache buffers lru chain';
---------------------------------
--> Tuning The Redo Log Buffer
=================================
There should never be a wait for log buffer space. Increase LOG_BUFFER in the init&SID.ora file if the selection below doesn't show "no rows selected".
set heading off
select 'Current LOG_BUFFER size is ' || value from v$parameter where name = 'log_buffer';
set heading on
set feedback on
select sid, event, state from v$session_wait
where event = 'log buffer space';
-------------------------------------------------------------------
--> Tables with Chain count greater than 10% of the number of rows
====================================================================
set feedback on
select owner, table_name, num_rows, chain_cnt, chain_cnt/num_rows "Percent"
from dba_tables where (chain_cnt/num_rows) > .1 and num_rows > 0;
-------------------
--> Tuning Sorts
===================
The ratio of disk sorts to memory sorts should be less than 5%. Consider increasing the SORT_AREA_SIZE parameter in the init&SID.ora file. You might also consider setting up separate temp tablespaces for frequent users of disk sorts.
set heading off
select 'Current SORT_AREA_SIZE value is ' || value from v$parameter where name = 'sort_area_size';
set heading on
select disk.value "Disk", mem.value "Mem", (disk.value/mem.value) * 100 "Ratio"
from v$sysstat mem, v$sysstat disk
where mem.name = 'sorts (memory)'
and disk.name = 'sorts (disk)';
-------------------------------
--> Tuning Rollback segments
===============================
If ratio of waits to gets is greater than 1%, you need more rbs segments.
set heading off
select 'Current number of rollback segments is ' || count(*) from dba_rollback_segs
where status = 'ONLINE' and owner = 'PUBLIC';
set heading on
select sum(waits)*100/sum(gets) "Ratio", sum(waits) "Waits", sum(gets) "Gets"
from v$rollstat;
----------------------------
--> Rollback segment waits
============================
Any waits indicates need for more segments.
set heading off
select 'Current number of rollback segments is ' || count(*) from dba_rollback_segs
where status = 'ONLINE' and owner = 'PUBLIC';
set heading on
select * from v$waitstat where class = 'undo header';
--------------------------------------------------
--> Rollback segment waits for transaction slots
==================================================
Any waits indicates need for more segments.
set heading off
select 'Current number of rollback segments is ' || count(*) from dba_rollback_segs
where status = 'ONLINE' and owner = 'PUBLIC';
set heading on
set feedback on
select * from v$system_event where event = 'undo segment tx slot';
--------------------------
--> Rollback contention
==========================
Should be zero for all rbs's.
column name format a10
select n.name,round (100 * s.waits/s.gets) "%contention"
from v$rollname n, v$rollstat s
where n.usn = s.usn;
---------------------------------------------------------------------------
Purpose: List locks on tables and other objects currently held by user sessions, including session information and commands to kill those sessions.
column object_name format a10
column username format a10
column owner format a10
select username,v$session.sid,serial#,owner,object_id,object_name,
object_type,v$lock.type
from dba_objects,v$lock,v$session where object_id = v$lock.id1
and v$lock.sid = v$session.sid and owner != 'SYS';
set heading off pagesize 0
!echo
!echo To kill locking sessions:
select 'alter system kill session ''' || v$session.sid || ',' ||
serial# || ''';'
from dba_objects,v$lock,v$session where object_id = v$lock.id1
and v$lock.sid = v$session.sid and owner != 'SYS';
--> Tuning Multi-Threaded Server
=================================
If SERVERS_HIGHWATER exceeds the MTS_SERVERS parameter, increase the number of
MTS_SERVERS in the init&SID.ora file.
set heading off
select 'Current MTS_SERVERS number is ' || value from v$parameter where name = 'mts_servers';
set heading on
select * from v$mts;
-----------------------------
--> Tuning The Library Cache
==============================
Library cache get/hit ratio for SQL AREA should be in high 90's. If not, there
is room to improve the efficiency of your application.
select namespace, gets, gethits, gethitratio from v$librarycache;
---------------------------
--> Reloads-to-pins ratio
===========================
If reloads-to-pins ratio is greater than .01, increase SHARED_POOL_SIZE in the init&SID.ora file.
set heading off
select 'Current SHARED_POOL_SIZE value is ' || value from v$parameter where name = 'shared_pool_size';
set heading on
select sum(pins) "Executions", sum(reloads) "LC Misses",
sum(reloads)/sum(pins) "Ratio" from v$librarycache;
---------------------------
--> Data Dictionary Cache
===========================
If ratio is greater than .15, consider increasing prompt SHARED_POOL_SIZE in the init&SID.ora file.
set heading off
select 'Current SHARED_POOL_SIZE value is ' || value from v$parameter where name = 'shared_pool_size';
set heading on
select sum(gets) "Total Gets", sum(getmisses) "Total Get Misses",
sum(getmisses)/sum(gets) "Ratio" from v$rowcache;
--------------------------------------------------------------------
--> Packages you might want to consider pinning into the shared pool
=====================================================================
column owner format a12
column name format a25
column type format a15
set feedback on
select owner, name, type, loads, executions, sharable_mem
from v$db_object_cache
where kept = 'NO'
and loads > 1 and executions > 50 and sharable_mem > 10000
and type in ('PACKAGE', 'PACKAGE BODY', 'FUNCTION', 'PROCEDURE')
order by loads desc;
--------------------------------
--> Shared Pool Reserved space
================================
The goal is to have zero REQUEST_MISSES and REQUEST_FAILURES, so increase SHARED_POOL_RESERVED_SIZE in the init&SID.ora file.
If either of them are greater than 0.
set heading off
select 'Current SHARED_POOL_RESERVED_SIZE value is ' || value from v$parameter where name = 'shared_pool_reserved_size';
set heading on
select request_misses, request_failures, last_failure_size
from v$shared_pool_reserved;
--------------------------------------
--> Tuning the Data Dictionary Cache
======================================
If the gethitratio is greater than .15 -- increase the SHARED_POOL_SIZE parameter in the init&SID.ora file.
set heading off
select 'Current SHARED_POOL_SIZE value is ' || value from v$parameter where name = 'shared_pool_size';
set heading on
select sum(gets) "total_gets", sum(getmisses) "total_get_misses",
sum(getmisses)/sum(gets) * 100 "gethitratio"
from v$rowcache;
----------------------------------
--> Tuning The Data Buffer Cache
==================================
Goal is to have a Cache Hit Ratio greater than 90% -- if lower, increase value for DB_BLOCK_BUFFERS in the init&SID.ora file.
set heading off
select 'Current DB_BLOCK_BUFFERS value is ' || value from v$parameter where name = 'db_block_buffers';
set heading on
select name, value from v$sysstat where
name in ('db block gets', 'consistent gets', 'physical reads');
select 1 - (phy.value / (cur.value + con.value)) "Cache Hit Ratio"
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';
------------------------------------------------------------------
--> If the number of free buffers inspected is high or increasing, consider increasing the DB_BLOCK_BUFFERS parameter in the init&SID.ora file.
set heading off
select 'Current DB_BLOCK_BUFFERS value is ' || value from v$parameter where name = 'db_block_buffers';
set heading on
select name, value from v$sysstat where name = 'free buffer inspected';
---------------------------------------------------------------------------
--> A high or increasing number of waits indicates that the db writer cannot keep up writing dirty buffers. Consider increasing the number of writers using the DB_WRITER_PROCESSES parameter in the init&SID.ora file.
set heading off
select 'Current DB_WRITER_PROCESSES value is ' || value from v$parameter where name = 'db_writer_processes';
set heading on
select event, total_waits from v$system_event where event in
('free buffer waits', 'buffer busy waits');
--------------------------
--> LRU Hit percentage
==========================
If the LRU Hit percentage is less than 99%, consider adding more DB_WRITER_PROCESSES and increasing the DB_BLOCK_LRU_LATCHES parameter in the init&SID.ora file.
set heading off
select 'Current DB_WRITER_PROCESSES value is ' || v1.value || chr(10) ||
'Current DB_BLOCK_LRU_LATCHES value is ' || v2.value
from v$parameter v1,v$parameter v2
where v1.name = 'db_writer_processes' and v2.name = 'db_block_lru_latches';
set heading on
select name, 100 - (sleeps/gets * 100) "LRU Hit%" from v$latch
where name = 'cache buffers lru chain';
---------------------------------
--> Tuning The Redo Log Buffer
=================================
There should never be a wait for log buffer space. Increase LOG_BUFFER in the init&SID.ora file if the selection below doesn't show "no rows selected".
set heading off
select 'Current LOG_BUFFER size is ' || value from v$parameter where name = 'log_buffer';
set heading on
set feedback on
select sid, event, state from v$session_wait
where event = 'log buffer space';
-------------------------------------------------------------------
--> Tables with Chain count greater than 10% of the number of rows
====================================================================
set feedback on
select owner, table_name, num_rows, chain_cnt, chain_cnt/num_rows "Percent"
from dba_tables where (chain_cnt/num_rows) > .1 and num_rows > 0;
-------------------
--> Tuning Sorts
===================
The ratio of disk sorts to memory sorts should be less than 5%. Consider increasing the SORT_AREA_SIZE parameter in the init&SID.ora file. You might also consider setting up separate temp tablespaces for frequent users of disk sorts.
set heading off
select 'Current SORT_AREA_SIZE value is ' || value from v$parameter where name = 'sort_area_size';
set heading on
select disk.value "Disk", mem.value "Mem", (disk.value/mem.value) * 100 "Ratio"
from v$sysstat mem, v$sysstat disk
where mem.name = 'sorts (memory)'
and disk.name = 'sorts (disk)';
-------------------------------
--> Tuning Rollback segments
===============================
If ratio of waits to gets is greater than 1%, you need more rbs segments.
set heading off
select 'Current number of rollback segments is ' || count(*) from dba_rollback_segs
where status = 'ONLINE' and owner = 'PUBLIC';
set heading on
select sum(waits)*100/sum(gets) "Ratio", sum(waits) "Waits", sum(gets) "Gets"
from v$rollstat;
----------------------------
--> Rollback segment waits
============================
Any waits indicates need for more segments.
set heading off
select 'Current number of rollback segments is ' || count(*) from dba_rollback_segs
where status = 'ONLINE' and owner = 'PUBLIC';
set heading on
select * from v$waitstat where class = 'undo header';
--------------------------------------------------
--> Rollback segment waits for transaction slots
==================================================
Any waits indicates need for more segments.
set heading off
select 'Current number of rollback segments is ' || count(*) from dba_rollback_segs
where status = 'ONLINE' and owner = 'PUBLIC';
set heading on
set feedback on
select * from v$system_event where event = 'undo segment tx slot';
--------------------------
--> Rollback contention
==========================
Should be zero for all rbs's.
column name format a10
select n.name,round (100 * s.waits/s.gets) "%contention"
from v$rollname n, v$rollstat s
where n.usn = s.usn;
---------------------------------------------------------------------------
Purpose: List locks on tables and other objects currently held by user sessions, including session information and commands to kill those sessions.
column object_name format a10
column username format a10
column owner format a10
select username,v$session.sid,serial#,owner,object_id,object_name,
object_type,v$lock.type
from dba_objects,v$lock,v$session where object_id = v$lock.id1
and v$lock.sid = v$session.sid and owner != 'SYS';
set heading off pagesize 0
!echo
!echo To kill locking sessions:
select 'alter system kill session ''' || v$session.sid || ',' ||
serial# || ''';'
from dba_objects,v$lock,v$session where object_id = v$lock.id1
and v$lock.sid = v$session.sid and owner != 'SYS';
Subscribe to:
Posts (Atom)
