Oracle Database Tuning Statistics
· Cache Hit Ratio
· Sorts in Memory
· Shared Pool
· Library Cache Hit Ratios
· Recursive Calls/Total Calls
· Short/Total Table Scans
· Redo Activity
· Table Contention
· CPU parse overhead
· Latches
· Rollback Segment Contention
Cache Hit Ratios
There are two main figures
Buffer Cache Hit Ratio
select round((1-(pr.value/(bg.value+cg.value)))*100,2)
from v$sysstat pr, v$sysstat bg, v$sysstat cg
where pr.name='physical reads'
and bg.name='db block gets'
and cg.name='consistent gets'
The buffer cache hit ratio is a measure of the proportion of requests for data which is satisfied by data already in the buffer cache. Higher ratios are better as access to data in memory is speedier than an IO operation to disk. There comes a point of diminishing returns when increasing the size of the database buffer. Also, remember that this is part of the SGA and it may be more important to use additional memory for other parts of the SGA. It is vital that the whole SGA fits within main memory, as paging of the SGA is disastrous for performance.
Optimum High
init.ora parameter DB_BLOCK_BUFFERS
Dictionary Cache Hit Ratio
select sum(gets-getmisses)*100/sum(gets)
from v$rowcache
The dictionary cache hit ratio is a measure of the proportion of requests for information from the data dictionary, the collection of database tables and views containing reference information about the database, its structures, and its users. On instance startup, the data dictionary cache contains no data, so any SQL statement issued is likely to result in cache misses. As more data is read into the cache, the likelihood of cache misses should decrease. Eventually the database should reach a "steady state" in which the most frequently used dictionary data is in the cache.
The dictionary cache resides within the Shared Pool, part of the SGA, so increasing the shared pool size should improve the dictionary cacge hit ratio.
Optimum High
init.ora parameter SHARED_POOL_SIZE
Sorts in Memory
select round((mem.value/(mem.value+dsk.value))*100,2)
from v$sysstat mem, v$sysstat dsk
where mem.name='sorts (memory)'
and dsk.name='sorts (disk)'
This is a measure of the proportion of data sorts which occur within memory rather than on disk. Sorts on disk make use of the user's tempoary table space. The maximum size of sort which will occur in memory is defined by the sort area size, which is the size within the PGA which will be used. Each Oracle process which sorts will allocate this much memory, though it may not need all of it. Use of memory for this purpose reduces that available to the SGA.
Optimum High
init.ora parameter SORT_AREA_SIZE
Shared Pool
There are two important figures
Shared Pool Free
select round((sum(decode(name,'free memory',bytes,0))/sum(bytes))*100,2)
from v$sgastat
The percentage of the shared pool not currently in use. If a large proportion of the shared pool is always free, it is likely that the size of the shared pool can be reduced. Low free values are not a cause for concern unless other factors also indicate problems, e.g. a poordictionary cache hit ratio or large proportion of reloads occurring.
Optimum Small but non-zero
init.ora parameter SHARED_POOL_SIZE
Shared Pool Reloads
select round(sum(reloads)/sum(pins)*100,2)
from v$librarycache
where namespace in ('SQL AREA','TABLE/PROCEDURE','BODY','TRIGGER')
This is similar to a Library Cache Miss Ratio, but is specific to SQL and PL/SQL blocks. Shared pool reloads occur when Oracle has to implicitly reparse SQL or PL/SQL at the point when it attempts to execute it. A larger shared pool wil reduce the number of times that code needs to be reloaded. Also, ensuring that similar pieces of SQL are written identically will increase sharing of code.
To take advantage of additional memory available for shared SQL areas, you may also need to increase the number of cursors permitted for a session. You can do this by increasing the value of the initialization parameter OPEN_CURSORS.
Optimum Low
init.ora parameter SHARED_POOL_SIZE
init.ora parameter OPEN_CURSORS
Library Cache Hit Ratios
These values are closely related to shared pool reloads . Improving these figures is carried out in the same manner as discussed in that section. There are two figures
Library Cache Get Hit Ratio
The proportion of requests for a lock on an object which were satisfied by finding that object's handle already in memory.
select round(sum(gethits)/sum(gets)*100,2)
from v$librarycache
Optimum High
init.ora parameter SHARED_POOL_SIZE
init.ora parameter OPEN_CURSORS
Library Cache Pin Hit Ratio
select round(sum(pinhits)/sum(pins)*100,2)
from v$librarycache
The proportion of attempts to pin an object which were satisfied by finding all the pieces of that object already in memory.
Optimum High
init.ora parameter SHARED_POOL_SIZE
init.ora parameter OPEN_CURSORS
Recursive Calls/Total Calls
select round((rcv.value/(rcv.value+usr.value))*100,2)
from v$sysstat rcv, v$sysstat usr
where rcv.name='recursive calls'
and usr.name='user calls'
A high ratio of recursive calls to total calls may indicate any of the following:
· Dynamic extension of tables due to poor sizing
· Growing and shrinking of rollback segments due to unsuitable OPTIMAL settings
· Large amounts of sort to disk resulting in creation and deletion of temporary segments
· Data dictionary misses
· Complex triggers, integrity constraints, procedures, functions and/or packages
Optimum Low
Short/Total Table Scans
select round((shrt.value/(shrt.value+lng.value))*100,2)
from v$sysstat shrt, v$sysstat lng
where shrt.name='table scans (short tables)'
and lng.name='table scans (long tables)'
This is the proportion of full table scans which are occurring on short tables. Short tables may be scanned by Oracle when this is quicker than using an index. Full table scans of long tables is generally bad for overall performance. Low figures for this graph may indicate lack of indexes on large tables or poorly written SQL which fails to use existing indexes or is returning a large percentage of the table.
Optimum High
Redo Activity
The redo log buffer is a circular buffer in the SGA that holds information about changes made to the database. This graph gives an indication of the level of contention occuring for redos. It is made up of two figures
Redo Space Wait Ratio
select round((req.value/wrt.value)*100,2)
from v$sysstat req, v$sysstat wrt
where req.name= 'redo log space requests'
and wrt.name= 'redo writes'
A redo space wait is when there is insufficient space in the redo buffer for a transaction to write redo information. It is an indication that the redo buffer is too small given the rate of transactions occurring in relation to the rate at which the log writer is writing data to the redo logs.
Optimum Very Low
init.ora parameter LOG_BUFFER
Redo Log Allocation Contention
There are two latches:
The Redo Allocation Latch
select round(greatest(
(sum(decode(ln.name,'redo allocation',misses,0))
/greatest(sum(decode(ln.name,'redo allocation',gets,0)),1)),
(sum(decode(ln.name,'redo allocation',immediate_misses,0))
/greatest(sum(decode(ln.name,'redo allocation',immediate_gets,0))
+sum(decode(ln.name,'redo allocation',immediate_misses,0)),1))
)*100,2)
from v$latch l,v$latchname ln
where l.latch#=ln.latch#
The redo allocation latch controls the allocation of space for redo entries in the redo log buffer. To allocate space in the buffer, an Oracle user process must obtain the redo allocation latch. Since there is only one redo allocation latch, only one user process can allocate space in the buffer at a time. The single redo allocation latch enforces the sequential nature of the entries in the buffer.
After allocating space for a redo entry, the user process may copy the entry into the buffer. This is called "copying on the redo allocation latch". A process may only copy on the redo allocation latch if the redo entry is smaller than a threshold size.
The maximum size of a redo entry that can be copied on the redo allocation latch is specified by the initialization parameter LOG_SMALL_ENTRY_MAX_SIZE.
Redo Copy Latches
select round(greatest(
(sum(decode(ln.name,'redo copy',misses,0))
/greatest(sum(decode(ln.name,'redo copy',gets,0)),1)),
(sum(decode(ln.name,'redo copy',immediate_misses,0))
/greatest(sum(decode(ln.name,'redo copy',immediate_gets,0))
+sum(decode(ln.name,'redo copy',immediate_misses,0)),1)) )*100,2)
from v$latch l,v$latchname ln
where l.latch#=ln.latch#
The user process first obtains the copy latch. Then it obtains the allocation latch, performs allocation, and releases the allocation latch. Next the process performs the copy under the copy latch, and releases the copy latch. The allocation latch is thus held for only a very short period of time, as the user process does not try to obtain the copy latch while holding the allocation latch.
If the redo entry is too large to copy on the redo allocation latch, the user process must obtain a redo copy latch before copying the entry into the buffer. While holding a redo copy latch, the user process copies the redo entry into its allocated space in the buffer and then releases the redo copy latch.
With multiple CPUs the redo log buffer can have multiple redo copy latches. These allow multiple processes to copy entries to the redo log buffer concurrently. The number of redo copy latches is determined by the parameter LOG_SIMULTANEOUS_COPIES.
Optimum Very Low
init.ora parameter LOG_SMALL_ENTRY_MAX_SIZE
init.ora parameter LOG_SIMULTANEOUS_COPIES
Table Contention
There are two figures which give indications of how well table storage is working. Figures are averaged across all tables in use. This means one table may be seriously at fault or many tables may have low level problems.
Chained Fetch Ratio
select round((cont.value/(scn.value+rid.value))*100,2)
from v$sysstat cont, v$sysstat scn, v$sysstat rid
where cont.name= 'table fetch continued row'
and scn.name= 'table scan rows gotten'
and rid.name= 'table fetch by rowid'
This is a proportion of all rows fetched which resulted in a chained row continuation. Such a continuation means that data for the row is spread across two blocks, which can occur in either of two ways:
Row Migration
This occurs when an update to a row cannot fit within the current block. In this case, the data for the row is migrated to a new block leaving a pointer to the new location in the original block.
Row Chaining
This occurs when a row cannot fit into a single data block, e.g. due to having large or many fields. In this case, the row is spread over two or more blocks.
Optimum Very Low
Free List Contention
select round((sum(decode(w.class,'free list',count,0))/
(sum(decode(name,'db block gets',value,0))
+ sum(decode(name,'consistent gets',value,0))))*100,2)
from v$waitstat w, v$sysstat
Free list contention occurs when more than one process is attempting to insert data into a given table. The table header structure maintains one or more lists of blocks which have free space for insertion. If more processes are attempting to make insert than there are free lists some will have to wait for access to a free list.
Optimum Very Low
CPU Parse Overhead
select round((prs.value/(prs.value+exe.value))*100,2)
from v$sysstat prs, v$sysstat exe
where prs.name like 'parse count (hard)'
and exe.name= 'execute count'
The CPU parse overhead is the proportion of database CPU time being spent in parsing SQL and PL/SQL code. High values of this figure indicate that either a large amount of once-only code is being used by the database or that the shared sql area is too small.
Optimum Low
init.ora parameter SORT_AREA_SIZE
Latches
Latches are simple, low-level serialization mechanisms to protect shared data structures in the SGA. When attempting to get a latch a process may be willing to wait, hence this graph includes two figures. See also redo log allocation latches.
Willing to Wait Latch Gets
select round(((sum(gets) - sum(misses)) / sum(gets))*100,2)
from v$latch
An attempt by a process to obtain a latch which is willing to wait will sleep and retry until it obtains the latch.
Optimum High
Immediate Latch Gets
select round(((sum(immediate_gets) - sum(immediate_misses)) / sum(immediate_gets))*100,2)
from v$latch
An attempt to obtain a latch which a process is not allowed to wait for will timeout.
Optimum High
Rollback Segment Contention
select round(sum(waits)/sum(gets)*100,2)
from v$rollstat
This figure is an indication of whether a process had to wait to get access to a rollback segment. To improve figures, increase the number of rollback segments available.
Optimum Low
Redo Log Buffer Latches
When a transaction is ready to write its changes to the redo log, it first has to grab the Redo Allocation Latch, of which there is only one, to keep others from writing to the log at the same time. If someone else has that latch, it has to wait for the latch, resulting in a "miss".
Once it grabs that latch, if the change is larger than log_small_entry_max_size bytes and if your server has multiple CPU's, it then tries to grab a Redo Copy Latch, of which there can be up to 2 times the number of CPU's, which would allow it to release the Redo Allocation Latch for someone else to use. If none of them are available, resulting in an "immediate miss", it will not wait for a Redo Copy Latch (thus, the "immediate"), but, instead, hangs on to the Redo Allocation Latch until the change is written.
Oracle keeps statistics for these latches in v$latch, including the number of gets and misses for the Redo Allocation Latch and the number of immediate gets and immediate misses for the Redo Copy Latches, which are cumulative values since instance startup. If you've got a 100% hit ratio for either of those latch types, that's a good thing. It just means that all of your transactions were able to grab and use the latch without retrying. It's when you get below a 99% hit ratio that you need to start looking out. The following sql figures the current hit ratios for those latches:
column latch_name format a20
select name latch_name, gets, misses,
round(decode(gets-misses,0,1,gets-misses)/
decode(gets,0,1,gets),3) hit_ratio
from v$latch where name = 'redo allocation';
column latch_name format a20
select name latch_name, immediate_gets, immediate_misses,
round(decode(immediate_gets-immediate_misses,0,1,
immediate_gets-immediate_misses)/
decode(immediate_gets,0,1,immediate_gets),3) hit_ratio
from v$latch where name = 'redo copy';
If your Redo Allocation Latch hit ratio consistently falls below 99%, and if you have a multi-CPU machine, you can lower the value for log_small_entry_max_size (see below) in your init.ora file (ours is currently 800 bytes, but, maybe 100 or so bytes may be better - you'll have to try out different values over time), which says that any change smaller than that will hang onto the Redo Allocation Latch until Oracle is finished writing that change. Anything larger than that grabs a Redo Copy Latch, if currently available, and releases the Redo Allocation Latch for another transaction to use.
If your Redo Copy Latch hit ratio consistently falls below 99%, and if you have a multi-CPU machine, you can raise the value of log_simultaneous_copies in your init.ora file up to twice the number of CPU's to provide more Redo Copy Latches (there is only one Redo Allocation Latch, so it is at a premium). Remember that you have to shut down your database instance and restart it to reread the new parameter values in the init.ora file ($ORACLE_HOME/dbs/initSID.ora). The following sql shows the current values for those associated parameters:
column name format a30
column value format a10
select name,value from v$parameter where name in
('log_small_entry_max_size','log_simultaneous_copies',
'cpu_count');
Database Buffer Cache Size
The Database Buffer Cache is part of the Shared Global Area (SGA) in memory for a single database instance (SID) and holds the blocks of data and indexes that you and everyone else is currently using. It may even contain multiple copies of the same data block if, for example, more than one transaction is making changes to it but not yet committed, or, if you are looking at the original copy (select) and someone else is looking at their modified but uncommitted copy (insert, update, or delete). The parameters db_block_buffers and db_block_size in your init.ora file determine the size of the buffer cache. db_block_size, in bytes, is set at database creation, and cannot be changed (unless you recreate the database from scratch), so, the only thing that you can adjust is the number of blocks in db_block_buffers (one buffer holds one block).
The Cache Hit Ratio shows how many blocks were already in memory (logical reads, which include "db block gets" for blocks you are using and "consistent gets" of original blocks from rollback segments that others are updating) versus how many blocks had to be read from disk ("physical reads"). Oracle recommends that this ratio be at least 80%, but, I like at least 90% myself. The ratio can be obtained from values in v$sysstat, which are constantly being updated and show statistics since database startup (it is only accessable from a DBA user account). You will get a more representative sample if the database has been running several hours with normal user transactions taking place. The Cache Hit Ratio is determined as follows:
select (1-(pr.value/(dbg.value+cg.value)))*100
from v$sysstat pr, v$sysstat dbg, v$sysstat cg
where pr.name = 'physical reads'
and dbg.name = 'db block gets'
and cg.name = 'consistent gets';
If you have a low Cache Hit Ratio, you can test to see what the effect of adding buffers would be by putting "db_block_lru_extended_statistics = 1000" in the init.ora file, doing a shutdown and startup of the database, and waiting a few hours to get a representative sample. Oracle determines how many Additional Cache Hits (ACH) would occur for each query and transaction for each of the 1000 buffer increments (or whatever other maximum value you might want to try out), and places them into the x$kcbrbh table, which is only accessable from user "sys". To measure the new Cache Hit Ratio with, for example, 100 extra buffers, determine ACH as follows:
select sum(count) "ACH" from x$kcbrbh where indx < 100;
and plug that value into the Cache Hit Ratio formula as follows:
select (1-((pr.value-&ACH)/(dbg.value+cg.value)))*100
from v$sysstat pr, v$sysstat dbg, v$sysstat cg
where pr.name = 'physical reads'
and dbg.name = 'db block gets'
and cg.name = 'consistent gets';
If the ratio originally was lower than 80% and is now higher with ACH, you may want to increase db_block_buffers by that number of extra buffers, restarting your database to put the increase into effect. Be sure to try several values for the number of extra buffers to find an optimum for your work load. Also, remove db_block_lru_extended_statistics from your init.ora file before restarting your database to stop gathering statistics, which tends to slow down the transaction time. (Removing that clears the x$kcbrbh table.) Also, make sure that your server has enough memory to accomodate the increase!
If you are running really tight on memory, and the Cache Hit Ratio is running well above 80%, you might want to check the effect of lowering the number of buffers, which would release Oracle memory that could then be used by other processes, but would also potentially slow down database transactions. To test this, put "db_block_lru_statistics = true" in your init.ora file and restart your database. This gathers statistics for Additional Cache Misses (ACM) that would occur for each query and transaction for each of the buffer decrements up to the current db_block_buffers value, placing them into the x$kcbcbh table, also only accessable from user "sys". To measure the new Cache Hit Ratio with, for example, 100 fewer buffers, determine ACM as follows:
select sum(count) "ACM" from x$kcbcbh
where indx >= (select max(indx)+1-100 from x$kcbcbh);
and plug that value into the Cache Hit Ratio formula as follows:
select (1-((pr.value+&ACM)/(dbg.value+cg.value)))*100
from v$sysstat pr, v$sysstat dbg, v$sysstat cg
where pr.name = 'physical reads'
and dbg.name = 'db block gets'
and cg.name = 'consistent gets';
If the ratio is still above 80%, you may want to decrease db_block_buffers by that number of fewer buffers, restarting your database to put the decrease into effect. Be sure to try several values for the number of fewer buffers to find an optimum for your work load. Also, remove db_block_lru_statistics from your init.ora file before restarting your database to stop gathering statistics, which tends to slow down the transaction time. (Removing that clears the x$kcbcbh table.)
I have three scripts which you can use to figure your instance's optimum number of db_block_buffers. The cache_hit_ratio.sql script computes the current ratio for the database buffer cache, and can be run from any DBA account. The adding_buffers.sql script computes the resulting ratio for an increase in the buffer cache size of the given number of buffer blocks (figuring ACH itself). It must be run from user "sys", after a representative sampling time with db_block_lru_extended_statistics in place. The removing_buffers.sql script computes the resulting ratio for a decrease in the buffer cache size of the given number of buffer blocks (figuring ACM itself). It must be run from user "sys", after a representative sampling time with db_block_lru_statistics in place.
Shared Pool Size
The Shared Pool is also part of the Shared Global Area (SGA) in memory for a single database instance (SID) and holds the Library Cache with the most recently used SQL statements and parse trees along with PL/SQL blocks, and the Data Dictionary Cache with definitions of tables, views, and other dictionary objects. Both of those sets of cached objects can be used by one or more users, and are aged out (Least Recently Used) as other objects need the space. (You can pin large frequently-used objects in the Shared Pool for performance and other reasons, but, I won't go into that here.)
There are several ratios that you can check after a representative sample time that may indicate that you need to enlarge the shared pool, which is set by the shared_pool_size parameter in your init.ora file and defaults to 3500000 (3.5 Meg). One indicator is the Library Cache Get Hit Ratio, which shows how many cursors are being shared (SQL statements (gets) which were already found and parsed (gethits) in the shared pool, with no parsing or re-parsing needed), and is determined by:
select gethits,gets,gethitratio from v$librarycache
where namespace = 'SQL AREA';
If the gethitratio is less than 90%, you should consider increasing the shared pool size. Another indicator is the reloads per pin ratio, which shows how many parsed statements (pins) have been aged out (reloaded) of the shared pool for lack of space (ideally 0), and is determined by:
select reloads,pins,reloads/pins from v$librarycache
where namespace = 'SQL AREA';
If the reloads/pins ratio is more than 1%, you should consider increasing the shared pool size. A third indicator, which is not as important as the first two, is the dictionary object getmisses per get ratio, which shows how many cached dictionary object definitions in the dictionary cache are encountering too many misses (aged out?), and is determined by:
select sum(getmisses),sum(gets),sum(getmisses)/sum(gets)
from v$rowcache;
If the getmisses/gets ratio is more than 15%, you should consider increasing the shared pool size.
If these ratios indicate that your shared pool is too small, you can estimate the size of the shared pool by doing the following. Set the shared_pool_size to a very large number, maybe a fourth or more of your system's available memory, depending on how many other instances and processes that you have running that are also using memory, then shutdown and startup your database and let it run for a representative time (like all day or when a large batch job is running that you want to accomodate), then, figure the memory required for packages and views, memory required for frequently used SQL statements, and memory required for users SQL statements executed, as shown below:
select sum(sharable_mem) "Packages/Views" from v$db_object_cache;
select sum(sharable_mem) "SQL Statements" from v$sqlarea
where executions > 5;
select sum(250 * users_opening) "SQL Users" from v$sqlarea;
Then, add the above three numbers and multiply the results by 2.5. Use this estimated size as a guideline for the value for shared_pool_size, changing that parameter to the estimated size or back to the original size and doing another shutdown/startup to put the value into effect. The shared_pool_size.sql script can be used to figure these values for you, which uses an example of the Select From Selects tip:
select sum(a.spspv) "Packages/Views", sum(a.spssql) "SQL Statements",
sum(a.spsusr) "SQL Users", round((sum(a.spspv) + sum(a.spssql) +
sum(a.spsusr)) * 2.5,-6) "Estimated shared_pool_size"
from (select sum(sharable_mem) spspv, 0 spssql, 0 spsusr
from v$db_object_cache
union all
select 0, sum(sharable_mem), 0 from v$sqlarea
where executions > 5
union all
select 0, 0, sum(250 * users_opening) from v$sqlarea) a;
set echo off
rem
rem Script: tuning.sql
rem
rem Purpose: Check various statistics for the currently-running database
rem to see if there are any database parameters or other modifications
rem you could make to tune the database for faster response.
rem
rem Author: David Midgett
rem Eastern Kentucky University
rem
rem Modifications: Stephen Rea
rem University of Arkansas Cooperative Extension Service
rem
rem Updates:
rem 7/16/03 - Added database SID to init.ora file names in output. Show
rem current values of init.ora parameters and other values in question.
rem Branch around multi-threaded server output if MTS not being used.
rem Cleaned up the formatting of the output for consistency.
rem
set feedback off
column SID new_value SID
select substr(substr(global_name,1,30),1,instr(substr(global_name,1,30),'.')-1) SID
from global_name;
prompt
rem Bypass multi-threaded server checks if there aren't any mts servers.
set termout off heading off
spool tmp~~~.sql
select '/*' from v$parameter where name = 'mts_servers' and value = '0';
spool off
@tmp~~~.sql
set termout on heading on
prompt ================================================================================;
prompt
prompt . Tuning Multi-Threaded Server
prompt
prompt ================================================================================;
prompt
prompt If SERVERS_HIGHWATER exceeds the MTS_SERVERS parameter, increase the number of
prompt 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;
-- */
!rm tmp~~~.sql
set termout on heading on
prompt ================================================================================;
prompt
prompt . Tuning The Library Cache
prompt
prompt ================================================================================;
prompt
prompt Library cache get/hit ratio for SQL AREA should be in high 90's. If not, there
prompt is room to improve the efficiency of your application.
select namespace, gets, gethits, gethitratio from v$librarycache;
prompt
prompt --------------------------------------------------------------------------------;
prompt
prompt If reloads-to-pins ratio is greater than .01, increase SHARED_POOL_SIZE in
prompt 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;
prompt
prompt --------------------------------------------------------------------------------;
prompt
prompt 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;
prompt
prompt --------------------------------------------------------------------------------;
prompt
prompt 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;
set feedback off
prompt --------------------------------------------------------------------------------;
prompt
prompt Shared Pool Reserved space -- The goal is to have zero REQUEST_MISSES and
prompt REQUEST_FAILURES, so increase SHARED_POOL_RESERVED_SIZE in the init&SID..ora
prompt 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;
prompt
prompt ================================================================================;
prompt
prompt . Tuning the Data Dictionary Cache
prompt
prompt ================================================================================;
prompt
prompt If the gethitratio is greater than .15 -- increase the SHARED_POOL_SIZE
prompt 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) "totl_gets", sum(getmisses) "totl_get_misses",
sum(getmisses)/sum(gets) * 100 "gethitratio"
from v$rowcache;
prompt
prompt ================================================================================;
prompt
prompt . Tuning The Data Buffer Cache
prompt
prompt ================================================================================;
prompt
prompt Goal is to have a Cache Hit Ratio greater than 90% -- if lower, increase value
prompt 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
column value format 999,999,999,999
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';
prompt
prompt --------------------------------------------------------------------------------;
prompt
prompt If the number of free buffers inspected is high or increasing, consider
prompt 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
column value format 999,999,999
select name, value from v$sysstat where name = 'free buffer inspected';
prompt
prompt --------------------------------------------------------------------------------;
prompt
prompt A high or increasing number of waits indicates that the db writer cannot keep
prompt up writing dirty buffers. Consider increasing the number of writers using the
prompt 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');
prompt
prompt --------------------------------------------------------------------------------;
prompt
prompt If the LRU Hit percentage is less than 99%, consider adding more
prompt DB_WRITER_PROCESSES and increasing the DB_BLOCK_LRU_LATCHES parameter
prompt 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';
prompt
prompt ================================================================================;
prompt
prompt . Tuning The Redo Log Buffer
prompt
prompt ================================================================================;
prompt
prompt Ideally, there should never be a wait for log buffer space. Increase LOG_BUFFER
prompt 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';
set feedback off
prompt --------------------------------------------------------------------------------;
prompt
prompt If there are any Wait events because of log switches, consider increasing the
prompt size of the redo log files.
set heading off
select 'Current size of redo log files is ' || bytes || ' bytes' from v$log where rownum = 1;
set heading on
column event format a30
select event, total_waits, time_waited, average_wait from v$system_event
where event like 'log file switch completion%';
prompt
prompt ================================================================================;
prompt
prompt 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;
set feedback off
prompt ================================================================================;
prompt
prompt . Tuning Sorts
prompt
prompt ================================================================================;
prompt
prompt The ratio of disk sorts to memory sorts should be less than 5%. Consider
prompt increasing the SORT_AREA_SIZE parameter in the init&SID..ora file. You
prompt might also consider setting up separate temp tablespaces for frequent
prompt 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)';
prompt
prompt ================================================================================;
prompt
prompt . Tuning Rollback segments
prompt
prompt ================================================================================;
prompt
prompt 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;
prompt
prompt --------------------------------------------------------------------------------;
prompt
prompt 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';
column event format a25
prompt
prompt --------------------------------------------------------------------------------;
prompt
prompt Rollback segment waits for transaction slots -- any waits indicates need for
prompt 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';
set feedback off
prompt --------------------------------------------------------------------------------;
prompt
prompt 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;
prompt
clear columns
set feedback on echo on
-- From http://www.oramag.com/code/cod46dba.html
prompt****************************************************
prompt Hit Ratio Section
prompt****************************************************
prompt
prompt =========================
prompt BUFFER HIT RATIO
prompt =========================
prompt (should be > 70, else increase db_block_buffers in init.ora)
--select trunc((1-(sum(decode(name,'physical reads',value,0))/
-- (sum(decode(name,'db block gets',value,0))+
-- (sum(decode(name,'consistent gets',value,0)))))
-- )* 100) "Buffer Hit Ratio"
--from v$sysstat;
column "logical_reads" format 99,999,999,999
column "phys_reads" format 999,999,999
column "phy_writes" format 999,999,999
select a.value + b.value "logical_reads",
c.value "phys_reads",
d.value "phy_writes",
round(100 * ((a.value+b.value)-c.value) /
(a.value+b.value))
"BUFFER HIT RATIO"
from v$sysstat a, v$sysstat b, v$sysstat c, v$sysstat d
where
a.statistic# = 37
and
b.statistic# = 38
and
c.statistic# = 39
and
d.statistic# = 40;
prompt
prompt
prompt =========================
prompt DATA DICT HIT RATIO
prompt =========================
prompt (should be higher than 90 else increase shared_pool_size in init.ora)
prompt
column "Data Dict. Gets" format 999,999,999
column "Data Dict. cache misses" format 999,999,999
select sum(gets) "Data Dict. Gets",
sum(getmisses) "Data Dict. cache misses",
trunc((1-(sum(getmisses)/sum(gets)))*100) "DATA DICT CACHE HIT
RATIO"
from v$rowcache;
prompt
prompt =========================
prompt LIBRARY CACHE MISS RATIO
prompt =========================
prompt (If > .1, i.e., more than 1% of the pins resulted in reloads, then increase the shared_pool_size in init.ora)
prompt
column "LIBRARY CACHE MISS RATIO" format 99.9999
column "executions" format 999,999,999
column "Cache misses while executing" format 999,999,999
select sum(pins) "executions", sum(reloads) "Cache misses while executing",
(((sum(reloads)/sum(pins)))) "LIBRARY CACHE MISS RATIO"
from v$librarycache;
prompt
prompt =========================
prompt Library Cache Section
prompt =========================
prompt hit ratio should be > 70, and pin ratio > 70 ...
prompt
column "reloads" format 999,999,999
select namespace, trunc(gethitratio * 100) "Hit ratio",
trunc(pinhitratio * 100) "pin hit ratio", reloads "reloads"
from v$librarycache;
prompt
prompt
prompt =========================
prompt REDO LOG BUFFER
prompt =========================
prompt
set heading off
column value format 999,999,999
select substr(name,1,30),
value
from v$sysstat where name = 'redo log space requests';
set heading on
prompt
prompt
prompt
column bytes format 999,999,999
select name, bytes from v$sgastat where name = 'free memory';
prompt
prompt****************************************************
prompt SQL Summary Section
prompt****************************************************
prompt
column "Tot SQL run since startup" format 999,999,999
column "SQL executing now" format 999,999,999
select sum(executions) "Tot SQL run since startup",
sum(users_executing) "SQL executing now"
from v$sqlarea;
prompt
prompt
prompt****************************************************
prompt Lock Section
prompt****************************************************
prompt
prompt =========================
prompt SYSTEM-WIDE LOCKS - all requests for locks or latches
prompt =========================
prompt
select substr(username,1,12) "User",
substr(lock_type,1,18) "Lock Type",
substr(mode_held,1,18) "Mode Held"
from sys.dba_lock a, v$session b
where lock_type not in ('Media Recovery','Redo Thread')
and a.session_id = b.sid;
prompt
prompt =========================
prompt DDL LOCKS - These are usually triggers or other DDL
prompt =========================
prompt
select substr(username,1,12) "User",
substr(owner,1,8) "Owner",
substr(name,1,15) "Name",
substr(a.type,1,20) "Type",
substr(mode_held,1,11) "Mode held"
from sys.dba_ddl_locks a, v$session b
where a.session_id = b.sid;
prompt
prompt =========================
prompt DML LOCKS - These are table and row locks...
prompt =========================
prompt
select substr(username,1,12) "User",
substr(owner,1,8) "Owner",
substr(name,1,20) "Name",
substr(mode_held,1,21) "Mode held"
from sys.dba_dml_locks a, v$session b
where a.session_id = b.sid;
prompt
prompt
prompt****************************************************
prompt Latch Section
prompt****************************************************
prompt if miss_ratio or immediate_miss_ratio > 1 then latch
prompt contention exists, decrease LOG_SMALL_ENTRY_MAX_SIZE in init.ora
prompt
column "miss_ratio" format .99
column "immediate_miss_ratio" format .99
select substr(l.name,1,30) name,
(misses/(gets+.001))*100 "miss_ratio",
(immediate_misses/(immediate_gets+.001))*100
"immediate_miss_ratio"
from v$latch l, v$latchname ln
where l.latch# = ln.latch#
and (
(misses/(gets+.001))*100 > .2
or
(immediate_misses/(immediate_gets+.001))*100 > .2
)
order by l.name;
prompt
prompt
prompt****************************************************
prompt Rollback Segment Section
prompt****************************************************
prompt if any count below is > 1% of the total number of requests for data
prompt then more rollback segments are needed
--column count format 999,999,999
select class, count
from v$waitstat
where class in ('free list','system undo header','system undo block',
'undo header','undo block')
group by class,count;
column "Tot # of Requests for Data" format 999,999,999
select sum(value) "Tot # of Requests for Data" from v$sys stat where
name in ('db block gets', 'consistent gets');
prompt
prompt =========================
prompt ROLLBACK SEGMENT CONTENTION
prompt =========================
prompt
prompt If any ratio is > .01 then more rollback segments are needed
column "Ratio" format 99.99999
select name, waits, gets, waits/gets "Ratio"
from v$rollstat a, v$rollname b
where a.usn = b.usn;
column "total_waits" format 999,999,999
column "total_timeouts" format 999,999,999
prompt
prompt
set feedback on;
prompt****************************************************
prompt Session Event Section
prompt****************************************************
prompt if average-wait > 0 then contention exists
prompt
select substr(event,1,30) event,
total_waits, total_timeouts, average_wait
from v$session_event
where average_wait > 0 ;
--or total_timeouts > 0;
prompt
prompt
prompt****************************************************
prompt Queue Section
prompt****************************************************
prompt average wait for queues should be near zero ...
prompt
column "totalq" format 999,999,999
column "# queued" format 999,999,999
select paddr, type "Queue type", queued "# queued", wait, totalq,
decode(totalq,0,0,wait/totalq) "AVG WAIT" from v$queue;
set feedback on;
prompt
prompt
--prompt****************************************************
--prompt Multi-threaded Server Section
--prompt****************************************************
--prompt
--prompt If the following number is > 1
--prompt then increase MTS_MAX_SERVERS parm in init.ora
--prompt
-- select decode( totalq, 0, 'No Requests',
-- wait/totalq || ' hundredths of seconds')
-- "Avg wait per request queue"
-- from v$queue
-- where type = 'COMMON';
--prompt
--prompt If the following number increases, consider adding dispatcher processes
--prompt
-- select decode( sum(totalq), 0, 'No Responses',
-- sum(wait)/sum(totalq) || ' hundredths of seconds')
-- "Avg wait per response queue"
-- from v$queue q, v$dispatcher d
-- where q.type = 'DISPATCHER'
-- and q.paddr = d.paddr;
--set feedback off;
--prompt
--prompt
--prompt =========================
--prompt DISPATCHER USAGE
--prompt =========================
--prompt (If Time Busy > 50, then change
MTS_MAX_DISPATCHERS in init.ora)
--column "Time Busy" format 999,999.999
--column busy format 999,999,999
--column idle format 999,999,999
--select name, status, idle, busy,
-- (busy/(busy+idle))*100 "Time Busy"
--from v$dispatcher;
--prompt
--prompt
--select count(*) "Shared Server Processes"
-- from v$shared_server
-- where status = 'QUIT';
--prompt
--prompt
--prompt high-water mark for the multi-threaded server
--prompt
--select * from v$mts;
--prompt
--prompt****************************************************
--prompt file i/o should be evenly distributed across drives.
--prompt
--select
--substr(a.file#,1,2) "#",
--substr(a.name,1,30) "Name",
--a.status,
--a.bytes,
--b.phyrds,
--b.phywrts
--from v$datafile a, v$filestat b
--where a.file# = b.file#;
--select substr(name,1,55) system_statistic, value
-- from v$sysstat
-- order by name;
Description: The output of this script will display all sql statements in the shared pool that are doing full table scans.
Code:
--run this as sys in SQL worksheet
create table full_sql (sql_text varchar2(1000), executions number);
create or replace procedure p_findfullsql as
v_csr number;
v_rc number;
v_string varchar2(2000);
v_count number;
cursor c1 is select sql_text,executions from v$sqlarea where lower(sql_text) like '%select%';
begin
for x1 in c1 loop
delete from plan_table ;
Begin
v_Csr := DBMS_SQL.OPEN_CURSOR;
v_string := 'explain plan for ' ;
v_string := v_string||x1.sql_text ;
DBMS_SQL.PARSE(v_csr, v_string, DBMS_SQL.V7);
v_rc := DBMS_SQL.EXECUTE(v_csr);
DBMS_SQL.CLOSE_CURSOR(v_csr);
Exception
when others then
null;
End ;
select count(*) into v_count from plan_table where options like '%FULL%' and operation like '%TABLE%' ;
if v_count > 0 then
insert into full_sql(sql_text,executions) values (x1.sql_text, x1.executions) ;
end if;
end loop ;
commit;
end ;
/
execute p_findfullsql ;
select * from full_sql;
drop table full_sql;
Description: Find the top 20 longest running processes in unix. Useful for high CPU bound systems with large number of users. Script also identifies processes without a oracle session.
Code:
#!/bin/ksh
#
# Find Highest CPU used Oracle processes and get the Username
# and SID from oracle
# Only 3 character SIDNAME is displayed - Adjust the script according to your need.
#
date
echo " Top 20 CPU Utilized Session from `hostname`"
echo " ============================================"
echo "O/S Oracle Session Session Serial UNIX Login Ora CPU Time"
echo "ID User ID Status ID No ID MMDD:HHMISS SID Used"
echo "-------- ----------- -------- ------- ------- ------- ----------- --- --------"
ps -ef|grep LOCAL|cut -c1-15,42-79|sort -rn +2 | head -20 | while read LINE
do
SIDNAME=`echo $LINE | awk '{ print $4 }' | cut -c7-14`
CPUTIME=`echo $LINE | awk '{ print $3 }'`
UNIXPID=`echo $LINE | awk '{ print $2 }'`
#echo $SIDNAME $CPUTIME $UNIXPID
export ORACLE_SID=$SIDNAME
SIDNAME=`echo $ORACLE_SID | cut -c4-6`
export ORACLE_HOME=`/dba_script/bin/find_ohome.sh ${ORACLE_SID}`
export SHLIB_PATH=$ORACLE_HOME/lib:/usr/lib
export TMPDIR=/tmp
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export SQLPLUS="$ORACLE_HOME/bin/sqlplus -s / "
$SQLPLUS >> $wlogfile <
set pages 0 lines 80 trims on echo off verify off pau off
column pu format a8 heading 'O/S|ID' justify left
column su format a11 heading 'Oracle|User ID' justify left
column stat format a8 heading 'Session|Status' justify left
column ssid format 999999 heading 'Session|ID' justify right
column sser format 999999 heading 'Serial|No' justify right
column spid format 999999 heading 'UNIX|ID' justify right
column ltime format a11 heading 'Login|Time' justify right
select p.username pu,
s.username su,
s.status stat,
s.sid ssid,
s.serial# sser,
lpad(p.spid,7) spid,
to_char(s.logon_time, 'MMDD:HH24MISS') ltime,
'$SIDNAME $CPUTIME'
from v\$process p,
v\$session s
where p.addr=s.paddr
and p.spid=$UNIXPID
union all
select a.username, 'Kill Me', 'NoOracle', a.pid, a.serial#,
lpad(a.spid,7) spid, 'KILL UNIXID', '$SIDNAME $CPUTIME'
from v\$process a
where a.spid = $UNIXPID
and not exists (select 1 from v\$session s
where a.addr=s.paddr);
EOF
done
echo "-------- ----------- -------- ------- ------- ------- ----------- --- --------"
date
#
# End of Script
Description: Monitor and Verify DEAD Locks: Holding and Waiting Sessions
Code:
Doc
Verify DEAD LOCK situation
Script: waiters.sql
Date: 07/1999
Revision:
#
set lines 80 echo on ver off timing on term on pages 60 feed on head on
spool DEAD_LOCK_WAITERS.LST
col " " for A25
col "Holding Session Info" for A25
col "Waiting Session Info" for A25
select --+ ORDERED
'Session ID' || CHR(10) ||
'Mode Held' || CHR(10) ||
'Lock Type' || CHR(10) ||
'Mode Requested' || CHR(10) ||
'Lock ID 1' || CHR(10) ||
'Lock ID 2' " "
-------------------------------- END of Header
,
HH.session_id || CHR(10) ||
HH.mode_held || CHR(10) ||
HH.lock_type || CHR(10) ||
HH.mode_requested || CHR(10) ||
HH.lock_id1 || CHR(10) ||
HH.lock_id2 "Holding Session Info"
------------------------------ END of Holding Session
,
Ww.session_id || CHR(10) ||
WW.mode_held || CHR(10) ||
Ww.lock_type || CHR(10) ||
Ww.mode_requested || CHR(10) ||
Ww.lock_id1 || CHR(10) ||
Ww.lock_id2 "Waiting Session Info"
------------------------------ END of Waiting Session
from
-----------------------------------------------------
(
select /*+ RULE */ *
from SYS.dba_locks
where blocking_others = 'Blocking' and
mode_held != 'None' and
mode_held != 'Null'
) HH,
-----------------------------------------------------
(
select /*+ RULE */ *
from SYS.dba_locks
where mode_requested != 'None'
) WW
-----------------------------------------------------
where WW.lock_type = HH.lock_type and
WW.lock_id1 = HH.lock_id1 and
WW.lock_id2 = HH.lock_id2
;
spool off
Description: An interesting script submitted by one of our readers to size the SGA
Code:
set serverout on
DECLARE
l_uplift CONSTANT NUMBER := 0.3;
/* i.e. 30% above calculated */
l_numusers NUMBER DEFAULT 50;
/* Change ths to a predicted number existing database */
l_avg_uga NUMBER;
l_max_uga NUMBER;
l_sum_sql_shmem NUMBER;
l_sum_obj_shmem NUMBER;
l_total_avg NUMBER;
l_total_max NUMBER;
BEGIN
dbms_output.enable(20000);
IF ( l_numusers = 0) THEN
SELECT sessions_highwater
INTO l_numusers
FROM v$license;
dbms_output.put_line('Maximum concurrent users on this database = '||TO_CHAR(l_numusers));
ELSE
dbms_output.put_line('Calculating SGA for = '||TO_CHAR(l_numusers)||' concurrent users');
END IF;
dbms_output.new_line;
SELECT
avg(value)*l_numusers
,max(value)*l_numusers
INTO l_avg_uga, l_max_uga
FROM v$sesstat s, v$statname n
WHERE s.statistic# = n.statistic#
AND n.name = 'session uga memory max';
SELECT sum(sharable_mem) INTO l_sum_sql_shmem FROM v$sqlarea;
SELECT sum(sharable_mem) INTO l_sum_obj_shmem FROM v$db_object_cache;
l_total_avg := l_avg_uga + l_sum_sql_shmem + l_sum_obj_shmem;
l_total_max := l_max_uga + l_sum_sql_shmem + l_sum_obj_shmem;
dbms_output.put_line('Recommended Shared_pool size between :' || TO_CHAR(ROUND(l_total_avg + (l_total_avg * l_uplift), 0) ) ||' and '
|| TO_CHAR(ROUND(l_total_max + (l_total_max * l_uplift), 0) ) ||' bytes');
dbms_output.put_line('Recommended Shared_pool size between :' || TO_CHAR(ROUND((l_total_avg + (l_total_avg * l_uplift)) /(1024*1024), 0) )
||' and '|| TO_CHAR(ROUND((l_total_max + (l_total_max * l_uplift )) /1024*1024) ,0) ) ||' M bytes');
end;
/
Description: This script uses the new way of calculating DB_Block Buffer Efficiency. Logical Read: consistent gets + db block gets
Hit-Ratio: (logical reads - physical reads) / (logical reads)
Our Aim: OLTP >= 95%, DSS/Batch >= 85%
Solution: enlarge block buffers, tune SQL, check appropriateness of indexes
Code:
select name, ((consistent_gets + db_block_gets) - physical_reads) /
(consistent_gets + db_block_gets) * 100 "Hit Ratio%"
from v$buffer_pool_statistics
where physical_reads > 0;
Description: Haven't you ever thought there should be an easier way to do the EXPLAIN PLAN and TKPROF statistics than to edit your
queries to add the commands (like EXPLAIN PLAN SET...), or to have to find or write a script that automates this? It should
be an automatic part of SQL*Plus. Well, as of SQL*Plus 3.3 it is!! The command is called 'SET AUTOTRACE ON'!
Code:
The SET AUTOTRACE Command
In SQL*Plus 3.3 there is a little known command (at least I didn't know about it until recently) called SET AUTOTRACE. It is
documented in the newest SQL*Plus document set, but who really reads the whole document set for changes? Well I did not. It
is very simple to use. Just type the command:
SET AUTOTRACE ON
And then run your select statement. Example:
SQL> SET AUTOTRACE ON
SQL> SELECT d.deptno, d.dname, e.empno, e.ename
2 FROM dept d, emp e
3 WHERE d.deptno = e.deptno
4 /
DEPTNO DNAME EMPNO ENAME
---------- -------------- ---------- ----------
10 ACCOUNTING 7839 KING
.
.
30 SALES 7900 JAMES
30 SALES 7521 WARD
14 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 MERGE JOIN
2 1 SORT (JOIN)
3 2 TABLE ACCESS (FULL) OF 'EMP'
4 1 SORT (JOIN)
5 4 TABLE ACCESS (FULL) OF 'DEPT'
Statistics
----------------------------------------------------------
0 recursive calls
4 db block gets
2 consistent gets
0 physical reads
0 redo size
670 bytes sent via SQL*Net to client
376 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
14 rows processed
There are also some other options, for example there is a TRACEONLY option which supresses the SQL output. See the
SQL*Plus 3.3 manual for a full description.
Some setup issues:
If you go off and try this on your instance, you may run into some problems. There are a few setup steps that need to be taken to
make this work:
1.Make sure you have access to PLAN_TABLE. If you don't, create it using utlxplan.sql (It should be in a directory like
$ORACLE_HOME/rdbms73/admin/) and make sure you have access to it from the user you are using to tune the SQL.
2.You also need to create the PLUSTRACE role, and grant it to the desired users. The script to create this role is in:
$ORACLE_HOME/plus33/Plustrce.sql
It has to be run from SYS in order to have the correct security access. Then grant the role to the desired users or ROLEs.
Health Check
Description: Look at the statistics of your Database
Code:
REM Database Health monitoring script.
REM
REM Segments Max extents & Current extent comparison
REM
set line 180
set pagesize 10000
set feedback off
prompt
col Time format a50 heading "System Time"
select to_char(sysdate,'DD-MON-YYYY:HH24:MI:SS') Time from dual;
prompt
prompt
prompt
prompt**---------------Objects Reaching Max extents-----------------------**
prompt
col segment_name format a40 heading "Object Name"
col max_extents format 9999999999 heading "Max Extents"
col curr_extents format 99999999999 heading "Curent Extents"
select a.segment_name,a.max_extents,b.curr_extents from dba_segments a,(select
segment_name,max(extent_id) curr_extents from dba_extents group by segment_name) b where a.segment_name = b.segment_name
and (a.max_extents - b.curr_extents) <= 10;
prompt
prompt**---------------User Session Information----------------------------**
prompt
col sid format 9999 heading "SID"
col serial# format 9999999 heading "SERIAL NO"
col logon_time format 9999999 heading "Login Time"
col username format a12
col osuser format a24
col program format a38
select s.sid,s.serial#,p.spid,s.username,s.osuser,s.program,to_char(s.LOGON_TIME,'DD-MON-YY:HH24:MI:SS') "Log on Time",round((s.LAST_CALL_ET/(60*60*24)),2)"Wait in Days" from v$session s,v$process p where s.paddr = p.addr and s.username is not null order by 8 desc;
prompt
prompt**---------------File Information-------------------------------------**
prompt
col file_name format a55 heading "File Name"
col SizeInMB format 99999999 heading "Total Size (MB)"
col MAXSIZE format 99999999 heading "Maximum Size (MB)"
select file_name,BYTES/(1024*1024) SizeInMB,AUTOEXTENSIBLE,MAXBYTES/(1024*1024) MAXSIZE from dba_data_files;
prompt
prompt**---------------Tablespace Information-------------------------------**
prompt
col tablespace_name format a25 heading "Tablespace Name"
col logging format a10
col status format a12
col extent_management format a30 heading "Local/Dict"
select tablespace_name,status,contents,decode(logging,LOGGING,'YES','NO') Logging,extent_management from dba_tablespaces;
SELECT Total.name "Tablespace Name",
Free_space, (total_space-Free_space) Used_space, total_space
FROM
(select tablespace_name, sum(bytes/1024/1024) Free_Space
from sys.dba_free_space
group by tablespace_name
) Free,
(select b.name, sum(bytes/1024/1024) TOTAL_SPACE
from sys.v_$datafile a, sys.v_$tablespace B
where a.ts# = b.ts#
group by b.name
) Total
WHERE Free.Tablespace_name = Total.name;
prompt
prompt**---------------Tablespace Fragmentation Status----------------**
prompt
col TABLESPACE_NAME format a25 heading "Tablespace Name"
select TABLESPACE_NAME Name,TOTAL_EXTENTS "Total Extents",EXTENTS_COALESCED,decode(PERCENT_EXTENTS_COALESCED,100,'NO','YES') "Frag" from
dba_free_space_coalesced;
prompt
prompt**---------------Latch Contention-------------------------------**
prompt
col name format a40 heading "Latch Name"
SELECT name, gets, misses,
round((gets-misses)/decode(gets,0,1,gets),3) hit_ratio,
sleeps,
round(sleeps/decode(misses,0,1,misses),3) "sleeps/misses"
from v$latch
where gets != 0
order by name;
select name,immediate_gets,immediate_misses,(immediate_gets)/(immediate_gets+immediate_misses) Hit_Ratio
from v$latch where immediate_gets != 0;
prompt
prompt**---------------Shared Pool Statistics-------------------------------**
prompt
col namespace format a22
select namespace,gets,gethits,round(gethitratio,2) gethitratio,pins,pinhits,round(pinhitratio,2)
pinhitratio,reloads,invalidations from v$librarycache;
prompt
prompt**---------------Roll back segment Statistics-------------------------------**
prompt
col segment_name format a30 heading "Segment Name"
col status format a15
select segment_name,a.status,initial_extent/(1024) "Initial KB",next_extent/(1024)"NextKB",max_extents,min_extents,optsize/102
"Opt Size KB",curext "Current Extents" from dba_rollback_segs a,v$rollstat b where a.segment_id = b.usn;
prompt
prompt**---------------Top 20 Events and System Statistics-------------------------------**
prompt
col event format a40 heading "Event Name"
col Stat format a40 heading "Stat Name"
select * from ( select name "Stat",value from v$sysstat order by value desc ) where rownum <= 20 ;
select * from ( select event,total_waits from v$system_event order by total_waits desc ) where rownum <=
20;
prompt
prompt**---------------Buffer Cache statistics-------------------------------**
prompt
select (1-(a.value/(b.value+c.value))) *100 "Buffer Cache Hit ratio" from v$sysstat a,v$sysstat b,v$sysstat c where a.name
= 'physical reads' and b.name = 'consistent gets' and c.name = 'db block gets';
col name format a20 heading "Buffer Pool Name"
select name,free_buffer_wait,write_complete_wait,buffer_busy_wait,db_block_gets,consistent_gets,physical_reads,physical_writes
from v$buffer_pool_statistics;
prompt
prompt
prompt
prompt**---------------File I/O statistics-------------------------------**
prompt
col file# format 99 heading "File No"
select file#,PHYRDS "Physical Reads",PHYWRTS "Physical Writes",READTIM "Read Time",WRITETIM "Write Time",AVGIOTIM "Avg Time" from v$filestat;
set feedback on
Description: This script will display the estimated work and time remaining for long operations (i.e. over 10 minutes).
Code:
select sid,
message || '(' || time_remaining || ')' "Long Ops"
from v$session_longops
where time_remaining > 600;
Description: This is possibly one of the most useful tuning scripts available. The V$SYSTEM_EVENT dynamic performance view is the highest-level view of the "Session Wait Interface". Information in this view is cumulative since the database instance was started, and one can get a very good idea of what types of contention a database instance is (or is not) experiencing by monitoring this view.
Code:
/**********************************************************************
* File: systemevent.sql
* Type: SQL*Plus script
* Description:
* This is possibly one of the most useful tuning scripts
* available.
*
* The V$SYSTEM_EVENT dynamic performance view is the highest-level
* view of the "Session Wait Interface". Information in this view
* is cumulative since the database instance was started, and one
* can get a very good idea of what types of contention a database
* instance is (or is not) experiencing by monitoring this view.
*
* NOTE: this script sorts output by the TIME_WAITED column in the
* V$SYSTEM_EVENT view. If the Oracle initialization parameter
* TIMED_STATISTICS is not set to TRUE, then the TIME_WAITED
* column will not be populated.
*
* Please *disregard* the advice of people who insist that turning
* off TIMED_STATISTICS is somehow a performance boost. Whatever
* performance overhead that might be incurred is more than
* compensated for by the incredible tuning information that
* results...
*
*********************************************************************/
set echo off feedback off timing off pause off verify off
set pagesize 100 linesize 500 trimspool on trimout on
col event format a26 truncate heading "Event Name"
col total_waits format 999,990.00 heading "Total|Waits|(in 1000s)"
col total_timeouts format 999,990.00 heading "Total|Timeouts|(in 1000s)"
col time_waited format 999,990.00 heading "Time|Waited|(in Hours)"
col pct_significant format 90.00 heading "% of|Concern"
col average_wait format 990.00 heading "Avg|Wait|(Secs)"
col instance new_value V_INSTANCE noprint
select lower(replace(t.instance,chr(0),
)) instance
from sys.v_$thread t,
sys.v_$parameter p
where p.name = 'thread'
and t.thread# = to_number(decode(p.value,'0','1',p.value));
col total_time_waited new_value V_TOTAL_TIME_WAITED noprint
select sum(time_waited) total_time_waited
from sys.v_$system_event
where event not in ('SQL*Net message from client',
'rdbms ipc message',
'slave wait',
'pmon timer',
'smon timer',
'rdbms ipc reply',
'SQL*Net message to client',
'SQL*Net break/reset to client',
'inactive session',
'Null event')
/
select event,
(total_waits / 1000) total_waits,
(total_timeouts / 1000) total_timeouts,
(time_waited / 360000) time_waited,
decode(event,
'SQL*Net message from client', 0,
'rdbms ipc message', 0,
'slave wait', 0,
'pmon timer', 0,
'smon timer', 0,
'rdbms ipc reply', 0,
'SQL*Net message to client', 0,
'SQL*Net break/reset to client', 0,
'inactive session', 0,
'Null event', 0,
(time_waited / &&V_TOTAL_TIME_WAITED)*100) pct_significant,
(average_wait / 100) average_wait
from sys.v_$system_event
where (time_waited/360000) >= 0.01
order by pct_significant desc, time_waited desc
spool sysevent_&&V_INSTANCE
/
spool off
Description: The Script lists the Session ID,Event Name,Time Waited on the Database. This Script is useful in identifying the resource intensive Queries. Can Be used on oracle 8i & 9i Databases.
Code:
select se.sid,s.username,Se.Event,se.time_waited
from v$session s, v$session_event se
where s.username is not null
and s.username not in ('SYS','SYSTEM')
and se.sid=s.sid
and s.status='ACTIVE'
and se.event not like '%SQL*Net%'
/
Description: Anything appear in the result will be a locked object .
Code:
select o.object_name,l.oracle_username,l.os_user_name,l.session_id
,decode(l.locked_mode,2,'Row-S',3,'Row-X',4,'Share',5,'S/Row-X',6 ,'Exclusive','NULL')
from user_objects o , v$locked_object l
where o.object_id = l.object_id;
Description: This script will display the waits on the instance since the instance has been created. Good place to look for system bottle necks
Code:
col event form A50
col Prev form 999
col Curr form 999
col Tot form 999
select
event,
sum(decode(wait_Time,0,0,1)) "Prev",
sum(decode(wait_Time,0,1,0)) "Curr",
count(*) "Tot"
from
v$session_Wait
group by event
order by 4
Description: Display the contention in rollback segments
Code:
column "Ratio" format 99.99999
select name, waits, gets, waits/gets "Ratio"
from v$rollstat a, v$rollname b
where a.usn = b.usn;
Description: Display IO by file
Code:
select
substr(a.file#,1,2) "#",
substr(a.name,1,30) "Name",
a.status,
a.bytes,
b.phyrds,
b.phywrts
from v$datafile a, v$filestat b
where a.file# = b.file#;
Description: Try to balance the I/O by mixing active and inactive data files / tablespaces on the same drive
Code:
column name format a18 heading 'Tablespace' jus cen
column file format a50 heading 'File Name' jus cen
column pbr format 99,999,999,990 heading 'Physical|Blocks|Read' jus cen
column pbw format 99,999,999,990 heading 'Physical |Blocks|Written' jus cen
column pyr format 99,999,999,990 heading 'Physical |Reads' jus cen
column pyw format 99,999,999,990 heading 'Physical|Writes' jus cen
ttitle center 'Disk I/O Activity by file' skip 2
compute sum of pyr on report
compute sum of pyw on report
compute sum of pbr on report
compute sum of pbw on report
break on report
select
df.tablespace_name name,
df.file_name "file",
f.phyrds pyr,
f.phyblkrd pbr,
f.phywrts pyw,
f.phyblkwrt pbw
from v$filestat f, dba_data_files df
where
f.file# = df.file_id
order by df.tablespace_name
Description: This script takes a snapshot of v$filestats at the current time and saves it. It then waits 10 seconds and takes another snapshot and reports on the delta.
Code:
col name for a50
set linesize 132
set pages 666
-- drop temporary table
drop table jh$filestats;
create table jh$filestats as
select file#, PHYBLKRD, PHYBLKWRT
from v$filestat;
prompt Waiting......
exec dbms_lock.sleep(10);
prompt NOTE: Only the top 10 files...
select * from (
select df.name, fs.phyblkrd - t.phyblkrd "Reads",
fs.PHYBLKWRT - t.PHYBLKWRT "Writes",
(fs.PHYBLKRD+fs.PHYBLKWRT) - (t.PHYBLKRD+t.PHYBLKWRT) "Total IO"
from v$filestat fs, v$datafile df, jh$filestats t
where df.file# = fs.file#
and t.file# = fs.file#
and (fs.PHYBLKRD+fs.PHYBLKWRT) - (t.PHYBLKRD+t.PHYBLKWRT) > 0
order by "Total IO" desc )
where rownum <= 10
/
Description: This script will display the table name and column name of the tables that need to be indexed.
Code:
COL table_name format A20 head 'TABLE_NAME'
COL constraint_name format A20 head 'CONSTRAINT_NAME'
COL table2 format A20 head 'TABLE_TO_BE_INDEXED'
COL column_name format A20 head 'COLUMN_TO_BE_INDEXED'
SET linesize 100
SELECT t.table_name,c.constraint_name,c.table_name table2
,acc.column_name
FROM all_constraints t, all_constraints c
, all_cons_columns acc
WHERE c.r_constraint_name = t.constraint_name
AND c.table_name =acc.table_name
AND c.constraint_name = acc.constraint_name
AND NOT EXISTS ( SELECT '1' FROM all_ind_columns aid
WHERE aid.table_name = acc.table_name
AND aid.column_name = acc.column_name)
ORDER BY c.table_name;
Description: Table to Index Cross Reference Report
Code:
ttitle left 'Date: ' format a10 cur_date -
center 'Table/Index Cross Reference for ' format a8 sql.user -
right 'Page:' format 999 sql.pno
set heading on
set pagesize 56
set linesize 100
set newpage 0
column uniqueness format a9 heading 'Unique?'
column index_name format a30 heading 'Index|Name'
column table_name format a24 heading 'Table|Name'
column column_name format a24 heading 'Column|Name'
column table_type format a8 heading 'Index|Type'
break on table_name skip 1 on table_type on index_name on uniqueness
select user_indexes.table_name, user_indexes.index_name,
uniqueness, column_name
from user_ind_columns, user_indexes
where user_ind_columns.index_name = user_indexes.index_name
and user_ind_columns.table_name = user_indexes.table_name
order by user_indexes.table_type, user_indexes.table_name,
user_indexes.index_name, column_position
;
clear breaks
clear columns
ttitle off
Description: This script lists foreign keys that are missing indexes on the foreign key columns in the child table. If the index is not in place, share lock problems may occur on the parent table.
Code:
ttitle 'Foreign Keys with Indexes Missing on Child Table'
SELECT acc.owner||'-> '||acc.constraint_name||'('||acc.column_name
||'['||acc.position||'])'||' ***** Missing Index' "Missing Index"
FROM all_cons_columns acc, all_constraints ac
WHERE ac.constraint_name = acc.constraint_name
AND ac.constraint_type = 'R'
and acc.owner not in ('SYS','SYSTEM')
AND (acc.owner, acc.table_name, acc.column_name, acc.position)
IN
(SELECT acc.owner, acc.table_name, acc.column_name, acc.position
FROM all_cons_columns acc, all_constraints ac
WHERE ac.constraint_name = acc.constraint_name
AND ac.constraint_type = 'R'
MINUS
SELECT table_owner, table_name, column_name, column_position
FROM all_ind_columns)
ORDER BY acc.owner, acc.constraint_name,
acc.column_name, acc.position;
ttitle off
escription: Calculate buffer cache hit ratio in the database. Make sure it is more than 80 for an oltp environment and 99 is the best value.
Code:
column "logical_reads" format 99,999,999,999
column "phys_reads" format 999,999,999
column "phy_writes" format 999,999,999
select a.value + b.value "logical_reads",
c.value "phys_reads",
round(100 * ((a.value+b.value)-c.value) /
(a.value+b.value))
"BUFFER HIT RATIO"
from v$sysstat a, v$sysstat b, v$sysstat c
where
a.statistic# = 38
and
b.statistic# = 39
and
c.statistic# = 40;
Description: Increase Shared pool size to reach a 90% hit ratio on Dictionary Cache. Entries for dc_table_grants, d_user_grants, and dc_users should be under 5% each in the MISS RATE % column
Code:
select
parameter,
gets,
Getmisses ,
getmisses/(gets+getmisses)*100 "miss ratio",
(1-(sum(getmisses)/ (sum(gets)+sum(getmisses))))*100 "Hit ratio"
from v$rowcache
where gets+getmisses <>0
group by parameter, gets, getmisses ;
escription: Reduce the Reloads and try to increase the hit ratios to above 85%
Code:
ttitle center 'LIBRARY CACHE STATS' skip 2
column namespace format a8 heading 'Library'
column gets format 9,999,990 heading 'GETS'
column gethits format 9,999,990 heading 'GETHITS'
column gethitratio format 99.90 heading 'GET|HIT|RATIO'
column pins format 999,999,990 heading 'PINS'
column pinhits format 999,999,990 heading 'PINHITS'
column pinhitratio format 99.90 heading 'PIN|HIT|RATIO'
column reloads format 999,990 heading 'RELOADS'
compute sum of gets on report
compute sum of gethits on report
compute sum of pins on report
compute sum of pinhits on report
compute sum of reloads on report
break on report
select
namespace,gets,gethits,gethitratio,pins,pinhits,
pinhitratio, reloads
from v$librarycache
where gets+gethits+pins+pinhits>0
Description: This should be near 0.If the Ratio is larger than 1% then increase the SHARED_POOL_SIZE.
Code:
column libcache format 99.99 heading 'Percentage' jus cen
select sum(pins) "Total Pins", sum(reloads) "Total Reloads",
sum(reloads)/sum(pins) *100 libcache
from v$librarycache
Description: To run this script you must get connect
as user sys
Code:
select name,
1 - ( physical_reads / ( db_block_gets +
consistent_gets)) "HIT RATIO"
from sys.v$buffer_pool_statistics
where db_block_gets + consistent_gets > 0
Description: THIS SCRIPT CHECK THE FREE SPACE IN THE SHARED POOL AND WHEN THE SIZE ITS VERY SMALL EXECUTE AN ALTER SYSTEM FLUSH SHARED POOL
Code:
CREATE OR REPLACE PROCEDURE flush_it ( pct_full IN NUMBER) AS
--
-- Cursor definitions
--
CURSOR get_share IS
SELECT SUM(sharable_mem) FROM
sql_summary;
--
CURSOR get_var IS
SELECT value FROM v$sga WHERE name LIKE 'Var%';
--
CURSOR get_time IS
SELECT sysdate FROM dual;
--
-- Variable definitions
--
todays_date DATE;
mem_ratio NUMBER;
share_mem NUMBER;
variable_mem NUMBER;
cur INTEGER;
sql_com VARCHAR2(60);
row_proc NUMBER;
--
-- Procedure Body
--
BEGIN
OPEN get_share;
OPEN get_var;
FETCH get_share INTO share_mem;
dbms_output.put_line('share_mem: '||to_char(share_mem));
FETCH get_var INTO variable_mem;
dbms_output.put_line('variable_mem: '||to_char(variable_mem));
mem_ratio:=share_mem/variable_mem;
dbms_output.put_line(to_char(mem_ratio));
IF mem_ratio>(pct_full/100) THEN
cur:=dbms_sql.open_cursor;
sql_com:='ALTER SYSTEM FLUSH SHARED_POOL';
dbms_sql.parse(cur,sql_com,dbms_sql.v7);
row_proc:=dbms_sql.execute(cur);
dbms_sql.close_cursor(cur);
OPEN get_time;
FETCH get_time INTO todays_date;
INSERT INTO dba_running_stats VALUES
(
'Flush of Shared Pool',1,35,todays_date,0
);
COMMIT;
END IF;
END;
Description: The Hit Ratio should be higher than 90%
Code:
select Username,
OSUSER,
Consistent_Gets,
Block_Gets,
Physical_Reads,
100*( Consistent_Gets + Block_Gets - Physical_Reads)/
( Consistent_Gets + Block_Gets ) "Hit Ratio %"
from V$SESSION,V$SESS_IO
where V$SESSION.SID = V$SESS_IO.SID
and ( Consistent_Gets + Block_Gets )>0
and username is not null
order by Username,"Hit Ratio %";
Description: If you want to copy the data from one database to another having many tables and constraints,all you need is this script which will first disable all the constraints then delete the prior data and then finaly copy the data from any remote database and finaly it enables all the constraints on the table of that database.......all you need is to have a table having names of all the tables and the corresponding constraints
Code:
CREATE OR REPLACE procedure pre_tra
as
cursor cconstraints is select table_name, constraint_name, status from all_constraints where owner='owner name';
c2 cconstraints%rowtype;
mytab varchar2(200);
mytab1 varchar2(200);
mytab3 varchar2(200);
mytab4 varchar2(200);
retrycounter number;
counter number;
cstatus varchar2(20);
enableerrorfound boolean:=true;
--MAX_TRY number:=10000;
begin
begin
open cconstraints;
loop
fetch cconstraints into c2;
exit when cconstraints%notfound;
mytab := c2.table_name;
mytab1 := c2.constraint_name;
mytab3 :='alter table ' || mytab || ' disable constraint ' || mytab1;
execute immediate mytab3;
end loop;
close cconstraints;
open cconstraints;
loop
fetch cconstraints into c2;
exit when cconstraints%notfound;
mytab :=c2.table_name;
execute immediate 'delete ' || mytab;
end loop;
close cconstraints;
end;
open cconstraints;
loop
fetch cconstraints into c2;
exit when cconstraints%notfound;
mytab :=c2.table_name;
mytab4 :='DATABASENAME';
execute immediate 'insert into ' || mytab || ' (select * from '|| mytab || '@' || mytab4 || ')';
DBMS_OUTPUT.PUT_LINE(mytab);
commit;
DBMS_OUTPUT.PUT_LINE(mytab);
end loop;
close cconstraints;
begin
DBMS_OUTPUT.PUT_LINE('Starting enable script.....');
RETRYCOUNTER := 3;
WHILE RETRYCOUNTER > 0
loop
open cconstraints;
loop
fetch cconstraints into c2;
exit when cconstraints%notfound;
DBMS_OUTPUT.PUT_LINE('retrycounter==>'||mytab);
mytab := c2.table_name;
mytab1 := c2.constraint_name;
--cstatus:= c2.status;
--if cstatus = 'DISABLED' then
mytab3 :='alter table ' || mytab || ' enable constraint ' || mytab1;
DBMS_OUTPUT.PUT_LINE('Q' || mytab3);
DBMS_OUTPUT.PUT_LINE(retrycounter);
--enableerrorfound:=false;
BEGIN
execute immediate mytab3;
EXCEPTION
WHEN OTHERS THEN
enableerrorfound:=true;
END;
--end if;
end loop;
RETRYCOUNTER := RETRYCOUNTER - 1;
DBMS_OUTPUT.PUT_LINE(retrycounter);
close cconstraints;
end loop;
DBMS_OUTPUT.PUT_LINE(retrycounter);
IF (enableerrorfound) THEN
DBMS_OUTPUT.PUT_LINE('enableerrorfound==> TRUE');
else
DBMS_OUTPUT.PUT_LINE('enableerrorfound==> FALSE');
end if;
end;
end pre_tra;
/
Description: List the UGA and PGA used by each session on the server
Code:
column name format a25
column total format 999 heading 'Cnt'
column bytes format 9999,999,999 heading 'Total Bytes'
column avg format 99,999,999 heading 'Avg Bytes'
column min format 99,999,999 heading 'Min Bytes'
column max format 9999,999,999 heading 'Max Bytes'
ttitle 'PGA = dedicated server processes - UGA = Client machine process'
compute sum of minmem on report
compute sum of maxmem on report
break on report
select se.sid,n.name,
max(se.value) maxmem
from v$sesstat se,
v$statname n
where n.statistic# = se.statistic#
and n.name in ('session pga memory','session pga memory max',
'session uga memory','session uga memory max')
group by n.name,se.sid
order by 3
/
Description: This script lists the size of stored objects
Code:
column num_instances heading "Num" format 999
column type heading "Object Type" format a12
column source_size heading "Source" format 99,999,999
column parsed_size heading "Parsed" format 99,999,999
column code_size heading "Code" format 99,999,999
column error_size heading "Errors" format 999,999
column size_required heading "Total" format 999,999,999
compute sum of size_required on report
select count(name) num_instances
,type
,sum(source_size) source_size
,sum(parsed_size) parsed_size
,sum(code_size) code_size
,sum(error_size) error_size
,sum(source_size)
+sum(parsed_size)
+sum(code_size)
+sum(error_size) size_required
from dba_object_size
group by type
order by 2
/
Description: This script will display the active user and the rollback segment being used in the database
Code:
column rr heading 'RB Segment' format a18
column us heading 'Username' format a15
column os heading 'OS User' format a10
column te heading 'Terminal' format a10
SELECT r.name rr,
nvl(s.username,'no transaction') us,
s.osuser os,
s.terminal te
FROM
v$lock l,
v$session s,
v$rollname r
WHERE
l.sid = s.sid(+) AND
trunc(l.id1/65536) = r.usn AND
l.type = 'TX' AND
l.lmode = 6
ORDER BY r.name
/
escription: This script reports how many hours it has been since the rollback segments wrapped
Code:
select n.name,
round(24*(sysdate-to_date(i1.value||' '||i2.value,'j SSSSS')) /
(s.writes/s.rssize),1) "Hours"
from v$instance i1,
v$instance i2,
v$rollname n,
v$rollstat s
where
i1.key = 'STARTUP TIME - JULIAN'
and i2.key = 'STARTUP TIME - SECONDS'
and n.usn = s.usn
and s.status = 'ONLINE'
Description: Gives lots of usefull easy to read info on how your RBS are performing. Needs 132 char display.
Code:
ol name for a7
col xacts for 9990 head "Actv|Trans"
col InitExt for 990.00 head "Init|Ext|(Mb)"
col NextExt for 990.00 head "Next|Ext|(Mb)"
col MinExt for 99 head "Min|Ext"
col MaxExt for 999 head "Max|Ext"
col optsize for 9990.00 head "Optimal|Size|(Mb)"
col rssize for 9990.00 head "Curr|Size|(Mb)"
col hwmsize for 9990.00 head "High|Water|Mark|(Mb)"
col wraps for 999 head "W|R|A|P|S"
col extends for 990 head "E|X|T|E|N|D|S"
col shrinks for 990 head "S|H|R|I|N|K|S"
col aveshrink for 990.00 head "AVG|Shrink|(Mb)"
col gets head "Header|Gets"
col waits for 99990 head "Header|Waits"
col writes for 999,999,990 head "Total|Writes|Since|Startup|(Kb)"
col wpg for 9990 head "AVG|Writes|Per|HedGet|(bytes)"
set lines 132 pages 40 feed off
break on report
compute sum of gets on report
compute sum of waits on report
compute avg of aveshrink on report
compute avg of wpg on report
select name,
XACTS,
initial_extent/1048576 InitExt,
next_extent/1048576 NextExt,
min_extents MinExt,
max_extents MaxExt,
optsize/1048576 optsize,
RSSIZE/1048576 rssize,
HWMSIZE/1048576 hwmsize,
wraps,
extends,
shrinks,
aveshrink/1048576 aveshrink,
gets,
waits,
writes/1024 writes,
writes/gets wpg
from v$rollstat,v$rollname,dba_rollback_segs
where v$rollstat.usn=v$rollname.usn
and dba_rollback_segs.segment_id=v$rollname.usn
order by name
Description: This script will show you the user's OS name, Username in the database and the SQL Text they are running
Code:
SELECT osuser, username, sql_text
from v$session a, v$sqltext b
where a.sql_address =b.address
order by address, piece
Description: When you connect to sqlplus, you see the the following sql prompt
SQL>
By using this sql in the glogin.sql, you will see a prompt similar to the following:
SCOTT@DB-01>
Code:
The following code works on Oracle 8i (8.1.5, 8.1.6, 8.1.7).
You have to insert the following line of code in glogin.sql which is usually found in
$ORACLE_HOME/sqlplus/admin
set termout off
set echo off
define X=NotConnected
define Y=DBNAME
Column Usr New_Value X
Column DBName New_Value Y
Select SYS_CONTEXT('USERENV','SESSION_USER' ) Usr From Dual;
--- The following does not work in 8.1.5 but works in 8.1.6 or above
---Select SYS_CONTEXT('USERENV','DB_NAME') DBNAME From Dual;
--- If you are using 8.1.5, use this .
Select Global_Name DBNAME from Global_Name;
set termout on
set sqlprompt '&X@&Y> '
Please note that this method will work only when you make a new sql plus session because when you make a new sql plus session, then only glogin.sql is executed.
Description: In N tier structure it is really hard to track
user session specially in a web enviroment,
All users connect to database using the same
application , so all requests are going to database on behalf of one oracle user.
then how can you track actions of one ENDUSER
(application user).
Code:
In the v$session, there are serval columns you
can use to distinguish ENDUSER such as Moule,Action and Client info.
for example, you can use
dbms_application_info.set_client_info('client info')
to set client info accroding to user's ip or
loginname in your application,
then it is easy to find out what sql statement
was execute by this client use following script
select s.sid, s.username, s.program, oc.sql_text
from v$session s, v$open_cursor oc
where s.saddr=oc.saddr and s.sid=oc.sid
and s.client_info='client_info';
if you want to capture all sql in real time, then
there is a tool Oracle Session Manager in
http://www.wangz.net can help you do it.
Description: Purpose: To read raw session trace file with option to
format trace file with TKPROF and read it in SQLPLUS Session
Code:
#!/bin/sh
#################################################################
#################################################################
## Copyright (c)2004 Kalson Systems ##
## Author :Neaman Ahmed ##
## ScriptName:trace ##
## Purpose: To read raw session trace file with option to ##
## format trace file with TKPROF and read it in SQLPLUS Session##
## You must set TRACE_DIR and ORACLE_SID in ##
## this script Put trace in < $HOME/bin > make it executable ##
## and run it from sqlplus session ##
## Example: SQL> ! trace ##
## Critics, Comment and suggestion are welcome nomiak@yahoo.com##
## Note:You Must Run this script from your sqlplus session ##
#################################################################
#################################################################
clear
n1=`ps |grep sqlplus|awk '{print $1}' `
n2=`ps -ef|grep $n1|grep oracle"$ORACLE_SID"|awk '{print $2}'`
TRACE_DIR=/u02/app/oracle/admin/$ORACLE_SID/udump
echo " 1.Do you want to view raw trace file"
echo " 2.Do you want to format trace file and view it"
read choice
if [ "$choice" = "1" ];
then cat $TRACE_DIR/"$ORACLE_SID"_ora_$n2.trc|more
else
if [ "$choice" = "2" ];
then
tkprof $TRACE_DIR/"$ORACLE_SID"_ora_$n2.trc $TRACE_DIR/trace_session_$n2
clear
echo "Your TKPROF formated file name is $TRACE_DIR/trace_session_$n2.prf "
echo " Do you want to read it now y/n "
read ans
if [ "$ans" = "y" ];
then
cat $TRACE_DIR/trace_session_$n2.prf|more
else
if [ "$ans" = "n" ];
then
echo "Happy Hunting Perfomance Problems"
fi
fi
fi
fi
