Tuning
Methodology
Quick thinks to check for
Modify init.ora Parameters
SQL Code Tuning
Collect Schema Statistics
Redo Log Switches
Large Full Table Scans
Small Full Table Scans and
Index Scans
Many Indexes on Data
Buffer Cache
Check for skewed Indexes (unbalanced)
Tuning Database Buffer Cache
Fragmentation on DB Objects
Size of LOG_BUFFER
Size of
SHARED_POOL_SIZE
Allocate Files Properly
(check waits on them)
Checking Active Statements
Use IPC for local Connections
Check Undo Parameters
Detect High SQL Parse
Monitor Open and Cached Cursors
Detect Top 10 Queries in SQL Area
Allocate
Objects into Multiple Block Buffers (another web page)
Check for Indexes not
Used and HOT Tables
Detect and Resolve
Buffer Busy Waits
Testing
Procedures or Packages for Performance
Using PGA
Advice Utility
Check Sorts
Optimizing Indexes (creating 32k block
size)
Quick Things to Check for
My goal is to quickly identify and correct performance problems.
Here is a summary of the things that I look at first:
1 - Install STATSPACK first, and get hourly snaps working.
2 - Get an SQL access report (or plan9i.sql), an spreport during peak
times, and statspack_alert.sql output.
3 - Look for "silver bullet fixes":
- partial schema statistics (using dbms_stats)
- missing indexes
- optimizer_index_cost_adj=(between 20-60 for OLTP systems, I
usually start with 20)
- optimizer_index_caching=xx (depending on RAM for index caching,
around 65)
- optimizer_mode=first_rows (for OLTP)
- parallel_automatic_tuning=TRUE (parallelizes full-table scans,
Because parallel full-table scans are very fast, the CBO will give a
higher cost to index access and be friendlier to full-table scans)
- hash_area_size too small (too many nested loop joins)
4 - Fully utilize server RAM - On a dedicated Oracle server, use
all extra RAM for db_cache_size less PGA's and 20%
RAM reserve for OS.
5 - Get the bottlenecks - See STATSPACK top 5 wait events - OEM
performance pack reports - TOAD reports
6 - Look for Buffer Busy Waits resulting from table/index freelist
shortages
7 - See if large-table full-table scans can be removed with well-placed
indexes
8 - If tables are low volatility, seek an MV that can
pre-join/pre-aggregate common queries. Turn-on automatic query
rewrite
9 - Look for non-reentrant SQL - (literals values inside SQL from
v$sql) - If so, set cursor_sharing=force
Modify init.ora Parameters
- For OLTP systems the parameter DB_FILE_MULTIBLOCK_READ_COUNT
is set
to values 8 - 16 while in decision support systems it is set to higher
values. This parameter determines the maximum number of
database blocks
read in one I/O operation during a full table scan. The
setting of this parameter can reduce the number of I/O calls
required for a full table scan, thus improving performance.
- OPTIMIZER_INDEX_COST_ADJ
This initialization parameter is a percentage value representing a
comparison between the relative cost of physical
I/O requests for indexed access and full table-scans. The default value
of 100 indicates to the cost-based optimizer that indexed access is
100% as costly (i.e., equally costly) as FULL table scan access.
Usually it's around 20-50 for an OLTP system. The smaller the value,
the cheaper the cost of index access. I usually start with 20. Query to
suggest its
value:
col c1 heading 'Average Waits
for|Full Scan Read I/O'
format 9999.999
col c2 heading 'Average Waits for|Index Read
I/O'
format 9999.999
col c3 heading 'Percent of| I/O Waits|for Full
Scans' format 9.99
col c4 heading 'Percent of| I/O Waits|for Index
Scans' format 9.99
col c5 heading 'Starting|Value|for|optimizer|index|cost|adj' format 999
select a.average_wait c1,
b.average_wait c2,
a.total_waits /(a.total_waits + b.total_waits) c3,
b.total_waits /(a.total_waits + b.total_waits) c4,
(b.average_wait / a.average_wait)*100 c5
from v$system_event a,
v$system_event b
where a.event = 'db file scattered read'
and b.event = 'db file sequential read';
Here is the listing from this script:
Starting
Value
for
optimizer
Percent of Percent
of index
Average Waits for Average Waits for
I/O Waits I/O
Waits cost
Full Scan Read I/O Index Read I/O for Full Scans for
Index Scans adj
------------------ ----------------- -------------- ---------------
---------
1.473
.289 .02
.98
20
As you can see, the suggested starting value for
optimizer_index_cost_adj may be
too high because 98% of data
waits are on index
(sequential) block access. How we can "weight" this starting
value for optimizer_index_cost_adj to reflect the reality that this
system has only 2% waits on
full-table scan reads (a typical OLTP system with few full-table
scans)? As a practical matter, we never want an automated value for
optimizer_index_cost_adj to be less and 1, nor more than 100.
Another one:
col
a1 head "avg. wait time|(db file sequential read)"
col a2 head "avg. wait time|(db file scattered read)"
col a3 head "new setting for|optimizer_index_cost_adj"
select a.average_wait a1, b.average_wait a2,
round(
((a.average_wait/b.average_wait)*100) ) a3
from (select d.kslednam EVENT, s.kslestim
/ (10000 * s.ksleswts) AVERAGE_WAIT
from x$kslei s, x$ksled d
where s.ksleswts != 0 and
s.indx = d.indx) a,
(select d.kslednam EVENT, s.kslestim
/ (10000 * s.ksleswts) AVERAGE_WAIT
from x$kslei s, x$ksled d
where s.ksleswts != 0 and
s.indx = d.indx) b
where a.event = 'db file
sequential read'
and b.event = 'db file scattered
read';
- OPTIMIZER_INDEX_CACHING
This initialization parameter represents a percentage value, ranging
between the values of 0 and 99. The default value of 0 indicates
to the CBO that 0% of database blocks accessed using indexed access can
be expected to be found in the Buffer Cache of the Oracle SGA.
This implies that all index accesses will require a physical read from
the I/O subsystem for every logical read from the Buffer Cache, also
known as a 0% hit ratio on the Buffer Cache. This parameter
applies only to the CBO’s calculations of accesses for blocks in an
index, not for the blocks in the table related to the index. It should
be set to 90.
- Set the OPTIMIZER_FEATURES_ENABLE
= 9.2.0
- OPTIMIZER_MODE = first_rows
(for OLTP systems). This parameter returns
the rows faster.
SQL
Code Tuning
If the SQL hash value (SHV) corresponding to the SQL statement is not
found in the library cache during the soft parse, the server process
must perform a hard parse on the statement. During this operation, the
execution plan for the statement must be determined and the result must
be stored in the library cache. This is a computationally
expensive step. The hard
parse is usually accompained by latch contention on the shared pool and
library cache latches. In OLTP the aim is to parse once, execute many
times. Ideally soft parse should be > 95%, if falls significantly
lower than 80% then we need to investigate.
--The following query is useful for detecting programs that are
performing excessive hard parses.
spool
excessive_hard_parses.txt
SELECT /*+ RULE */
substr(s.program,1,20) program,
COUNT(*) users,
SUM(t.value) parses, SUM(t.value)/COUNT(*) parses_per_session,
SUM(t.value)/(SUM(sysdate-s.logon_time)*24) parses_per_hour
FROM v$session s,
v$sesstat t
WHERE t.statistic#
= 153
AND
s.sid =
t.sid
GROUP BY s.program
HAVING
SUM(t.value)/COUNT(*) > 2.0
ORDER BY parses_per_hour
DESC;
spool off
The query produces several parse metrics aggregated by program name.
The parses column indicates
the total hard parse count. parses_per_session
is the average number of parses for all sessions running the program,
and parses_per_hour is the
average number of parses per hour for all sessions running the program.
Search for high numbers in
the parses_per_hour column. The term high is relative. For OLTP
programs, numbers below 10 are reasonable. For batch programs, higher
values are acceptable. Any programs with values higher than 10 should
be investigated further.
For programs that are suspect, query the library cache to identify the
SQL statements being executed using the following query. Run this query
as many times as are required to get a reasonable sample.
SELECT /*+ RULE */
t.sql_text
FROM v$sql t, v$session s
WHERE s.sql_address =
t.address
AND
s.sql_hash_value = t.hash_value
AND s.sid =
&SID;
--Identifying unnecessary parse
calls at system level
spool unnecessary_parse_calls_system_level.txt
select parse_calls, executions, substr(sql_text, 1, 300)
from v$sqlarea
where command_type in (2, 3, 6, 7)
order by 3;
spool off
Check for statements with a lot
of executions. It is bad to have the PARSE_CALLS value in the above
statement
close to the EXECUTIONS value. The previous query will fire only for
DML
statements (to check on other types of statements use the appropriate
command
type number). Also ignore Recursive calls (dictionary access), as it is
internal to Oracle
--Identifying unnecessary parse calls at session level
spool
unnecessary_parse_calls_sess_level.txt
select b.sid,
substr(c.username,1,12) username,
substr(c.program,1,15) program, substr(a.name,1,20) name, b.value
from
v$sesstat b, v$statname a , v$session c
where
a.name in ('parse count (hard)', 'execute count')
and b.statistic# = a.statistic#
and b.sid = c.sid
and c.username not in ('SYS','SYSTEM')
order
by sid;
spool off
Identify the sessions involved with a lot of re-parsing (VALUE column).
Query these sessions from V$SESSION and then locate the program that is
being executed, resulting in so much parsing.
select
a.parse_calls, a.executions, substr(a.sql_text, 1, 100)
from v$sqlarea a, v$session b
where b.schema# = a.parsing_schema_id
and b.sid = &sid
order by 1 desc;
As stated earlier, excessive parsing will result in higher than optimal
CPU consumption.
However, the greater impact is likely to be contention for resources in
the shared pool. If many small statements are hard parsed, shared pool
fragmentation is likely to result. As the shared pool becomes more
fragmented, the amount of time required to complete a hard parse
increases. As the process of executing many unique statements
continues, resource contention worsens. The critical resources will
likely be memory in the library cache and the various latches
associated with the shared pool. There are several straightforward
methods to detect contention. The following query shows a list events
on which sessions are waiting to complete before continuing. Since
v$session_wait contains one row for each session, the query will return
the total number of sessions waiting for each event. The view contains
real-time data so it should be run repeatedly to detect possible
problems.
SELECT /*+ RULE */
SUBSTR(event,1,30) event, COUNT(*)
FROM v$session_wait
WHERE wait_time = 0
GROUP BY SUBSTR
(event,1,30), state;
If the latch free event
appears continuously, then there is latch resource contention. The
following query can be used to determine which latches have contention.
Since v$latchholder contains one row for each session, the query will
return the total number of sessions waiting for each latch. The view
contains real-time data so it should be run repeatedly.
SELECT /*+ RULE */ name,
COUNT(*)
FROM v$latchholder
GROUP BY name;
If library cache or shared pool latches appear continuously with any
frequency, then there is contention.
Latch
Contention Analysis
When an Oracle session needs to place a new SQL statement in the shared
pool, it has to acquire a latch, or internal lock. Under some
circumstances, contention for these latches can result in poor
performance. This does not happen frequently but it is worth checking.
Set the db_block_lru_latches to a higher number if you are experiencing
a high number of misses or sleeps.
spool latch_content_analysis.txt
clear breaks
clear computes
clear columns
column name heading "Latch Type"
format a25
column pct_miss heading
"Misses/Gets (%)" format 999.99999
column pct_immed heading
"Immediate Misses/Gets (%)" format 999.99999
ttitle 'Latch Contention Analysis
Report' skip
select n.name,
misses*100/(gets+1) pct_miss,
immediate_misses*100/(immediate_gets+1) pct_immed
from v$latchname n,v$latch l
where n.latch# = l.latch#
and n.name in('%cache
bugffer%','%protect%');
spool off
The Quick
Fix
Correcting the offending software may require days or weeks However, if
performance is poor, there are some things that can be done to improve
performance until the source of the problem can be corrected.
1. Increase the size of the
shared pool. For minor contention problems, an increase of 20%
should be suitable. For more severe problems, consider incremental
increases of 50% until performance improves. If the host system has
limited memory and the buffer cache hit rate is above 90%, consider
reducing the size of the buffer cache to increase the size of the
shared pool. A buffer cache hit ratio of 80-85% with reduced latch
contention will likely produce better database performance than a
higher buffer cache hit ratio with high latch contention.
2. Consider reducing the
value of the optimizer_max_permutations parameter if the
cost-based optimizer is being used and the database is using Oracle
Enterprise Server Version 8.0 or higher. This parameter controls the
maximum number of execution plans that the optimizer will develop to
identify the one with the lowest cost. The default value is 80,000 but
values of 100 to 1,000 usually produce identical execution plans to
those when a higher value is used. Since hard parses account for a
significant amount of CPU consumed on short-running SQL statements, one
of the artifacts of high hard parse counts is high CPU consumption.
Reducing the value of optimizer_max_permutations will help mitigate the
problem.
3. Flush the shared pool
periodically. This will reduce memory fragmentation in the
shared pool, which will reduce the elapsed time of the hard parse. The
frequency
depends upon the size of the shared pool and the severity of the
problem. For mild problems, consider flushing twice each day. For
severe problems, it may be
necessary to flush the shared pool every few hours.
4. Pin frequently used PL/SQL
functions and packages in the shared pool. When a program calls
a method within a package, the entire package must be loaded into the
shared pool. If the shared pool is highly fragmented and there is
considerable latch contention, a significant amount of clock time may
be required to load large packages into memory. Pinning packages and
functions will improve the response time when they are accessed.
spool
frequently_used_reloaded_objects.txt
--To
view a list of frequently used and re-loaded objects
set linesize 200
select loads,
executions, substr(owner, 1, 15) "Owner",
substr(namespace, 1, 20) "Type", substr(name, 1, 100) "Text"
from
v$db_object_cache
where owner not in ('SYS','SYSTEM','PERFSTAT','WMSYS','XDB')
order by
loads desc;
spool off
--To pin a package in memory
exec
dbms_shared_pool.keep('standard', 'p');
spool
pinned_objects.txt
--To
view a list of pinned objects
select
substr(owner, 1, 15) "Owner",
substr(namespace, 1, 20) "Type",
substr(name, 1, 42) "Text"
from v$db_object_cache
where kept = 'YES'
and owner not in ('SYS','SYSTEM')
order by 1,3;
spool off
It is straightforward to verify that an application is using bind
variables using the Oracle trace facility and tkprof, the application
profiler.
Tkprof produces a list of all SQL statements executed along with their
execution plans and some performance statistics. These metrics are
aggregated for each unique SQL statement. Verify that excess parsing is
not occurring. Below is an example of a query that was parsed once for
each execution. Notice that in the count
column, the number of parses is equal to the number of executions. The
Parse row indicates the number of hard parses that occurred for the
statement. In the ideal case, the statement would be parsed once and
executed many times. call count cpu elapsed disk query current rows
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 27 0.02 0.00 0 0 0 0
Execute 27 0.00 0.00 0 0 0 0
Fetch 108 0.03 0.00 0 189 0 81
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 162 0.05 0.00 2 189 0 81
Once the application has been corrected, the size of the shared pool
should be reevaluated to determine if it could be reduced to its
original size. If shared pool flushes were employed as a temporary
remedy, try to reduce the number of flushes to perhaps once per day.
Excessive shared pool flushes will also result in performance
degradation.
Collect Schema and DB
Statistics
Is CRITICAL for Oracle to have accurate statistics. More information HERE. Examples:
--For one Table and all its indexes
BEGIN dbms_stats.gather_table_stats
(ownname => 'LABTEST',
tabname => 'DIEGO',
partname => null,
estimate_percent => 10, --or DBMS_STATS.AUTO_SAMPLE_SIZE
degree => 3 ,
cascade => true); END;
--For a Full Schema
BEGIN dbms_stats.gather_schema_stats(ownname => 'LABTEST',
estimate_percent => 10,
granularity =>
'ALL',
method_opt => 'FOR ALL COLUMNS', --or method_opt=>'FOR ALL
COLUMNS SIZE AUTO'
degree => DBMS_STATS.DEFAULT_DEGREE,
options => 'GATHER
AUTO',
cascade => TRUE ); END;
Redo
Logs Switches
Check Alert Log File to see frequency of Redo Log Swtiches. If you see
errors there or that the switches are too often (ideally once every 30
minutes), then :
1- Increase Redo Log Files
2- Add more groups
3- Modify LOG_CHECKPOINT_TIMEOUT=0 and duplicate the value on
LOG_CHECKPOINT_INTERVAL
spool
redo_log_switches.txt
set pages 100
column
d1 form
a20 heading "Date"
column
sw_cnt form
99999 heading
'Number|of|Switches'
column
Mb form
999,999 heading "Redo Size"
column redoMbytes form
999,999,9999 heading "Redo Log File Size (Mb)"
break on report
compute sum of sw_cnt on report
compute sum of Mb on report
var redoMbytes number;
begin
select
max(bytes)/1024/1024 into :redoMbytes from v$log;
end;
/
print redoMbytes
select trunc(first_time) d1
, count(*) sw_cnt
, count(*) * :redoMbytes Mb
from v$log_history
group by trunc(first_time);
spool off
Check for Large Table Full
Scans
spool large_table_scans.txt
--Find Large Table
Scans
SELECT
substr(table_owner,1,10)
Owner,
substr(table_name,1,15) Table_Name,
size_kb, statement_count, reference_count,
substr(executions,1,4) Exec,
substr(executions * reference_count,1,8) tot_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
AND a.object_owner != 'SYS'
GROUP BY a.object_owner, a.object_name, a.operation, b.bytes/1024,
b.segment_type
ORDER BY 4 DESC, 1, 2 );
spool off
spool
recent_full_table_scans.txt
-- Recent full table scan
-- Should be run as SYS user
set verify off
col object_name form a30
col o.owner form
a15
PROMPT Column flag in x$bh table
is set to value 0x80000, when
PROMPT block was read by a
sequential scan.
SELECT
o.object_name,o.object_type,o.owner, count(*)
FROM dba_objects
o,x$bh x
WHERE
x.obj=o.object_id
AND
o.object_type='TABLE'
AND
standard.bitand(x.flag,524288)>0
AND
o.owner<>'SYS'
having count(*) > 2
group by o.object_name,o.object_type,o.owner
order by 4 desc;
spool off
spool unused_indexes.txt
-- Do these tables contain
indexes ??
-- This query creates a mini "unused indexes" report that you can use
to ensure that
-- any large tables that are being scanned on your system have the
proper indexing scheme.
SELECT DISTINCT
substr(a.object_owner,1,10) table_owner,
substr(a.object_name,1,15) table_name,
b.bytes / 1024 size_kb,
d.index_name
FROM sys.v_$sql_plan a,
sys.dba_segments b, sys.dba_indexes d
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 b.bytes / 1024 >
1024
AND b.segment_name =
d.table_name
AND b.owner =
d.table_owner
AND b.owner != 'SYS'
ORDER BY 1, 2;
spool off
spool physical_IO.txt
--How much
physical I/O, etc., a large table scan causes on a system
--It displays I/O and some wait metrics that can give a DBA more
insight into what Oracle is doing behind the scenes to access
the
object.
--Solution:
Create indexes, force use with hints
SELECT DISTINCT
substr(a.object_owner,1,8) table_owner,
substr(a.object_name,1,15) table_name,
b.bytes / 1024 size_kb,
substr(c.tablespace_name,1,10) Tablespace,
substr(c.statistic_name,1,27) Statistic_Name ,
substr(c.value,1,5) Value
FROM sys.v_$sql_plan a,
sys.dba_segments b,
sys.v_$segment_statistics 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 b.bytes / 1024
> 1024
AND b.owner = c.owner
AND b.owner != 'SYS'
AND b.segment_name =
c.object_name
ORDER BY 1, 2;
spool off
Solution
Create indexes, force use with hints
Check for Small Table and
Index Full-Scans
spool Object_Access.txt
--
You detect this by watching db file scattered reads' on top 5 wait
events
set
heading on
set feedback on
set linesize 120
ttitle 'Full Table Scans and Counts| |The "K" indicates that the
table is in the KEEP Pool.'
select substr(p.owner,1,10)
owner, substr(p.name,1,30) name,
t.num_rows,
-- ltrim(t.cache) ch,
decode(t.buffer_pool,'KEEP','Y','DEFAULT','N') K,
s.blocks blocks, sum(a.executions) nbr_FTS
from dba_tables t, dba_segments
s, v$sqlarea a,
(select
distinct address, object_owner owner, object_name name
from v$sql_plan
where operation = 'TABLE ACCESS'
and options = 'FULL') p
where a.address = p.address
and t.owner = s.owner
and t.table_name =
s.segment_name
and t.table_name =
p.name
and t.owner = p.owner
and t.owner not in
('SYS','SYSTEM')
having sum(a.executions) > 1
group by p.owner, p.name,
t.num_rows, t.cache, t.buffer_pool, s.blocks
order by sum(a.executions) desc;
column nbr_scans
format 999,999,999
column num_rows
format 999,999,999
column tbl_blocks format
999,999,999
column
owner format a15;
column table_name format a25;
column index_name format a25;
ttitle 'Index full scans and
counts'
select p.owner, d.table_name,
p.name index_name,
seg.blocks tbl_blocks, sum(s.executions) nbr_scans
from dba_segments seg, v$sqlarea
s, dba_indexes d,
(select distinct address,
object_owner owner, object_name name
from v$sql_plan
where
operation = 'INDEX'
and options = 'FULL SCAN') p
where d.index_name = p.name
and s.address =
p.address
and d.table_name =
seg.segment_name
and seg.owner =
p.owner
and seg.owner not in
('SYS','SYSTEM')
having sum(s.executions) > 9
group by p.owner, d.table_name,
p.name, seg.blocks
order by sum(s.executions) desc;
ttitle 'Index range scans
and
counts'
select p.owner, d.table_name,
p.name index_name,
seg.blocks tbl_blocks, sum(s.executions) nbr_scans
from dba_segments seg, v$sqlarea
s, dba_indexes d,
(select distinct address,
object_owner owner, object_name name
from v$sql_plan
where
operation = 'INDEX'
and options = 'RANGE SCAN') p
where d.index_name = p.name
and s.address =
p.address
and d.table_name =
seg.segment_name
and seg.owner =
p.owner
and seg.owner not in
('SYS','SYSTEM')
having sum(s.executions) > 9
group by p.owner, d.table_name,
p.name, seg.blocks
order by sum(s.executions) desc;
ttitle 'Index unique scans
and
counts'
select p.owner, d.table_name,
p.name index_name, sum(s.executions) nbr_scans
from v$sqlarea s, dba_indexes d,
(select distinct address,
object_owner owner, object_name name
from v$sql_plan
where
operation = 'INDEX'
and options = 'UNIQUE SCAN') p
where d.index_name = p.name
and s.address =
p.address
having sum(s.executions) > 9
group by p.owner, d.table_name,
p.name
order by sum(s.executions) desc;
spool off
Solution
Check if is it OK those access. Pin those tables and indexes.
Example: alter table/index …. Storage (buffer_pool keep);
Check for many
indexes on data buffer cache
Query the tables $BH and user_indexes
spool
indexused_on_data_buffer_cache.txt
--Solution:
Adjust parameters OPTIMIZER_INDEX_COST_ADJ=20 AND
OPTIMIZER_INDEX_CACHING with the % of indexes on data buffer cache
/*
Recently used indexes */
/* Should be run as SYS user */
set serverout on size 1000000
set verify off
column owner format a20 trunc
column segment_name format a30
trunc
select distinct b.owner,
b.segment_name
from x$bh a,
dba_extents b
where
b.file_id=a.dbarfil
and
a.dbablk between b.block_id
and
b.block_id+blocks-1
and
segment_type='INDEX'
and b.owner = upper('&OWNER')
/
spool off
Solution
Adjust parameters OPTIMIZER_INDEX_COST_ADJ=20 AND
OPTIMIZER_INDEX_CACHING with the % of indexes on data buffer cache
Check for skewed Indexes (Unbalanced)
Another performance issue could be that your indexes are skewed, this
happens when you have a lot of DML activity in your tables. In order to
check that, perform the following steps:
1- Analyze your indexes with compute (or estimate if the you have more
than 100,000 rows in your table)
analyze index xxxxxxx compute statistics;
2- Run the following query to see the BLEVEL of the index and if you
need to rebuid them. If the blevel is higher than 3, you should rebuild
it.
spool
Unbalanced_Indexes.txt
--If
the blevel is higher than 3, you should rebuild
it
select
substr(table_name,1,15) "Table Name",
substr(index_name,1,20) "Index Name", blevel,
decode(blevel,0,'OK BLEVEL',1,'OK BLEVEL',
2,'OK BLEVEL',3,'OK BLEVEL', null,'?????????','***BLEVEL HIGH****') OK
from
dba_indexes
where
owner=UPPER('&OWNER')
order
by 1,2;
spool off
3- Gather more index statistics using the VALIDATE STRUCTURE option of
the ANALYZE command to populate the INDEX_STATS virtual table.
analyze index
xxxxxxxxx validate structure;
4-The INDEX_STATS view will hold information for one index at a time:
it
will never contain more than one row. Therefore you need to query this
view before you analyze next index
select name "INDEXNAME", HEIGHT,
DEL_LF_ROWS*100/decode(LF_ROWS, 0, 1, LF_ROWS)
PCT_DELETED,
(LF_ROWS-DISTINCT_KEYS)*100/ decode(LF_ROWS,0,1,LF_ROWS) DISTINCTIVENESS
from index_stats;
The PCT_DELETED column shows what percent of leaf entries (index
entries) have been deleted and remain unfilled. The more deleted
entries exist on an index, the more unbalanced the index becomes. If
the PCT_DELETED is 20% or higher, the index is candidate for
rebuilding. If you can afford to rebuild indexes more frequently,
then do so if the value is higher than 10%.
Leaving indexes with high PCT_DELETED without rebuild
might cause excessive redo allocation on some systems.
The DISTINCTIVENESS column shows how often a value for the column(s) of
the index is repeated on average. For example, if a table has 10000
records and 9000 distinct SSN values, the formula would result in
(10000-9000) x 100 / 10000 = 10. This shows a good distribution of
values. If, however, the table has 10000 records and only 2 distinct
SSN values, the formula would result in (10000-2) x 100 /10000 = 99.98.
This shows that there are very few distinct values as a percentage of
total records in the column. Such columns are not candidates for a
rebuild but good candidates for bitmapped indexes.
The following PL/SQL code will analyze your indexes and then create a
report of the indexes to rebuild. Run it as the owner of the indexes.
declare
pMaxHeight integer
:= 3;
pMaxLeafsDeleted
integer := 20;
cursor csrIndexStats
is
select name, height, lf_rows as leafRows,
del_lf_rows as leafRowsDeleted
from index_stats;
vIndexStats
csrIndexStats%rowtype;
cursor
csrGlobalIndexes is
select index_name, tablespace_name
from user_indexes
where partitioned = 'NO';
cursor
csrLocalIndexes is
select index_name, partition_name, tablespace_name
from user_ind_partitions
where status = 'USABLE';
vCount integer := 0;
begin
dbms_output.enable(100000);
/* Working with
Global/Normal indexes */
for vIndexRec in
csrGlobalIndexes
loop
execute immediate 'analyze index ' || vIndexRec.index_name ||' validate
structure';
open csrIndexStats;
fetch csrIndexStats into vIndexStats;
if
csrIndexStats%FOUND then
if (vIndexStats.height > pMaxHeight)
or (vIndexStats.leafRows > 0
and vIndexStats.leafRowsDeleted > 0
and (vIndexStats.leafRowsDeleted * 100 / vIndexStats.leafRows) >
pMaxLeafsDeleted) then
vCount := vCount + 1;
dbms_output.put_line('Rebuilding index ' || vIndexRec.index_name ||
'...');
execute immediate 'alter index ' || vIndexRec.index_name ||
' rebuild online parallel
nologging compute statistics' ||
' tablespace ' || vIndexRec.tablespace_name;
end if;
end if;
close csrIndexStats;
end loop;
dbms_output.put_line('Global indexes rebuilt: ' || to_char(vCount));
vCount := 0;
/* Local indexes */
for vIndexRec in
csrLocalIndexes
loop
execute immediate 'analyze index ' || vIndexRec.index_name ||
' partition (' || vIndexRec.partition_name ||
') validate structure';
open csrIndexStats;
fetch csrIndexStats into vIndexStats;
if
csrIndexStats%FOUND then
if (vIndexStats.height > pMaxHeight)
or (vIndexStats.leafRows > 0
and vIndexStats.leafRowsDeleted > 0
and (vIndexStats.leafRowsDeleted * 100 / vIndexStats.leafRows) >
pMaxLeafsDeleted) then
vCount := vCount + 1;
dbms_output.put_line('Rebuilding index ' || vIndexRec.index_name ||
'...');
execute immediate 'alter index ' || vIndexRec.index_name ||
' rebuild partition ' ||
vIndexRec.partition_name ||
' online parallel nologging estimate
statistics' ||
' tablespace ' || vIndexRec.tablespace_name;
end if;
end if;
close csrIndexStats;
end loop;
dbms_output.put_line('Local indexes rebuilt: ' || to_char(vCount));
end RebuildUnbalancedIndexes;
/
Fragmentation on DB Objects
Another performance problem may be the DB fragmentation. Run the
following to detect:
REM Segments that are fragmented
and level of fragmentation
REM It counts number of extents
set heading on
set termout on
set pagesize 66
set line 132
select substr(de.owner,1,8)
"Owner",
substr(de.segment_type,1,8) "Seg_Type",
substr(de.segment_name,1,25) "Segment_Name",
substr(de.tablespace_name,1,15) "Tblspace_Name",
count(*) "Frag NEED",
substr(df.name,1,40) "DataFile_Name"
from sys.dba_extents de,
v$datafile df
where de.owner not in
('SYS','SYSTEM')
and de.file_id = df.file#
and de.segment_type =
'TABLE'
group by de.owner,
de.segment_name, de.segment_type, de.tablespace_name, df.name
having count(*) > 4
order by count(*) asc;
Tuning buffer cache
Step 1.Identify how frequently data blocks are accessed
from
the buffer cache (a. k. a Block Buffer Hit Ratio).
Oracle database maintains
dynamic
performance view V$BUFFER_POOL_STATISTICS with overall buffer usage
statistics. This view maintains the following counts every time a data
block is accessed either from the block buffers or from the disk:
NAME – Name of the buffer pool
PHYSICAL_READS – Number of physical reads
DB_BLOCK_GETS – Number of reads for INSERT, UPDATE and DELETE
CONSISTENT_GETS – Number of reads for SELECT
DB_BLOCK_GETS + CONSISTENT_GETS
= Total Number of reads
Based on above statistics we can calculate
the percentage of data
blocks being accessed from the memory to that of the disk (block buffer
hit ratio). The following SQL statement will return the block buffer
hit ratio:
SELECT NAME, 100 – round
((PHYSICAL_READS / (DB_BLOCK_GETS +
CONSISTENT_GETS))*100,2) HitRatio
FROM V$BUFFER_POOL_STATISTICS;
NAME
HITRATIO
-------------------- ----------
DEFAULT
96.82
Before measuring the database buffer hit
ratio, it is very important to
check that the database is running in a steady state with normal
workload and no unusual activity has taken place. For example, when you
run a SQL statement just
after database startup, no data blocks have been cached in the block
buffers. At this point, Oracle reads the data blocks from the disk and
will cache the blocks in the memory. If you run the same SQL statement
again, then most likely the data blocks will still be present in the
cache, and Oracle will not have to perform disk IO. If you run the same
SQL statement multiple times you will get a higher buffer hit ratio. On
the other hand, if you either run SQL statements that rarely query the
same data, or run a select on a very large table, the data block may
not be in the buffer cache and Oracle will have to perform disk IO,
thereby lowering the buffer hit ratio.
A hit ratio of 95% or greater is considered to be a good hit ratio for
OLTP systems. The hit ratio for DSS (Decision Support System) may vary
depending on the database load. A lower hit ratio means Oracle is
performing more disk IO on the server. In such a situation, you can
increase the size of database block buffers to increase the database
performance. You may have to increase the physical memory on the server
if the server starts swapping after increasing block buffers.
Step 2: Identify frequently used and rarely used data blocks. Cache
frequently used blocks and discard rarely used blocks.
If you have a low buffer hit ratio and you cannot increase the size of
the database block buffers, you can still gain some performance
advantage by tuning the block buffers and efficiently caching the data
block that will provide maximum benefits. Ideally, we should cache data
blocks that are either frequently used in SQL statements, or data
blocks used by performance sensitive SQL statements (A SQL statement
whose performance is critical to the system performance). An ad-hoc
query that scans a large table can significantly degrade overall
database performance. A SQL on a large table may flush out frequently
used data blocks from the buffer cache to store data blocks from the
large table. During the peak time, ad-hoc queries that select data from
large tables or from tables that are rarely used should be avoided. If
we cannot avoid such queries, we can limit the impact on the buffer
cache by using RECYCLE buffer pool.
A DBA can create multiple buffer pools in the SGA to store data blocks
efficiently. For example, we can use RECYCLE pool to cache data blocks
that are rarely used in the application. Typically, this will be a
small area in the SGA to store data blocks for current SQL statement /
transaction that we do not intend to hold in the memory after the
transaction is completed. Similarly, we can use KEEP pool to cache data
blocks that are frequently used by the application. Typically, this
will be big enough to store data blocks that we want to always keep in
memory. By storing data blocks in KEEP and RECYCLE pools you can store
frequently used data blocks separately from the rarely used data
blocks, and control which data blocks are flushed from the buffer
cache. Using RECYCLE pool, we can also prevent a large table scan from
flushing frequently used data blocks. You can create the RECYCLE and
KEEP pools by specifying the following init.ora parameters:
DB_KEEP_CACHE_SIZE = <size of
KEEP pool>
DB_RECYCLE_CACHE_SIZE = < size of RECYCLE pool>
When you use the above parameters, the
total memory allocated to the
block buffers is the sum of DB_KEEP_CACHE_SIZE, DB_RECYCLE_CACHE_SIZE,
and
DB_CACHE_SIZE.
Step 3: Assign tables to KEEP /
RECYCLE
pool. Identify buffer hit ratio for KEEP, RECYCLE, and DEFAULT pool.
Adjust the initialization parameters for optimum performance.
By default, data blocks are cached
in the DEFAULT pool. The DBA must configure the table to use the KEEP
or the RECYCLE pool by specifying BUFFER_POOL keyword in the CREATE
TABLE or the ALTER TABLE statement. For example, you can assign a table
to the recycle pool by using the following ALTER TABLE SQL statement.
ALTER TABLE <TABLE NAME>
STORAGE (BUFFER_POOL RECYCLE)
The DBA can take help from application
designers in identifying tables that should use KEEP or RECYCLE pool.
You can also query X$BH to examine the current block buffer usage by
database objects (You must log in as SYS to query X$BH).
spool tables_to_RECYCLE_Pool.txt
--The following
query returns a list of tables that are rarely used and can be assigned
to the RECYCLE pool.
Col owner
format a14
Col object_name format a36
Col object_type format a15
SELECT o.owner, object_name, object_type, COUNT(1) buffers
FROM SYS.x$bh, dba_objects o
WHERE (tch = 1 OR (tch = 0 AND lru_flag < 8))
AND obj = o. object_id
AND o.owner upper('&OWNER')
GROUP BY o.owner,
object_name, object_type
ORDER BY buffers;
spool off
spool tables_to_KEEP_Pool.txt
--The following query will
return a list
of tables that are frequently
-- used by SQL statements and can be
assigned to the KEEP pool.
Col owner
format a14
Col object_name format a36
Col object_type format a15
SELECT o.owner, object_name,
object_type, COUNT(1) buffers
FROM SYS.x$bh, dba_objects o
WHERE tch > 10
AND lru_flag = 8
AND obj = o.object_id
AND o.owner =
upper('&OWNER')
GROUP BY o.owner,
object_name, object_type
ORDER BY buffers;
spool off
Once you have setup the database to use
KEEP and RECYCLE pools, you can monitor the buffer hit ratio by
querying V$BUFFER_POOL_STATISTICS and V$DB_CACHE_ADVICE to adjust the
buffer pool initialization parameters.
Step 4: Identify the amount of memory
needed to maintain required performance.
Oracle 9i maintains block buffer advisory information in
V$DB_CACHE_ADVICE. This view contains simulated physical reads for a
range of buffer cache sizes. The DBA can query this view to estimate
buffer cache requirement for the database. The cache advisory can be
activated by setting DB_CACHE_ADIVE initialization parameter.
DB_CACHE_ADVICE = ON
There is a minor overhead associated with
cache advisory collection.
Hence, it is not advisable to collect these statistics in production
databases until there is a need to tune the buffer cache. The DBA can
turn on DB_CACHE_ADVISE dynamically for the duration of sample workload
period and collect advisory statistics.
Conclusion
Using this methodical approach, a DBA
can
easily identify the problem areas, and tune the database block buffers.
The DBA can create the following buffer pool to efficiently cache data
blocks in
SGA:
- KEEP: Cache tables that are very
critical for system performance. Typically, lookup tables are very good
candidates for the KEEP pool. The DBA should create the KEEP pool large
enough to maintain 99% buffer hit ratio on this pool.
- RECYCLE: Cache tables that are
not
critical for system performance. Typically, a table containing
historical information that is either rarely queried or used by batch
process is a good candidate for the RECYCLE pool. The DBA should create
the RECYCLE pool large enough to finish the current transaction.
- DEFAULT: Cache tables that do
not belong to either KEEP or RECYCLE pool.
The DBA can setup OEM jobs, Oracle
statspack, or custom monitoring scripts to monitor your production
database block buffer efficiency, and to identify and tune the problem
area.
Check Size of LOG_BUFFER
Bigger is better and reduces I/O
Check ML 147471.1 item 4.
Check for contention on 'redo allocation latch', 'redo copy latch'.
Using that query check if 'redo log space request' not near 0, process
had to wait for space in the buffer
If you get 'redo allocation latch', then increase LOG_PARALLELISM
If you get 'redo copy latch', then increase _LOGSIMULTANEOUS_COPIES
(default is 2 times # of CPU)
Check Size of
SHARED_POOL_SIZE Variable
Usually we want this variable to be around 250-300MB.
Using the v_$SGASTAT, check if you see a large value under "shared pool
free memory", if so, reduce it. You don't want to have a big space with
lot of SQL Staments that are not re-used. If you have that, then Oracle
is going to take too long to find those statements in memory.
Allocate Files properly
(Tuning buffer busy waits by file)
Check for Buffer busy Waits.
This view (based on X$KCBWAIT) reports the number of times an instance
has had buffer busy waits on different classes of blocks since the
instance was started.
Oracle also provides a companion view called X$KCBFWAIT which
duplicates the function of X$KCBWAIT, but summarises the waits by file
id.
SPOOL file_wait.txt
SET linesize 180
SET pagesize 9000
COLUMN filename FORMAT
a40 HEAD
"File Name"
COLUMN
file# FORMAT
99
HEAD "F#"
COLUMN
ct FORMAT
999,999,999 HEAD "Waits"
COLUMN
time FORMAT 999,999,999 HEAD
"Time"
COLUMN
avg FORMAT
999.999 HEAD "Avg Time"
SELECT indx+1 file#
, b.name
filename
,
count ct
, time
,
time/(DECODE(count,0,1,count)) avg
FROM x$kcbfwait a,
v$datafile b
WHERE indx < (select count(*)
from v$datafile)
AND a.indx+1 = b.file#
order by ct desc
/
spool off
Checking ACTIVE
Statements
spool Active_Statements.txt
set linesize 110
--Extracting the active SQL a user is executing
select sesion.sid,
substr(sesion.username,1,15) username,
substr(optimizer_mode,1,10) opt_mode,
hash_value,
address,
cpu_time,
elapsed_time,
sql_text
from v$sqlarea sqlarea,
v$session sesion
where sesion.sql_hash_value
= sqlarea.hash_value
and
sesion.sql_address = sqlarea.address
and sesion.username
is not null;
--I/O being done by an active SQL statement
select sess_io.sid,
sess_io.block_gets,
sess_io.consistent_gets,
sess_io.physical_reads,
sess_io.block_changes,
sess_io.consistent_changes
from v$sess_io sess_io,
v$session sesion
where sesion.sid =
sess_io.sid
and sesion.username
is not null;
-- If by chance the query shown earlier in the V$SQLAREA view did not
show your full SQL text
-- because it was larger than 1000 characters, this V$SQLTEXT view
should be queried
-- to extract the full SQL. It is a piece by piece of 64 characters by
line,
-- that needs to be ordered by the column PIECE.
-- SQL to show the full SQL executing for active sessions
select sesion.sid,
sql_text
from v$sqltext sqltext,
v$session sesion
where sesion.sql_hash_value
= sqltext.hash_value
and
sesion.sql_address = sqltext.address
and sesion.username
is not null
order by sqltext.piece;
spool off
Use IPC for local connections
When a process is on the same machine as the server, use the IPC
protocol for connectivity instead of TCP. Inner Process Communication
on the same machine does not have the overhead of packet building and
deciphering that TCP has. I've seen a SQL job that runs in 10 minutes
using TCP on a local machine run as fast as one minute using an IPC
connection.
You can set up your tnsnames file like this on a local machine so that
local connection with use IPC connections first and then TCP connection
second.
PROD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(Key =
IPCKEY))
(ADDRESS = (PROTOCOL = TCP)(HOST =
MYHOST)(PORT = 1521))
)
(CONNECT_DATA =
(SID = PROD)
)
)
Check
undo parameters
When you are working with UNDO (instead of ROLLBACK), there are two
important things to consider:
The size of the UNDO tablespace
The UNDO_RETENTION parameter
There are two ways to proceed to optimize your resources.
You can choose to allocate a specific size for the UNDO tablespace and
then set the UNDO_RETENTION parameter to an optimal value according to
the UNDO size and the database activity. If your disk space is limited
and you do not want to allocate more space than necessary to the UNDO
tablespace, this is the way to proceed.
If you are not limited by disk space, then it would be better to choose
the UNDO_RETENTION time that is best for you (for FLASHBACK, etc.).
Allocate the appropriate size to the UNDO tablespace according to the
database activity.
This tip help you get the information you need whatever the method you
choose. It was tested on Oracle9i (9.2.0.4, 9.2.0.5).
spool
Check_Undo_Parameters.txt
set serverout on size
1000000
set feedback off
set heading off
set lines 132
declare
cursor get_undo_stat is
select d.undo_size/(1024*1024) "C1",
substr(e.value,1,25) "C2",
(to_number(e.value) * to_number(f.value) * g.undo_block_per_sec) /
(1024*1024) "C3",
round((d.undo_size / (to_number(f.value) *
g.undo_block_per_sec)))
"C4"
from (select sum(a.bytes) undo_size
from v$datafile a,
v$tablespace b,
dba_tablespaces c
where c.contents = 'UNDO'
and c.status = 'ONLINE'
and b.name = c.tablespace_name
and a.ts# = b.ts#) d,
v$parameter e,
v$parameter f,
(select max(undoblks/((end_time-begin_time)*3600*24))undo_block_per_sec
from v$undostat) g
where e.name = 'undo_retention'
and f.name = 'db_block_size';
begin
dbms_output.put_line(chr(10)||chr(10)||chr(10)||chr(10)
|| 'To optimize UNDO you have two choices :');
dbms_output.put_line('===================================================='
|| chr(10));
for rec1 in get_undo_stat
loop
dbms_output.put_line('A) Adjust UNDO tablespace size according to
UNDO_RETENTION :' || chr(10));
dbms_output.put_line(rpad('ACTUAL UNDO SIZE ',61,'.')|| ' : ' ||
TO_CHAR(rec1.c1,'999999') || ' MEGS');
dbms_output.put_line(rpad('OPTIMAL UNDO SIZE WITH ACTUAL UNDO_RETENTION
(' ||
ltrim(TO_CHAR(rec1.c2/60,'999999'))
|| ' MINUTES) ',61,'.') || ' : '
|| TO_CHAR(rec1.c3,'999999') || ' MEGS');
dbms_output.put_line(chr(10));
dbms_output.put_line('B) Adjust UNDO_RETENTION according to UNDO
tablespace size :' || chr(10));
dbms_output.put_line(rpad('ACTUAL UNDO RETENTION ',61,'.') || ' : ' ||
TO_CHAR(rec1.c2/60,'999999')
|| ' MINUTES');
dbms_output.put_line(rpad('OPTIMAL UNDO RETENTION WITH ACTUAL UNDO SIZE
(' || ltrim(TO_CHAR(rec1.c1,'999999'))
|| ' MEGS) ',61,'.') || ' : ' || TO_CHAR(rec1.c4/60,'999999')
|| ' MINUTES');
end loop;
dbms_output.put_line(chr(10)||chr(10));
end;
/
select 'Number of
"ORA-01555
(Snapshot too old)" encountered since the last startup of the instance
: ' || sum(ssolderrcnt)
from v$undostat;
spool off
Detect High SQL parse calls
One of the first things that an Oracle DBA does when checking the
performance of any database is to check for high-use SQL statements.
The script below will display all SQL where the number of parse calls
is more than twice the number of SQL executions. The output from this
script is a good starting point for detailed SQL tuning. This query can
also be modified to display the most frequently executed SQL statements
that reside in the library cache.
prompt
**********************************************************
prompt SQL High parse calls
prompt
**********************************************************
select sql_text, parse_calls,
executions
from v$sqlarea
where parse_calls > 300
and executions <
2*parse_calls
and executions >
1;
This script is great for finding non-reusable SQL statements that
contain embedded literals. As you may know, non-reusable SQL statements
place a heavy burden on the Oracle library cache. When
cursor_sharing=FORCE, Oracle8i will re-write the SQL with literal
values so it can use a host variable instead. This is a great “silver
bullet” for system where the literal SQL cannot be changed.
Monitor
Open and Cached Cursors
Open cursors take up space in the shared pool, in the library cache. To
keep a renegade session from filling up the library cache, or clogging
the CPU with millions of parse requests, we set the parameter
OPEN_CURSORS.
OPEN_CURSORS sets the maximum number of cursors each session can have
open, per session. For example, if OPEN_CURSORS is set to 1000, then
each session can have up to 1000 cursors open at one time. If a single
session has OPEN_CURSORS # of cursors open, it will get an ora-1000
error when it tries to open one more cursor.
The default is value for OPEN_CURSORS is 50, but Oracle recommends that
you set this to at least 500 for most applications. Some applications
may need more, eg. web applications that have dozens to hundreds of
users sharing a pool of sessions. Tom Kyte recommends setting it around
1000.
If SESSION_CACHED_CURSORS is not set, it defaults to 0 and no
cursors will be cached for your session. (Your cursors will still be
cached in the shared pool, but your session will have to find them
there.) If it is set, then when a parse request is issued, Oracle
checks the library cache to see whether more than 3 parse requests have
been issued for that statement. If so, Oracle moves the session cursor
associated with that statement into the session cursor cache.
Subsequent parse requests for that statement by the same session are
then filled from the session cursor cache, thus avoiding even a soft
parse. (Technically, a parse can't be completely avoided; a "softer"
soft parse is done that's faster and requires less CPU.)
The obvious advantage to caching cursors by session is reduced parse
times, which leads to faster overall execution times. This is
especially so for applications like Oracle Forms applications, where
switching from one form to another will close all the session cursors
opened for the first form. Switching back then opens identical cursors.
So caching cursors by session really cuts down on reparsing.
There's another advantage, though. Since a session doesn't have to go
looking in the library cache for previously parsed SQL, caching cursors
by session results in less use of the library cache and shared pool
latches. These are often points of contention for busy OLTP systems.
Cutting down on latch use cuts down on latch waits, providing not only
an increase in speed but an increase in scalability.
This will give the number of currently opened cursors, by session:
--total cursors open, by session
select a.value, s.username,
s.sid, s.serial#
from v$sesstat a,
v$statname b, v$session s
where a.statistic# =
b.statistic# and s.sid=a.sid
and b.name = 'opened cursors current';
If you're running several N-tiered applications with multiple
webservers, you may find it useful to monitor open cursors by username
and machine:
--total cursors open, by username
& machine
select sum(a.value) total_cur,
avg(a.value) avg_cur, max(a.value) max_cur, s.username, s.machine
from v$sesstat a,
v$statname b, v$session s
where a.statistic# =
b.statistic# and s.sid=a.sid
and b.name =
'opened cursors current'
group by s.username,
s.machine
order by 1 desc;
The best advice for tuning OPEN_CURSORS is not to tune it. Set it high
enough that you won't have to worry about it. If your sessions are
running close to the limit you've set for OPEN_CURSORS, raise it. If
you set OPEN_CURSORS to a high value, this doesn't mean that every
session will have that number of cursors open. Cursors are opened on an
as-needed basis
To see if you've set OPEN_CURSORS high enough, monitor v$sesstat for
the maximum opened cursors current. If your sessions are running close
to the limit, up the value of OPEN_CURSORS.
select max(a.value) as
highest_open_cur, p.value as max_open_cur
from v$sesstat a,
v$statname b, v$parameter p
where a.statistic# =
b.statistic#
and b.name =
'opened cursors current'
and p.name=
'open_cursors'
group by p.value;
HIGHEST_OPEN_CUR MAX_OPEN_CUR
---------------- ------------
1953 2500
Monitoring the session cursor
cache
v$sesstat also provides a statistic to monitor the number of
cursors each session has in its session cursor cache.
--session cached cursors, by session
select a.value, s.username, s.sid, s.serial#
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic# and s.sid=a.sid
and b.name = 'session cursor cache count' ;
You can also see directly what is in the session cursor cache by
querying v$open_cursor. v$open_cursor lists session cached cursors by
SID, and includes the first few characters of the statement and the
sql_id, so you can actually tell what the cursors are for.
select c.user_name, c.sid, sql.sql_text
from v$open_cursor c, v$sql sql
where c.sql_id=sql.sql_id
and c.sid=&sid;
Tuning SESSION_CACHED_CURSORS
If you choose to use SESSION_CACHED_CURSORS to help out an
application that is continually closing and reopening cursors, you can
monitor its effectiveness via two more statistics in v$sesstat. The
statistic "session cursor cache hits" reflects the number of times that
a statement the session sent for parsing was found in the session
cursor cache, meaning it didn't have to be reparsed and your session
didn't have to search through the library cache for it. You can compare
this to the statistic "parse count (total)"; subtract "session cursor
cache hits" from "parse count (total)" to see the number of parses that
actually occurred.
select cach.value cache_hits, prs.value all_parses, prs.value-cach.value sess_cur_cache_not_used
from v$sesstat cach, v$sesstat prs, v$statname nm1, v$statname nm2
where cach.statistic# = nm1.statistic#
and nm1.name = 'session cursor cache hits'
and prs.statistic#=nm2.statistic#
and nm2.name= 'parse count (total)'
and cach.sid= &sid and prs.sid= cach.sid ;
Enter value for sid: 947
old 8: and cach.sid= &sid and prs.sid= cach.sid
new 8: and cach.sid= 947 and prs.sid= cach.sid
CACHE_HITS ALL_PARSES SESS_CUR_CACHE_NOT_USED
---------- ---------- -----------------------
106 210 104
Monitor this in concurrence with the session cursor cache count.
--session cached cursors, for a given SID, compared to max
select a.value curr_cached, p.value max_cached, s.username, s.sid, s.serial#
from v$sesstat a, v$statname b, v$session s, v$parameter2 p
where a.statistic# = b.statistic# and s.sid=a.sid and a.sid=&sid
and p.name='session_cached_cursors'
and b.name = 'session cursor cache count' ;
Detect Top 10
Queries
in SQL Area
spool
top10_sqlarea.txt
/*
This script queries the SQL area ordered by the the average cost of
the statement.
The "Avg Cost" row is basically the No. of Buffer Gets per Rows
processed.
Where no rows are processed, all Buffer Gets are reported for the
statement.
The script also lists any potential candidates for a converting to
stored procedures
by running a case insensitive query.
*/
set pagesize 66 linesize
132
set echo off
column
executions heading
"Execs" format 99999999
column rows_processed
heading "Rows Procd" format 99999999
column
loads
heading "Loads" format
999999.99
column
buffer_gets heading "Buffer Gets"
column
disk_reads heading "Disk Reads"
column
elapsed_time heading "Elasped Time"
column
cpu_time heading "CPU Time"
column
sql_text heading "SQL
Text" format a120 wrap
column
avg_cost heading "Avg
Cost" format 99999999
column gets_per_exec
heading "Gets Per Exec" format 99999999
column reads_per_exec
heading "Read Per Exec" format 99999999
column rows_per_exec
heading "Rows Per Exec" format 99999999
break on report
compute sum of
rows_processed on report
compute sum of
executions on report
compute avg of
avg_cost on report
compute avg of
gets_per_exec on report
compute avg of
reads_per_exec on report
compute avg of
row_per_exec on report
PROMPT
PROMPT Top 10 most expensive SQL
by Elapsed Time...
PROMPT
select rownum as rank, a.*
from ( select
elapsed_Time, executions, buffer_gets, disk_reads, cpu_time,
hash_value, sql_text
from v$sqlarea
where elapsed_time > 20000
order by elapsed_time desc) a
where rownum < 11;
PROMPT
PROMPT Top 10 most expensive SQL
by CPU Time...
PROMPT
select rownum as rank, a.*
from ( select
elapsed_Time, executions, buffer_gets, disk_reads, cpu_time,
hash_value, sql_text
from v$sqlarea
where cpu_time > 20000
order by cpu_time desc) a
where rownum < 11;
PROMPT
PROMPT Top 10 most expensive SQL
by Buffer Gets by Executions...
PROMPT
select rownum as rank, a.*
from (select buffer_gets,
executions,
buffer_gets/ decode(executions,0,1, executions) gets_per_exec,
hash_value, sql_text
from v$sqlarea
where buffer_gets > 50000
order by buffer_gets desc) a
where rownum < 11;
PROMPT
PROMPT Top 10 most expensive SQL
by Physical Reads by Executions...
PROMPT
select rownum as rank, a.*
from (select disk_reads,
executions,
disk_reads / decode(executions,0,1, executions) reads_per_exec,
hash_value, sql_text
from v$sqlarea
where disk_reads > 10000
order by disk_reads desc) a
where rownum < 11;
PROMPT
PROMPT Top 10 most expensive SQL
by Rows Processed by Executions...
PROMPT
select rownum as rank, a.*
from (select rows_processed,
executions,
rows_processed / decode(executions,0,1, executions) rows_per_exec,
hash_value, sql_text
from v$sqlarea
where rows_processed > 10000
order by rows_processed desc) a
where rownum < 11;
PROMPT
PROMPT Top 10 most expensive SQL
by Buffer Gets vs Rows Processed...
PROMPT
select rownum as rank, a.*
from ( select buffer_gets,
lpad(rows_processed ||
decode(users_opening + users_executing, 0, ' ','*'),20)
"rows_processed",
executions, loads,
(decode(rows_processed,0,1,1)) * buffer_gets/
decode(rows_processed,0,1,rows_processed) avg_cost,
sql_text
from v$sqlarea
where decode(rows_processed,0,1,1) * buffer_gets/
decode(rows_processed,0,1,rows_processed) > 10000
order by 5 desc) a
where rownum < 11;
rem Check to see if there are any
candidates for procedures or
rem for using bind variables.
Check this by comparing UPPER
rem
rem This May be a candidate
application for using the init.ora parameter
rem CURSOR_SHARING = FORCE|SIMILAR
select rownum as rank, a.*
from (select
upper(substr(sql_text, 1, 65)) sqltext, count(*)
from v$sqlarea
group by upper(substr(sql_text, 1, 65))
having count(*) > 1
order by count(*) desc) a
where rownum < 11;
prompt Output spooled to top10_sqlarea.txt
spool off
If you want to see the full text of the sql statement, you can run the
following query:
select v2.sql_text, v2.address
from v$sqlarea v1, v$sqltext v2
where v1.address=v2.address
and v1.sql_text like 'SELECT
COUNT(*) FROM DEPT%'
order by v2.address, v2.piece;
The next query returns the SQL text from a hash value that must be
determined from each v$sqlarea row in question.
select sql_text
from v$sqltext
where hash_value=&hash_value
order by piece;
Check for Indexes not
Used and HOT Tables
If you want to know if an index has ever been used since instance
startup, or the use of a specific table, the solution is quite
easy.
Simply query V$SEGMENT_STATISTICS to see if there has even been a
physical read on the index in question. Queries similar to the
following can help:
select index_name from all_indexes
where owner = 'FRAUDGUARD'
and index_name not
in ( select object_name
from v$segment_statistics
where owner='FRAUDGUARD'
and statistic_name='physical reads');
If you get no rows, that means that all your indexes has been used.
Next, we'll determine the top 10 tables that have incurred the most
physical I/O operations.
select table_name,total_phys_io
from (select
owner||'.'||object_name as table_name, sum(value) as total_phys_io
from v$segment_statistics
where owner='FRAUDGUARD'
and
object_type='TABLE'
and statistic_name in ('physical reads','physical reads
direct','physical
writes','physical writes direct')
group by owner||'.'||object_name
order by total_phys_io desc)
where rownum <=10;
TABLE_NAME
TOTAL_PHYS_IO
-------------------------------------------------------------
-------------
FG83_DEV.FLOWDOCUMENT_ARCH
1011844
FG83_DEV.FLOWDOCUMENT
697512
FG83_DEV.FLOWFIELD_ARCH
21423
FG83_DEV.USERACTIVITYLOG_ARCH
13987
FG83_DEV.FLOWDATA
13607
FG83_DEV.USERACTIVITYLOG
12334
FG83_DEV.SIGNATURES
8992
FG83_DEV.PROCESSLOG
4764
FG83_DEV.EXCEPTIONITEM_ARCH
399
FG83_DEV.USERLEVELPERMISSION
276
The query above eliminated any data dictionary tables from the results.
It should now be clear what the exact table is that experiences the
most physical I/O operations. Appropriate actions can now be taken to
isolate this potential hotspot from other highly active database
segments.
If you've ever dealt with wait events, you may have seen the 'buffer busy waits'
event. This event occurs when one session is waiting on another session
to read the buffer into the cache, or some other session is changing
the buffer. This even can often be seen when querying V$SYSTEM_EVENT.
If I query my database, I have approximately 13 million waits on this
specific event.
select event,total_waits from
v$system_event
where
event='buffer busy waits';
EVENT
TOTAL_WAITS
----------------------------------------
-----------
buffer busy
waits
12976210
The big question is to determine which segments are contributing to
this overall wait event. Querying V$SEGMENT_STATISTICS can help us
determine the answer.
select
substr(segment_name,1,30) segment_name,
object_type,total_buff_busy_waits
from (select
owner||'.'||object_name as segment_name,object_type, value as total_buff_busy_waits
from v$segment_statistics
where statistic_name in ('buffer busy waits')
order by total_buff_busy_waits desc)
where rownum <=10;
SEGMENT_NAME
OBJECT_TYPE TOTAL_BUFF_BUSY_WAITS
-----------------------------------
------------- ---------------------
WEBMAP.SDE_BLK_1103
TABLE
10522135
WEBMAP.SDE_BLK_804
TABLE
1176185
SRTM.SDE_BLK_1101
TABLE
651175
WEBMAP.SDE_BLK_804_UK
INDEX
100242
SYS.DBMS_LOCK_ALLOCATED
TABLE
64695
NED.SDE_BLK_1002
TABLE
48582
WEBMAP.BTS_ROADS_MD
TABLE
27068
WEBMAP.SDE_BLK_1103_UK
INDEX
25707
ARCIMS.SDE_LOGFILE_DATA_IDX1
INDEX
24618
NED.SDE_BLK_62
TABLE
14710
From the query above, we can see that one specific table contributed
10.5 million, or approximately 80%, of the total waits.
If you ever want to know why the access to a specific table (Example:
EMP) is slow, one of the first actions would be to run:
select statistic_name, value
from v$segment_statistics
where owner='SCOTT' and
object_name = 'EMP';
STATISTIC_NAME
VALUE
----------------------------------------------------------------
----------
logical
reads
17653
buffer busy
waits
1744
db block
changes
16234
physical
reads
1110
physical
writes
516
physical reads
direct
0
physical writes
direct
0
global cache cr blocks
served
0
global cache current blocks
served
0
ITL
waits
0
row lock
waits
6
From the above query we can see that EMP is forever being modified and
rarely just being selected. And those modifications has problems
because of the high number of bussy waits (users try to access to the
same block). Perhaps if that table has a higher PCTFREE the problem
would disappear. Or maybe this is a case for ASSM.
Detect and Resolve Buffer Busy Waits
One of the most confounding problems with
Oracle is the resolution of buffer busy wait events. Buffer busy waits
are common in an I/O-bound Oracle system, as evidenced by any system
with read (sequential/scattered) waits in the top-five waits in the
Oracle STATSPACK report, like this:
Top 5 Timed Events
% Total
Event
Waits Time
(s) Ela Time
--------------------------- ------------ ----------- -----------
db file sequential
read
2,598
7,146 48.54
db file scattered read
25,519
3,246 22.04
library cache load
lock
673
1,363
9.26
CPU
time 2,154
934
7.83
log file parallel write
19,157
837
5.68
The main way to reduce buffer busy waits
is to reduce the total I/O
on the system. This can be done by tuning the SQL to access rows with
fewer block reads (i.e., by adding indexes). Even if we have a huge db_cache_size,
we may still see buffer busy waits, and increasing the buffer size
won't help.
In order to look at system-wide wait events, we can query the v$system_event
performance view. This view, shown below, provides the name of the wait
event, the total number of waits and timeouts, the total time waited,
and the average wait time per event.
spool Wait_Events.txt
select substr(event,1,25) event, total_waits, total_timeouts,
time_waited, average_wait
from
v$system_event
where
event like '%wait%'
order by 2 desc;
spool off
EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT
--------------------------- ----------- -------------- ----------- ------------
buffer busy waits 636528 1557 549700 .863591232
write complete waits 1193 0 14799 12.4048617
free buffer waits 1601 0 622 .388507183
The
type of buffer that
causes the wait can be queried using the v$waitstat view. This view
lists the waits per
buffer type for buffer busy waits, where COUNT is the sum of all waits
for the class of block, and TIME is the sum of all wait times for that
class:
select * from v$waitstat;
CLASS
COUNT TIME
------------------ ---------- ----------
data
block
1961113 1870278
segment
header
34535 159082
undo
header
233632 86239
undo
block 1886
1706
Buffer
busy waits occur when
an Oracle session needs to access a block in the buffer cache, but
cannot because the buffer copy of the data block is locked. This buffer
busy wait condition can happen for either of the following reasons:
- The block is being read into the
buffer by another session, so
the waiting session must wait for the block read to complete.
- Another session has the buffer block locked in a mode that is
incompatible with the waiting session's request.
Because buffer busy waits are due to
contention between particular
blocks, there's nothing you can do until you know which blocks are in
conflict and why the conflicts are occurring. Tuning therefore involves
identifying and eliminating the cause of the block contention.
The v$session_wait performance view, shown below, can give some
insight into what is being waited for and why the wait is occurring.
SQL>
desc v$session_wait
Name
Null? Type
-----------------------------------------
-------- ---------------------
SID
NUMBER
SEQ#
NUMBER
EVENT
VARCHAR2(64)
P1TEXT
VARCHAR2(64)
P1
NUMBER
P1RAW
RAW(4)
P2TEXT
VARCHAR2(64)
P2
NUMBER
P2RAW
RAW(4)
P3TEXT
VARCHAR2(64)
P3
NUMBER
P3RAW
RAW(4)
WAIT_TIME
NUMBER
SECONDS_IN_WAIT
NUMBER
STATE
VARCHAR2(19)
The columns of the v$session_wait
view that are of
particular interest for a buffer busy wait event are:
- P1—The absolute file number
for the data file involved in
the wait.
- P2—The block number within the data file referenced in P1
that is being waited upon.
- P3—The reason code describing why the wait is occurring.
Here's an Oracle data dictionary query for
these values:
select p1 "File
#", p2 "Block #", p3 "Reason Code"
from v$session_wait
where event = 'buffer busy waits';
If the output from repeatedly running the above query shows that a
block or range of blocks is experiencing waits, the following query
should show the name and type of the segment:
select owner,
segment_name, segment_type
from dba_extents
where file_id = &P1
and &P2 between block_id and block_id + blocks -1;
Once the segment is identified, the v$segment_statistics
performance view facilitates real-time monitoring of segment-level
statistics. This enables a DBA to identify performance problems
associated with individual tables or indexes, as shown below.
select
object_name, statistic_name, value
from
V$SEGMENT_STATISTICS
where
object_name = 'SOURCE$';
OBJECT_NAME STATISTIC_NAME
VALUE
-----------
-------------------------
----------
SOURCE$
logical reads
11216
SOURCE$
buffer busy
waits
210
SOURCE$
db block
changes
32
SOURCE$
physical
reads
10365
SOURCE$
physical
writes
0
SOURCE$
physical reads
direct
0
SOURCE$
physical writes
direct
0
SOURCE$
ITL
waits
0
SOURCE$
row lock waits
We can also query the dba_data_files
to determine the file_name
for the file involved in the wait by using the P1 value from v$session_wait
for the file_id.
SQL>
desc dba_data_files
Name
Null? Type
-----------------------------------------
-------- ----------------------------
FILE_NAME
VARCHAR2(513)
FILE_ID
NUMBER
TABLESPACE_NAME
VARCHAR2(30)
BYTES
NUMBER
BLOCKS
NUMBER
STATUS
VARCHAR2(9)
RELATIVE_FNO
NUMBER
AUTOEXTENSIBLE
VARCHAR2(3)
MAXBYTES
NUMBER
MAXBLOCKS
NUMBER
INCREMENT_BY
NUMBER
USER_BYTES
NUMBER
USER_BLOCKS
NUMBER
Interrogating the P3 (reason code) value
from v$session_wait
for a buffer busy wait event will tell us why the session is waiting.
The reason codes range from 0 to 300 and can be decoded, as shown in Table
A.
Table A
Code |
Reason
for wait |
- |
A modification is
happening on a
SCUR or XCUR buffer but has not yet completed. |
0 |
The block is being read
into the
buffer cache. |
100 |
We want to NEW the block,
but the
block is currently being read by another session (most likely for undo).
|
110 |
We want the CURRENT block
either
shared or exclusive but the block is being read into cache by another
session, so we have to wait until its read()
is completed. |
120 |
We want to get the block
in
current mode, but someone else is currently reading it into the cache.
Wait for the user to complete the read. This occurs during buffer
lookup. |
130 |
Block is being read by
another
session, and no other suitable block image was found, so we wait until
the read is completed. This may also occur after a buffer cache assumed
deadlock. The kernel can't get a buffer in a certain amount of time and
assumes a deadlock. Therefore it will read the CR version of the block.
|
200 |
We want to NEW the block,
but
someone else is using the current copy, so we have to wait for that
user to finish. |
210 |
The session wants the
block in
SCUR or XCUR mode. If this is a buffer exchange or the session is in
discrete TX mode, the session waits for the first time and the second
time escalates the block as a deadlock, so does not show up as waiting
very long. In this case, the statistic: "exchange deadlocks" is
incremented, and we yield the CPU for the "buffer deadlock" wait event.
|
220 |
During buffer lookup for
a CURRENT
copy of a buffer, we have found the buffer but someone holds it in an
incompatible mode, so we have to wait. |
230 |
Trying to get a buffer in
CR/CRX
mode, but a modification has started on the
buffer that has not yet been completed. |
231 |
CR/CRX scan found the
CURRENT
block, but a modification has started on the buffer that has not yet
been completed. |
|
Reason codes
As I mentioned at the beginning of this article, buffer busy waits are
prevalent in I/O-bound systems. I/O contention, resulting in waits for
data blocks, is often due to numerous sessions repeatedly reading the
same blocks, as when many sessions scan the same index. In this
scenario, session one scans the blocks in the buffer cache quickly, but
then a block has to be read from disk. While session one awaits the
disk read to complete, other sessions scanning the same index soon
catch up to session one and want the same block currently being read
from disk. This is where the buffer busy wait occurs—waiting for the
buffer blocks that are being read from disk. The following rules of
thumb may be useful for resolving each of the noted contention
situations:
- Data block contention—Identify
and eliminate HOT blocks
from the application via changing PCTFREE and or PCTUSED values to
reduce the number of rows per data block. Check for repeatedly scanned
indexes. Since each transaction updating a block requires a transaction
entry, increase the INITRANS value.
- Freelist block contention—Increase the FREELISTS value.
Also, when using Parallel Server, be certain that each instance has its
own FREELIST GROUPs.
- Segment header contention—Again, increase the number of
FREELISTs and use FREELIST GROUPs, which can make a difference even
within a single instance.
- Undo header contention—Increase the number of rollback
segments.
The following STATSPACK script is very useful for detecting
those times when the database has a high-level of buffer busy waits.
prompt
***********************************************************
prompt Buffer Busy Waits may
signal a high update table with too
prompt few freelists. Find the
offending table and add more freelists.
prompt
***********************************************************
prompt
column buffer_busy_wait format
999,999,999
column mydate heading 'yr. mo dy
Hr.'
select
to_char(snap_time,'yyyy-mm-dd HH24') mydate,
new.name,
new.buffer_busy_wait-old.buffer_busy_wait buffer_busy_wait
from
perfstat.stats$buffer_pool_statistics old,
perfstat.stats$buffer_pool_statistics new,
perfstat.stats$snapshot sn
where snap_time >
sysdate-&1
and new.name
<> 'FAKE VIEW'
and
new.snap_id = sn.snap_id
and
old.snap_id = sn.snap_id-1
and
new.buffer_busy_wait-old.buffer_busy_wait > 1
group by
to_char(snap_time,'yyyy-mm-dd HH24'), new.name,
new.buffer_busy_wait-old.buffer_busy_wait ;
Testing
Procedures or Packages for Performance
-- before.sql
set echo off
set timing off
set recsep off
column CPU noprint new_value
before_cpu
column READS noprint new_value
before_reads
select s_cpu.value CPU,
sum(s_reads.value) READS
from sys.v_$session se,
sys.v_$statname n_cpu,
sys.v_$statname n_reads,
sys.v_$sesstat s_cpu,
sys.v_$sesstat s_reads
where n_reads.name in ('db block
gets', 'consistent gets')
and n_cpu.name = 'CPU used
by this session'
and n_cpu.statistic# =
s_cpu.statistic#
and n_reads.statistic# =
s_reads.statistic#
and s_cpu.sid = se.sid
and s_reads.sid = se.sid
and se.audsid =
userenv('SESSIONID')
group by s_cpu.value
/
column CPU clear
column READS clear
will display nothing but blank lines but will collect values before
your PL/SQL runs; immediately after your PL/SQL, run this :
-- after.sql
set echo off
set timing off
set recsep off
column CPU print format 999999
column READS print format
9999999999999
select s_cpu.value -
&&before_cpu - 97 CPU,
sum(s_reads.value) - &&before_reads - 10 READS
from sys.v_$session se,
sys.v_$statname n_cpu,
sys.v_$statname n_reads,
sys.v_$sesstat s_cpu,
sys.v_$sesstat s_reads
where n_reads.name in ('db block
gets', 'consistent gets')
and n_cpu.name = 'CPU used
by this session'
and n_cpu.statistic# =
s_cpu.statistic#
and n_reads.statistic# =
s_reads.statistic#
and s_cpu.sid = se.sid
and s_reads.sid = se.sid
and se.audsid =
userenv('SESSIONID')
group by s_cpu.value
/
column CPU clear
column READS clear
Check Sorts
spool sorts.txt
--The ratio of sorts (disk) to
sorts (memory) should be < 5%.
-- Increase the size of
SORT_AREA_SIZE if it is less than 5%.
-- Increments of 10% should be
fine.
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)';
spool off
Optimizing Indexes
Move Indexes to a 32k Block Size
Create a 32k_block Cache in the SPFILE
db_32k_cache_size = 32M
Create a Tablespace using 32K Blocks
CREATE TABLESPACE "TS_32K_INDEXES" LOGGING DATAFILE
'/oradata/SID/TS_32K_IND.dbf'
SIZE 100M BLOCKSIZE 32768 EXTENT
MANAGEMENT LOCAL UNIFORM SIZE 1M SEGMENT SPACE MANAGEMENT AUTO;