UNIT IV

Oracle Tuning and Troubleshooting

Oracle performance tuning methodology

Performance tuning is the process of administering a database to improve performance.

Performance tuning in Oracle databases includes optimizing SQL statements and query execution
plans so that the requests can be completed more efficiently.

The organization of a database the SQL statements used to access that data, determine the level of
resources needed to respond to queries when an application communicates with the database.

Problems like poorly optimized SQL statements force the database to work much harder to retrieve
information (resulting in more system resources being used). The more system resources that are
used the greater the chance it will affect the experience of users on connected applications.

In an enterprise, users will report a slow application to a database administrator who will then
attempt to pinpoint the root cause of the problem. The administrator analyzes statement code and
searches for database bottlenecks. The process is extensive, as the administrator has to diagnose
the root cause of the problem before it can be addressed.

Monitoring Performance in Oracle Databases: Response Time and

Throughput
When performance tuning an Oracle database there are two metrics that are useful to measure:

 Response time – How long the database takes to complete a request.

 System throughput – The number of processes completed in a period of time.

High response time means that an application is providing a slow user experience.

On the other hand, low system throughput means that the database only has the resources to
manage a small number of tasks in a short time period. An administrator has to be able to know
how they are trying to improve performance before tuning.

The Two Types of Tuning: Proactive Monitoring and Bottleneck Elimination

Now that you know what performance tuning is, it’s important to look at the two main models of
tuning:

 Proactive Monitoring
 Bottleneck Elimination

Database administrators use these two models to manage performance issues and keep
applications functioning at a high level.

Proactive Monitoring

Proactive monitoring is the process of monitoring a database to discover and address performance
issues early rather than simply reacting when there is a problem. With proactive monitoring,
administrators will periodically review databases to identify the signs of performance
degradation.
The idea behind proactive monitoring is to catch issues and inefficiencies before they develop
into greater problems further down the line. Some common issues database administrators look
out for include:

 Database wait events – A high number of events can negatively affect database
performance. Finding obstructive sessions and killing them can prevent performance
degradation.
 Load average – Monitoring the load average of a server will tell you if server resources
are functioning as normal. A high load average can result in slow database performance.
 Database sessions – Monitoring the number of active sessions can stop you from
reaching the maximum (which will prevent you from being able to open new sessions).

However, monitoring proactively does carry some risk. Any changes an administrator makes can
result in a decrease in performance for the database. Administrators can mitigate the risks by
being cautious before making new changes.

Bottleneck Elimination

Bottlenecks are one of the most common causes of poor performance. Bottlenecks block requests
from reaching the destination and increase the response time of applications. Bottlenecks can be
caused by a range of factors from badly coded SQL statements and high resource usage.

Bottleneck elimination is more of a reactive process than proactive monitoring. An administrator

identifies a bottleneck and then finds a way to fix it. Fixing a bottleneck is a complex process and
depends on what the root cause is (and whether it is internal or external). Recoding SQL
statements is one solution for fixing internal bottlenecks, which should be addressed first.

How to Performance Tune

Performance tuning an Oracle database is a very complex subject because there are so many
different factors that can affect database performance. To keep things simple, we’re going to look
at some basic ways you can optimize performance.

1. Identify High-Cost Queries

The first step to tuning SQL code is to identify high-cost queries that consume excessive
resources. Rather than optimizing every line of code it is more efficient to focus on the most
widely-used SQL statements and have the largest database / I/O footprint.

One easy way to identify high-cost queries is to use Oracle database monitoring tools . One useful
tool is Oracle SQL Analyze, which can identify resource-intensive SQL statements. Tuning these
statements will give you the greatest return on your time investment.

2. Minimize the workload (Use Indexes!)

You can make the same query in many different ways so it is advantageous to write code that
minimizes the workload as much as possible. If you only need a snapshot of data from a table it
makes no sense processing thousands of rows you don’t need (all you’re doing is wasting system
resources!) A full table scan takes up more database resources and I/O.

To eliminate the stress of sustaining a large workload you can use indexes to access small sets of
rows rather than processing the entire database at once. Use indexes in those scenarios where a
column is regularly queried.
3. Use Stateful Connections with Applications

Sometimes the cause of poor performance doesn’t come from code but because the connection
keeps dropping between the application and the database. If your application isn’t configured
correctly then it could form a connect to the database to access a table and then drop the
connection once it has the information it needs.

Dropping the connection after accessing the table is terrible for performance. Instead, try to keep
a stateful connection so that the application stays connected to the database at all times.
Maintaining the connection will stop system resources from being wasted each time the
application interacts with the database.

4. Collect and Store Optimizer Statistics

Optimizer statistics are data that describe a database and its objects. These statistics are used by
the database to choose the best execution plan for SQL statements. Regularly collecting and
storing optimizer statistics on database objects is essential for maintaining efficiency.

Collecting optimizer statistics makes sure that the database has accurate information on table
contents. If the data is inaccurate then the database can choose a poor execution plan, which will
affect the end-user experience. Oracle databases can automatically collect optimizer statistics or
you can do so manually with the DBMS_STATS package.

Oracle alert and trace files

The trace file and alert log contain information about errors.

Alert Logs: Alert logs contain important information about error messages and exceptions that
occur during database operations.
Each Oracle Database for Windows instance has one alert log; information is appended to the file
each time you start the instance. All threads can write to the alert log.

For example, when automatic archiving of redo logs is halted because no disk space is available, a
message is placed in the alert log. The alert log is the first place to check if something goes wrong
with the database and the cause is not immediately obvious.

The alert log is named alert_SID.log and is found in the ADR directory specified by the
parameter DIAGNOSTIC_DEST in the initialization parameter file. Alert logs must be deleted or
archived periodically.

The alert log is a chronological log of messages and errors, and includes the following items:
 All internal errors (ORA-00600), block corruption errors (ORA-01578), and deadlock errors
(ORA-00060) that occur
 Administrative operations, such as some CREATE, ALTER, and DROP statements
and STARTUP, SHUTDOWN, and ARCHIVELOG statements
 Messages and errors relating to the functions of shared server and dispatcher processes
 Errors occurring during the automatic refresh of a materialized view
 The values of all initialization parameters that had nondefault values at the time the database
and instance start
Oracle Database uses the alert log to record these operations as an alternative to displaying the
information on an operator's console (although some systems also display information on the
console). If an operation is successful, a "completed" message is written in the alert log, along with
a timestamp.

The alert log is maintained as both an XML-formatted file and a text-formatted file. You can view
either format of the alert log with any text editor or you can use the ADRCI utility to view the
XML-formatted version of the file with the XML tags stripped.

Trace Files
Oracle Database for Windows background threads use trace files to record occurrences and
exceptions of database operations, and errors.

Background thread trace files are created and stored in the Automatic Diagnostic Repository (ADR)
directory specified by the parameter DIAGNOSTIC_DEST in the initialization parameter file.
Oracle Database creates a different trace file for each foreground and background thread. The name
of the trace file contains the name of the thread, followed by the extension ".trc". The following are
examples of foreground trace file names:
 ops_ora_5804.trc
 ops_ora_4160.trc

The following are the examples of the background trace file names:

 ops_pmon_1556.trc
 ops_mmon_3768.trc
 ops_lgwr_2356.trc
 ops_dbw0_132.trc
Trace files are also created for user threads and stored in the ADR directory specified by the
parameter DIAGNOSTIC_DEST in the initialization parameter file. Trace files for user threads
have the form [Link], where xxxxx is a 5-digit number indicating the Windows thread ID.

Each server and background process can write to an associated trace file. When an internal error is
detected by a process, it dumps information about the error to its trace file. Some of the information
written to a trace file is intended for the database administrator, and other information is for Oracle
Support Services. Trace file information is also used to tune applications and instances.

Here are some best practices for working with Alert Log and Trace Files:
1. Regular monitoring: The Alert Log should be monitored regularly to detect and troubleshoot
issues. Oracle recommends checking the Alert
Log at least once a day.

2. Alert Log rotation: To avoid the Alert Log from becoming too large, you can configure
automatic rotation of the log file. This is done through the Automatic Diagnostic Repository (ADR)
settings.

3. Trace file location: Trace files are typically located in the diagnostic destination directory, in a
subdirectory called trace. You can also use the SQL TRACE facility to generate trace files for
specific sessions or SQL statements.

4. Analyzing Trace Files: Trace files are generated in Oracle trace file format, which can be
analyzed using the TKPROF utility or other third-party tools.
5. Oracle Support: Oracle Support can request to review Alert Log and Trace Files to diagnose and
troubleshoot issues. It is recommended to upload these files to Oracle Support to accelerate issue
resolution.

In summary, the Alert Log and Trace Files are important diagnostic tools that should be monitored
regularly to detect and troubleshoot issues with the Oracle Database instance.

Tuning the Shared Pool

Oracle Database uses the shared pool to cache many different types of data. Cached data includes
the textual and executable forms of PL/SQL blocks and SQL statements, dictionary cache data,
result cache data, and other data.

The main components of the shared pool include:

 Library cache
The library cache stores the executable (parsed or compiled) form of recently referenced
SQL and PL/SQL code.
 Data dictionary cache
The data dictionary cache stores data referenced from the data dictionary.
 Server result cache (depending on the configuration)
The server result cache is an optional cache that stores query and PL/SQL function results
within the shared pool

Many of the caches in the shared pool—including the library cache and the dictionary cache—
automatically increase or decrease in size, as needed.

1) Tuning Library Cache and Dictionary Cache

To tune the Library Cache:

1. Query the V$LIBRARYCACHE dynamic performance view:

SELECT NAMESPACE, GETS, GETHITRATIO, PINS, PINHITRATIO,RELOADS,

INVALIDATIONS FROM V$LIBRARYCACHE;

2. Calculate the Library Cache Hit ratio:

SELECT SUM(PINS - RELOADS)*100/SUM(PINS) AS "Hit Ratio" FROM

V$LIBRARYCACHE;

The Library Cache Hit Ratio is an important parameter to evaluate the use of Library Cache. The
result should be around 99.9 percent.

The Library Cache stores parsed SQL statements, execution plans, PL/SQL blocks, and Java
classes, ready to be executed. The application code shared in the Library Cache can be easily reused
by different database sessions. The reuse of a piece of code already in the cache is called a Library
Cache Hit. A Library Cache Miss occurs when the execution of a piece of code cannot find the
already parsed code in the Library Cache.

The Library Cache Hit is also called a soft parse; the Library Cache Miss is called a hard parse.
Data Dictionary Cache Concepts

Information stored in the data dictionary cache includes:

 Usernames
 Segment information
 Profile data
 Tablespace information
 Sequence numbers

The data dictionary cache also stores descriptive information, or metadata, about schema objects.
Oracle Database uses this metadata when parsing SQL cursors or during the compilation of
PL/SQL programs.

2) Measuring shared-pool hit ratio

The hit ratios for each component of the shared pool is calculated based on performance
information from the V$ performance views in the data dictionary.
Hits on the Row Cache
Activity in the row cache is monitored by a dynamic performance view called V$ROWCACHE.
This dynamic performance view stores statistics about row cache performance. As with other
dynamic performance views discussed this far, the V$ROWCACHE view is accessed only by those
users granted the select any table object privilege and the owner of the dynamic performance view,
SYS. Some examples of users with the select any table privilege are those users with
the sysdba privilege granted to them.
The ratio required for determining performance on this cache can be derived by executing the
following query under the appropriate user:
SELECT (SUM(getmisses)/SUM(gets))*100 dc_hit_ratio
FROM v$rowcache;
Hits on the Library Cache
Determining the library cache miss ratio from the V$LIBRARYCACHE dynamic performance
view is similar in formula to that of every other hit ratio calculation, namely (MISSES/TOTAL
REQUESTS)*100. The value from the MISSES column in this equation equals the sum of the
number of reloads for all rows in V$LIBRARYCACHE and the total requests is equal to the sum
of the number of pins for all rows in the same dynamic performance view.
SELECT (SUM(reloads)/SUM(pins))*100 lc_hit_ratio
FROM v$librarycache;

3) Sizing the Shared Pool

To improve the performance of the dictionary cache or the library cache, add memory to the shared pool.
This is done by increasing SHARED_POOL_SIZE.
The most effective way to add memory to the shared pool without adversely impacting the database
instance or the rest of the processes on the machine (if any) is to add real memory to the machine
hosting Oracle. Memory is added by placing memory chips or cards to the actual hardware of the
system, then allocating that new memory to the Oracle shared pool.
Another aspect of appropriate sizing for the shared pool with the SHARED_POOL_SIZE
parameter involves the relationship between the contents of the shared pool. The two elements of
the shared pool—the row cache and the library cache—eliminate the oldest data when the cache is
full and space is needed to store new information corresponding to their various roles.

4) Pinning Objects in the Shared Pool

At times, performance may be crucial for a certain block of code. For example, an organization
may have a mission-critical data feed that needs to happen in a short period of time. In some older
versions of Oracle, there may also be problems with fitting large PL/SQL blocks into shared
memory in order to even parse and execute the code. Whatever the reason, it may be necessary to
place objects into the library cache in such a way that the shared SQL will not be paged out of the
shared pool. This method is known as pinning the shared SQL in the shared pool.
The process for pinning shared SQL into the shared pool is accomplished as follows. There are
several stored procedures provided by Oracle as part of the software release that can be used for
the purposes of manipulating the shared pool. The package name containing these stored procedures
is DBMS_SHARED_POOL. This package must be created before using it. From within Server
Manager, execute the following SQL scripts located in the rdbms/admin subdirectory of the Oracle
software home directory: [Link] and [Link].
Svrmgr> @dbmspool
Package created.
Svrmgr> @prvtpool
Package body created.
Svrmgr>
Once the DBMS_SHARED_POOL package is created in the database, the DBA can then pin
objects into the shared pool. But first, the DBA should rid the shared pool of all shared SQL
information and all cached dictionary objects. This "flush" temporarily frees all space in the shared
pool for new SQL statements to come in and parse; however, it temporarily reduces performance
for other statements running on the system The statement for flushing the shared pool that the DBA
must use is alter system flush shared pool.
ALTER SYSTEM
FLUSH SHARED POOL;

Buffer Cache
 The largest component of the SGA is usually the database buffer cache, which is the part of
the SGA that holds copies of blocks of data read from the Oracle datafiles on disk.
 The size of the database buffer cache is controlled by the [Link] parameter
DB_BLOCK_BUFFERS, which specifies the number of database blocks that will be
contained in the database buffer cache.
 Since this is expressed as database blocks, the size of the database buffer cache is the value
of DB_BLOCK_BUFFERS multiplied by the DB_BLOCK_SIZE.
  For example, if a database has been created with a DB_BLOCK_SIZE of 8192 (8K) and
DB_BLOCK_BUFFERS is set to 1000, then the database buffer cache component of the
SGA would be 8192 × 1000 or 8,192,000 bytes.
 When a user process needs data from the database, Oracle first checks to see if the required
block is already in the database buffer cache.
 If it is, it is retrieved from the cache and a disk I/O operation is avoided. Oracle maintains
an LRU (least recently used) list of blocks in the cache; when a block is read, its identifier
is moved to the end of the list, making it the last block to be purged from the cache.

Dirty Blocks
Blocks which have been updated in memory(buffer cache) but not yet flushed back/written to
disk is dirty blocks.
DBWR or DBWn processes write the dirty blocks to the disk.
Tuning the Buffer Cache
Following are the possible solution in case of Buffer Cache:
1. Check the full table scan and avoid them because increase in value of DB_BLOCK_BUFFERS
not help always.
Use index based query which will reduce the full table scan.
2. Avoid useless sorting of data, it also has effect DB Cache the sort area is reached its limit then it
use disk sorting which data sent by the utilization of DB cache buffer to Disk for sorting.
SORT_AREA_SIZE and SORT_AREA_RETAINED_SIZE has impact on it.
3. Buffer cache size can be increased with help of using the information by db cache advisory.
4. You different type of buffer cache if you know that this table is small in size and can be
cached/no-cache in memory:
Set the following parameter to keep the objects in it:

SQL> show parameter buffer_pool

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
buffer_pool_keep string
buffer_pool_recycle string
5. Increase the DBWR or DBWn writer process.

Redo Log Buffer

 The redo log buffer is an area of memory within the SGA that holds information about
changes to the database, called redo log entries.
 These entries are used if database recovery is necessary, and they contain information
required to reconstruct changes made by INSERT, UPDATE, DELETE, CREATE, DROP,
or ALTER statements.
  The redo log buffer is circular—that is, when it is full, entries are written to it from the
beginning.
 The LGWR process writes the contents of the redo log buffer to the active redo log file on
disk.
 The size of the redo log buffer is determined by the [Link] parameter LOG_BUFFER,
which is expressed in bytes.
 The default value of this size is four times the DB_BLOCK_SIZE, but it is often desirable
to set this value higher, particularly if there are many or long transactions generating high
rates of redo generation.

Redo Log Buffer Tuning

Redo Log Buffer and Performance Issues related to redo log buffer
1. Inadequate Redo Log Buffer Size: If the redo log buffer size is too small, it can cause frequent
log buffer space waits. The redo log buffer should be large enough to accommodate the changes
made during transactions to avoid such waits and to prevent the LGWR from being invoked too
frequently.

2. Frequent Log Switches: Redo log files should be sized appropriately to prevent too frequent log
switches, which can cause waits and affect performance. If the alert log or the dynamic performance
view V$SYSTEM_EVENT shows 'log file switch (checkpoint incomplete)' events, it indicates that
the log files may be too small.

3. High Redo Buffer Allocation Retries: A high value of 'redo buffer allocation retries' from the
V$SYSSTAT view indicates that the user processes often had to wait for space in the redo log
buffer. This usually occurs when the LGWR process cannot write the contents of the redo log buffer
to the disk fast enough, which could be due to an undersized redo log buffer or I/O system
performance issues.

4. Inefficient Disk I/O Performance: Inefficient disk I/O performance can result in the redo entries
not being written quickly enough from the redo log buffer to the online redo log files. This can cause
'log file parallel write' events where LGWR waits for I/O operations to complete.

5. Inadequate Redo Log Files: Having too few redo log groups can lead to increased checkpointing
and log file switches. More groups and members can offer better multiplexing and reduce the
probability of 'log file switch (checkpoint incomplete)' events.

6. Excessive 'redo wastage': This issue arises when transactions generate redo that is not written to
disk because of a log switch. Redo wastage can be viewed in the V$SYSSTAT dynamic
performance view. If this is high, consider resizing the log files.

7. Unoptimized Redo Log File Configuration: If redo log files are not mirrored or multiplexed, any
failure could lead to a loss of data. Multiplexing redo log files can prevent this and increase system
reliability.

8. Inefficient use of NOLOGGING option: The NOLOGGING option allows certain bulk operations
to bypass redo logging, improving performance. However, misuse of this option can lead to data
recovery problems.
Oracle database configuration and I/O issues
Configuring an Oracle database involves several aspects to ensure optimal performance and
mitigate I/O issues. Here are some considerations:

Storage Configuration:

Use separate disks for different types of files: The Oracle database consists of various file types
(data files, redo log files, control files). Placing these files on different disks or disk groups can
distribute I/O and improve performance.

RAID configurations: Depending on your performance needs and budget, RAID configurations
(like RAID 10 for both redundancy and performance) can significantly enhance I/O operations.

File Placement:

Locate redo log files on the fastest storage available to ensure quick writes.

Spread data files across different disks or disk groups to distribute I/O load.

Block Size:

Set an appropriate block size (DB_BLOCK_SIZE) considering the application's workload. Smaller
blocks can be beneficial for OLTP systems, while larger blocks can improve data warehouse
performance.

Buffer Cache:

Properly size the buffer cache (DB_CACHE_SIZE) to hold frequently accessed data blocks in
memory, reducing disk I/O.

Monitor cache hit ratios (BUFFER_CACHE_HIT_RATIO) to ensure efficient cache usage.

Redo Log Tuning:

Tune the redo log buffer (LOG_BUFFER) size to handle the volume of changes efficiently.

Ensure that the redo log files are appropriately sized and located for optimal write performance.

I/O Monitoring and Diagnostics:

Use Oracle's diagnostic tools (AWR, ADDM, and Statspack) to monitor I/O performance and
identify bottlenecks.

Analyze wait events (V$WAITSTAT) and I/O-related views (V$IOSTAT_*) to pinpoint

problematic areas.

Database Parameters:

Adjust DB_WRITER_PROCESSES, DB_WRITER_IO_SLAVES, and DB_WRITER_MAX_IO

to manage the database writer processes efficiently and balance I/O.

Tune parameters like DB_FILE_MULTIBLOCK_READ_COUNT to optimize multiblock reads

during I/O operations.
Indexing and Table Design:

Properly index tables to reduce I/O by facilitating quicker access to data.

Implement partitioning or clustering to organize data physically, reducing I/O overhead.

Regular Maintenance:

Regularly perform maintenance tasks like statistics gathering, index rebuilding, and data
reorganization to prevent fragmentation and optimize I/O operations.

Upgrade and Patches:

Keep the database up-to-date with Oracle patches and updates. Sometimes, new versions or patches
might include performance improvements related to I/O.

Addressing I/O issues in an Oracle database involves a comprehensive approach that includes not
just configuration settings, but also monitoring, diagnostics, and periodic optimizations. Fine-
tuning these elements based on the specific workload and system requirements can significantly
improve database performance and mitigate I/O-related challenges.

Rollback Segment Tunning

Every update,insert, or delete statement executed on a table produces rollback segment entries,thus
making rollback segments an important and heavily used resource.

1) Using V$ Views to Monitor Rollback Segment Performance

Contention for the rollback segment resource is indicated by contention in memory for the buffers
containing blocks of rollback segment information.
The dynamic performance view V$WAITSTAT can be used to detect this type of contention. This
view is available to users with select any table privileges, such as SYS and SYSTEM. To let another
user access this view, the DBA can connect to the database as SYS and grant select privileges on
this view to a named user or role.
There are four types of blocks associated with rollback segments that are important to monitor in
conjunction with detecting contention on rollback segments. They are the system undo
header, system undo block, undo header, and undo block.
The following list shows the types of blocks associated with rollback segments whose usage should
be monitored by the DBA:

SYSTEM undo header Number of times user processes waited for buffers
containing SYSTEM rollback segment header blocks
 SYSTEM undo block Number of times user processes waited for buffers containing
SYSTEM rollback segment nonheader blocks

Undo header Number of times user processes waited for buffers containing
non-SYSTEM rollback segment header blocks

Undo block Number of times user processes waited for buffers containing
non-SYSTEM rollback segment nonheader blocks

To calculate the ratio of rollback buffer waits to total number of data requests, the DBA can use
the following SQL statement:
SELECT [Link], ([Link]/SUM([Link]))*100 ratio
FROM v$waitstat w, v$sysstat s
WHERE [Link] IN (‘system undo header’,‘system undo block’,
‘undo header’,‘undo block’)
AND [Link] IN (‘db block gets’,‘consistent gets’);

If a value in the ratio column for any block class in the output of this query is greater than 1, then
there is an issue with contention for that block class.
The DBA can reduce contention for rollback segments by adding to the number of rollback
segments available to the Oracle instance.
The appropriate number of rollback segments for a database instance corresponds to the average
number of concurrent transactions against the database.
To do so, the DBA can apply the rule of four.

Use the rule of four to determine the appropriate number of rollback segments for your Oracle instance--
divide concurrent transactions by 4. If result is less than 4 + 4, round up to nearest multiple of 4. Don’t
use more than 50 rollback segments.

2) Modifying rollback-segment configuration

Rollback segments are configured to be a certain size during database creation. Other features about
rollback segments handled during configuration are the number and size of extents and
the optimal size of the rollback segment.
The V$ROLLSTAT view can be queried for the name, number of extents, optimal size setting in
bytes, and current size in bytes for the rollback segment named. V$ROLLNAME was used to derive
the associated "undo," or rollback segment number, or USN, for the rollback segment named. This
number is required to derive any rollback segment information from V$ROLLSTAT.

SELECT [Link], [Link], [Link], [Link]

FROM v$rollname rn, v$rollstat rs
WHERE [Link] = ‘rbs_name’
AND [Link] = [Link];
For example, say a rollback segment’s optimal size is 5M and each extent comprises 1M of the
segment. If the value in the RSSIZE column of the V$ROLLSTAT view for this rollback segment
is 6M, then the DBA would know that the rollback segment has extended beyond its optimal size.
The rollback segment will try to shrink itself back to the optimal size when a query against the
rollback segment requires it to extend a second time. Rather than waiting for the transaction to
show up that makes the rollback segment extend again, the DBA may want to force the rollback
segment to reduce to optimal size. This task is accomplished by using the alter rollback segment
shrink statement. If the DBA does not state the size to which the rollback segment should shrink,
Oracle will shrink the rollback segment to the size specified by optimal.

EXTENDS This column stores the statistic for the number of times the rollback segment
obtained an extent.

SHRINKS This column stores the statistic for the number of times
the rollback segment deallocated extents to return to its optimal size.

AVESHRINK This column stores the average shrink size--the amount of space
shed by the rollback segment when it reduced itself to the optimal size

AVEACTIVE This column stores the average active extent size in bytes for that rollback
segment.
This statistic represents the number of bytes for the rollback segment that w
part of a transaction that hadn’t committed yet.

SHRINKS AVESIZE OPTSIZE

High High Too high, lower optimal

High Low Too high, lower optimal

Low Low Too low, lower optimal (unless nearly equal to

AVEACTIVE)

Low High OK
Table 1: V$ROLLSTAT Settings and Their Meaning

To alter the rollback segment as described, use the alter rollback segment statement. Any or
several options may be used, including the next, optimal, and other options.
ALTER ROLLBACK SEGMENT rbs_02
STORAGE (NEXT 20M OPTIMAL 100M);

3) Allocating Rollback Segments to Transactions

It is useful to create a certain number of rollback segments that are much larger than the standard
rollback segment that currently exists on the database.
The larger segment can exist for the use of those marathon processes. In order to force those longer-
running queries to use the large rollback segments, the user executing the process can include a
statement assigning that query to the larger rollback segment.
That assignment statement is the set transaction use rollback segment statement. Given the
existence of larger rollback segments to handle the large transaction activity, this statement can
eliminate cases of marathon processes receiving the dreaded "snapshot too old" error.
At the beginning of the transaction, the process would issue the set transaction statement and
specify the large rollback segment in the use rollback segment clause.
Although it is a good idea to assign marathon processes to their own rollback segments, it is not a
good idea to assign every transaction explicitly to a rollback segment.
In order to assign transactions to rollback segments en masse throughout the database, each process
must have a complete idea of the processes running at that time, as well as the knowledge of which
rollback segments are online.
If too many transactions request the same rollback segment, that could cause the rollback segment
to extend and shrink unnecessarily while other rollback segments remain inactive. Oracle itself can
do an appropriate job at finding rollback segments for most short and medium duration transactions.

Using Oracle Blocks Efficiently

Determining block size

Setting pctfree and pctused

Detecting and resolving row migration

Detecting and resolving freelist contention

The foundation of all I/O activity in the Oracle database is the Oracle block. Row data for indexes
and columns, rollback segment information, data dictionary information, and every other database
component stored in a tablespace is stored in an Oracle block. Proper use of Oracle at the block
level will go a long way in enhancing performance of Oracle.
  Determining Block Size

The size of Oracle blocks is determined by the DBA and should be based on a few different
factors. First and foremost, the size of Oracle blocks should be based on some multiple of the size
of operating system blocks in the database.

The reason this approach is a good idea is because it allows the operating system to handle I/O
usage by Oracle processes in a manner consistent with its own methods for reading operating
system blocks from the filesystem. Most operating systems’ block size is 512 or 1,024 bytes.
Usually, Oracle block size is a multiple of that. Many times, it is 2K or based on a multiple of 2K—
either 4K or 8K. On certain large systems, the Oracle block size can be 16K. Oracle’s default size
for blocks depends on the operating system hosting Oracle, and should always be set higher than
the size of operating system blocks in order to reduce the number of physical reads the machine
hosting Oracle will have to perform as part of I/O activities.

Oracle block size is determined at database creation using the initialization parameter
DB_BLOCK_SIZE, which is expressed in bytes.

Most of the time, the default setting for Oracle blocks as provided by the operating-system-specific
installation is fine. However, there are some situations where an alternate block size is worth
consideration.

These situations have everything to do with the operating system’s ability to handle I/O and on the
size of its own blocks.

For example, some large machines, such as massively parallel servers or mainframes, use larger
operating system blocks; hence, it is possible to set DB_BLOCK_SIZE to a higher value in Oracle
at database creation time.

 Setting PCTFREE and PCTUSED

Key to the usage of Oracle database blocks is the appropriate setting of block storage
options pctfree and pctused to utilize the space within each block in an effective manner that is
consistent with the needs of the Oracle object being stored.

For example, the needs of a high-performance, "high response time required" OLTP application
with thousands of users entering new information daily are not the same as a mostly static, complex
query-intensive data warehouse system with few users. Similarly, the storage options used by
database blocks within each system will not be the same, either.

The two options, pctfree and pctused, determine how Oracle will fill the space in each Oracle
block with table or index data. They can be configured for tables in two ways:

Within create table or alter table and create index or alter index statements

Within create tablespace or alter tablespace statements as default storage clause values

The definition of pctfree is the percentage of each data block that Oracle leaves empty, or free,
for existing rows in the data block to expand as may be required by updates.
 When a process calls for row insertions on that object, Oracle will insert rows into the block
until the pctfree value is reached. After a data block’s pctfree value is reached, no more rows go
into that block. At this point, the pctused value comes into play.

The definition of pctused is the usage threshold that any block’s current capacity must fall under
before Oracle considers it a "free" block again. A free block is one that is available for insertion
of new rows.

The pctfree and pctused options are configured in relation to one another to manage space
utilization in data blocks effectively. When set up properly, pctfree and pctused can have many
positive effects on the I/O usage on the Oracle database. However, the key to
configuring pctfree and pctused properly is knowledge of a few different aspects of how the
application intends to use the object whose rows are in the block. Some of the important questions
that need to be answered by the DBA before proceeding with pctfree and pctused configuration
are as follows:

What kind of data object is using these blocks?

How often will the data in this object be updated?

Will updates to each row in the object increase the size of the row in bytes?

The ranges allowed for specifying pctfree and pctused are between 0 and 99. The sum of the
values for these two options should not exceed 100. Consider the effects of various values
for pctfree. For example, a pctfree value of 80 will leave 80 percent free from each data block
used for storage, while setting pctfree equal to 5 means that only 5 percent of the data block is
left free.
Examples of PCTFREE and PCTUSED Usage

It is usually not wise to set pctfree and pctused to values that add up to 100 exactly. When these
two options add up to 100, Oracle will work very hard to enforce that no data block keeps more
free space than specified by pctfree. This additional work keeps Oracle’s processing costs
unnecessarily high. A better approach is to set the values for pctfree and pctused to add up to a
value close to 100, perhaps 90–95. This approach represents a desirable balance between higher
processing costs and efficient storage management. Consider some examples
of pctfree and pctused settings and what they mean.

PCTFREE=25, PCTUSED=50

This combination might be used on high transaction volume OLTP systems with some anticipated
growth in row size as a result of updates to existing rows. The value for pctfree should
accommodate the increase in row size, although it is important to assess as closely as possible the
anticipated growth of each row as part of updates in order to maximize the storage of data. The
value for pctused prevents a block from being added to the freelist until there is 60 percent free
space in the block, allowing many rows to be added to the block before it is taken off the freelist.

PCTFREE=5, PCTUSED=85

This combination of values may be useful for systems such as data warehouses. The setting
for pctfree leaves a small amount of room for each row size to increase. The pctused value is
high in order to maximize data storage within each block. Since data warehouses typically store
mass amounts of data for query access only, these settings should manage storage well.
PCTFREE=10, PCTUSED=40

Oracle assigns a default value to each option if one is not specified either in tablespace default
settings or in the table and index creation statements. For pctfree, that value is 10. For pctused,
that value is 40.

 Detecting and Resolving Row Migration

The DBA should consider the "soft" performance gain offered when considering use of
higher pctfree values—a proactive solution to row chaining and migration.

Row migration occurs when a user process updates a row in an already crowded data block,
forcing Oracle to move the row out of that block and into another one that can accommodate the
row.

Chaining is when Oracle attempts to migrate the row but cannot find a block large enough to fit
the entire row, so it breaks the row into several parts and stores the parts separately.

The DBA should avoid allowing Oracle to migrate or chain rows, due to the fact that performance
can drop significantly if many rows are chained or migrated in the tableThere are tools available
with the Oracle database that detect migrated database rows. The analyze command offers
several different parameters for use in determining everything from the validity of a table or index
structure to collecting statistics for table usage that are incorporated into cost-based query
optimization. It also provides the functionality required for discovering if there are chained rows
in the database. The syntax for this statement is listed below:

ANALYZE { TABLE | CLUSTER } name

LIST CHAINED ROWS
INTO chained_rows;

The name in this context is the name of the table or cluster being analyzed for chained rows.
CHAINED_ROWS is the name of the table into which analyze places the results of its execution.
The DBA can then query CHAINED_ROWS table to determine if there is an issue with row
chaining on the database. This table is not automatically created as part of database creation.

 Detecting and Resolving Freelist Contention

When a data block’s row storage capacity hits the limit as expressed by pctfree, no more rows can
be inserted into it. When the capacity for that block falls below pctused, the block is again
considered available for row insertion. Oracle maintains records of blocks for the particular tables
that have space available for data insertion. These records are called freelists. When Oracle needs
to insert a new row into a block, it looks at the freelist for that table in memory to find some blocks
in which to put the new record.

However, sometimes there is contention in memory for getting to those lists of free blocks. The
DBA can identify freelist contention in the Oracle database by looking to see if there is contention
for free data blocks within the database buffer cache of the SGA. This information is contained in
the dynamic performance view that Oracle maintains called V$WAITSTAT.

Within the V$WAITSTAT view there are columns called CLASS and COUNT. This column
contains the names of various classes of statistics Oracle maintains in this view. The class in this
case is called ‘free list’. The value in the COUNT column identifies freelist contention—the
number of times a process had to wait for free blocks in the database buffer cache since the instance
was started.

SELECT ([Link]/SUM([Link]))*100 fl_wait_ratio

FROM v$waitstat w, v$sysstat s
WHERE [Link] = ‘free list’
AND [Link] in (‘db block gets’,‘consistent gets’);

Determining an appropriate value for the freelists storage clause in the event of detecting freelist
contention for a table is as follows. Determine how many processes are concurrently adding to the
table. The freelists clause can then be set to that number of processes that are looking for free
blocks to add their data in memory. With the number of freelists set to the same number of processes
adding row entries to that table, there should be little if any contention for freelists on that table.

Monitoring and Detecting Lock Contention

Levels of locking in Oracle

Identifying possible causes for contention
Using tools to detect lock contention
Resolving contention in an emergency
Preventing locking problems
Identifying and preventing deadlocks

 Levels of Locking in Oracle

Locks help to maintain transaction consistency on the Oracle database.

Two different basic types of locks relate to Oracle data structures—they are the DDL locks and
DML locks.

DDL stands for data definition language—the statements used in the Oracle architecture for
defining tables, indexes, sequences, and other devices used to define structures that are used by
applications in data processing. These locks prevent the fundamental structure of a database object
from changing as a user attempts to query or change data within it.

For example, an alter table statement issued by the DBA will not complete until all user processes
that are querying or changing that table have completed their query or change, and all other locks
issued on that object before the DDL lock was requested have been resolved. Similarly, a DDL
lock prevents a user process from querying or changing data while the object definition takes place.

The other type of locks in Oracle are the DML locks. These are the locks that user processes hold
while they make changes to the database information
In addition to scope, there are some different access levels to which locks permit or deny access to
the data being changed to other user processes.

Exclusive access means that for as long as the lock on a row or table is held, only the user process
holding the lock can see or change the data in that row or table. The other access level is
called shared access.

This access level means that the user process holding the lock will be the only process allowed to
make changes to that locked data, but other user processes will have the ability to access the data
via query at all times. Exclusive access is much more controlled than shared access; however, in
many situations, shared access is suitable for application needs, and as such, most Oracle default
lock capability uses shared locks in some form.

Consider another subject of transaction processing—transaction-level read consistency. This term

means that as a process executes a series of data change statements that constitute a transaction, the
process should have a version of the data that is consistent throughout the entire transaction. With
one exception, as noted below, all types of locks within the Oracle database provide transaction-
level read consistency.

With those terms defined, turn attention now to the actual types of locks available in the Oracle
architecture. There are five different types of locks in Oracle, and they are: exclusive, shared,
shared row exclusive, row shared, and row exclusive.

exclusive A lock on the entire contents of the table, during which time no user other
(X) than the holder of the lock can select or update any row in the table.

shared A lock on the entire contents of the table that allows any user to select data
in the table when another user holds this lock, but only the holder
of the lock can actually update the table data.

shared row A lock on the entire contents of a table that allows users holding this
exclusive lock to update data in the table. However, other processes are
(SRX) allowed to acquire row locks on the table, thereby NOT providing
transaction-level read consistency.

row exclusive A lock on a single row or group of rows that allows users holding
(RX) this lock access to update the locked row.
No user can query or change the row that is being held in row exclusive
mode until the process holding the lock has relinquished it.

row shared A lock on a single row or group of rows that allows the user holding
the lock access to update the locked row. Other users can query the row
being held in a row shared lock, but only the holder of that lock can
change the data.

 Identifying Possible Causes for Contention

Users may sometimes have to wait to acquire locks on the database system, or wait to see data on
the database that is held by a lock. This process of waiting for a lock itself is not contention, as
many times a process will have to wait until another process completes its changes. However, when
something happens to hold up data processing, then a lock contention issue may be the cause.

One example of contention for resources as a result of locking may come from the unexpected
placement of a database update statement in a stored procedure that is used by processes expecting
to perform mainly queries against the database. In this example, the process is not expecting to
perform an update to the database, so it does not contain any transaction completion statements
like commits or rollbacks that would indicate to Oracle that the lock should be released.

The effects can be particularly damaging—hundreds of locks may pile up on a table, causing
performance on the table to reduce drastically, and the cleanup will require putting the database
in restricted session mode to prevent additional locks accumulating while the DBA forces Oracle
to relinquish the locks being held by user processes. Meanwhile, there is also the issue of correcting
the stored procedure or embedded SQL that actually causes the problem.

The final area to cover with respect to locking is the dangerous situation where one process holds
a lock that a second process must have before the second process can give up the lock it has that
the first process needs.

This situation is known as a deadlock. Oracle has gone to great lengths to make sure that the
database can detect situations in which deadlocking occurs. However, there are other situations
where deadlocks occur and the DBA will have a great deal of difficulty detecting them.

Another possibility for contention exists within the usage of the shared row exclusive lock.
Although this lock is a table lock, it allows access to the table by other processes that also have the
ability to acquire row locks on the table and change data. This situation means that the holder of
the original shared row exclusive lock may have to wait for other processes that acquire row
exclusive locks on the table to complete their changes and relinquish the lock before the original
process can proceed.

A final possibility for contention exists on client/server systems. In this environment, it is possible
for network problems or process errors on the client side to cause a process failure on the client.

 Using Tools to Detect Lock Contention

One tool that is commonly used by DBAs in the task of identifying contention is the UTLLOCKT
utility that is provided as part of the Oracle distribution software.

On most systems, this utility SQL script can be found in the rdbms/admin directory under the
Oracle software home directory. UTLLOCKT queries the V$ACCESS and the
V$SESSION_WAIT views to find the sessions and processes that are holding locks and the
sessions and processes that are waiting for those locks to be relinquished so they can update the
resource being held.

UTLLOCKT places the output from that query in a readable tree graph form. This script should be
run by the DBA while logged on as user SYS. Before using this script, the DBA will need to
run [Link]. The output from this script looks something like the following code block:

WAITING_SESSION TYPE MODE REQUESTED MODE HELD LOCK ID1 LOCK ID2
--------------- ---- -------------- --------- -------- --------
8 NONE None None 0 0
9 TX Share Exclusive (X) 604 302
7 RW Exclusive (X) S/Row-X(SSX) 50304040 19
10 RW Exclusive (X) S/Row-X(SSX) 50304040 19
There is a method for determining if there are locking issues on the database.

The method is to select information from V$SESSION where the value in the LOCKWAIT column
is not NULL. Not only will this query obtain for the DBA which processes on the database are in
contention, but the DBA can potentially identify what data manipulation operation is happening
based on the value stored in the COMMAND column for sessions that are currently experiencing
a lock wait.

 Resolving Contention in an Emergency

One of the only guarantees a DBA will have in the course of regular production support on the
Oracle database is that emergencies will arise that require immediate resolution of locking and
contention issues.

There are several ways for the DBA to combat the problem of lock contention.

One blanket solution to resolving contention is to determine what session is holding the locks that
make the whole database wait, and to kill that session.

The DBA can execute the query listed above on the DBA_WAITERS view to determine the session
ID (SID) of the process holding the lock.

The other component required for killing a session is the serial number for that session. This
information can be obtained from the V$SESSION dynamic performance view with the following
query. Once the SERIAL# and SID are obtained from V$SESSION, the DBA can then issue
the alter system kill session statement.

Please note, however, that this method is a blanket solution that, at the very least, does not address
the underlying problem of the locking situation. However, it is important to know at least how the
"solution of last resort" works.

SELECT sid, serial#

FROM v$session
WHERE sid in (SELECT holding_session FROM dba_waiters);

ALTER SYSTEM
KILL SESSION ‘sid,serial#’;

 Preventing Locking Problems

The two procedures that may be of greatest use in lock management are the convert( ) and release(
) procedures of the DBMS_LOCK package.

The first procedure takes a lock of one type and converts it to another.

For example, a process may be holding an exclusive lock on a table, in order to update several
rows in a read-consistent manner.

It may be possible to obtain the same data change information with a share lock, and by having the
lock in that state, several SQL selects do not then have to wait for the process to relinquish its lock
in order to simply select data. Or, if the application developer does not want other processes to see
the changes it makes until the transaction completes, perhaps a reduction in lock scope from table
to row is in order.
By default, Oracle acquires the lowest level of locking for select for
update or update statements—the shared row or exclusive row lock, respectively. For acquiring
all other locks, the application developer must use the allocate_unique( ) procedure, which
identifies the lock given with a lock ID consisting of a numeric unique value.

For use of the convert( ) function, that lock ID must be passed to the procedure, as well as a
numeric identifier for the lock mode requested and a time-out identifying the period of time after
which the convert( ) function will no longer attempt to change the lock mode.

The convert( ) function will return an integer value that details how the processing went for that
execution. The release( ) function simply takes the lock ID generated by allocate_unique( ) and
releases the lock. There is a return code for this function as well.

The above information about DBMS_LOCK is provided as an outline for the discussion between
DBA and developer that must take place in order to prevent contention issues from occurring. Other
functionality that DBMS_LOCK can provide is to ensure all processes on the system use the Oracle
default locking mechanisms used in the select for update or update statements, rather than using
higher levels of locking if those higher levels are not absolutely critical to the application.

 Identifying and Preventing Deadlocks

Deadlocks are situations that cause painful performance problems on the Oracle database.
Situations arise where sometimes one process holds a lock on a resource while trying to obtain a
lock for a second resource.

A second process holds the lock for that second resource, but needs to obtain the lock for the first
resource in order to release the lock on the second. This catch-22 is known as a deadlock.

This situation can involve more than two processes as well.

Both processes in the diagram hold a lock, but they each need the other’s lock to relinquish their
own. Since neither process can proceed without the other giving up its lock, both processes are
considered to be deadlocked.

When Oracle’s deadlock detection mechanisms discover a deadlocking situation on the database,
they write a message to the alert log for the Oracle instance. This special trace file, which is
maintained by the database, contains all error messages, along with some other meaningful
information about the instance.

The DBA should take note of the "deadlock detected while waiting for a resource" error
messages, and any included process information from the alert log sent in order to assist the DBA
in determining the cause of the deadlock.

There are three final notes to make on preventing deadlocks.

The DBA should recommend to developers that they should try to set their processes up such that
all processes acquire locks in the same order. This will prevent the situation where processes
acquire locks on resources that others need in reversed order, which has a high probability of
creating deadlock situations.

The second point is for applications to always specify the lowest level of locking provided by
Oracle in select for update and update statements. The locking mechanisms provided by Oracle
in those two data change statements should be sufficient for almost all application development
needs.
 Finally, in the interest of preventing lock contention in OLTP systems, all long-running batch
updates should be scheduled to happen outside of the normal business day’s data processing.

Tuning Sort Operations

Identify SQL operations that use sorts

Ensuring sorts happen in memory
Allocating temporary disk space for sorts
Using direct writes for sorts

 Identifying SQL Operations that Use Sorts

Several data manipulation activities will require sorts. One example is the order by operation. This
option is commonly used in SQL selects in order to produce output from a query in an order on a
certain column that is specified by the query.

This option improves readability of data for the purposes of providing a more meaningful report.

For example, a table dump for all employee data contains the information needed to produce a
comparison report to find out who the 65 highest-paid employees are. However, since the data is
provided in a haphazard format, through which the reader has to search intensively for several
minutes or hours to find those 65 highly paid employees, the data really has no meaning. Instead,
the report could be designed to list every employee and their salary in a department, in descending
order on the SALARY column on the relevant table, using the order by clause of
the select statement.

Another SQL operation that utilizes sorts is the group by clause. This operation is used to collect
data into groups based on a column or columns.

For example, a table of states, their cities, and their cities’ populations may appear in a table. To
derive the population for each state, the following group by statement may be used:

SELECT DISTINCT state_name, sum(city_population)

FROM state
GROUP BY state_name;

Sorts are used in several different situations on the Oracle database. Both
the order and group operations use sorts. Sorts are also conducted as part of select, select
distinct, minus, intersect, and union statements, as well as in the min( ), max( ), and count(
) operations. The sort join internal Oracle operation, run behind the scenes when a user executes
a select statement to create a join, also uses sorts, as does the creation of indexes.

 Ensuring Sorts Happen in Memory

There are performance considerations involved in executing sorts. Oracle requires some temporary
space either in memory or on disk in order to perform a sort. If Oracle cannot get enough space in
memory to perform the sort, then it must obtain space in the temporary tablespace on disk to use.
In most cases, the default size for this area in memory used for sorting is enough to store the entire
sort; however, there can be situations where a large sort will require space on disk. Since data in
memory can be accessed faster than data on a disk, it benefits the performance on sorts to keep all
aspects of the sort within memory.

The DBA should monitor sort activities. The dynamic performance view that stores information
about how frequently Oracle needs to access disk space to perform a sort is called V$SYSSTAT.
To find the number of sorts occurring in memory vs. the number of sorts occurring on disk, the
DBA can select the NAME and VALUE from V$SYSSTAT where the name is
either ‘sorts(memory)’ or ‘sorts(disk)’. In the output from this query, a high value for memory
sorts is desirable, while the desired value for disk sorts is as close to zero as possible.

One method the DBA can exercise in order to avoid problems with memory management as a result
of increasing SORT_AREA_SIZE is to decrease another parameter associated with sorts, the
SORT_AREA_RETAINED_SIZE. This initialization parameter represents the smallest amount of
space Oracle will retain in a process’s sort area when the process is through using the data that was
sorted.

This may help memory, but at the expense of creating some additional disk utilization to move data
around in temporary segments on disk. The DBA and application administrators may also improve
database performance by ensuring that batch processing does not interfere with OLTP data usage
during the normal business day.

Another way to improve performance with respect to sorting is to avoid them entirely. This method
is particularly useful in the creation of indexes. As stated earlier, indexes use sorts to create the
binary search tree that can then be used to find a particular value in the indexed column and its
corresponding ROWID quickly.

Use of sorts for index creation can only be accomplished if the data in the table is already sorted
in appropriate order on the column that needs to be indexed. This option is useful if the operating
system on the machine hosting Oracle has a particularly efficient sorting algorithm, or if there is
only a tight window available for the DBA to create the index.

The nosort clause allows the DBA to create an index based on table data that is already sorted
properly. Important to remember in this scenario is that the table data needs to be sorted on the
column being indexed in order for nosort to work. If the data in the table whose column is being
indexed is not sorted, then the index creation process will fail.

CREATE INDEX uk_emp_01

ON emp (empid)
NOSORT;

 Allocating Temporary Disk Space for Sorts

When a sort operation takes place and requires disk space to complete successfully, the disk space
it uses is temporary.

The appropriate tablespace to allocate this space in is the TEMP tablespace. The TEMP tablespace
is used for user processes that require allocating temporary segments in order to process certain
SQL statements. Sorts are one type of operation that may require temporary disk storage.

The group by and order by clauses are two types of SQL statements that require sorts, which then
in turn may create segments in the user’s temporary tablespace for the purpose of sorting.
Care should be taken to ensure that the user’s temporary tablespace is not set to default to the
SYSTEM tablespace, as temporary allocation of segments for operations like sorts can contribute
to fragmenting a tablespace.

Both the default and temporary tablespaces for a user are set in the create user or alter
user statements. If the tablespaces are not set in either of those statements, the user will place
temporary segments used for sorts in the SYSTEM tablespace.

CREATE USER stacy

IDENTIFIED BY spanky
DEFAULT TABLESPACE users_01
TEMPORARY TABLESPACE temp_01
DEFAULT ROLE ALL;

ALTER USER DINAH

TEMPORARY TABLESPACE temp_02;

 Using Direct Writes for Sorts

In some situations, the machine hosting the Oracle database may have extensive disk and memory
resources available for effective performance on data sorts that the use of direct writes for sorting
provides.

This option is set up using three parameters from the Oracle initialization parameter file. Those
parameters, along with an explanation of their usage, are as follows:

SORT_DIRECT_WRITES—should be TRUE or AUTO. When TRUE, Oracle will obtain

buffers in memory that are designed to handle disk writes as part of the sort.
SORT_WRITE_BUFFERS—specified as an integer. When SORT_DIRECT_WRITES is
TRUE, Oracle will obtain this number of buffers to handle disk I/O on sorts.
SORT_WRITE_BUFFER_SIZE—value specified as an integer in bytes. When
SORT_DIRECT_WRITES is TRUE, Oracle will size each buffer obtained for disk writes to be
the value specified for this parameter.

SQL issues and tuning considerations for different application

OLTP (Online Transaction Processing) Applications:

Indexing: Ensure appropriate indexing on frequently used columns to speed up data retrieval and
queries.

Transaction Size: Keep transactions small to minimize locking and contention issues.

Normalization: Use normalized tables to reduce redundancy and improve data consistency.

Concurrency Control: Implement effective concurrency control mechanisms to handle

simultaneous transactions without conflicts.

Query Optimization: Review and optimize frequently executed queries by using appropriate joins,
WHERE clauses, and avoiding unnecessary operations.
OLAP (Online Analytical Processing) Applications:

Denormalization: Optimize for read-heavy operations by denormalizing data to improve query

performance.

Aggregation Tables: Use precomputed aggregation tables to speed up complex analytical queries.

Indexing Strategy: Create indexes based on analytical query patterns to enhance data retrieval.

Partitioning: Partition large tables to manage and query data more efficiently.

Materialized Views: Utilize materialized views to store and access precomputed results for complex
queries.

Web Applications:

Connection Pooling: Use connection pooling to efficiently manage database connections.

Caching: Implement caching mechanisms to reduce the need for frequent database queries.

Optimized ORM (Object-Relational Mapping): Ensure that the ORM used is generating efficient
SQL queries.

Asynchronous Operations: Implement asynchronous operations for non-blocking database access.

Query Optimization: Review and optimize queries specific to the application's requirements and
usage patterns.

Considerations for All Applications:

Monitoring and Profiling: Regularly monitor and profile the database to identify performance
bottlenecks.

Hardware and Infrastructure: Ensure the database server has adequate resources like memory, CPU,
and storage for optimal performance.

Query Plan Analysis: Analyze query execution plans to identify inefficient queries and optimize
them.

Regular Maintenance: Perform routine database maintenance tasks like index rebuilds, statistics
updates, and data purging.

Security Measures: Implement security measures without compromising performance, such as

proper indexing on columns used in security-related queries.

What is data integrity?

 Data integrity is the overall accuracy, completeness, and consistency of data.
 Data integrity also refers to the safety of data in regard to regulatory compliance — such as
GDPR compliance — and security.
  It is maintained by a collection of processes, rules, and standards implemented during the
design phase.
 When the integrity of data is secure, the information stored in a database will remain
complete, accurate, and reliable, no matter how long it’s stored or how often it’s accessed.
 The importance of data integrity in protecting yourself from data loss or a data leak cannot
be overstated.
 In order to keep your data safe from outside forces acting with malicious intent, you must
first ensure that internal users are handling data correctly.
 By implementing the appropriate data validation and error checking, you can ensure that
sensitive data is never miscategorized or stored incorrectly, thus exposing you to potential
risk.
 Data integrity in SQL databases refers to ensuring that each row of a table is uniquely
identified so that data can be retrieved separately.
 To achieve this, you need constraints on columns (constraints are sets of rules).
 Data constraints prevent invalid data entry into the base tables of the database, which helps
maintain data integrity.

Types of data integrity

Data integrity is of different types – they all consist of a series of methods and processes that ensure
data integrity in relational and hierarchical databases.

Physical integrity
Physical data integrity refers to protecting the data as you store and access it when needed.

For instance, it relates to your ability to maintain the database functions when the electricity goes
out in case of a natural disaster or if a hacker were to compromise the database.

Keeping accurate data at this stage is threatened by storage erosion, human error, and others.

This is where DaaS, or data as a service, can bring numerous benefits and cut costs while ensuring
data security.

Logical integrity

Logical integrity ensures that data remain unchanged in your database.

It also aims to protect the information against human mistakes or errors while keeping hackers at
bay.

There are four types of logical integrity:

 Entity integrity consists of creating primary keys to avoid data duplication or null values.

 Referential integrity contains several processes to ensure that your data are used and stored
efficiently, avoiding changing, adding, or deleting the data by mistake.

 Domain integrity means that each data piece in the domain is accurate. For instance, each column
may contain only a specific type of data in terms of format, type, or even amount of information.
 User-defined integrity contains rules or constraints that allow the user to make the information
suit particular requirements or needs.

Integrity Constraints
o Integrity constraints are a set of rules. It is used to maintain the quality of information.
o Integrity constraints ensure that the data insertion, updating, and other processes haveto
be performed in such a way that data integrity is not affected.
o Thus, integrity constraint is used to guard against accidental damage to the database.

Types of Integrity Constraint

1. Domain constraints

o Domain constraints can be defined as the definition of a valid set of values for an
attribute.
o The data type of domain includes string, character, integer, time, date, currency, etc.
The value of the attribute must be available in the corresponding domain.

Example:
2. Entity integrity constraints
o The entity integrity constraint states that primary key value can't be null.
o This is because the primary key value is used to identify individual rows in relation
andif the primary key has a null value, then we can't identify those rows.
o A table can contain a null value other than the primary key field.

Example:

3 Referential Integrity Constraints

o A referential integrity constraint is specified between two tables.

o In the Referential integrity constraints, if a foreign key in Table 1 refers to the

Primary Key of Table 2, then every value of the Foreign Key in Table 1 must be
null or be available in Table 2.

Example:
4. Key constraints
1. Keys are the entity set that is used to identify an entity within its entity set
uniquely.
2. An entity set can have multiple keys, but out of which one key will be the primary
key.A primary key can contain a unique and null value in the relational table.

Example:
 INTRODUCTION TO DATABASE SECURITY ISSUES
Database security is a broad area that addresses many issues, including the following: Various
legal and ethical issues regarding the right to access certain information—for example, some
information may be deemed to be private and can-not be accessed legally by unauthorized
organizations or persons.

Database Security

Security of databases refers to the array of controls, tools, and procedures designed to ensure
and safeguard confidentiality, integrity, and accessibility. This tutorial will concentrate on
confidentiality because it's a component that is most at risk in data security breaches.

Security for databases must cover and safeguard the following aspects:

o The database containing data.

o Database management systems (DBMS)
o Any applications that are associated with it.
o Physical database servers or the database server virtual, and the hardware that runs it.
o The infrastructure for computing or network that is used to connect to the database.

Security of databases is a complicated and challenging task that requires all aspects ofsecurity
practices and technologies. This is inherently at odds with the accessibility of databases. The
more usable and accessible the database is, the more susceptible we are to threats from security.
The more vulnerable it is to attacks and threats, the moredifficult it is to access and utilize.

Why Database Security is Important?

According to the definition, a data breach refers to a breach of data integrity in databases. The
amount of damage an incident like a data breach can cause our business is contingent on various
consequences or elements.

o Intellectual property that is compromised: Our intellectual property--trade secrets,

inventions, or proprietary methods -- could be vital for our ability to maintain an
advantage in our industry. If our intellectual property has been stolen or disclosed and
our competitive advantage is lost, it could be difficult to keep or recover.
o The damage to our brand's reputation: Customers or partners may not want to
purchase goods or services from us (or deal with our business) If they do not feel they
can trust our company to protect their data or their own.
o The concept of business continuity (or lack of it): Some businesses cannot continueto
function until a breach has been resolved.
o Penalties or fines to be paid for not complying: The cost of not complying with
international regulations like the Sarbanes-Oxley Act (SAO) or Payment Card Industry
Data Security Standard (PCI DSS) specific to industry regulations on data privacy, like
HIPAA or regional privacy laws like the European Union's General Data Protection
 Regulation (GDPR) could be a major problem with fines in worst cases in excess of
many million dollars for each violation.
o Costs for repairing breaches and notifying consumers about them: Alongside
notifying customers of a breach, the company that has been breached is required to
cover the investigation and forensic services such as crisis management, triage repairs
to the affected systems, and much more.

The areas in which you can configure security are as follows:

 User accounts. When you create user accounts, you can secure them in a variety of ways.
You can also create password profiles and resource limits to better secure password policies
for your site. Oracle provides a large set of predefined user accounts for administrative,
non-administrative, and sample schema uses.
 Authentication methods. Oracle Database provides several ways to configure
authentication for users and database administrators. For example, you can authenticate
users on the database level, from the operating system, and on the network, and for multitier,
global users, and application servers. If you use Microsoft Active Directory, you can
authenticate and authorize Microsoft Active Directory users with the database directly.
 Privileges and roles. You can use privileges and roles to restrict user access to data in the
following ways:
 Creating and granting privileges and roles to users or other roles
 Performing privilege analysis to find information about how privileges are used in
your site
 Configure definer's rights and invoker's rights for your applications
 Manage fine-grained access in PL/SQL packages and types
 Use Enterprise Manager to manage security
 Application security. The first step to creating a database application is to ensure that it
you have properly incorporated application security into your application security policies.
 User session information using application context. An application context is a name-
value pair that holds the session information. You can retrieve session information about a
user, such as the user name or terminal, and restrict database and application access for that
user based on this information.
 Database access on the row and column level using Virtual Private Database. A Virtual
Private Database policy dynamically imbeds a WHERE predicate into SQL statements the
user issues.
 Classify and protect data in different categories. You can create Transparent Sensitive
Data Protection policies to find all table columns in a database that hold sensitive data (such
as credit card or Social Security numbers), classify this data, and then create a policy that
protects this data as a whole for a given class.
 Network data encryption. You can use the DBMS_CRYPTO PL/SQL package to encrypt
data as it travels on the network to prevent unauthorized access to that data. You can
configure native Oracle Net Services data encryption for both servers and clients.
 Thin JDBC client network configuration. You can configure thin Java Database
Connectivity (JDBC) clients to securely connect to Oracle databases.
 Strong authentication. You can configure your databases to use strong authentication with
Oracle authentication adapters that support various third-party authentication services,
 including SSL with digital certificates. Oracle Database provides the following strong
authentication support:
 Centralized authentication and single sign-on.
 Kerberos
 Remote Authentication Dial-in User Service (RADIUS)
 Transport Layer Security (TLS) (formerly called Secure Sockets Layer)
 Auditing database activities. You can audit database activities in general terms, such as
auditing all SQL statements, SQL privileges, schema objects, and network activity. Or, you
can audit in a granular manner, such as when the IP addresses from outside the corporate
network is being used.