Find the cause of the wait event

-- Main Waiting View
Select * from V $ session_wait;
Select * from V $ session_event;
Select * from V $ system_event;
Select * from V $ waitstat where count> 0;

-- Related view
Select * from V $ session_wait
 
Select * from V $ filestat

Select * From dba_data_files

Select * From dba_extents where owner = 'wang'

Select * From SYS. uet $

Select * from V $ Latch

Select * from V $ filestat where file # In (select P1 from V $ session_wait)

 

-- Performance optimization method based on wait events

-- Common wait events
Select * from V $ system_event
Where event in ('buffer busy waits ', 'free buffer waits', 'db file sequential read ',
'Db file scattered read', 'enqueue ', 'latch free', 'Log file parallel write', 'Log file sync ')

-- View the wait events that contribute to the above display

Select B. username, B. Program, B. Status, A. event, A. total_waits, A. total_timeouts, A. time_waited, A. average_wait
From v $ session_event A, V $ session B where B. username is not null and A. Sid = B. Sid
And a. event not like 'SQL * Net %'
And B. Status = 'active'

-- To locate the current wait event related to the connected session, use the following query. this information is dynamic. To view the most waiting events of a session, you need to execute this query multiple times.

Select Sw. Sid, S. username, SW. event, SW. wait_time,
Sw. State, SW. seconds_in_wait sec_in_wait
From v $ session S, V $ session_wait SW
Where S. username is not null
And S. Sid = Sw. Sid
And Sw. event not like 'SQL * Net %'
Order by Sw. wait_time DESC;

-- The query displays other information about the test wait event.

Select Sid, event, p1text, P1, p2text, P2, p3text, p3
From v $ session_wait
Where event not like '% SQL %'
And event not like '% RDBMS %'

-- Using P1 and P2 information, you can easily find out which segment is

Select owner, segment_name, segment_type, tablespace_name from dba_extents
Where file_id = & fileid_in
And & blockid_in between block_id and block_id + blocks-1

-- Access Table item_master

-- Getsqltxt. SQL

Create or replace function getsqltxt (hashaddr_in v $ sqltext. hash_value % type, addr_in v $ sqltext. Address % Type) return varchar2
Is
Temp_sqltxt varchar2 (32767 );
Cursor sqlpiece_cur
Is
Select piece, SQL _text
From v $ sqltext
Where hash_value = hashaddr_in
And address = addr_in
Order by piece;
Begin
For sqlpiece_rec in sqlpiece_cur
Loop
Temp_sqltxt: = temp_sqltxt | sqlpiece_rec. SQL _text;
End Loop

Return temp_sqltxt;
End getsqltxt;
/

-- Check how many full table scans have occurred since the database was started!
Select * from V $ sysstat where name like '% table scan %'

-- Monitor full table Scan

Select Sid, serial #, opname, to_char (start_time, 'hh: MI: ss') "Start time", SOFAR/totalwork "% complete"

From v $ session_longops

**************************************** **************************************** ************************************
-- A long Solution Process for full table Scan

Current ORACLE System Performance
It takes 400 seconds to query the musicsong table.
Query the ________ resources used

Basic time spent
1. Read the database and load a table into the memory.
2. The computing part (the entire CPU) is used to process these tables.
3. Database write back

-- Identify Oracle system bottlenecks

Select * from V $ system_event where total_timeouts> 0 order by total_timeouts DESC

Drop table begin_sys_event;
Drop table end_sys_event;

/* Create Table begin_sys_event at time T1 */
Create Table begin_sys_event
Select * from V $ system_event

/* Wait n seconds or n minutes */

/* Create Table end_sys_event at time t2 */
Create Table end_sys_event
Select * from V $ system_event

Select t1.event, (t2.total _ waits-T1.total_waits) "delta waits ",
(T2.total _ timeouts-T1.total_timeouts) "delta timeouts ",
(T2.time _ waited-T1.time_waited) "delta time waited ",
(T2.average _ wait-t2.Average_wait) "delta average wait"
From begin_sys_event T1, end_sys_event T2
Where t1.event = t2.event and t2.total _ waits! = 0;

Select * from V $ event_name;

Select * from V $ system_event where event not in '% pmon timer %'
And event not like '% rdbms ipc message %'

-- Specify the maximum number of LRU locks. This value must be increased only when the failure rate in V $ latch exceeds 3%.
Select * from V $ latch;

-- How to identify internal latch conflicts

Server Manager monitor is a useful tool for monitoring latch waits, requests, and conflicts.
You can also query related data dictionary tables:
V $ latch, V $ latchholder, V $ latchname.

-- There are more than 40 latch types, but DBAs should focus on the following types:

Cache buffers chains latch: This latch is used when the user process searches for SGA database cache buffers.

Cache buffers LRU chain latch: This latch is used when a user process needs to search for the LRU (least recently used) chain of buffer cache including all dirty blocks.

Redo log buffer latch: This latch controls the space allocation of each redo entries in the redo log buffer.

Row cache objects latch: when a user's process accesses the cached data dictionary value, the row cache objects latch is used.

Redo copy latch is only applicable to multiple CPU Systems. In a multi-CPU instance, if a redo entry is too large to exceed the defined value of log_small_entry_max_size, "copy on redo allocation latch" cannot be performed ", in this case, the user process must obtain redo copy latch. An instance can have multiple redo copy latch. The number is determined by the initial parameter log_simultaneous_copies. The default value is the number of CPUs.

In the case of a single CPU, there is no redo copy latch. All redo entries, regardless of their size, are copied on the redo allocation latch ".

Excessive access to the redo log buffer will lead to a redo log buffer latch conflict. Latch conflicts will reduce system performance. We can detect this latch conflict through the following query:

Col name for A40

Select ln. Name, gets, misses, immediate_gets, immediate_misses

From v $ latch L, V $ latchname ln

Where ln. Name in ('redo allocation', 'redo copy') and LN. latch # = L. latch #

/

If the ratio of misses to gets exceeds 1% or the ratio of immediate_misses to (immediate_gets + immediate_misses) exceeds 1%, measures should be taken to reduce latch conflicts.

**************************************** **************************************** ****************************************
Here is a page I downloaded before, I forget where it come from. share with you all. I think this is a very good document for you.

Performance Tuning webwizard
Oracle RDBMS tuning for Aix

Use this webwizard to tuning your Oracle RDBMS running on an IBM pseries with Aix. This webwizard assumes you are a Database Administrator (DBA ).
Note these are the AIX specific tuning tips. See also the Unix General tuning tips.

We take no credit for this information as its taken from the Oracle manuals.

Tip 1-use asynchronous I/O
In the init. ora configuration file set: use_async_io = true
Then set the minservers and maxservers using Smit-> devices-> asynchronous I/O-> change/show characteristics of asynchronous I/O (or just type Smit AIO):

Maxservers = 10 * Number of disks
Minservers = maxservers/2
This is likely to increase performance by 6-8%.
Tip 2-use parallel recovery
In the init. ora configuration file set: recovery_parallelism = [number of CPUs but not less than 2]
This is likely to increase recovery processing by 0-50%.

Tip 3-Use logical volume manager
To spread out the data transmission SS disks you could use ORACLE files or the aix lvm. it is stronugly recommended that the LVM is used. striping data transmission SS disks is very valid tive as it makes full use of the disks in usage terms, makes excellent use of read ahead for sequential I/O and spreads I/O evenly (better extends amnce ). for striping use the following:
Stripe unit size 64kb
Max_coalesce 64kb
Minpgahead 2
Maxpgahead 16
Note: The striped LV size must be a multiple number of the drives used.
Note: striped data and log must be on different sets of dicks.
Note: AIx LVM does not currently allow striping and loading ing at the same time.
This is likely to increase performance by 0-500%.

Tip 4-use readv ()
In the init. ora configuration file set: use_readv = true
This requires tively ask the AIX Kernel not to buffer reads (Special JFS files) and shocould increase performance.

Note This can make performance worse so test this firsts.

Tip 5-use db_file_multiblock_read_count
In the init. ora configuration file set: db_file_multiblock_read_count = [8 or 16]
This shoshould be set to db_block_size * db_file_multiblock_read_count is greater than the LVM stripe size.

Tip 6-use JFS or raw partitions
This is a well worn subject with agruments on both sided.
JFS-if your database is not I/O bound I. e. You do lots of computation on data retrieved the JFS will be nice because its simpler to administer and backup/recover.
Raw partitions/raw disks/raw logical volume-otherwise use these for performance.
Moving to raw disks is likely to increase performance by 0-50%.

Tip 7-direct I/O
Not Implemented on Aix.
Tip 8-Use Write behind
Disable the AIX feature by setting the AIX Parameter Using: vmtune-C 0
Note to set the AIX parameter back to normal: vmtune-C 8

This might not be suitable unless the machine is solely a database server.

Tip 9-tune sequential read ahead
The Virtual Memory Manager spots sequential reading of files by watching the access pattern. after a number of reads in order are noticed, it will attempt to read upto maxphahead blocks of the file in adavance. by default these are:
Minpgahead 2
Maxpgahead 8
These can be increased to increase sequential reading of data using: vmtune-r 512-r 1024
Keep the numbers powers of 2.

Tip 10-tune disk I/O pacing
Disk I/O pacing is an AIX feature that stops disk I/O intension applications flooding the CPU. this is done with low and high water marks via: Smit-> system environment-> change/show characteristics of OS.
Be careful as this can hurt performance if not set correctly.

Tip 11-using raid
Raid can improve read performance but is slow in write. For performance never use RAID as random I/O is typically 4 times slower.
Tip 12-disk geometry considerations
On AIX you can place data of the participating parts of the disk. The middle part of the disk being the fastest as it has CES seek times.
This may increase performance by 10%

Tip 13-use processor binding on SMP
Certain processes can be locked to run on a participant CPU. This increases level 1 cache hits but the process cannot then float to unused CPU's if its CPU is busy. Use the bindprocessor command.
Note: Do not use bind processor on AIX 3

You can use this feature to bind the main Oracle processes to different CPUs with good effect. Also, if the SQL * Net Listener is bound its forked off servers for use connection are also bound.

This may increase performance by 15%

Tip 14-spin count on SMP
This can reduce Oracle internal latch contention. In the init. ora configuration file the default is: spin_count = 2000
Increasing this means the process will spin longer waiting for the process on other CPUs to free the latch so it can continue. setting this to 0 can help on single CPU machines or when CPU usage is very high.

Tip 15-process priority
Warning: getting this wrong may crash your machine.
Only the root user can set this using the setpri () System Call.

Increasing the priority (Allocation The number) can improve performance if there are lots of runnable processes on the machine. Oracle provide a setorapri command to do this: setorapri 39

This may increase created amnce by 15%

Tip 16-buffer cache Paging
For JFS database there can be a copy of the SGA block in the buffer cahce too. This can effect performance and cause I/O bottlenecks. There are four AIX buffer cache tuning parameters:
Minfree-below this page stealing starts trying to reclaim memory pages
Maxfree-abve this page stealing stops
Minperm-minimum number of pages allocated to file I/O
Maxperm-maximum number of pages allocated to file I/O
Increase minfree and maxfree so that read ahead pages do not reduce free pages to zero and there is alway free memory.
Tip 17-file buffer cache
You can adjust the minperm and maxperm (see Tip 16) to effect the size of the buffer cache resources.
This depend so much on the workload and I/O characterists of your database that its difficult to recommend particle values. Try: vmtune-P 30-P 60

The defaults are 20 and 80.

Tip 18-Paging Space
Never run out of paging space. Two to three time Ram is typical. Use: LSPs-a to determine the size and use of paging.
Tip 19-block size
Db_blcok_size = 4096 for small, JFS and OLTP/mixed workload Databases
Db_block_size = 8192 for raw Lare DSS workload
Tip 20-Redo buffer Latch
Set the following init. ora file parameters:
Log_small_entry_max_size = 0
Log_simultaneous_copies = [2 times the number of CPUs]
Tip 21-archiver Buffers
The log_archive_buffer_size effects the specified amcne of the archiver which is used to copy log files to other resources so they can be reused later. Set the init. ora file parameter:
Log_archive_buffer_size = [upto 128]
Log_archive_buffer = [Default of 4]
Over doing this can degrade performance but may give 20% better performance.

Tip 22-SGA size
The SGA must not be paged or swapped out. The amount of memory that can be allocated to the SGA depends on:
High numbers of users need more SGA
Actual Ram available
If the machine is a DB server or stand alone
Set the init. ora parameters:
Db_block_buffers
Shared_pool_size
For example as a rough guide:

System type stand alone server only
OLTP 30% of Ram 40% to 60% of RAM
DSS 40% to 70% of Ram 50% to 80% of RAM

Tip 23-SQL * loader I/O buffers
While loading data with SQL * loader it ends up waiting for the I/O to complete. Increasing the buffers parameter will improve load performance.
Tip 24-Out-Of-bound breacks with SQL * Net TCPIP
This is the communications use between client and server and by default is switched on (orasrv does this ).
This may increase perfromance by 20%

Tip 25-post-Wait kernel Extension
This operation CES the overhead of semaphore operations.
Make sure the correct version is installed.
This may increase performance by 20%

Tip 26-TCPIP
SQL * Net V2 uses 2kb packet sizes. The underlying packet size is 1kb for most installations.
This can be changed with SQL * net connection string parameters.
It is not recommended to change this as it can degrade your amnce.

Tip 27-compliling Pro * C
Use the right optimisation level
-O3
Compile for the right chipset:

-Qarch = com for the common mode (I. e. Runs on everything)
-Qarch = PWR for power only machines
-Qarch = pwrx for power2 only machines
-Qarch = PPC for PowerPC only machines
**************************************** **************************************** ***************************************