Tuning

Performance Tuning

Last modification: 18-Aug-11 Includes:

- Installation and Top Init.ora Parameters

-

- Instance Tuning

- Application and SQL Tuning

- Distribution of Disk I/O

- ANALYZE and DBMS_STATS Package

- Working with UNDO

- Indexes on Foreign Keys (FK)

- Rebuild Indexes

- Hints

- Nologging

- CBO Options (Optimizer Mode)

- Connect using IPC to Local Databases

- Space used per block

Installation

The total available memory on a system should be configured in such a manner, that all components of the system function at optimum levels. The following is a rule-of-thumb breakdown to help assist in memory allocation for the various components in a system with an Oracle back-end.

SYSTEM COMPONENT ALLOCATED % OF MEMORY

Oracle SGA Components ~ 50%

Operating System +Related Components ~15%

User Memory ~ 35%

The following is a rule-of-thumb breakdown of the ~50% of memory that is allocated for an Oracle SGA. These are good starting numbers and will potentially require fine-tuning, when the nature and access patterns of the application is determined.

ORACLE SGA COMPONENT ALLOCATED % OF MEMORY

Database Buffer Cache ~80%

Shared Pool Area ~12%

Fixed Size + Misc ~1%

Redo Log Buffer ~0.1%

The following is an example to illustrate the above guidelines. In the following example, it is assumed that the system is configured with 2 GB of memory, with an average of 100 concurrent sessions at any given time. The application requires response times within a few seconds and is mainly transactional. But it does support batch reports at regular intervals.

SYSTEM COMPONENT ALLOCATED MEMORY(IN MB)

Oracle SGA Components ~1024

Operating System +Related Components ~306

User Memory ~694

In the aforementioned breakdown, approximately 694MB of memory will be available for Program Global Areas (PGA) of all Oracle Server processes. Again, assuming 100 concurrent sessions, the average memory consumption for a given PGA should not exceed ~7MB. It should be noted that SORT_AREA_SIZE is part of the PGA.

ORACLE SGA COMPONENT ALLOCATED MEMORY(IN MB)

Database Buffer Cache ~800

Shared Pool Area ~128 - 188

Fixed Size + Misc ~ 8

Redo Log Buffer ~ 1 (average size 512K)

Another Example

Let's assume that we have a high water mark of 100 connects sessions to our Oracle database server. We multiply 100 by the total area for each PGA memory region, and we can now determine the maximum size of our SGA:

The total RAM demands for Oracle is 20 percent of total RAM for MS-Windows, 10% of RAM for UNIX

Here we can see the values for sort_area_size and hash_area_size for our Oracle database. To compute the value for the size of each PGA RAM region, we can write a quick data dictionary query against the v$parameter view :

set pages 999;

column pga_size format 999,999,999 select

2048576 + a.value + b.value pga_size from v$parameter a, v$parameter b where a.name = 'sort_area_size' and b.name = 'hash_area_size';

PGA_SIZE 3,621,440

The output from this data dictionary query shows that every connected Oracle session will use 3.6 megabytes of RAM memory for the Oracle PGA. Now, if we were to multiply the number of connected users by the total PGA demands for each connected user, we will know exactly how much RAM memory in order to reserve for connected sessions.

Total RAM on Windows Server 1250 MB Less:

Total PGA regions for 100 users: 362 MB RAM reserved for Windows (20 percent) 500 MB 862 MB

Hence, we would want to adjust the RAM to the data buffers in order to make the SGA size less than 388 MB (that is 1250MB - 862 MB). Any SGA size greater than 388 MB, and the server will start RAM paging, adversely affecting the performance of the entire server. The final task is to size the Oracle SGA such that the total memory involved does not exceed 388 MB.

Examples for UNIX Environments

0) for super machines with 4 GB of ram & swap 12 GB set shmsys:shminfo_shmmax=3221225471 set shmsys:shminfo_shmmin=1 set shmsys:shminfo_shmmni=1024 set shmsys:shminfo_shmseg=100 set semsys:seminfo_semmni=1024 set semsys:seminfo_semmns=163840 set semsys:seminfo_semmsl=160 set semsys:seminfo_semmap=163840 set semsys:seminfo_semmnu=163840 set msgsys:msginfo_msgmap=163840 set msgsys:msginfo_msgmax=6144 set msgsys:msginfo_msgmni=640 set msgsys:msginfo_msgssz=64 set msgsys:msginfo_msgtql=640 set msgsys:msginfo_msgseg=32768

1) For high end machines with 2 GB of RAM & 6 GB of swap, we recommend the following: set shmsys:shminfo_shmmax=1073741824 set shmsys:shminfo_shmmin=1 set shmsys:shminfo_shmmni=250 set shmsys:shminfo_shmseg=100 set semsys:seminfo_semmni=750 set semsys:seminfo_semmns=75000 set semsys:seminfo_semmsl=100 set semsys:seminfo_semmap=75000 set semsys:seminfo_semmnu=75000 set msgsys:msginfo_msgmap=75000 set msgsys:msginfo_msgmax=6144 set msgsys:msginfo_msgmni=640 set msgsys:msginfo_msgssz=64 set msgsys:msginfo_msgtql=640 set msgsys:msginfo_msgseg=32768

2) For medium end machines with 1 GB of RAM & 3 GB of swap we recommend the following: set shmsys:shminfo_shmmax=536870912 set shmsys:shminfo_shmmin=1 set shmsys:shminfo_shmmni=150 set shmsys:shminfo_shmseg=50 set semsys:seminfo_semmni=500 set semsys:seminfo_semmns=50000 set semsys:seminfo_semmsl=100 set semsys:seminfo_semmap=50000 set semsys:seminfo_semmnu=50000 set msgsys:msginfo_msgmap=50000 set msgsys:msginfo_msgmax=2048 set msgsys:msginfo_msgmni=512 set msgsys:msginfo_msgssz=32 set msgsys:msginfo_msgtql=512 set msgsys:msginfo_msgseg=16384

Top Oracle Init.ora's Parameters

BUFFER_POOL_KEEP - How many buffers to have for pinned objects that you need

BUFFER_POOL_RECYCLE - How many buffers to have for new stuff that will get pushed out CHECKPOINT_PROCESS = True (starts CKPT process for better performance at checkpoints)

CLOSED_CACHED_OPEN_CURSORS = Indicates if the cursors must be closed immediatly after the committ. If you are using a lot of cursors or Developer 2000, use FALSE

COMPATIBLE - Set for correct version and features CPU_COUNT = number of CPUs on your system.

DB_CACHE_SIZE = This parameter determines the number of blocks in the database buffer cache in the SGA. The buffer cache is a holding area in memory for database blocks retrieved from disk. Oracle will typically check for the existence of a needed data block before performing an I/O operation to retrieve it. Increment if the hit ratio < 95%. If this value is too low, then the data will be flushed from memory, if it is too high then Swapping. Suggestion: 40% or 50% of the total SGA size (for the main application). The standard interpretation of this value is that we don't have enough buffers in memory if the ratio is less than 90. In this case, almost &frac12; of the time that we request a buffer we need to go to the disk to find it.

*Determine if DB_CACHE_SIZE is high enough (Goal > 98% for web systems, 95% for others)

select 100-(sum(decode(name, 'physical reads', value,0))/ (sum(decode(name, 'db block gets', value,0)) +

(sum(decode(name, 'consistent gets', value,0))))) * 100 "Read Hit Ratio"

from v$sysstat;

Per Buffer

Another way to see this ratio, as of V8.1, is per pool from the V_BUFFER_POOL_STATISTICS view. This does not include the direct physical reads, so per pool we would have:

select name,(1-(physical_reads/(db_block_gets+consistent_gets)))*100 cache_hit_ratio from v$buffer_pool_statistics; NAME CACHE_HIT_RATIO --- ---KEEP 77.42 RECYCLE 100.00 DEFAULT 50.91

Now logically, we don't care about the hit ration in the RECYCLE pool since this is for buffers that we think will only be used once and then flushed out. The KEEP and DEFAULT pools still have a much smaller hit ratio than we are told we need. So if we followed the guidelines we would add more buffers.

A Different Approach

We can ask the question the other way around. Instead of 'Do we need more?' we can as 'Do we have more than we need?' No matter what the hit ratio is, if we are not using all of the buffers that have been allocated, there is no advantage in allocating more. In fact, this could slow us down by forcing more swapping at the OS level. So we can just check if there are free buffers:

select count(1) from v$bh where status='free';

COUNT(1) ---984

This is from the same instance in which I have the 56 percent hit ratio. Here I see that increasing the number of buffers will not impact the hit ratio at all since I have free buffers right now. But I might want to shift my allocation of buffers between the pools. I want the highest hit ratio in my keep pool since I know that I am going to be reusing this data. Ideally, I have one buffer free all the time. This would tell me that I have not over-allocated and that I have exactly what is needed. At the same time I will want to check my paging on the server. I might make the instance faster by decreasing the size of my SGA. Of course, there are other factors in memory

consumption and you will want to take all into account.

DB_BLOCK_SIZE - Size of the blocks (db_block_size x db_cache_size=bytes for data). Setup on database creation. Generally 8K, for DW 16K

DB_FILE_MULTIBLOCK_READ_COUNT= DB_FILE_MULTIBLOCK_READ_COUNT controls the number of data blocks read for each read request during a full table scan. If you are using LVM or striping, this parameter should be set so that

DB_BLOCK_SIZE * DB_FILE_MULTIBLOCK_READ_COUNT is a multiple of the LVM stripe size. If you are not using LVM or striping, DB_BLOCK_SIZE * DB_FILE_MULTIBLOCK_READ_COUNT should equal the maximum operating system read buffer. On many UNIX systems and Windows systems this is 64 KB. In any case, DB_FILE_MULTIBLOCK_READ_COUNT cannot be larger than

DB_CACHE_SIZE / 4.

The maximum read buffer is generally higher on raw file systems. It varies from 64 KB (on AIX) to 128 KB (on Solaris) to 1 MB (HP-UX). On a UNIX file system, it is usually only possible to read one buffer per I/O, usually 8KB. On 32-bit Windows, the buffer is 256KB. This parameter will significantly increase the performance of a reorganization if properly tuned. For example, suppose the OS read buffer is 64 KB, the database block size is 4 KB and DB_FILE_MULTIBLOCK_READ_COUNT is set to eight. During a full table scan, each I/O operation will read only 32 KB. If DB_FILE_MULTIBLOCK_READ_COUNT is reset to 16, performance will almost double because twice as much data can be read by each I/O operation.

DB_WRITERS = In Oracle 8.0 and up this parameter has been de-supported and replaced by 2 other parameters namely DB_WRITER_PROCESSES and DBWR_IO_SLAVES.

DB_BLOCK_LRU_LATCHES - DBWR_IO_SLAVES and DB_WRITER_PROCESSES The DB_WRITER_PROCESSES parameter supported on Windows NT/Windows 2000?

The Oracle8i documentation and [BUG:925955] incorrectly state that this parameter is not supported on Windows NT/2000. Multiple DBWR processes are mainly used to simulate asynchronous I/O when the operating system does not support it. Since Windows NT and Windows 2000 use asynchronous I/O by default, using multiple DBWR processes may not necessarily improve performance. Increasing this parameter is also likely to have minimal effect on single-CPU systems. Increasing this parameter could, in fact, reduce performance on systems where the CPU's are already over burdened. In cases where the main performance bottleneck is that a single DBWR process cannot keep up with the work load, then increasing the value for DB_WRITER_PROCESSES may improve performance.

When increasing DB_WRITER_PROCESSES it may also be necessary to increase the DB_BLOCK_LRU_LATCHES parameter, as each DBWR process requires an LRU latch.

Reference for setting DB_BLOCK_LRU_LATCHES parameter Default value: 1/2 the # of CPU's

MAX Value: Min 1, Max about 6 * max(# cpu's,# processor groups)

1)Oracle has found that a optimal value for this would be 2 X # CPU's and would recommend testing at this level.

2)Also setting this parameter to a multiple of # CPU's is important for Oracle to properly allocate and utilize working sets. 3)This value is hard coded in 9i

**IMPORTANT**

Increasing this parameter greater than 2 X # CPU's may have a negative impact on the system. FREQUENTLY ASKED QUESTIONS

You have just upgraded to 8.0 or 8.1 and have found that there are 2 new parameters regarding DBWR. You are wondering what the differences are and which one you should use.

DBWR_IO_SLAVES

In Oracle7, the multiple DBWR processes were simple slave processes; i.e., unable to perform async I/O calls. In Oracle80, true asynchronous I/O is provided to the slave processes, if available. This feature is implemented via the init.ora parameter

dbwr_io_slaves. With dbwr_io_slaves, there is still a master DBWR process and its slave processes. This feature is very similar to the db_writers in Oracle7, except the IO slaves are now capable of asynchronous I/O on systems that provide native async I/O, thus allowing for much better throughput as slaves are not blocked after the I/O call. I/O slaves for DBWR are allocated

immediately following database open when the first I/O request is made. DB_WRITER_PROCESSES

Multiple database writers is implemented via the init.ora parameter db_writer_processes. This feature was enabled in Oracle8.0.4, and allows true database writers; i.e., no master-slave relationship. With Oracle8 db_writer_processes, each writer process is assigned to a LRU latch set. Thus, it is recommended to set db_writer_processes equal to the number of LRU latches

(db_block_lru_latches) and not exceed the number of CPUs on the system. For example, if db_writer_processes was set to four and db_lru_latches=4, then each writer process will manage its corresponding set.

Things to know and watch out for....

1. Multiple DBWRs and DBWR IO slaves cannot coexist. If both are enabled, then the following error message is produced: ksdwra("Cannot start multiple dbwrs when using I/O slaves.\n"); Moreover, if both parameters are enabled, dbwr_io_slaves will take precedence.

2. The number of DBWRs cannot exceed the number of db_block_lru_latches. If it does, then the number of DBWRs will be minimized to equal the number of db_block_lru_latches and the following message is produced in the alert.log during startup: ("Cannot start more dbwrs than db_block_lru_latches.\n"); However, the number of lru latches can exceed the number of DBWRs.

3. dbwr_io_slaves are not restricted to the db_block_lru_latches; i.e., dbwr_io_slaves >= db_block_lru_latches. Should you use DB_WRITER_PROCESSES or DBWR_IO_SLAVES?

Although both implementations of DBWR processes may be beneficial, the general rule, on which option to use, depends on the following :

1) the amount write activity;

2) the number of CPUs (the number of CPUs is also indirectly related to the number LRU latch sets); 3) the size of the buffer cache;

4) the availability of asynchronous I/O (from the OS).

There is NOT a definite answer to this question but here are some considerations to have when making your choice. Please note that it is recommended to try BOTH (not simultaneously) against your system to determine which best fits the environment. -- If the buffer cache is very large (100,000 buffers and up) and the application is write intensive, then db_writer_processes may be beneficial. Note, the number of writer processes should not exceed the number of CPUs.

-- If the application is not very write intensive (or even a DSS system) and async I/O is available, then consider a single DBWR writer process; If async I/O is not available then use dbwr_io_slaves.

-- If the system is a uniprocessor(1 CPU) then implement may want to use dbwr_io_slaves.

Implementing db_io_slaves or db_writer_processes comes with some overhead cost. Multiple writer processes and IO slaves are advanced features, meant for high IO throughput. Implement this feature only if the database environment requires such IO throughput. In some cases, it may be acceptable to disable I/O slaves and run with a single DBWR process.

Other Ways to Tune DBWR Processes

It can be easily seen that reducing buffer operations will be a direct benefit to DBWR and also help overall database performance. Buffer operations can be reduced by:

1) using dedicated temporary tablespaces 2) direct sort reads

3) direct Sqlloads

4) performing direct exports.

In addition, keeping a high buffer cache hit ratio will be extremely beneficial not only to the response time of applications, but the DBWR as well.

DML_LOCKS = Concurrent Users * 10

JOB_QUEUE_PROCESSES - To use DBMS_JOB

LOG_BUFFER = Size in Bytes for Redo Logs Buffer. Increasing the size of this parameter can increase I/O efficiency, when the transactions are long and/or numerous. Generally = 512K. Size over 1 MB is not good.

LOG_CHECKPOINT_INTERVAL = This value forces checkpoints to occur only at log file switches. LOG_ENTRY_PREBUILD_THRESOLD= 2048. On multiple CPU machines only.

LOG_SIMULTANEOUS_COPIES = 2 * cpu_count

LOG_SMALL_ENTRY_MAX_SIZE = 50 . On multiple CPU machines only. OPEN_CURSORS = Give it a big value, at least 100

OPTIMIZER_FEATURES_ENABLED - Don't miss out on features OPTIMIZER_INDEX_COST_ADJ - Force index use

OPTIMIZER_MODE - Choose, Rule, First_Rows or All_Rows (More information HERE)

PARALLEL_MAX_SERVERS = This value specifies the minimum number of query servers that will be active on the instance. There are system resources involved in starting a query server, and having the query server started and waiting for requests will accelerate

processing. The recommended value is:

2 * max_degree * number_of_concurrent_users.

If the value for the statistics, "Servers Busy" is high, increase PARALLEL_MAX_SERVERS

PARALLEL_MIN_SERVERS = This parameter sets the number of query server processes that are started when the instance starts, thus eliminating the performance penalties of frequent query server process startups and shutdowns

PRE_PAGE_SGA = true

PROCESSES = Increase this parameter, default is 50

ROLLBACK_SEGMENTS = The general rule is to put # of concurrent users / 4. Create at least 2 tablespaces for rollbacks. SHARED_POOL_RESERVED_SIZE - Memory held for future big PL/SQL or ORA-error

SHARED_POOL_SIZE - Memory allocated for data dictionary and SQL & PL/SQL and reusable objects (library cache and the data dictionary cache). Increment it if CACHE hit ratio < 95%. 40% of the total SGA size. If we need to duplicate the SHARED_POOL_SIZE also we need to increment MAXDATAFILES. You can query v$sgastat to show the available free memory. This will tell you memory is being wasted. As an example:

select pool, name, bytes/1024/1024 "Size in MB" from v$sgastat

where name='free memory'; You should see output similar to the following:

NAME Size in MB Free memory 39.6002884

What this return would tell you is that there is 39 M of free memory in the shared pool, which would mean that the shared pool is being under utilized. If the shared pool was 70 M, over half of it would be under utilized. This memory could be allocated elsewhere.

*DATA DICTIONARY cache miss ratio (Goal > 90%, increase SHARED_POOL) Contains:

Preparsed database procedures Preparsed database triggers

Recently parsed SQL & PL/SQL requests

This is the memory allocated for the library and data dictionary cache select sum(gets) Gets, sum(getmisses) Misses,

(1 - (sum(getmisses) / (sum(gets) + sum(getmisses))))*100 HitRatio

from v$rowcache;

* El HIT RATIO del SHARED_POOL_SIZE (LIBRARY CACHE hit ratio) debe ser superior al 99%

column namespace heading "Library Object" column gets format 9,999,999 heading "Gets"

column gethitratio format 999.99 heading "Get Hit%" column pins format 9,999,999 heading "Pins" column pinhitratio format 999.99 heading "Pin Hit%" column reloads format 99,999 heading "Reloads" column invalidations format 99,999 heading "Invalid" column db format a10

select namespace, gets, gethitratio*100 gethitratio,

pins, pinhitratio*100 pinhitratio, RELOADS, INVALIDATIONS from v$librarycache

/

If all Get Hit% (gethitratio in the view) except for indexes are greater than 80-90 percent, this is the desired state; the value for indexes is low because of the few accesses of that type of object. Notice that the Pin Hit% should ve also greater than 90% (except for indexes). The other goals of tuning this area are to reduce reloads to as small a value as possible (this is done by proper sizing and pinning) and to reduce invalidations. Invalidations happen when for one reason or another an object becomes unusable.

Guideline: In a system where there is no flushing increase the shared pool size in 20% increments to reduce reloads and invalidations and increase hit ratios.

select sum(pins) Executions, sum(pinhits) Execution_Hits, ((sum(pinhits) / sum(pins)) * 100) phitrat,

sum(reloads) Misses,

((sum(pins) / (sum(pins) + sum(reloads))) * 100) RELOAD_hitrat from v$librarycache;

* How much memory is left for SHARED_POOL_SIZE col value for 999,999,999,999 heading "Shared Pool Size" col bytes for 999,999,999,999 heading "Free Bytes"

select to_number(v$parameter.value) value, v$sgastat.bytes, (v$sgastat.bytes/v$parameter.value)*100 "Percent Free" from v$sgastat, v$parameter

where v$sgastat.name = 'free memory' and v$parameter .name = 'shared_pool_size'; A better query:

select sum(ksmchsiz) Bytes, ksmchcls Status from SYS.x$ksmsp

group by ksmchcls;

If there is free memory then there is no need to increase this parameter. * Identifying objects reloaded into the SHARED POOL again and again

select substr(owner,1,10) owner,substr(name,1,25) name, substr(type,1,15) type, loads, sharable_mem from v$db_object_cache

-- where owner not in ('SYS','SYSTEM') and

where loads > 1 and type in ('PACKAGE','PACKAGE BODY','FUNCTION','PROCEDURE') order by loads DESC;

* Large Objects NOT 'pinned' in Shared Pool

may causing a problem, execute the following query:

select name, sharable_mem from v$db_object_cache where sharable_mem > 10000

and (type = 'PACKAGE' or type = 'PACKAGE BODY' or type = 'FUNCTION' or type = 'PROCEDURE')

and kept = 'NO';

SORT_AREA_SIZE = Indica la cantidad de memoria reservada para sorts en bytes. Deberia haber pocos valores (especialmente en disco), si no es asi, entonces incrementar SORT_AREA_SIZE. Para saber si debo incrementar o no el parametro uso:

select name, value from v$sysstat where name like '%sort%';

SORT_AREA_RETAINED_SIZE = is the size that the SORT_AREA_SIZE is actually reduced to once the sort is complete. This parameter should be set less than or equal to SORT_AREA_SIZE. If we are going to make a big import or use several batch processes, increase it. Just use ALTER SESSION (for batch) or ALTER SYSTEM DEFERRED (for imports). Remember to put back to its original value. Sorts (memory) tells you the number of sorts done entirely in memory. Sorts (disk) indicates the number of sorts that required access to disk. The recommended setting for this parameter and SORT_AREA_SIZE is 65K-1MB.

SORT_DIRECT_WRITES = Setting SORT_DIRECT_WRITES to true allows Oracle to bypass the buffer cache for the writing of sort runs to the temporary tablespace. This can improve the performance by a factor of three or more. Be sure to also set

SORT_WRITE_BUFFERS=8 and SORT_WRITE_BUFFER_SIZE=65536. SORT_DIRECT_WRITES, SORT_WRITE_BUFFERS and SORT_WRITE_BUFFER_SIZE are obsoleted in 8.1.3. The same considerations for SORT_AREA_SIZE apply to

SORT_DIRECT_WRITES when using the parallel query option. Under Oracle8i, sorts always use direct writes and automatically configure the number and size of the direct write buffers.

Oracle Performance Checklist

As a consultant, I follow a standard procedure when I come into a new shop with a database that I have never seen before. My goal is to quickly identify and correct performance problems. Here is a summary of the things that I look at first:

1 - Install STATSPACK first, and get hourly snaps working.

2 - Get an SQL access report, a spreport during peak times, and statspack_alert.sql output. 3 - Look for silver bullets:

- partial schema stats - missing indexes

-optimizer_index_cost_adj=15 # 10-15 for OLTP systems, 50 for DW # This adjusts the optimizer to favor index access -optimizer_index_caching=85 (depending on RAM for index caching, around 85)

- optimizer_mode=first_rows (for OLTP) # More information, HERE

- hash_area_size too small (too many nested loop joins)

- parallel_automatic_tuning=TRUE When set to "on", this parameter parallelizes full-table scans . Because parallel full-table scans are very fast, the CBO will give a higher cost to index access and be friendlier to full-table scans.

6 - Look for Buffer Busy Waits resulting from table/index freelist shortages 7 - See if large-table full-table scans can be removed with well-placed indexes

8 - If tables are low volatility, seek an MV that can pre-join/pre-aggregate common queries. Turn-on automatic query rewrite 9 - Look for non-reentrant SQL - (literals values inside SQL from v$sql) - If so, set cursor_sharing=force

10 - Monitor over time - The ongoing STATSPACK reports should show any new performance problems.

INSTANCE TUNING

1) Library Cache Hit Ratio:

In the most basic terms, the library cache is a memory structure that holds the parsed (ie. already examined to determine syntax correctness, security privileges, execution plan, etc.) versions of SQL statements that have been executed at least once. As new SQL statements arrive, older SQL statements will be pushed from the memory structure to provide space for the new statements. If the older SQL statements need to be re-executed, they will now have to be re-parsed. Also, a SQL statement that is not exactly the same as an already parsed statement (including even capitalization) will be reparsed even though it may perform the exact same operation. Parsing is an expensive operation, so the objective is to make the memory structure large enough to hold enough parsed SQL statements to avoid a large percentage of re-parsing.

Target: 99% or greater.

Value: SELECT (1 - SUM(reloads)/SUM(pins)) FROM v$librarycache;

Correction: Increase the SHARED_POOL_SIZE parameter (in bytes) in the INIT.ORA file. 2) Dictionary Cache Hit Ratio:

The dictionary cache is the memory structure that holds the most recently used contents of ORACLE's data dictionary, such as security privileges, table structures, column data types, etc. This data dictionary information is necessary for each and every parsing of a SQL

statement. Recalling that memory is around 300 times faster than disk, it is needless to say that performance is improved by holding enough data dictionary information in memory to significantly minimize disk accesses.

Target: 90%

Value: SELECT (1 - SUM(getmisses)/SUM(gets)) FROM v$rowcache;

Correction: Increase the SHARED_POOL_SIZE parameter (in bytes) in the INIT.ORA file. 3) Buffer Cache Hit Ratio:

The buffer cache is the memory structure that holds the most recently used blocks read from disk, whether table, index, or other segment type. As new data is read into the buffer cache, data that hasn't been recently used is pushed out. Again recalling that memory is

approximately 300 times faster than disk, the objective is to hold enough data in memory to minimize disk accesses. Note that data read from tables through the use of indexes is held in the buffer cache much longer than data read via full-table scans.

Target: 90% (although some shops find 80% or even 70% acceptable) Value:

SELECT value FROM v$sysstat WHERE name = 'consistent gets'; SELECT value FROM v$sysstat WHERE name = 'db block gets'; SELECT value FROM v$sysstat WHERE name = 'physical reads';

Buffer cache hit ratio = 1 - physical reads/(consistent gets + db block gets) Correction: Increase the DB_CACHE_SIZE parameter in the INIT.ORA file. Other notes:

- Compare the values for "table scans" and "table access by rowid" in the v$sysstat table to gain general insight into whether additional indexing is needed. Tuning specific applications via indexing will increase the "table access by rowid" value (ie. tables read through the use of indexes) and decrease the "table scans" values. This effect tends to improve the buffer cache hit ratio since a smaller volume of data is read into the buffer cache from disk, so less previously cached data is pushed out. (See the article on application tuning for more details

regarding indexing.)

- A low buffer cache hit ratio can very quickly lead to an I/O bound situation, as more reads are required per period of time to provide the requested data. When the reads/time period exceed the workload supported by the disk subsystem, exponential performance degradations can occur. (Please see the section on Operating System tuning.)

- Since the buffer cache will typically be the largest memory structure allocated in the ORACLE instance, it is the structure most likely to contribute to O/S paging. If the buffer cache is sized such that the hit ratio is 90%, but excessive paging occurs at this setting, performance may be better if the buffer cache were sized to achieve an 85% hit ratio. Careful analysis is necessary to balance the buffer cache hit ratio with the O/S paging rate.

4) Sort Area Hit Ratio:

Sorts that are too large to be performed in memory are written to disk. Once again, memory is about 300 times faster than disk, so for instances where a large volume of sorting occurs (such as decision support systems or data warehouses), sorting on disk can degrade performance. The objective, of course, is to allow a significant percentage of sorts to occur in memory.

Target: 90% (although many shops find 80% or less acceptable) Value:

SELECT value FROM v$sysstat WHERE name = 'sorts (memory)'; SELECT value FROM v$sysstat WHERE name = 'sorts (disk)'; Sort area hit ratio = 1 - disk sorts/(memory sorts + disk sorts);

Correction: Increase the SORT_AREA_SIZE parameter (in bytes) in the INIT.ORA file. Other notes:

- With release 7.3 and above, setting the SORT_DIRECT_WRITES = TRUE initialization parameter causes sorts to disk to bypass the buffer cache, thus improving the buffer cache hit ratio.

- As with buffer cache hit ratio, examine the values for "table scans" and "table access by rowid" in the v$sysstat table to determine if additional indexing is needed. In some cases, the optimizer will choose to retrieve the rows in the correct order by using the index, thus avoiding a sort. In other cases, retrieval by index rather than full-table scan tends to collect a smaller quantity of rows to be sorted, thus increasing the probability that the sort can occur in memory, which also tends to improve the sort area hit ratio.

- Also, as with buffer cache hit ratio, sort area size (if very large) can contribute to O/S paging. In general, sorting on disk should be favored over excessive paging, as paging effects all memory structures (ORACLE and non-ORACLE) while sorting on disk only effects sorts

performed by the ORACLE instance. 5) Redo Log Space Requests:

Redo logs (and archive logs if the ORACLE instance is run in ARCHIVELOG mode) are transaction logs involving a variety of structures. The redo log buffer is a memory structure into which changes are recorded as they are applied to blocks in the buffer cache (including data, index, rollback segments, etc.). Committed changes are synchronously flushed to redo log file members on disk, while uncommited changes are asynchronously written to redo log files. (This approach makes perfect sense on inspection. If an instance crash occurs, commited changes are already written to the redo logs on disk and are applied during instance recovery. Uncommited changes in the redo log buffer not yet written to disk are lost, and any uncommited changes that have been written to disk are rolled-back during instance recovery.) A

session performing an update and an immediate commit will not return until the committed change has been written to the redo log buffer and flushed to the redo log files on disk. Redo log groups are written to in a round-robin manner. When the mirrored members of a redo log group become full, a log switch occurs, thus archiving one member of the redo log group (if ARCHIVELOG mode is TRUE), then clearing the members of that redo log group. Note that a checkpoint also occurs at least on each redo log switch. In most basic form, the redo log buffer should be large enough that no waits for available space in the memory structure occur while changes are written to redo log files. The redo log file size should be large enough that the redo log buffer does not fill during a redo log switch. Finally, there should be enough redo log groups that the archiving and clearing of filled redo logs does not cause waits for redo log switches, thus causing the redo log buffer to fill. The inability to write changes to the redo log buffer because it is full is reported as redo log space requests in the v$sysstat table.

Target: 0

Value: SELECT value FROM v$sysstat WHERE name = 'redo log space requests'; Correction:

- Increase the LOG_BUFFER parameter (in bytes) in the INIT.ORA file. - Increase the redo log size.

- Increase the number of redo log groups. Other notes:

- The default configuration of small redo log size and two redo log groups is seldom sufficient. Between 4 and 10 groups typically yields adequate results, depending on the particular archive log destination (whether a single disk, RAID array, or tape). Size will be very dependent upon the specific application characteristics and throughput requirements, and can range from less than 10 Mb to 500 Mb or greater.

- Since redo log sizes and groups can be changed without a shutdown/restart of the instance, increasing the redo log size and number of groups is typically the best area to start tuning for reduction of redo log space requests. If increasing the redo log size and number of groups appears to have little impact on redo log space requests, then increase the LOG_BUFFER initialization parameter.

6) Redo Buffer Latch Miss Ratio:

One of the two types of memory structure locking mechanisms used by an ORACLE instance is the latch. A latch is a locking mechanism that is implemented entirely within the executable code of the instance (as opposed to an enqueue, see below). Latch mechanisms most likely to suffer from contention involve requests to write data into the redo log buffer. To serve the intended purpose, writes to the redo log buffer must be serialized (ie. one process locks the buffer, writes to it, then unlocks it, a second process locks, writes, and unlocks, etc., while other processes wait for their chance to acquire these same locks). There are four different groupings applicable to redo buffer latches: redo allocation latches and redo copy latches, each with immediate and willing-to-wait priorities. Redo allocation latches are acquired by small redo entries (having an entry size smaller than or equal to the LOG_SMALL_ENTRY_MAX_SIZE initialization parameter) and utilize only a single CPU's resources for execution. Redo copy latches are requested by larger redo entries (entry size larger than the

LOG_SMALL_ENTRY_MAX_SIZE), and take advantage of multiple CPU's for execution. Recall from above that committed changes are synchronously written to redo logs on disk: these entries require an immediate latch of the appropriate type. Uncommitted changes are asynchronously written to redo log files, thus they attempt to acquire a willing-to-wait latch of the appropriate type. Below, each category of redo buffer latch will be considered seperately.

- Redo allocation immediate and willing-to-wait latches: Target: 1% or less

Value (immediate):

SELECT a.immediate_misses/(a.immediate_gets + a.immediate_misses + 0.000001) FROM v$latch a, v$latchname b

WHERE b.name = 'redo allocation' AND b.latch# = a.latch# ; Value (willing-to-wait):

SELECT a.misses/(a.gets + 0.000001) FROM v$latch a, v$latchname b

WHERE b.name = 'redo allocation' AND b.latch# = a.latch# ;

Correction: Decrease the LOG_SMALL_ENTRY_MAX_SIZE parameter in the INIT.ORA file. Other notes:

- By making the max size for a redo allocation latch smaller, more redo log buffer writes qualify for a redo copy latch instead, thus better utilizing multiple CPU's for the redo log buffer writes. Even though memory structure manipulation times are measured in nanoseconds, a larger write still takes longer than a smaller write. If the size for remaining writes done via redo allocation latches is small enough, they can be completed with little or no redo allocation latch contention.

- On a single CPU node, all log buffer writes are done via redo allocation latches. If log buffer latches are a significant bottleneck,

performance can benefit from additional CPU's (thus enabling redo copy latches) even if the CPU utilization is not an O/S level bottleneck. - In the SELECT statements above, an extremely small value is added to the divisor to eliminate potential divide-by-zero errors.

- Redo copy immediate and willing-to-wait latches: Target: 1% or less

Value (immediate):

SELECT a.immediate_misses/(a.immediate_gets + a.immediate_misses + 0.000001) FROM v$latch a, v$latchname b

WHERE b.name = 'redo copy' AND b.latch# = a.latch# ; Value (willing-to-wait):

SELECT a.misses/(a.gets + 0.000001) FROM v$latch a, v$latchname b

WHERE b.name = 'redo copy' AND b.latch# = a.latch# ;

Correction: Increase the LOG_SIMULTANEOUS_COPIES parameter in the INIT.ORA file. Other Notes:

- Essentially, this initialization parameter is the number of redo copy latches available. It defaults to the number of CPU's (assuming a multiple CPU node). Oracle Corporation recommends setting it as large as 2 times the number of CPU's on the particular node, although quite a bit of experimentation may be required to get the value adjusted in a suitable manner for any particular instance's workload. Depending on CPU capability and utilization, it may be beneficial to set this initialization parameter smaller or larger than 2 X # CPU's. - Recall that the assignment of log buffer writes to either redo allocation latches or redo copy latches is controlled by the maximum log buffer write size allowed for a redo allocation latch, and is specified in the LOG_SMALL_ENTRY_MAX_SIZE initialization parameter. Recall also that redo copy latches apply only to multiple CPU hosts.

7) Enqueue Waits:

The second of the two types of memory structure locking mechanisms used by an ORACLE instance is the enqueue. As opposed to a latch, an enqueue is a lock implemented through the use of an operating system call, rather than entirely within the Instance's executable code. Exactly what operations use locks via enqueues is not made sufficiently clear from any Oracle documentation (or at least none that the author has seen), but the fact that enqueues waits do degrade instance performance is reasonably clear. Luckily, tuning enqueues is very straight-forward.

Target: 0

Value: SELECT value FROM v$sysstat WHERE name = 'enqueue waits';

8) Checkpoint Contention:

A checkpoint is the process of flushing all changed data blocks (table, index, rollback segments, etc.) held in the buffer cache to their corresponding datafiles on disk. This process occurs during each redo log switch, each time the number of database blocks specified in the LOG_CHECKPOINT_INTERVAL initialization parameter is reached, and each time the number of seconds specified in the

LOG_CHECKPOINT_TIMEOUT is reached. (Also, checkpoints occur during a NORMAL or IMMEDIATE SHUTDOWN, when a tablespace is placed in BACKUP mode, or when an ALTER SYSTEM CHECKPOINT is manually issued, but these occurrences are usually outside the scope of normal daytime operation.) Depending on the number of changed blocks in the buffer cache, a checkpoint can take considerable time to complete. Since this process is essentially done asynchronously, user sessions performing work will typically not have to wait for a

checkpoint to complete. However checkpoints can effect overall system performance since they are fairly resource intensive operations, even though they occur in the background. Checkpoints are, of course, absolutely necessary, but it is quite possible for one checkpoint to begin (because of LOG_CHECKPOINT_INTERVAL or LOG_CHECKPOINT_TIMEOUT settings) and partially complete, then be rolled-back because another checkpoint was issued (perhaps because of a redo log switch). It is desirable to avoid this checkpoint contention because it wastes considerable resources that can be used by other processes. Checkpointing statistics are readily available in the v$sysstat table, and the contention is fairly simple to determine.

Target: 1 or less Value:

SELECT value FROM v$sysstat WHERE name = 'background checkpoints started'; SELECT value FROM v$sysstat WHERE name = 'background checkpoints completed'; Checkpoints rolled-back = checkpoints started - checkpoints completed;

Correction:

- Increase the LOG_CHECKPOINT_TIMEOUT parameter (in seconds) in the INIT.ORA file, or set it to 0 to disable time-based checkpointing. If time-based checkpointing is not disabled, set it to checkpoint once per hour or more.

- Increase the LOG_CHECKPOINT_INTERVAL parameter (in db blocks) in the INIT.ORA file, or set it to an arbitrarily large value so that change-based checkpoints will only occur during a redo log switch.

- Examine the redo log size and the resulting frequency of redo log switches.

Other notes: Note that regardless of the checkpoint frequency, no data is lost in the event of an instance crash. All changes are recorded to the redo logs and would be applied during instance recovery on the next startup, so checkpoint frequency will impact the time required for instance recovery. Presented below is a typical scenario:

- Set the LOG_CHECKPOINT_INTERVAL to an arbitrarily large value, set the LOG_CHECKPOINT_TIMEOUT to 2 hours, and size the redo logs so that a log switch will normally occur once per hour. During times of heavy OLTP activity, a change-based log switch will occur approximately once per hour, and no time-based checkpoints will occur. During periods of light OLTP activity, a time-based checkpoint will occur at least once every two hours, regardless of the number of changes. Setting the LOG_CHECKPOINT_INTERVAL arbitrarily large allows change-based checkpoint frequency to be adjusted during periods of heavy use by re-sizing the redo logs on-line rather than adjusting the initialization parameter and performing an instance shutdown/restart.

9) Rollback Segment Contention:

Rollback segments are the structures into which undo information for uncommited changes are temporarily stored. This behavior serves two purposes. First, a session can remove a change that was just issued by simply issuing a ROLLBACK rather than a COMMIT. Second, read consistency is established because a long-running SELECT statement against a table that is constantly being updated (for example) will get data that is consistent with the start time of the SELECT statement by reading undo information from the appropriate rollback segment. (Otherwise, the answer returned by the long-running SELECT would vary depending on whether that particular block was read before the update occurred, or after.) Rollback segments become a bottleneck when there are not enough to handle the load of concurrent activity, in

which case, sessions will wait for write access to an available rollback segment. Some waits for rollback segment data blocks or header blocks (usually header blocks) will always occur, so criteria for tuning is to limit the waits to a very small percentage of the total number of all data blocks requested. Note that rollback segments function exactly like table segments or index segments: they are cached in the buffer cache, and periodically checkpointed to disk.

Target: 1% or less Value:

Rollback waits = SELECT max(count) FROM v$waitstat

WHERE class IN ('system undo header', 'system undo block','undo header', 'undo block') GROUP BY class;

Block gets = SELECT sum(value) FROM v$sysstat WHERE name IN ('consistent gets','db block gets'); Rollback segment contention ratio = rollback waits / block gets

Correction: Create additional rollback segments. 10) Freelist contention:

In each table, index, or other segment type, the first one or more blocks contain one or more freelists. The freelist(s) identify the blocks in that segment that have free space available and can accept more data. Any INSERT, UPDATE, or DELETE activity will cause the freelist(s) to be accessed. Change activity with a high level of concurrency may cause waits to access to these freelist(s). This is seldom a problem in decision support systems or data warehouses (where updates are processed as nightly single-session batch jobs, for example), but can become a bottleneck with OLTP systems supporting large numbers of users. Unfortunately, there are no initialization parameters or other instance-wide settings to correct freelist contention: this must be corrected on a table by table basis by re-creating the table with additional freelists and/or by modifying the PCT_USED parameter. (Please see the article on storage management.) However, freelist contention can be measured at the instance level. Some freelist waits will always occur; the objective is to limit the freelist waits to a small percentage of the total blocks requested.

Target: 1% or less Value:

Freelist waits = SELECT count FROM v$waitstat WHERE class = 'free list';

Block gets = SELECT sum(value) FROM v$sysstat WHERE name IN ('consistent gets','db block gets'); Freelist contention ratio = Freelist waits / block gets

Correction: No method for instance-level correction. Please see the article on storage management. 11) Oracle Session hogs

If the complaint of poor performance is current, then the connected sessions are one of the first things to check to see which users are impacting the system in undesirable ways. There are a couple of different avenues to take here. First, you can get an idea of the percentage that each session is/has taken up with respect to I/O. One rule of thumb is that if any session is currently consuming 50% or more of the total I/O, then that session and its SQL need to be investigated further to determine what activity it is engaged in. If you are a DBA that is just concerned with physical I/O, then the physpctio.sql query will provide the information you need:

This script queries the sys.v_$statname, sys.v_$sesstat, sys.v_$session, and sys.v_$bgprocess views.

select sid, username,

round(100 * total_user_io/total_io,2) tot_io_pct from (select b.sid sid,nvl(b.username,p.name) username, sum(value) total_user_io

sys.v_$session b, sys.v_$bgprocess p where a.statistic#=c.statistic# and

p.paddr (+) = b.paddr and b.sid=a.sid and

c.name in ('physical reads', 'physical writes', 'physical writes direct', 'physical reads direct', 'physical writes direct (lob)', 'physical reads direct (lob)') group by b.sid, nvl(b.username,p.name)), (select sum(value) total_io

from sys.v_$statname c, sys.v_$sesstat a where a.statistic#=c.statistic# and

c.name in ('physical reads', 'physical writes', 'physical writes direct', 'physical reads direct', 'physical writes direct (lob)', 'physical reads direct (lob)')) order by 3 desc;

Regardless of which query you use, the output might resemble something like the following:

SID USERNAME TOT_IO_PCT

---- --- ---9 USR1 71.26 20 SYS 15.76 5 SMON 7.11 2 DBWR 4.28 12 SYS 1.42 6 RECO .12 7 SNP0 .01 10 SNP3 .01 11 SNP4 .01 8 SNP1 .01 1 PMON 0 3 ARCH 0 4 LGWR 0

In the above example, a DBA would be prudent to examine the USR1 session to see what SQL calls they are making. You can see that the above queries are excellent weapons that you can use to quickly pinpoint problem I/O sessions.

Application and SQL Tuning

select substr(name,1,20), substr(value,1,40), isdefault, isses_modifiable, issys_modifiable from v$parameter

where issys_modifiable <> 'FALSE' or isses_modifiable <> 'FALSE' order by name;

* The SQL sentences must be the same in order to re-use them in memory. * Size of Database

compute sum of bytes on report break on report

Select tablespace_name, sum(bytes) bytes From dba_data_files

Group by tablespace_name;

* How much Space is Left ?

compute sum of bytes on report

Select tablespace_name, sum(bytes) bytes From dba_free_space

Group by tablespace_name;

* Memory Values.

select substr(name,1,35) name, substr(value,1,25) value from v$parameter

where name in ('db_cache_size','db_block_size','shared_pool_size','sort_area_size');

* Identify the SQL responsible for the most BUFFER HITS and/or DISK READS. If I want to see what is on SQL AREA:

SELECT SUBSTR(sql_text,1,80) Text, disk_reads, buffer_gets, executions

FROM v$sqlarea

WHERE executions > 0 AND buffer_gets > 100000 and DISK_READS > 100000

ORDER BY (DISK_READS * 100) + BUFFER_GETS desc;

The column BUFFER_GETS is the total number of times the SQL statement read a database block from the buffer cache in the SGA. Since almost every SQL operation passes through the buffer cache, this value represents the best metric for determining how much work is being performed. It is not perfect, as there are many direct-read operations in Oracle that completely bypass the buffer cache. So, supplementing this information, the column DISK_READS is the total number times the SQL statement read database blocks from disk, either to satisfy a logical read or to satisfy a direct-read. Thus, the formula:

(DISK_READS * 100) + BUFFER_GETS

is a very adequate metric of the amount of work being performed by a SQL statement. The weighting factor of 100 is completely arbitrary, but it reflects the fact that DISK_READS are inherently more expensive than BUFFER_GETS to shared memory.

Patterns to look for

DISK_READS close to or equal to BUFFER_GETS This indicates that most (if not all) of the gets or logical reads of database blocks are becoming physical reads against the disk drives. This generally indicates a full-table scan, which is usually not desirable but which usually can be quite easy to fix.

* Finding the top 25 SQL declare top25 number; text1 varchar2(4000); x number; len1 number; cursor c1 is

select buffer_gets, substr(sql_text,1,4000) from v$sqlarea

order by buffer_gets desc; begin dbms_output.put_line('Gets'||' '||'Text'); dbms_output.put_line('---'|| ' '||'---'); open c1; for i in 1..25 loop

fetch c1 into top25, text1;

dbms_output.put_line(rpad(to_char(top25),9)|| ' '||substr(text1,1,66));

len1:=length(text1); x:=66;

while len1 > x-1 loop

dbms_output.put_line('" '||substr(text1,x,66)); x:=x+66; end loop; end loop; end; /

* Displays the porcentage of SQL executed that did NOT incur an expensive hard parse. So a low number may indicate a literal SQL or other sharing problem.

Ratio success is dependant on your development environment. OLTP should be 90 percent.

select 100 * (1-a.hard_parses/b.executions) noparse_hitratio

from (select value hard_parses from v$sysstat

where name = 'parse count (hard)' ) a ,(select value executions

from v$sysstat

where name = 'execute count') b;

* HIT RATIO BY SESSION:

column HitRatio format 999.99

select substr(Username,1,15) username, Consistent_Gets,

Block_Gets,Physical_Reads,100*(Consistent_Gets+Block_Gets-Physical_Reads)/(Consistent_Gets+Block_Gets) HitRatio from V$SESSION, V$SESS_IO

and (Consistent_Gets+Block_Gets)>0

and Username is not null;

* IO PER DATAFILE:

select substr(DF.Name,1,40) File_Name,

FS.Phyblkrd Blocks_Read,

FS.Phyblkwrt Blocks_Written,

FS.Phyblkrd+FS.Phyblkwrt Total_IOs from V$FILESTAT FS, V$DATAFILE DF

where DF.File#=FS.File#

order by FS.Phyblkrd+FS.Phyblkwrt desc;

* Schema's Report

select substr(username,1,10) "Username", created "Created", substr(granted_role,1,25) "Roles",

substr(default_tablespace,1,15) "Default TS", substr(temporary_tablespace,1,15) "Temporary TS" from sys.dba_users, sys.dba_role_privs

where username = grantee (+) order by username;

* Free space on TABLESPACES:

select substr(a.tablespace_name,1,10) tablespace, round(sum(a.total1)/1024/1024, 1) Total, round(sum(a.total1)/1024/1024, round(sum(a.sum1)/1024/1024, 1) used, round(sum(a.sum1)/1024/1024, 1) Free, round(sum(a.sum1)/1024/1024,1)*100/round(sum(a.total1)/1024/1024,1) porciento_fr, round(sum(a.maxb)/1024/1024, 1) Largest, max(a.cnt) Fragment

from (select tablespace_name, 0 total1, sum(bytes) sum1, max(bytes) MAXB,count(bytes) cnt

from dba_free_space group by tablespace_name union

select tablespace_name, sum(bytes) total1, 0, 0, 0 from dba_data_files

group by tablespace_name) a group by a.tablespace_name;

* Segments whose next extent can't fit

select substr(owner,1,10) owner, substr(segment_name,1,40) segment_name, substr(segment_type,1,10) segment_type, next_extent from dba_segments

where next_extent>

(select sum(bytes) from dba_free_space

* Find Tables/Indexes fragmented into > 15 pieces

Select substr(owner,1,8) owner, substr(segment_name,1,42) segment_name, segment_type, extents From dba_segments

Where extents > 15;

* COALESCING FREE SPACE = Los distintos bloques libres (chunks) que sean adjuntos se pueden juntar en uno mas grande. Inspecciono con:

select file_id, block_id, blocks, bytes from dba_free_space where tablespace_name = 'xxx' order by 1,2;

Esto me devuelve una lista de resultados. Si file_id de 2 filas es igual y el block_id + blocks = Block_id de la fila siguiente, entonces los puedo juntar.

Se hace con ALTER TABLESPACE XX COALESCE;

* Quick Script to coalesce all the tablespaces tablespaces

set echo off pages 0 trimsp off feed off spool coalesce.sql

select 'alter tablespace '||tablespace_name||' coalesce;' from sys.dba_tablespaces

where tablespace_name not in ('TEMP','ROLLBACK'); spool off

@coalesce.sql

host rm coalesce.sql

* Information about a Table

Select Table_Name, Initial_Extent, Next_Extent, Pct_Free, Pct_Increase

From dba_tables

Where Table_Name = upper('&Table_name');

* Information about an Index:

Select Index_name, Initial_Extent, Next_Extent From Dba_indexes

Where Index_Name = upper('&Index_name');

* Fixing Table Fragmentation

Example: CUSTOMER Table is fragmented Currently in 22 Extents of 1M each.

(Can be found by querying DBA_EXTENTS) CREATE TABLE CUSTOMER1

TABLESPACE NEW

STORAGE (INITIAL 23M NEXT 2M PCTINCREASE 0) AS SELECT * FROM CUSTOMER;

DROP TABLE CUSTOMER;

RENAME CUSTOMER1 TO CUSTOMER; (Create all necessary privileges,grants, etc.)

* PINS and UNPIN objects:

execute dbms_shared_pool_keep('object_name','P o R o Q');

Use 'P' for procedure (or funcion), 'R' for trigger and 'Q' for sequence. Previously I should run the package dbmspool.sql y prvtpool.plb located on $ORACLE_HOME/rdbms/admin as sys or internal and grant execute on dbms_shared_pool.

exec dbms_shared_pool.unkeep('SCOTT.TEMP','P');

If you want to have a table in memory, add the CACHE word at the end of the creation script. You can also use the /*+ cache(table) */ hint. To Load the code automatically on each startup::

1- Create the following Trigger

create or replace trigger pin_packs after startup on database

begin

--You can interrogate the v$db_object_cache view to see the most frequently used packages -- Application-specific packages

-- Oracle-supplied software packages dbms_shared_pool.keep('DBMS_ALERT'); dbms_shared_pool.keep('DBMS_DDL'); dbms_shared_pool.keep('DBMS_DESCRIBE'); dbms_shared_pool.keep('DBMS_LOCK'); dbms_shared_pool.keep('DBMS_OUTPUT'); dbms_shared_pool.keep('DBMS_PIPE'); dbms_shared_pool.keep('DBMS_SESSION'); dbms_shared_pool.keep('DBMS_STANDARD'); dbms_shared_pool.keep('DBMS_UTILITY'); dbms_shared_pool.keep('STANDARD'); -- Son usados estos?

dbms_shared_pool.keep('DBMS_SYS_SQL'); dbms_shared_pool.keep('DBMS_SQL'); dbms_shared_pool.keep('DBMS_JOB'); end;

2- Run the following Script to check pinned/unpinned packages

SELECT substr(owner,1,10)||'.'||substr(name,1,35) "Object Name", ' Type: '||substr(type,1,12)|| ' size: '||sharable_mem || ' execs: '||executions|| ' loads: '||loads|| ' Kept: '||kept FROM v$db_object_cache

WHERE type in ('TRIGGER','PROCEDURE','PACKAGE BODY','PACKAGE') -- AND executions > 0

ORDER BY executions desc, loads desc,

* ROW_CHAINING

* To find out chained rows

ANALYZE TABLE TEST ESTIMATE STATISTICS;

Then from DBA_TABLES,

SELECT (CHAIN_CNT / NUM_ROWS) * 100 FROM DBA_TABLES WHERE TABLE_NAME = upper('&Table_name');

This will give us the chained rows as a percentage of the total number of rows in that table. If this percentage is high near 5% and the row doe not contain LONG or similar datatype or the row can be contained inside one single data block then PCTFREE should definitely be decreased.

Distribution of disk I/O

Locate the logfiles on their own disks and on the fastest-writing disks. Oracle writes to the redo logs frequently and sequentially. If there are other files on the same disk, the disks heads have to move between the end of the logfile and the other files. This movement increases the disk seek time, causing unnecessary delays in redo log I/O operations, and resulting in poor performance.

If easy manageability is your goal, use the UNIX file system.

If you are an experienced Unix and Oracle administrator, raw logical volumes can give you some performance benefits. Don't put Logfiles and archived logfiles on the same disk as your datafiles

Allocate one disk for the User Data Tablespace.

Place Rollback, Index, and System Tablespaces on separate disks. 2 DISKS:

1- exec, index, redo logs, export files, control files

2- data, rollback segments, temp, archive log files, control files 3 DISKS

Disk 1: SYSTEM tablespace, control file, redo log

Disk 2: INDEX tablespace, control file, redo log, ROLLBACK tablespace Disk 3: DATA tablespace, control file, redo log

or

Disk 1: SYSTEM tablespace, control file, redo log Disk 2: INDEX tablespace, control file, redo log

Disk 3: DATA tablespace, control file, redo log, ROLLBACK tablespace 4 DISKS

1- exec, redo logs, export files, control files 2- data, temp, control files

3- indexes, control files

5 DISKS

1- exec, redo logs, system tablespace, control files 2- data, temp, control files

3- indexes, control files

4- rollback segments, export, control files 5- archive, control files

ANALYZE and DBMS_STATS Package

Oracle Corporation strongly recommends that you use the DBMS_STATS package rather than ANALYZE to collect optimizer statistics. That package lets you collect statistics in parallel, collect global statistics for partitioned objects, and fine tune your statistics collection in other ways. Further, the cost-based optimizer will eventually use only statistics that have been collected by DBMS_STATS

However, you must use the ANALYZE statement rather than DBMS_STATS for statistics collection not related to the cost-based optimizer, such as:

* To use the VALIDATE or LIST CHAINED ROWS clauses * To collect information on freelist blocks

The DBMS_STATS package can gather statistics on indexes, tables, columns, and partitions, as well as statistics on all schema objects in a schema or database. The statistics-gathering operations can run either serially or in parallel (DATABASE/SCHEMA/TABLE only)

Previous to 8i, you would be using the ANALYZE ... methods. However 8i onwards, using ANALYZE for this purpose is not recommended because of various restrictions; for example:

1. ANALYZE always runs serially.

2. ANALYZE calculates global statistics for partitioned tables and indexes instead of gathering them directly. This can lead to inaccuracies for some statistics, such as the number of distinct values.

3. ANALYZE cannot overwrite or delete some of the values of statistics that were gathered by DBMS_STATS. 4. Most importantly, in the future, ANALYZE will not collect statistics needed by the cost-based optimiser.

ANALYZE can gather additional information that is not used by the optimiser, such as information about chained rows and the structural integrity of indexes, tables, and clusters. DBMS_STATS does not gather this information.

- In 10g statistics get gathered automatically DML Monitoring

Used by dbms_stats to identify objects with "stale" statistics - On by default in 10g, not in 9i

alter table <table_name> monitoring;

- Tracked in [DBA|ALL|USER]_TAB_MODIFICATIONS

In Oracle 10g, Oracle automatically gathers index statistics whenever the index is created or rebuilt. Example:

EXEC DBMS_STATS.gather_table_stats(USER, 'LOOKUP', cascade => TRUE); execute dbms_stats.gather_table_stats (ownname => 'SCOTT' , tabname => 'DEPT' , partname=> null , estimate_percent => 20 , degree => 5 , cascade => true

, options => 'GATHER AUTO'); execute dbms_stats.gather_schema_stats (ownname => 'SCOTT' , estimate_percent => 10 , degree => 5 , cascade => true); execute dbms_stats.gather_database_stats (estimate_percent => 20 , degree => 5 , cascade => true);

There are several values for the options parameter that we need to know about: - gather - re-analyzes the whole schema.

- gather empty - Only analyze tables that have no existing statistics.

- gather stale - Only re-analyze tables with more than 10% modifications (inserts, updates, deletes). The table should be in monitor status first.

- gather auto - This will re-analyze objects which currently have no statistics and objects with stale statistics.The table should be in monitor status first.

Using gather auto is like combining gather stale and gather empty .

Note that both gather stale and gather auto require monitoring. If you issue the "alter table xxx monitoring" command, Oracle tracks changed tables with the dba_tab_modifications view. Below we see that the exact number of inserts, updates and deletes are tracked since the last analysis of statistics.

The most interesting of these options is the gather stale option. Because all statistics will become stale quickly in a robust OLTP database, we must remember the rule for gather stale is > 10% row change (based on num_rows at statistics collection time).

Hence, almost every table except read-only tables will be re-analyzed with the gather stale option. Hence, the gather stale option is best for systems that are largely read-only. For example, if only 5% of the database tables get significant updates, then only 5% of the tables will be re-analyzed with the "gather stale" option.

let Oracle decide whether or not new index statistics are needed. The DEGREE Option

Note that you can also parallelize the collection of statistics because the CBO does full-table and full-index scans. When you set degree=x, Oracle will invoke parallel query slave processes to speed up table access. Degree is usually about equal to the number of CPUs, minus 1 (for the OPQ query coordinator).

In Oracle 10g, set DEGREE to DBMS_STATS.AUTO_DEGREE to let Oracle select the appropriate degree of parallelism. Force Statistics to a Table

You can use the following sentence to force statistics to a Table:

exec dbms_stats.set_table_stats( user, 'EMP', numrows => 1000000, numblks => 300000 );

STATISTICS FOR THE DATA DICTIONARY

New in Oracle Database 10g is the ability to gather statistics for the data dictionary. The objective is to enhance the performance of queries. There are two basic types of dictionary base tables.

The statistics for normal base tables are gathered using GATHER_DICTIONARY STATISTICS. They may also be gathered using GATHER_SCHEMA_STATS for the SYS schema. Oracle recommends gathering these statistics at a similar frequency as your other database objects.

Statistics for fixed objects (the V$ views on the X$ tables) are gathered using the GATHER_FIXED_OBJECT_STATS procedure. The initial collection of these statistics is normally sufficient. Repeat only if workload characteristics have changed dramatically. The SYSDBA privilege or ANALYZE ANY DICTIONARY and ANALYZE ANY privileges are required to execute the procedures for gathering data dictionary statistics.

SQL Source - Dynamic Method

DECLARE

sql_stmt VARCHAR2(1024); BEGIN

FOR tab_rec IN (SELECT owner,table_name

FROM all_tables WHERE owner like UPPER('&1') ) LOOP sql_stmt := 'BEGIN dbms_stats.gather_table_stats (ownname => :1, tabname => :2, partname => null, estimate_percent => 10, degree => 3 ,

cascade => true); END;' ;

EXECUTE IMMEDIATE sql_stmt USING tab_rec.owner, tab_rec.table_name ; END LOOP;

END; /

* Some Dictionary Views

DBA_TABLES -> owner, table_name, num_rows, blocks, emptiy_blocks, avg_space, chain_cnt, avg_row_len, sample_size, last_analyzed DBA_INDEXES -> owner, INDEX_name, leaf_blocks, distinct_keys, avg_leaf_blocks_per_key, avg_data_blocks_per_key.

Also, DBA_PART_COL_STATISTICS y DBA_TAB_COL_STATISTICS More examples:

CREATE OR REPLACE PROCEDURE analyze_any_schema ( p_inOwner IN all_users.username%TYPE) IS

BEGIN

FOR v_tabs IN (SELECT owner, table_name FROM all_tables

WHERE owner = p_inOwner AND temporary <> 'Y') LOOP

DBMS_OUTPUT.put_line ('EXEC DBMS_STATS.gather_table_stats('''||v_tabs.owner|| ''','''||v_tabs.table_name||''',NULL,1);' );

BEGIN

DBMS_STATS.gather_table_stats(v_tabs.owner,v_tabs.table_name,NULL,1); DBMS_OUTPUT.put_line ('Analyzed '||v_tabs.owner||'.'||table_name||'... '); EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.put_line ('Exception on analysis of '||v_tabs.table_name||'!'); DBMS_OUTPUT.put_line (SUBSTR(SQLERRM,1,255));

END; END LOOP; END;

/

CREATE OR REPLACE Procedure DB_Maintenance_Weekly is sql_stmt varchar2(1024);

v_sess_user varchar2(30); BEGIN

select sys_context('USERENV','SESSION_USER') into v_sess_user from dual ;

--Analyze all Tables

FOR tab_rec IN (SELECT table_name FROM all_tables

WHERE owner = v_sess_user

and table_name not like 'TEMP_%') LOOP sql_stmt := 'BEGIN dbms_stats.gather_table_stats (ownname => :1, tabname => :2, partname => null, estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, degree => 3 ,

cascade => true); END;' ;

EXECUTE IMMEDIATE sql_stmt USING v_sess_user, tab_rec.table_name ; END LOOP;

EXCEPTION

WHEN NO_DATA_FOUND THEN NULL ;

end; /

Analyze Options

- Estimate over all rows

DBMS_UTILITY.ANALYZE_SCHEMA('userid', 'COMPUTE'); - Estimate 20% of all rows for a specific Schema

DBMS_UTILITY.ANALYZE_SCHEMA('userid', 'ESTIMATE',NULL,20); - Estimate 20% of a table

DBMS_UTILITY.ANALYZE_SCHEMA('TABLE' , 'schema', 't_name', 'ESTIMATE',null,20); or

ANALYZE TABLE table ESTIMATE STATISTICS sample 20 percent; - Estimate 20% of an index

DBMS_UTILITY.ANALYZE_SCHEMA('INDEX' , 'schema', 'i_name', 'COMPUTE'; - Estimate 1000 rows of all the tables for a schema

DBMS_UTILITY.ANALYZE_SCHEMA ('userid', 'ESTIMATE', 100000); or

ANALYZE TABLE table ESTIMATE STATISTICS sample 5000 rows; - Delete all stats

DBMS_UTILITY.ANALYZE_SCHEMA ('userid', 'DELETE'); or

ANALYZE TABLE table DELETE STATISTICS;

Working with UNDO Parameters

When you are working with UNDO Tablespace, there are two important things to consider: The size of the UNDO tablespace

The UNDO_RETENTION parameter

To get information of your current settings you can use the following query:

set serveroutput on DECLARE tsn VARCHAR2(40); tss NUMBER(10); aex BOOLEAN; unr NUMBER(5);

rgt BOOLEAN; retval BOOLEAN;

v_undo_size NUMBER(10); BEGIN

select sum(a.bytes)/1024/1024 into v_undo_size

from v$datafile a, v$tablespace b, dba_tablespaces c where c.contents = 'UNDO'

and c.status = 'ONLINE'

and b.name = c.tablespace_name and a.ts# = b.ts#;

retval := dbms_undo_adv.undo_info(tsn, tss, aex, unr, rgt); dbms_output.put_line('UNDO Tablespace is : ' || tsn);

dbms_output.put_line('UNDO Tablespace size is : ' || TO_CHAR(v_undo_size) || ' MB'); IF aex THEN

dbms_output.put_line('Undo Autoextend is set to : TRUE'); ELSE

dbms_output.put_line('Undo Autoextend is set to : FALSE'); END IF;

dbms_output.put_line('Undo Retention is : ' || TO_CHAR(unr)); IF rgt THEN

dbms_output.put_line('Undo Guarantee is set to : TRUE'); ELSE

dbms_output.put_line('Undo Guarantee is set to : FALSE'); END IF;

END; /

UNDO Tablespace is : UNDOTBS1 UNDO Tablespace size is : 925 MB Undo Autoextend is set to : TRUE Undo Retention is : 900 Undo Guarantee is set to : FALSE

You can choose to allocate a specific size for the UNDO tablespace and then set the UNDO_RETENTION parameter to an optimal value according to the UNDO size and the database activity. If your disk space is limited and you do not want to allocate more space than necessary to the UNDO tablespace, this is the way to proceed. If you are not limited by disk space, then it would be better to choose the UNDO_RETENTION time that is best for you (for FLASHBACK, etc.). Allocate the appropriate size to the UNDO tablespace according to the database activity.

This tip help you get the information you need whatever the method you choose.

set serverout on size 1000000 set feedback off

set heading off set lines 132 declare