- The 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 selection * 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 event
- Frequently waiting for events Select * from V $ SYSTEM_EVENT WHERE EVENT IN ('Buffer Business Waits',' DB File Sequential Read ',' DB File Scattered Read ',' Enqueue ',' Latch Free ", 'log file parallel write', 'log file sync')
- View waiting events that have contributed above
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. Sidand a.Event Not Like 'SQL * Net%' and B.Status = 'Active'
- In order to find the current wait event related to the connected session, use the following query. This information is dynamic, in order to view a session, the event is the most waiting, you need to execute this query multiple times.
Select sw.SID, S.Username, sw.Event, sw.seconds_in_wait second, sw.seconds_in_wait sec_in_wait from v $ session s, v $ session_wait swwhere s.username is not null and s.SID = sw.sid And sw.event not like 'sql * net%' order by sw.wait_time desc;
- Query shows additional information about the test.
Select Sid, Event, P1Text, P1, P2Text, P2, P3Text, P3 from V $ Session_Waitwhere Event Not Like '% SQL%' And Event Not Like '% RDBMS%'
- Use the information of P1 and P2 easy to find out what paragraphs
Select Owner, Segment_name, Segment_Type, TableSpace_name from DBA_EXTENTSWHERE File_ID = & FileId_Inand & Blockid_in Between Block_ID and Block_ID Blocks-1
- Access table item_master
--Getsqltxt.sql
CREATE OR REPLACE function GetSQLTxt (HashAddr_in in V $ SQLTEXT.Hash_Value% Type, Addr_in 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; / - to see how much a full table scan occurred since the database started select * from v $! Sysstat where name like '% table scan%'
- Monitor full surface scan
Select Sid, Serial #, OPNAME, TO_CHAR (Start_Time, 'HH: MI: SS') "Start Time", Sofar / Totalwork "% Complete"
From v $ session_longops
*********************************************************** *********************************************************** **************** - Full table Scan time longer resolution process
Current Oracle System Performance Query Musicsong Table, full table query uses 400 seconds query use ____ how much resources
The most basic time spent 1. Database read, put a table into memory 2. Calculate the section (entire CPU), process these table 3. Database
- Determine Oracle System Bottleneck
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 AS SELECT * FROM V $ SYSTEM_EVENT
/ * Wait n seconds or n minutes * /
/ * CREATE TABLE END_SYS_EVENT AT TIME T2 * / CREATE TABLE END_SYS_EVENT AS SELECT * FROM V $ SYSTEM_EVENT
SELECT T1.EVENT, (T2.TOTAL_WAITS-T1.TOTAL_WAITAL_WAITS ", (T2.TOTAL_TIMEOUTS-T1.TOTAL_TIMEOMEOMEOUTS", (t2.time_waited-t1.time_waITED) "Delta Time Waite", (T2 . Above_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%'
- Specifies the upper limit of the number of LRU latch sets. This value is required only if the failure rate in V $ Latch exceeds 3%. Select * from V $ latch;
- How to identify the conflict between internal latch
Server Manager Monitor is a tool that is quite useful to monitor Latch waiting, request and conflict. You can also query the relevant data dictionary table: V $ Latch, V $ Latchholder, V $ latchname.
- 4. There are more than 40 species, but as the main concern of DBA, there should be the following:
Cache Buffers CHAINS LATCH: This latch is required when the user process search SGA is looking for Database Cache Buffers.
Cache Buffers LRU Chain Latch: Use this latch when the user process is to search for the LRU (Least Recessly Used) chain including all Dirty Blocks, including the Buffer Cache.
Redo Log Buffer Latch: This Latch Controls Spatial Assignment of each Redo Entries in Redo Log Buffer.
Row Cache Objects Latch: Row Cache Objects Latch will be used when the user process accesses the cache data dictionary
Redo Copy Latch is only applied to a multi-CPU system. In a multi-CPU's instance, if a Redo Entry is too large, more than the log_small_entry_max_size definition value, the "copy on the Redo Allocation Latch" is not possible, and the user process must obtain Redo Copy Latch. There are multiple Redo Copy Latches in an Instance, which is determined by the initial parameter log_simultaneous_copies, the default value is the number of CPUs.
In the case of single CPU, there is no Redo Copy Latch, and all Redo Entry performs "copy of the Redo Allocation Latch" regardless of the size.
Excessive access to Redo Log Buff will lead to the conflict of Redo Log Buffer Latch, Latch conflicts will reduce system performance, and we can detect this Latch conflict by queries:
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 proportion of Misses is more than 1% or IMMEDIATE_MISSES with (IMMEDIATE_GETS Immediate_Misses), it should be considered to reduce the conflict of the LATCH.
*********************************************************** *********************************************************** **************************** Here is a page i downloaded Before, i forget where it com. 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) TO:
. 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 across disks you could use Oracle files or the AIX LVM It is strongly recommended that the LVM is used Striping data across disks is very effective 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 perforamnce) 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 mirroring at the same time This is likely to increase performance by 0 - 500% .Tip... 4 - Use readv () in The init.ora configure file set: USE_READV = True This Effectively Ask The Aix Kernel NOT To Buff READS (Particular JFS Files) and Should 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 should 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 ie 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 Likey to Increase Performance BY 0 - 50%.
TIP 7 - Direct I / O Not Implement 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 8th 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 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 particular parts of the disk THE MIDDLE PART OF THE DISK Being The Fastest AS It Reduces Seek Times. This May Increase Performance By 10%
Tip 13 - Use Processor Binding on SMP Certain processes can be locked to run on a particular CPU This increases level 1 cache hits but the process can not 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.
.
Increasing the prior (reducing 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 Perforamnce 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 - above 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 read ahead pages do not reduce free that 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 Particular Values. Try: VMTune -p 30 -P 60The 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 perforamcne 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 Sitched On (ORASRV DOES THIS). This May Increase Perfromance By 20%
Tip 25 - Post-Wait Kernel Extension This Reduces 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 perforamnce...
TIP 27 - Compliling Pro * c Use the right optimisation level -o3 compile for the right chipset:
-QARCH = com for the commit (ie runs on everything) -qarch = pwr for power only machines -qarch = pwrx for power2 only machine -qarch = PPC for powerpc online = ppc for powerpc online *********************************************************** *********************************************************** *******
