As a system administrator, you will often be confronted with users who say that the response time on the system is unacceptable. What is unacceptable performance? There are two sides to this:
* Quantifiable performance
* Unmeasurable performance like user dissatisfaction.
This paper will help identify quantifiable performance problems and methods to solve them. When performance-tuning databases, it is a good to split the problem into parts to facilitate the process. Performance tuning Oracle databases can be divided into three subcategories:
1. Application tuning
2. RDBMS tuning
3. UNIX system tuning
Each of the above categories has a different maximum possible effect on the performance of your ORACLE database.
1. Application Tuning
Application tuning is by far the most effective aspect for database tuning. You will need to tune your SQL statements since they intervolve both your application and database. To analyze your SQL statements you will need to:
1. Add the following lines to your init.ora file:
SQL_TRACE = true
TIMED_STATISTICS = true
2. Restart your database.
3. Run your application.
4. Run tkprof against the tracefile created by your application:
tkprof
5. Look at formatted output of trace command and make sure that your
SQL statement is using indexes correctly. Refer to DBA guide for a list
of rules that the oracle optimizer uses when choosing a path for a SQL statement.
<<<<<< OUTPUT OF TKPROF FILE >>>>>>>
count = number of times OPI procedure was executed
cpu = cpu time executing in hundredths of seconds
elap = elapsed time executing in hundredths of secs
phys = number of physical reads of buffers (from disk)
cr = number of buffers gotten for consistent read
cur = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the OPI call
===========================================================
select * from emp where empno=7369
count cpu elap phys cr cur rows
Parse: 1 0 0 0 0 0
Execute: 1 0 0 0 0 2 0
Fetch: 1 0 0 219 227 0 2
Execution plan:
TABLE ACCESS (FULL) OF 'EMP'
===========================================================
select empno from emp where empno=7934
count cpu elap phys cr cur rows
Parse: 2 0 0 0 0 0
Execute: 2 0 0 0 0 2 0
Fetch: 2 0 0 0 2 0 2
Execution plan:
INDEX (RANGE SCAN) OF 'ALEX_INDX' (NON-UNIQUE)
===========================================================
When your query is returning less than 10% of the rows of a table and the table is a reasonably large table, you will want to index your query. Above is an example of the same query run twice. The first time the optimizer chose to do a full table scan on the table EMP. The second time an index was created called ALEX_INDX. The optimizer chose to use the index. On a table
with 1000 rows this should have resulted in a faster query.
Oracle uses a rule-based optimizer for its SQL statements. When a SQL statement is parsed, the optimizer decides which query path will be chosen. The optimizer can often times make mistakes or simply illustrate that your SQL statement was written incorrectly. You can refer to Chapter 2 of the Performance Tuning Guide for more information on SQL statement tuning. Page 19-17 of the V6 DBA guide has a listing of the query paths that the optimizer will use ranked by speed.
Other considerations for application tuning may include the actual design of your application. A common problem found in menu5/forms30 applications is the way in which one calls the other. Make sure that menu5 is calling forms30 directly and not through a UNIX system call. Calling forms30 through a UNIX system call has the effect of doubling the number of connections to the database and also doubling the load on the UNIX machine.
2. RDBMS Tuning
The default init.ora database configuration file is inadequate for large
RDBMS's. Tools for tuning the database are:
1. SQLDBA * Monitor
2. Bstat/Estat
3. V$ database tables.
By looking at SQLDBA * Monitor one can get a good idea of what is happening to the database at that moment. The first place to look is the IO display. This will display the CACHE HIT RATIO. The cache-hit ratio is the ratio of hits to misses in the SGA for data. The higher the ratio, the more data is being found in the SGA. This means that oracle will not have to do as many disk reads to retrieve information, thus saving time and CPU processing.
While a hit ratio of 1.00 would be ideal, it is more realistic to aim for achieving a hit ratio of about .80.DB_BUFFER values by setting the init.ora parameter
DB_BLOCK_LRU_EXTENDED_STATISTICS equal to the additional number of DB_BUFFERS you wish to add. You will then be able to see the additional number of cache hits that would occur if you add X number of database buffers. For more information on this you can refer to pg 3-20 of the database performance tuning guide.
After looking at the cache-hit ratio and adjusting the number of database buffers accordingly you can analyze FILE IO. This screen
in SQLDBA * Monitor will show IO to each oracle datafile. It allows you
to identify two problem areas:
1. Table scans (indicated by a low number of reads/s & high number of blks/R)
2. Heavily used tablespaces
Table scans indicate that your SQL statements might not be tuned correctly. If your query is returning less than 15% of the rows of a table you should be using indexes to query the tables. Secondly you will also be aware of which tablespaces are used heavily and perhaps consider moving them to a separate disk to balance the IO.
The Rollback screen can be very useful on an update or insert-heavy system. Whenever you are changing or inserting new data, you write the changes to a rollback segment until they are committed. If you have 30 transactions and 1 rollback segment, you will have contention for that rollback segment. This will incur a performance penalty. To make sure that this is not happening, verify that every rollback segment has a maximum of 4 active transactions using it during heavy updating of the database.
The table V$ROWCACHE also contains useful information for tuning. The following select statement can determine whether or not any of the init.ora parameters beginning with DC_XXXX should be raised. These parameters control the size of various data dictionary caches.
SELECT parameter,gets,getmisses,count,usage FROM sys.v$rowcache;
PARAMETER GETS GETMISSES COUNT USAGE
------------- ------- ---------- ---------- -----
dc_usernames 134 5 50 5
dc_columns 11772 288 300 300
The parameter dc_usernames has a very low number of cache misses (GETMISSES). We have allocated 50 username entries (COUNT) and are only using 5 (USAGE). There is no need to raise the value of dc_usernames.
We have allocated 300 DC_COLUMNS and we are using all 300. In addition, the number of GETMISSES or cache misses are high for the parameter DC_COLUMNS. In this case, it is recommended to increase the value of DC_COLUMNS to reduce the number of GETMISSES or cache misses. A cache miss can be a very expensive operation for oracle since it means that the data requested was not in cache and thus a recursive call had to be made. For more information on this you can refer to pg 3-13 of the v6 performance tuning guide.
3. UNIX System Analysis
It is useful to subdivide UNIX system analysis into three subcategories:
1. Memory
2. CPU
3. IO
1. MEMORY
One of the most common problems when running large numbers of concurrent users on UNIX machines is lack of memory. In this case, a quick review of UNIX memory management is useful to see what effect lack of RAM can have on performance. A UNIX machine has virtual memory: the total addressable memory range. Virtual memory is composed of RAM, DISK and SWAP space.
Generally, you will want to have the available SWAP space equal to 2 to 3 times the RAM.
How does UNIX use SWAP space? It uses two memory management policies: swapping and paging. Swapping occurs when UNIX transfers an entire process from RAM to a SWAP device. This frees up a large amount of RAM. Paging occurs when UNIX only transfers a "PAGE" of memory to the SWAP device. Only a tiny portion of a process might actually be "paged out" to a SWAP device. While swapping frees up memory - it is slower than paging. Paging generally is more efficient but does not allow for large amounts of memory to be freed simultaneously. Most UNIX systems today use a combination of paging and swapping to manage memory. Generally, you will see the following behavior:
* System lightly used: no paging or swapping occurs.
* System under a medium load: paging occurs as RAM memory runs low
* System under a very heavy load: paging stops and swapping begins.
When analyzing your UNIX machine, make sure that the machine is not swapping at all and at worst paging lightly. This indicates a system with a healthy amount of memory available. To analyze paging and swapping, use the following commands. Commands used in Berkeley UNIX based systems will be marked as BSD. Commands used in ATT system V will be marked as ATT.
1. vmstat 5 5 (BSD)
procs memory page disk faults cpu
r b w avm fre re at pi po fr de sr d0 s1 d2 s3 in sy cs us sy id
0 0 0 0 1088 0 2 2 0 1 0 0 0 0 0 0 26 72 24 0 1 98
Note: There are NO pageouts (po) occurring on this system. There are also 1088 * 4k pages of free RAM available (4 Meg). It is OK and normal to have page out (po) activity. You should get worried when the number of page ins (pi) starts rising. This indicates that you system is starting to page.
2. pstat -s (BSD)
12112k allocated + 3252k reserved = 15364k used, 37280k available
Note: pstat will also give you the amount of RAM and SWAP currently available on your machine.
3. sar -wpg 5 5 (ATT)
09:54:29 swpin/s pswin/s swpot/s pswot/s pswch/s
atch/s pgin/s ppgin/s pflt/s vflt/s slock/s
pgout/s ppgout/s pgfree/s pgscan/s %s5ipf
09:54:34 0.00 0.0 0.00 0.0 12
0.00 0.22 0.22 0.65 3.90 0.87
0.00 0.00 0.00 0.00 0.00
Note: There is absolutely no swapping or paging going on. (swpin,swpot,ppgin,ppgout).
4. sar -r 5 5 (ATT)
10:10:22 freemem freeswp
10:10:27 790 5862
This will give you a good indication of how much free swap and RAM you have on your machine. There are 790 pages of memory available and 5862 disk blocks of SWAP available.
2. CPU
Once you have monitored your systems available memory you will want to make sure the the CPU(s) are not being overloaded. Here is some general information about how processes get allocated CPU time. UNIX is a multi-processing operating system. That means that a UNIX machine has to manage and process multiple user processes simultaneously. UNIX does this in the same way that people wait in line to buy groceries.
When a process is ready to be processed by a CPU it will be placed on the waiting line or RUN-QUEUE. This is a queue of processes waiting to be run. Obviously there are limits within which one wants to keep the RUN-QUEUE size. Another factor of interest is the percentage of time the the CPU spends in user mode, system mode, or idle mode. Some commands that determine whether or not there is a CPU resource problem occurring:
1. vmstat 5 5 (BSD)
procs memory page disk faults cpu
r b w avm fre re at pi po fr de sr d0 s1 d2 s3 in sy cs us sy id
0 0 0 0 1088 0 2 2 0 1 0 0 0 0 0 0 26 72 24 0 1 98
Note: The CPU is spending most of its time in IDLE mode (id). That means that the CPU is not being heavily used at all! There are no processes that are waiting to be run (r), blocked (b), or waiting for IO (w) in the RUN QUEUE.
2. sar -qu 5 5 (ATT)
10:58:02 runq-sz %runocc swpq-sz %swpocc
%usr %sys %wio %idle
10:58:07 2.8 100
0 2 4 94
Note: The CPU is spending most (94%) of its time in idle mode. This CPU is not being heavily used at all. Two solutions to this are:
1. Obtain a faster processor
2. Use more CPU's.
Avoid overloading your CPU. Response time on your machine will suffer if it is overloaded. Try to keep the run queue 100% occupied and have less that 6 processes waiting to be run for one CPU. This changes as you add more CPU's or a faster CPU. You may also want to avoid the CPU spending most of its time (more than 50%) in system mode. This may indicate that you are spending too much time in kernel mode servicing interrupts, swapping processes etc.
3. I/O
The last step in analyzing your UNIX machine is taking a look at IO. After having looked at SQLDBA monitor to see which datafiles are being used heavily you may also want to take a look at what UNIX says about file IO. These commands are for analyzing file IO on file systems.
1. iostat -d 5 5(BSD)
sd1 sd3
bps tps msps bps tps msps
1 0 0.0 4 0 0.0
iostat will display the number of kilobytes transferred per second, the number of transfers per second, and the milliseconds per average seek. In the example above, both SD1 and SD3 are not used heavily at all. BPS rates over 30 indicate heavy usage of a particular disk. If only one disk shows heavy usage, consider moving some of your datafiles off it or striping your data across several disks.
2. sar -d 5 5 (ATT)
09:17:20 device %busy avque r+w/s blks/s avwait avserv
09:17:26 iop0/pdisk010 472.45 1.18 9 107 39.26 512.66
iop0/pdisk000 18.43 2.66 8 132 31.36 24.10
iop0/pdisk020 317.08 1.11 11 165 31.95 294.02
iop0/pdisk021 590.88 1.34 27 518 96.26 219.96
iop0/pdisk040 34.94 1.64 18 113 43.70 19.58
iop0/pdisk041 45.33 1.17 20 79 3.73 22.89
Note: The "-d" option reports activity for each block device, The following is an explanation on the output. %busy, avque - portion of time device was busy servicing a transfer request, average number of requests outstanding during that time; r+w/s, blks/s - number of data transfers from or to device, number of bytes transferred in 512-byte units; avwait, avserv - average time (in ms) that transfer requests wait idly on queue, and average time to be serviced. There is a relationship between the number of blocks transferred per second and the average wait time. You can use this to identify which disks are heavily used and which are
underutilized.
3. sar -b 5 5 (ATT)
15:52:57 bread/s lread/s %rcache bwrit/s lwrit/s %wcache pread/s pwrit/s
15:53:12 0 2 90 1 2 38 0 0
Note: The "-b" option indicates the overall health of the IO subsystem. The %rcache should be greater than 90% and %wcache should be greater than 60%. If this is not the case, your system may be bound by disk IO. The sum of bread, bwrit, pread, and pwrit gives a good indicator of how well your file subsystem is doing. The sum should not be greater than 40 for 2 drives and
60 for 4-8 drives. If you exceed these values, your system may be IO bound. For more information on this refer to pg 2-22 of the Oracle V7 technical reference guide.
When analyzing disk IO, make sure that you have balanced the load on your system. Here is a "wish" list of steps for designing a disk layout for Oracle:
1. Make sure that your logfiles and archived logfiles are NOT on the same disk as your datafiles. This is a basic safety precaution against disk failure.
2. Put your files on raw devices.
3. Allocate one disk for the User Data Tablespace.
4. Place Rollback, Index, and System Tablespaces on separate disks.
5. Consider using Raid level 5 disk striping to stripe your datafiles across separate disks. You can also use the above-mentioned UNIX commands to monitor the IO on your system and identify problem areas.
Other tools for system administratation are platform-dependent. For example, HP and SEQUENT provide a facility called MONITOR that gives much of the above information in a graphical format. ATT SVR 4 also provides a product called GSAR (graphical sar). Look in your operating system (OS) documentation for additional monitoring commands. As a further
reference, consider "SYSTEM PERFORMANCE TUNING" published by O'Reilly and Associates.
Conclusion: There are many ways to approach Oracle performance issues. A structured approach like the one discussed above will allow the system administrator to systematically analyze her system and identify any problem areas. Once this has been accomplished, measures can be taken to correct problems. Performance is subjective, so find out what is expected.
No comments:
Post a Comment