| News | See also | Recommended Links | Recommended Papers | Security Audit | Checklists | SQL Injection | FAQs |
| NSA Guides | NIST guides | Oracle Developer Network Papers | Unix security | Commercial Tools | Humor | Etc |
Accidental removal of indexes and indexes being disabled, that the DBA has not noticed, are a common problem. For example, an index can get disabled when the SQL*Loader is used to directly load data, and the re-index failed. So, check the indexes for the following:
If an index is missing, there is nothing within the database to check on it. To detect a missing index, there must be a definitive list of required indexes, and the columns that should be in the index.
If there is an index problem, then fix it before tuning anything.
If indexes are missing, then use the Oracle parallel index creation feature to make the index in the shortest possible time. This works very well on SMP machines, but you may want to restart the database with larger than normal SORT_AREA_SIZE to allow fast sorting of the resulting index.
There are a limited number of all the Oracle parameters that have a large impact on performance. Without these being set correctly, Oracle cannot operate properly or give good performance. These need to be checked before further fine tuning. This is covered in detail in 15.3, Evaluate the top Oracle parameters. It is worth tuning the database further only if all these top parameters are okay.
Oracle has warned all customers that rule based optimization will be dropped in future releases. As the cost based optimizer is now likely to give the best performance in most cases, this should be used. The cost based optimizer needs data to decide the access plan, and this data is generated by the analyze command or the newer dbms_stats package. For Oracle 9i, always use dbms_stats.
Oracle depends on data about the tables and indexes. Without this data, the optimizer has to guess. It is worth checking that the optimizer has the following information:
Most parallel SQL statements, SQL hints, and many of the new performance features of Oracle, such as hash, star joins, and partitions, will only be available using the cost based optimizer. If the dbms_stats.get_table_stats, analyze, or dbms_utility.analyze_schema command is not run, and the SQL does not use SQL hints, the optimizer has to use rule based optimization and will not make the new performance feature available.
The optimization mode is set in the Oracle parameters using the init.ora file with the OPTIMIZER_MODE variable. While the parameter is usually set to CHOOSE or RULE, the possible values are:
It can also be set at the session level by using the OPTIMIZER_GOAL or OPTIMIZER_MODE options of the alter session command. We do not generally recommend setting this at the session level, but the syntax is:
ALTER SESSION SET OPTIMIZER_GOAL = RULE
or for Oracle 8.1.5 onwards:
ALTER SESSION SET OPTIMIZER_MODE = RULE
Setting the optimizer mode to CHOOSE in the init.ora file so that the cost based optimizer is used is highly recommend ed in most cases. The main exception to using the cost based optimizer is when an application has been manually tuned by developers for the rule based optimizer. Even this should be tested with the latest release of Oracle to check if the cost based optimizer can now improve on the older rule based query plans and performance.
To determine if a table is analyzed, check the AVG_ROW_LEN column of the USER_TABLES table. If it is non-zero, the analyze or dbms_stats command has been run at least once on this table.
To determine if an index is analyzed, check the COLUMN_LENGTH column of the USER_IND_COLUMNS table. If it is non-zero, the analyze or dbms_stats command has been run at least once on this index.
If you analyze a table, then its current indexes are automatically analyzed too. If you analyze a index, then the table is not analyzed.
For tables and indexes where the data is highly skewed, it is worth creating histogram statistics. The database usually assumes that there is an even spread of values between the highest and lowest value found in a column. If this is not true, the data is skewed. For example, in England, there are many people with surnames of Smith and Jones and hardly any starting with the letter Z. This means a surname column has skewed values. Another example might be a column containing the year's sales order. If a ten year old company is growing rapidly, it might find that the last year includes 60% of sales orders; this is highly skewed. The command will be similar to:
ANALYZE TABLE orders COMPUTE STATISTICS FOR COLUMN release_date
or for Oracle 9i:
EXECUTE DBMS_STATS.GATHER_TABLE_STATS(`scott', `orders',METHOD_OPT=>`FOR COLUMN SIZE 10 release_date')
The default number of the histogram buckets is 75. For table columns with a large ranges and large numbers of clustered values, having a higher number of buckets can be useful.
To investigate the histograms on the system, use the USER_HISTOGRAMS table. For example:
SELECT * FROM USER_HISTOGRAMS;
Collecting the full statistics using the analyze table <name> compute statistics command on large tables takes a lot of time and space in the system (roughly the cost of a full table scan and sort operation). For tables with an even distribution of data in the columns, this will yield little extra value over an estimated analysis using a sample of the rows. This is performed with the analyze table <name> estimate statistics command. We recommend an estimate of 5% of the rows as a minimum for large tables and their indexes. For example:
ANALYZE TABLE orders ESTMATE STATISTICS SAMPLE 5 PERCENT
or for Oracle 9i:
EXECUTE DBMS_STATS.GATHER_TABLE_STATS(`scott', `orders', DBMS_STATS.AUTO_SAMPLE_SIZE)
With the cost based optimizer, there is a further choice to make about the way the database is requested to provide the results. If your application can make use of the first few rows of the SQL statement, for example, displaying them on the user's screen, then the OPTIMIZER_GOAL of FIRST_ROWS_N can make the application look faster to the user. This is set at the session level with:
ALTER SESSION SET OPTIMIZER_GOAL = FIRST_ROWS_100
In general, it is hard to code an application this way, so it is not common. Otherwise, the OPTIMIZER_GOAL of ALL_ROWS will finish the query in the shortest possible time. This changes the access method the optimizer will choose, as some techniques provide the first rows earlier than others. The default is to maximize throughput of the system. We recommend using the default unless early screen updates are coded into the application.
The non-default Oracle parameter values are worth investigating. Of course, many of them have to be non-default for the system to run at all.
|
Hint
|
Benefit
|
Risk
|
|---|---|---|
|
Medium
|
None
|
|
|
Very high
|
None
|
|
|
Very high
|
None
|
|
|
Medium
|
None
|
|
|
Medium
|
None
|
|
|
Medium
|
Low
|
|
|
Medium
|
None
|
|
|
Medium
|
None
|
|
|
High
|
Low
|
|
|
Medium
|
None
|
|
|
Medium
|
None
|
Having said that, they are strongly pushing the one stop shop solution of Enterprise Linux and Oracle technology. Given that most of Oracle's application stack is certified for use on Red Hat Enterprise Linux, it would make sense for anyone currently deploying Oracle applications and considering a move to Linux to try Oracle's offering.
However, Red Hat have been in the Linux business a long time and are home to some top Linux bods, making their offering rock solid. Also, as Red Hat quite rightly point out there will always be a slight delay for important security fixes to trickle down to Oracle Enterprise Linux as they work to strip out any trademarked items and rebuild each patch.
More importantly though, Oracle are intent on applying bugfixes of their own to Enterprise Linux, something that invalidates any Red Hat support you may have and something that you seriously need to consider.
Unusually, Oracle are claiming that they will support your operating system indefinitely as part of the Premier Support package which works out at $1199 and $1999. This includes bugfixes for that version as well as support requests and access to software. Not even Red Hat provides this length of support, officially only supporting RHEL for a maximum of seven years. Our advice? Consider a switch to Oracle Enterprise Linux.
The debate on whether to use a file system or raw devices for implementing databases still rages among Oracle database administrators. Most database administrators are familiar with the UNIX file system implementation, and prefer the ease of management of a file system as compared to a raw implementation. In addition, all UNIX commands and tools for file manipulation can still be used with a file system implementation. This is not the case for a raw database. Therefore, DBAs frequently choose file systems for implementing databases.
However, there is considerable performance overhead when using a file system to implement a database. The file locking inherent to UNIX file systems (files are exclusively accessed during write operations) and double buffering (once in kernel space, once in user space) introduce significant performance overhead. This paper attempts to quantify these overheads.
Our results indicate that database implementations using raw devices have the best performance, with 92% transactional throughput improvement over file-system based implementations (log option). We believe that administrators will progressively migrate to raw databases as database management tools, such as Oracle Enterprise Manager (OEM), continue to improve.
We used an OLTP workload for our evaluation. We report performance measurements of the Oracle database using raw logical volumes and using file system and explore the effect of using asynchronous and synchronous I/O operations for the raw device implementation. We also investigate various file system optimizations to determine their effect on database performance. We also vary the number of Oracle database writer processes and Oracle I/O slaves processes to get the best performance for a given configuration. The experiments tend to cover all the different combinations possible when implementing an Oracle database. The Oracle Stripe And Mirror Everything (SAME) methodology was used for the database layout for all the tests.
... ... ...
On HP-UX, asynchronous IO is only supported with a raw device (raw disk partition or raw logical volume), although this will change with HP-UX 11i v3 (internally known as 11.31). With asynchronous IO configured, Oracle can submit multiple IO requests in parallel. In some cases it may be necessary to use multiple database writer processes (DBWR) to stress the backend, but we were able to achieve this with only 2 DBWR processes. In our case, we did not see any improvement beyond 2 database writer processes. The right number could be determined through the Oracle IO statistics but is beyond the scope of this paper. The usage of asynchronous IO will require HP-UX kernel changes and modification of the Oracle initialization parameters file, init.ora. For more information, consult the Oracle Installation and Administrators Reference Guide documentation on HP.
"Load Test Windows 2003 v Red Hat 4.0 (64 bit)"
...here are my summary from their slides:
"RAC Stress Test (150-250 users)
RAC User Load Tests (2500–4500 users)
Sun-PostgreSQL win shows benefit of Solaris over Linux. Pretty interesting trends happening in open-source databases.
You can read more at: http://www.computerworld.com.au/index.php/id;413111662
This also shows why you can't just look at over-optimized benchmarks link TPC-C, one needs to look at real factors in the datacenter. For TPC-C issues you see previous posts:
http://blogs.sun.com/bmseer/entry/judging_by_the_wrong_things
...and also some questionable(?) changes over time:
http://blogs.sun.com/bmseer/entry/ibm_tpc_c_more_hints
Database Performance Tuning on AIX, SG24-5511-01
AIX 5L Performance Tools Handbook, SG24-6039-01
Server Oriented System Tuning Info
Java Performance Tuning on Linux Servers
redhat.com | EX442 - System Monitoring and Performance Tuning Exam
Tuning Red Hat for maximum performance
Red Hat Enterprise System Monitoring and Performance Tuning (RH-442)
Tuning Red Hat for maximum performance
redhat.com Tuning Oracle Database 10g for ext3 file systems
Oracle 9i Database Performance Tuning - BASE Limited
Red Hat Directory Server Administrator's Guide- Chapter 14 Tuning ...
Oracle9iR2 on Linux- Performance, Reliability and Manageability ...
Copyright © 1996-2009 by Dr. Nikolai Bezroukov. www.softpanorama.org was created as a service to the UN Sustainable Development Networking Programme (SDNP) in the author free time. Submit comments This document is an industrial compilation designed and created exclusively for educational use and is placed under the copyright of the Open Content License(OPL). Site uses AdSense so you need to be aware of Google privacy policy. Original materials copyright belong to respective owners. Quotes are made for educational purposes only in compliance with the fair use doctrine.
Disclaimer:
Last modified: August 12, 2009