Some Scripts of Our Own
Through the years, we've been writing scripts to do all kinds of things. The ones included in this section are our favorite database tuning scripts--developed, begged, and borrowed over the last few years. These scripts aren't definitive in any way; they could have been, and probably have been, written in a dozen alternative ways. But they do get the work done. (You will find all of these scripts on the O'Reilly web site.)
What Version of Oracle?
The version of Oracle that you are running can have a significant impact on your performance. Use the following command to show version information:
SELECT *
FROM v$version;
--------------------------------------------------------------
Oracle7 Server Release 7.2.3.2.0 - Production Release
PL/SQL Release 2.2.2.2.0 - Production
CORE Version 2.3.7.1.0 - Production (LSF Alpha)
TNS for SVR4: Version 2.1.6.0.0 - Production
NLSRTL Version 2.3.6.0.0 - Production
What Are the INIT.ORA Settings?
In addition to the standard INIT.ORA parameters, Oracle also has a number of undocumented parameters, that appear with an underscore in front of them in parameter displays. Several of the undocumented parameters, such as _DB_BLOCK_WRITE_BATCH and _LOG_ENTRY_PREBUILD_THRESHOLD, can be used to improve your performance. (See the discussion of such parameters in Chapters and .)
Run this script to show your system's parameter setting; then compare your settings to the recommendations in this book.
# undoc.sql
SELECT name, value
FROM v$parameter;
SELECT ksppinm, ksppivl
FROM x$ksppi
WHERE SUBSTR(ksppinm,1,1) = '_';
We don't show the output from this script because of its excessive length.
Looking Inside the SGA
This script shows details of the System Global Area in memory. The most significant information is the reading for "free memory," which may indicate that the SHARED_POOL_SIZE INIT.ORA parameter should be reduced if the free memory is excessive. If the parameter setting is low, you should not be decreasing the SHARED_POOL_SIZE.
WARNING: Oracle tends to maintain some free memory even when the shared pool size is flooded with activity and needs to be made larger.
Other figures that are useful are the "db_block_buffers," size of the buffer cache, which is usually required to be at least 20 megabytes for optimum performance; the "sql area," which is where all of the shared SQL is placed; the dictionary cache, which is where Oracle's dictionary is placed; and the "log buffer," which is where all changes are written prior to writing to your redo logs.
The log buffer should typically be at least 32,078 bytes or larger. The "shared_sql" and "dictionary cache" sizes are affected by the size of your SHARED_POOL_SIZE INIT.ORA parameter. Unfortunately, the dictionary cache is tuned automatically and not very well by the kernel. The majority of sites operate most efficiently with a shared pool size of at least 30,000,000 bytes.
# sgastat.sql
SELECT *
FROM v$sgastat
WHERE name IN ('free memory', 'db_block_buffers', 'log_buffer'
`dictionary cache', `sql area', `library cache');
NAME BYTES
---------------------------------- ----------
free memory 88652
db_block_buffers 20480000
log_buffer 512000
dictionary cache 2528868
sql area 43658416
library cache 13177800
Identifying Database Extents
One of the most common activities you'll find yourself doing as a DBA is scanning the physical database, looking for new table and index extents. You ought to do this on a regular basis, ideally as part of an automated daily or weekly overnight procedure. Your goal is to minimize the number of extents on disk. Access to contiguous areas of disk is much faster than access to noncontiguous areas. In one test that we did on a 4,000-row table, we found that when the entire table fit on one extent, it took 0.76 second to scan it; when the table was spread over 10 extents, it took 3.62 seconds.
We have found that the existence of a small number of such extents (fewer than five) doesn't seem to affect performance very much, but it is still good practice to store your objects in a single extent. (In the next section, we describe how to size the table for the reorganization while not wasting valuable disk space.)
The following script assumes that the operating system Oracle block size is 4 kilobytes and that all ROLLBACK segments were created with 10 initial extents (MINEXTENTS parameter).
# objstor.sql
SELECT SUBSTR(s.segment_name,1,20) OBJECT_NAME,
SUBSTR(s.segment_type,1,5) TYPE,
SUBSTR(s.tablespace_name,1,10) T_SPACE,
NVL(NVL(t.initial_extent, i.initial_extent),r.initial_extent)/ 4096
FST_EXT,
NVL(NVL(t.next_extent,i.next_extent),R.NEXT_EXTENT) / 4096 NXT_EXT,
s.extents - 1 tot_ext,
s.blocks tot_blks
FROM
dba_rollback_segs R,
dba_indexes I,
dbs_tables T,
dba_segments S
WHERE s.segment_name LIKE UPPER('&S_NAME') || '%'
AND s.tablespace_name LIKE UPPER('&T_SPACE') || '%'
AND s.extents > 1
AND s.owner = t.owner (+)
AND s.segment_name = t.table_name (+)
AND s.tablespace_name = t.tablespace_name (+)
AND s.owner = i.owner (+)
AND s.segment_name = i.index_name (+)
AND s.tablespace_name = i.tablespace_name (+)
AND s.owner = r.owner (+)
AND s.segment_name = r.segment_name (+)
AND s.tablespace_name = r.tablespace_name (+)
ORDER BY s.segment_name, s.segment_type;
OBJECT_NAME TYPE T_SPACE FST NXT TOT TOT
EXT EXT EXT BLKS
------------------ ------ -------- --- --- --- ----
ALL_TRAN_AUDX_INDX INDEX DEV_IDX 125 63 2 251
OBJ$ TABLE SYSTEM 13 13 1 26
PRODUCT_PROFILE TABLE SYSTEM 13 13 1 26
RBACK1 ROLLB RBK 25 25 9 300
RBACK2 ROLLB RBK 25 25 9 525
RBACK_BIG ROLLB RBK 256 256 9 2560
XREF$ TABLE SYSTEM 13 13 1 26
Performing Database Table Sizing
This section contains several scripts that we use to size a database.
Looking for tablespace space shortages
When tables, indexes, and rollback segments are created, they are preassigned a storage allocation (extent), which is reserved and cannot be used by any other object. Although the objects may not use all of the space allocated at the start, as more information is placed into the area the amount of available free space diminishes. This query helps you instantly find application problems resulting from space shortages. Run it at regular intervals for the best information. Note that this script assumes that you have Oracle data blocks of 4 kilobytes (4,096 bytes). This size is operating system dependent, and you will have to modify the query if your block sizes differ.
## tspuse.sql
SELECT SUBSTR(D.tablespace_name,1,15) TSPACE,
D.file_id FILE_ID,
D.bytes / 1024 / 1024 TOT_MB,
D.bytes / 4096 ORA_BLKS,
SUM(E.blocks) TOT_USED,
ROUND(SUM(E.blocks) / D.bytes / 4096, 4 * 100 PCT_USED
FROM sys.dba_extents E,
sys.dba_data_files D
WHERE D.file_id = E.file_id (+)
GROUP BY D.tablespace_name, D.file_id, D.bytes /
TSPACE FILE_ID TOT_MB ORA_BLKS TOT_USED PCT_USED
------- ------- ------ -------- -------- --------
DEV 4 250 64000 36633 57.2
DEV_AUD 6 100 25600 3691 14.4
DEV_IDX 5 300 76800 61317 79.8
HST 7 200 51200 38400 75.0
INV 8 80 20480 13739 67.1
INV_IDX 9 50 12800 7673 59.9
RBK 3 25 6400 4110 64.2
SYSTEM 1 20 5120 2366 46.2
TMP 2 50 12800
Looking for tablespace fragmentation
This query of the database gives a detailed breakdown of the fragmentation of each tablespace file within the database.
# fragment.sql
SELECT SUBSTR(ts.name,1,10) TSPACE,
tf.blocks BLOCKS,
SUM(f.length) FREE,
COUNT(*) PIECES,
MAX(f.length) BIGGEST,
MIN(f.length) SMALLEST,
ROUND(AVG(f.length)) AVERAGE,
SUM(DECODE(SIGN(f.length-5), -1, f.length, 0)) DEAD
FROM sys.fet$ F,
sys.file$ TF,
sys.ts$ TS
WHERE ts.ts# = f.ts#
AND ts.ts# = tf.ts#
GROUP BY ts.name, tf.blocks;
Tspace Blocks Free Pieces Biggest Smallest Average Dead
DEV 64000 27366 9 25614 105 3041 0
DEV_AUD 25600 21908 1 21908 21908 21908 0
DEV_IDX 76800 15482 16 175 4 968 2
HST 51200 12799 1 12799 12799 12799 0
INV 20480 6740 12 6740 6740 6740 0
INV_IDX 12800 5126 4 2565 63 1282 0
RBK 6400 2289 1 2289 2289 2289 0
SYSTEM 5120 2753 74 487 3 16 12
TMP 12800 12799 41 1536 11 312 0
The last column, "Dead," is based on the assumption that any contiguous block smaller than five Oracle blocks (20 kilobytes for the operating system that we used for testing) cannot be used. That is, no table or index has an INITIAL or NEXT extent size less than 20 kilobytes.
Looking at space use by individual tables
This query reports how full a particular table actually is. It compares the number of Oracle blocks that have at least one record against the total number of blocks allocated to the table extent(s). You can use this query to interrogate table after table; the table name replaces the name &TAB_NAME in each statement execution.
# tabused.sql
SELECT BLOCKS ALLOCATED_BLKS,
COUNT(DISTINCT SUBSTR(T.ROWID,1,8)
|| SUBSTR(T.ROWID,15,4)) USED,
(COUNT(DISTINCT SUBSTR(T.ROWID,1,8)
|| SUBSTR(T.ROWID,15,4))
/ BLOCKS) * 100 PCT_USED
FROM SYS.DBA_SEGMENTS E,
&TAB_NAME T
WHERE E.SEGMENT_NAME = UPPER ('&TAB_NAME')
AND E.SEGMENT_TYPE = 'TABLE'
GROUP BY E.BLOCKS;
ALLOCATED_BLKS USED PCT_USED
-------------- ---- --------
2560 1728 67.50
Looking at the average number of records per block
This query reports the number of rows physically residing in Oracle blocks of a table. This query can be used to calculate how much space a table will ultimately require.
# blokrows.sql
SELECT SUBSTR(T.ROWID,1,8) || '-' ||SUBSTR(T.ROWID,15,4) BLOCK,
COUNT(*) ROW_CNT,
FROM &TAB_NAME T
WHERE ROWNUM < 2000
GROUP BY SUBSTR(T.ROWID,1,8) || '-' || SUBSTR(T.ROWID,15,4);
Output from this query is as follows:
BLOCK ROW_CNT
------------- -------
00001F52-0002 93
00001F53-0002 85
00001F54-0002 82
00001F55-0002 100
00001F56-0002 83
00001F57-0002 71
00001F58-0002 82
00001F59-0002 91
00001F5A-0002 93
00001F5B-0002 91
00001F5C-0002 63
00001F5D-0002 69
00001F5E-0002 75
00001F5F-0002 1
00001F60-0002 4
00001F61-0002 5
Putting it together
By looking at the results of the set of scripts that are included in this section, you can do a good job of calculating future table requirements:
-
Script 1
- Tells us that the DEV tablespace is only 43% used. Of the initial 250-megabyte allocation, more than 107 megabytes are still free. This tablespace should not be a problem for some time. Script 2
- Tells us that the 107 megabytes of free space within the DEV tablespace comprise only nine contiguous segments and no dead blocks. This tells us that the free space is indeed free and usable. Script 3
- Tells us that the table being analyzed currently has consumed only 67% of its current extent allocations. Script 4
- Tells us that the table being analyzed can store an average of 80 to 90 records per Oracle block (4 kilobytes per block). Therefore the current volume represents one year of growth and is already 40,000 records in size. What storage will be needed for 10 years' growth?
We advise you to be relatively conservative in making estimates so that you don't consume needless amounts of disk space, while being sensible enough not to run out of space too soon. This is not always an easy task! Our calculation is as follows:
Total records = 40,000 x 10
= 400,000 records (adjust to 500,000)
Records Per Block = 80 . . . 90 (adjust to 75)
Space Requirements = (Total Records / Records Per Block) x Block Size = ( 500,000 / 75 ) * ( 1024 * 4 )
= 27,306,667 bytes = 26.4 megabytes
Checking Extent Sizes and PCTINCREASE
If the default extent sizes and PCTINCREASE on your tablespaces are set incorrectly, these can have a marked impact on your performance. The SYSTEM tablespace has a default PCTINCREASE of 50%. If you decrease that value to 0, as many sites do, the number of extents on the dictionary objects becomes excessive and degrades performance. The default INITIAL and NEXT parameters on your temporary tablespace can also cause a lot of extents to be thrown if they are set to a value smaller than the SORT_AREA_SIZE. Ideally, the NEXT extent sizes should be equal to, or a multiple of, the SORT_AREA_SIZE.
SELECT SUBSTR(tablespace_name, 1,18), initial_extent,
next_extent, pct_increase
FROM dba_tablespaces
ORDER BY tablespace_name;
Another troublesome problem that can affect performance is when a user other than SYSTEM or SYS has his or her default tablespace set to the SYSTEM tablespace. Use the following query to ensure that all of your users have been assigned to an appropriate tablespace:
SELECT username
FROM dba_users
WHERE username NOT IN (`SYS', `SYSTEM')
AND (default_tablespace = `SYSTEM'
OR
temporary_tablespace = `SYSTEM');
Looking at Objects That Can't Throw an Extent
Some scripts list all objects that will not have a free extent that is large enough, assuming that the object has filled its current space allocation and is forced to throw an extent. The following script provides the same information but runs 20 times faster:
# exent.sql
SELECT seg.owner, seg.segment_name,
seg.segment_type, seg.tablespace_name, t.next_extent
FROM sys.dba_segments seg, sys.dba_tables t
WHERE (seg.segment_type = 'TABLE'
AND seg.segment_name = t.table_name
AND seg.owner = t.owner
AND NOT EXISTS
(SELECT tablespace_name
FROM dba_free_space free
WHERE free.tablespace_name = t.tablespace_name
AND bytes >= t.next_extent ))
UNION
SELECT seg.owner, seg.segment_name,
seg.segment_type, seg.tablespace_name,
DECODE (seg.segment_type,
'CLUSTER', c.next_extent)
FROM sys.dba_segments seg,
sys.dba_clusters c
WHERE (seg.segment_type = 'CLUSTER'
AND seg.segment_name = c.cluster_name
AND seg.owner = c.owner
AND NOT EXISTS
(SELECT tablespace_name
from dba_free_space free
WHERE free.tablespace_name = c.tablespace_name
AND bytes >= c.next_extent ))
UNION
SELECT seg.owner, seg.segment_name,
seg.segment_type, seg.tablespace_name,
DECODE (seg.segment_type,
'INDEX', i.next_extent )
FROM sys.dba_segments seg,
sys.dba_indexes i
WHERE (seg.segment_type = 'INDEX'
AND seg.segment_name = i.index_name
AND seg.owner = i.owner
AND NOT EXISTS
(SELECT tablespace_name
FROM dba_free_space free
WHERE free.tablespace_name = i.tablespace_name
AND bytes >= i.next_extent ))
UNION
SELECT seg.owner, seg.segment_name, seg.segment_type,
seg.tablespace_name,
DECODE (seg.segment_type, 'ROLLBACK', r.next_extent)
FROM sys.dba_segments seg, sys.dba_rollback_segs r
where (seg.segment_type = 'ROLLBACK'
AND seg.segment_name = r.segment_name
AND seg.owner = r.owner
AND NOT EXISTS
(SELECT tablespace_name
FROM dba_free_space free
WHERE free.tablespace_name = r.tablespace_name
AND bytes >= r.next_extent ));
Determining Archive Log Disk Location
From both a performance and recovery perspective, it's important to put the archive logs on a different disk from that used by your other data files and redo logs. If the archive logs are on the same disk as other data files or the redos, there is a high likelihood of disk I/O bottlenecks.
# datafile.sql
SELECT value
FROM v$parameter
WHERE name like 'log_archive_dest'
UNION
SELECT name
FROM v$datafile
UNION
SELECT member
FROM v$logfile
/
Which User Is Using the CPU?
The following query lists four users' usage of the CPU, ordered by largest usage first. You must have TIMED_STATISTICS set to TRUE for the readings to appear. If TIMED_STATISTICS is set to FALSE, all of the readings will be zero.
# sesscpu.sql
SELECT SUBSTR(name,1,30) parameter,
ss.username||'('||se.sid||') ' user_process, value
FROM v$session ss, v$sesstat se, v$statname sn
WHERE se.statistic# = sn.statistic#
AND name like '%CPU used by this session%'
AND se.sid = ss.sid
ORDER BY substr(name,1,25), value DESC
/
Computing the Hit Ratio
Throughout this book, we have emphasized how proper sizing of the Oracle cache buffers can help to reduce I/O. Computing the hit ratio is a very helpful way to do this sizing. The hit ratio tells us how many times Oracle has needed to retrieve a database block and has found it in memory (rather than having to access it on disk). Because memory access is so much faster than disk access, the higher the hit ratio, the better your performance.
You can ordinarily obtain the hit ratio for your application only by looking at either the UTLBSTAT/UTLESTAT statistics or the DBA MONITOR screens, as described earlier in this chapter. The script included below shows how to get the hit ratio from SQL*Plus. If you do this, you can automatically schedule hit ratio queries and can direct the output to a report or another database table. That table can then be used to produce application statistics or management reporting.
# hitrate.sql
SELECT
SUM(DECODE(name, 'consistent gets',value, 0)) "Consis Gets",
SUM(DECODE(name, 'db block gets',value, 0)) "DB Blk Gets",
SUM(DECODE(name, 'physical reads',value, 0)) "Phys Reads",
(SUM(DECODE(name, 'consistent gets',value, 0))
+ SUM(DECODE(name, 'db block gets',value, 0))
- SUM(DECODE(name, 'physical reads',value, 0)))
/
(SUM(DECODE(name, 'consistent gets',value, 0))
+ SUM(DECODE(name, 'db block gets',value, 0)) ) * 100 "Hit Ratio"
FROM v$sysstat;
The following output is taken from an actual site. Before the buffer cache was increased and a handful of SQL statements were tuned, the hit ratio was at 26%.
Consis Gets DB Blk Gets Phys Reads Hit Ratio
---------- ----------- ---------- ----------
436987321 877262 2142974 99. 5105852
The next step you must take is to find out which user is causing the poor hit ratio.
# userhit.sql
SELECT se.username||'('|| se.sid||')' "User Session",
SUM(DECODE(name, 'consistent gets',value, 0)) "Consis Gets",
SUM(DECODE(name, 'db block gets',value, 0)) "DB Blk Gets",
SUM(DECODE(name, 'physical reads',value, 0)) "Phys Reads",
(SUM(DECODE(name, 'consistent gets',value, 0))
+ SUM(DECODE(name, 'db block gets',value, 0))
- SUM(DECODE(name, 'physical reads',value, 0)))
/
(sum(DECODE(name, 'consistent gets',value, 0))
+ SUM(DECODE(name, 'db block gets',value, 0)) ) * 100 "Hit Ratio"
FROM v$sesstat ss, v$statname sn, v$session se
WHERE ss.sid = se.sid
AND sn.statistic# = ss.statistic#
AND value != 0
AND sn.name IN ('db block gets', 'consistent gets', 'physical reads')
GROUP BY se.username||'('|| se.sid||')' ;
User Session Consis Gets DB Blk Gets Phys Reads Hit Ratio
------------- ----------- ----------- ---------- ---------
(5) 27679 8934 11012 69.92
(6) 36 272 24 92.21
CORRIGANP(16) 173176 385 521 99.70
GURRYM(18) 1265544 2187 11959 99.06
OREILLYT(21) 22705 149 21 99.91
RUSSELLD(61) 128754 317 185 99.86
Looking at the Dictionary Cache
This script lets you interrogate the Oracle data dictionary performance tables via SQL*Plus. These tables give you information about all the objects stored in your dictionary (tablespaces, files, users, rollback segments, constraints, synonyms, etc.). This information is available in other ways, but getting at it through SQL*Plus lets you automate your queries, as we described for the hit ratio in the previous section.
If the dictionary cache is perfectly tuned, the query below would return no rows. When entries are loaded into the dictionary cache for the first time, the "Getmisses" figure is incremented by one. If the "Count," which is the number of entries set aside for each dictionary cache type, is set too small, entries will have to be thrown out to make way for new dictionary entries being read from disk into memory. This will cause rows to appear in the following query.
# rowcache.sql
SELECT parameter, count, getmisses
FROM v$rowcache
WHERE getmisses > count;
Dictionary Cache (Part of Shared Buffer Pool)
PARAMETER COUNT GETMISSES
-------------------------------- ---------- ----------
dc_free_extents 41 172
dc_used_extents 18 150
dc_segments 125 202
dc_objects 1798 1815
dc_columns 4428 4639
Some people prefer to work with ratios and percentages. The dictionary cache miss ratio should ideally be less than 1%, although when the database is first started, the miss ratio will be higher because each dictionary item loaded into memory will record a miss. If the miss ratio is greater than 2% and you have spare memory, increase your SHARED_POOL_SIZE. If the ratio has decreased, you should have improved your performance.
# rowcache.sql
SELECT SUM(gets) "Gets", SUM(misses) "Misses",
TO_CHAR(SUM(getmisses) / SUM(gets) * 100 , `999.99')||'%' "Miss Ratio"
FROM v$rowcache;
Gets Misses Miss Ratio
-------------------------------------------------------------------------
119,929,181 314,100 2.61%
Looking at Rollback Segment Usage
It is sometimes useful to know which users are accessing the rollback segments. This is important information when a user is continually filling the rollbacks and causing extents to be thrown.
# rolbusrs.sql
SELECT r.name "Rollback Segment Name ",
p.spid "System Process Id ",
s.username||'(`||l.sid||')' "Oracle User Pid"
FROM v$lock l, v$process p, v$rollname r, v$session s
WHERE l.sid = p.pid(+)
AND s.sid=l.sid
AND TRUNC (l.id1(+)/65536) = r.usn
AND l.type(+) = 'TX'
AND l.lmode(+) = 6
ORDER BY r.name
/
Finding Foreign Key Relationships
It's useful to know the foreign keys and the unique or primary keys to which they relate. Foreign keys produce potentially damaging locking problems if the foreign key columns on the child table are not indexed, as we describe in Chapter 8, Selecting a Locking Strategy. The first query below lists all of the foreign keys and the parent table and columns to which they relate.
# forgnkey.sql
SELECT a.owner , a.table_name , c.column_name ,
b.owner , b.table_name , d.column_name
FROM dba_constraints a, dba_constraints b,
dba_cons_columns c, dba_cons_columns d
WHERE a.r_constraint_name = b.constraint_name
AND a.constraint_type = 'R'
AND b.constraint_type = 'P'
AND a.r_owner=b.owner
AND a.constraint_name = c.constraint_name
AND b.constraint_name=d.constraint_name
AND a.owner = c.owner
AND a.table_name=c.table_name
AND b.owner = d.owner
AND b.table_name=d.table_name;
The second query lists all of the foreign keys that do not have the appropriate indexes in place on the child table. It shows the foreign key constraints that cause locking problems.
# forgnkey.sql
SELECT acc.owner||'-> '||acc.constraint_name||'('||acc.column_name
||'['||acc.position||'])'||' ***** 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, 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;
Listing Columns with Inconsistent
Data Types or Lengths
The following query lists all columns that have differing lengths or data types but that have the same column name. For example, ACCOUNT_NO may be NUMBER(9) in one table and VARCHAR(9) in another. Having different data types can cause data casting problems and result in indexes not being used. (See Chapter 6 for details on data casting.)
# coldiffs.sql
SELECT owner, column_name
, table_name||' '||data_type||'('||
DECODE(data_type, 'NUMBER', data_precision, data_length)||')'
"Characteristics"
FROM all_tab_columns
WHERE (column_name, owner)
IN
(SELECT column_name, owner
FROM all_tab_columns
GROUP BY column_name, owner
HAVING MIN(DECODE(data_type, 'NUMBER', data_precision,
data_length))
< MAX(DECODE(data_type, 'NUMBER', data_precision,
data_length)) )
AND owner NOT IN ('SYS', 'SYSTEM')
Listing Tables That Are Cached
Oracle7.1 and later provide a mechanism for caching tables in the buffer cache, using the command
ALTER TABLE tablename CACHE
Caching tables will speed up data access and improve performance by finding the data in memory and avoiding disk reads. To determine which tables have been cached, run the following command:
# tabcache.sql
SELECT owner, table_name, cache
FROM all_tables
WHERE owner not in ('SYS', 'SYSTEM')
AND cache not like 'N%';
Listing Invalid Objects
Having invalid objects in your database usually indicates that your underlying tables have been altered to add a new column or have had DDL operations performed on them. The most common objects that become invalid are views, packages, and procedures. Invalid packages and procedures can cause a long response delay because they have to be recompiled. The user is forced to wait for the recompilation to complete. If you do alter your tables, you should always recompile your invalid packages and procedures to avoid user frustration. To obtain a list of all of the invalid objects, run the following query:
# invalobj.sql
SELECT owner, object_type, object_name, status
FROM all_objects
WHERE status = 'INVALID'
ORDER BY owner, object_type, object_name
/
Listing All Triggers
Triggers can significantly affect performance. They can cause major problems if the trigger has been disabled and someone has forgotten to reenable the trigger. In this case, the changes that the trigger was to have made will be difficult to recreate, since it is difficult to determine what updates have been applied to the table after the trigger was disabled. To obtain a list of all of the triggers and their status, run this query:
# triggers.sql
TTITLE 'List All Triggers'
SELECT table_name, trigger_name, status
FROM all_triggers
ORDER BY table_name, trigger_name;
Doing Latch Analysis
Latches are low-level locking mechanisms that are used to protect Oracle data and memory structures, such as the least-recently-used list in the buffer cache or the redo allocation of space in the log buffer. (We describe latch tuning in Chapter 11.) The following script investigates who is holding the latches:
# latchhld.sql
SELECT l.name "Latch Held", p.username "User Holding the Latch"
FROM v$process p,v$latchholder l
WHERE l.pid = p.pid;
Checking the Number of Objects
This listing provides you with a list of the number of objects on a per-user basis. It can be run regularly to make sure that your database is operating as you expect it to.
# objcount.sql
SELECT username,
COUNT(DECODE(o.type, 2, o.obj#, `')) Tab,
COUNT(DECODE(o.type, 1, o.obj#, `')) Ind,
COUNT(DECODE(o.type, 5, o.obj#, `')) Syn,
COUNT(DECODE(o.type, 4, o.obj#, `')) Vew,
COUNT(DECODE(o.type, 6, o.obj#, `')) Seq,
COUNT(DECODE(o.type, 7, o.obj#, `')) Prc,
COUNT(DECODE(o.type, 8, o.obj#, `')) Fun,
COUNT(DECODE(o.type, 9, o.obj#, `')) Pck,
COUNT(DECODE(o.type,12, o.obj#, `')) Trg,
COUNT(DECODE(o.type,10, o.obj#, `')) Dep
FROM sys.obj$ o, sys.dba_users U
WHERE u.user_id = o.owner# (+)
GROUP BY username;
http://www.oreilly.com/catalog/oracle2/chapter/ch10.html#73980[@more@]
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/257699/viewspace-814993/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/257699/viewspace-814993/