Performance and Tuning
Monitoring for performance issues and doing the regular maintenance against the database will keep the database tuned and running faster.
4.1 Better-Performing Systems
Planning the initial database design, monitoring, and maintenance are the proactive steps to achieving better-performing systems. This applies to any database environment. Along with the proactive monitoring and tuning for performance, DBAs need to deal with performance issues that arise.
Oracle has a view into the sessions and a way to see the current statements that are running against the database. Looking at the queries running and validating that statistics are current on the tables might be first steps. With the cost-based optimizer, current statistics are important for the queries to choose the right plan. In OEM alerts, waits are shown with the alert, and OEM provides a list of top queries running and also shows if any process is being blocked. In Oracle, blocking normally is checked after figuring out what is running and validating statistics.
Indexes exist to help speed up queries. Having the proper columns indexed can reduce the logical I/Os for queries. There are costs associated with data changes when the indexes are involved. The maintenance requirements should be considered. The performance gains of adding the index should be more than the cost of maintaining the index. Also, too many indexes can add to performance issues instead of resolving them. Indexes should be used selectively and their usage monitored.
- Index Monitoring
By enabling index monitoring, you can see which indexes are being used in Oracle. The owner of the index can alter the index to enable monitoring, and leave monitoring on for a set period. The v$object_usage table will show whether or not the index is used.
SQLPLUS> alter index IDX1_EMP_DEPT monitoring usage;
SQLPLUS> select empno from emp where deptno=10;
SQLPLUS> select index_name,used, monitoring
INDEX_NAME USED MONITORING
—————————– ————- ————–
IDX1_EMP_DEPT YES YES
The v$object_usage table has two other columns that show the time the monitoring was started and stopped. To end the monitoring of the index, use this statement:
alter index index_name nomonitoring usage
Index monitoring will not track how many times the index is used, but it does offer a way to find out if there are unused indexes on a table.
- Index Types
Indexes are definitely a useful tool for improving access to the data in the database. Understanding which type of index is being used and how to improve that index will help in performance tuning. Knowing how the various index types affect data changes and improve SELECT statements will help you to decide if the benefits of the index outweigh the costs for putting it in place.
Primary Key Indexes – The primary key index is created for a table when a constraint is added, and you can either use an existing index or create a new one.
Function-Based Indexes – Oracle’s function-based index type can dramatically reduce query time. Function-based indexes can be useful for large tables even with simple functions like UPPER to do string comparisons.
Indexes for Views – Views use the indexes on their associated tables to build the information in the view, but there might be a need for an index for selecting from the view. Oracle has materialized views, which are similar to views but are a snapshot of the data. Oracle materialized views can have functions and aggregations, along with subqueries and other views, including self-joins.
Bitmap Indexes – Bitmap indexes are stored differently than b-tree indexes. Instead of storing the row ID, a bitmap for each key is used. Because of this, these indexes are typically smaller in size and are useful for columns that have a low cardinality.
Reverse Key Indexes – Reverse key indexes are a nice little trick to spread out index blocks for a sequenced column. Reversing the numbers will allow for the index to have different beginning values and use different blocks in the index b-tree structure. This is especially useful for RAC environments. When you are doing inserts, the reverse index will minimize the concurrency on the index blocks.
Partitioned Indexes – Partitioning is a useful way to tune a large database environment. Oracle offers options for partitioning table, such as LIST, HASH, RANGE, and COMPOSITE. The partition key is how the table is partitioned. You can create partitioned indexes for these tables. The index can be a local partitioned index based on the partition key and set up for each partition. Local indexes are easier to manage because they are handled with each partition, as partitions might be added, dropped, or merged.
Invisible Indexes – Invisible indexes are hidden from the optimizer, but not from being maintained, so as rows are changed. One reason to use an invisible index is to test the performance of the queries without the index.
Holding locks on a database object will also cause another concurrent session to wait. Waits to acquire a lock or perform a transaction could even cause blocking, depending on the locks required to perform a select or transaction.
A deadlock is when two or more users are waiting to access data locked by each other. When the deadlock occurs, Oracle chooses a victim and rolls back the transaction, and allows the other process to continue.
Because of how Oracle handles locking, blocking is not always the first area that I check for performance, unless I know that the application is trying to explicitly handle the locking outside Oracle. Access outside of the application, such as using query tools for ad hoc queries, could open a transaction, and since the flow of the query is waiting on the user, the Oracle database will also wait on the user and hold onto the locks. So, if an UPDATE, INSERT, or DELETE statement is open in such a tool, there is no auto commit that will release the locks. If the user does not issue a commit or rollback, this would leave an uncommitted transaction open, which could block others
4.4 Current Activity Views
Oracle has various system views that provide current session and wait information. These are very helpful for performance tuning and troubleshooting.
- Current Sessions
Obviously, when there are performance issues, it is necessary to take a look at the current sessions on the database. There is no sp_who, sp_who2, or sp_lock in Oracle, but there is the v$session view. This view shows which sessions are active. You can join this with another view to see which queries a session is running.
- Activity Monitors
There are statistics that are gathered as part of the Automatic Workload Repository to provide reports for analyzing the health of the database and looking for performance issues. The historical views are based on snapshots that have been gathered. Viewing these areas of activity can help you to troubleshoot performance issues by pointing to an area that might be responding slowly or be experiencing an overload, such as too many physical I/Os or hard parsing of SQL statements.
The Instance Activity section shows values since the database has been up and running or back until the last snapshot that is available.
Another area to check in Oracle for performance issues is wait events. This information will be available in the OEM views. The SQL*Net message from client event is the wait for the client to tell the database server to do something. It is just waiting for instructions, and really isn’t contributing to issues. There might be applications that open sessions and then just wait for responses before getting data from the database.
4.5 Automatic Workload Repository
The Automatic Workload Repository (AWR) contains significant information that can be helpful when it comes to tuning the database environment. The database takes regular snapshots to get information about the database settings and the workload in the environment, and stores them in the AWR metadata tables (WRM$_) and historical statistics tables (WRH$_).
In Oracle Database 11g, these reports and information are part of the Oracle Diagnostic Pack, which provides automated gathering of the information and ways to pull the information out of the workload and history tables for review and evaluation of performance issues. You can also create baseline templates to be able to compare information.
- AWR Reports
AWR reports have information about the different waits. The reports list the top waits, providing a quick way to determine the areas that might be of interest or where to start looking for bottlenecks.
The AWR reports can be viewed in OEM. The reports are based on the snapshot times. If different intervals are needed, different reports can be generated. In OEM, you can view the details in the list or see the reports in HTML format.
- Active Session History View
The Active Session History (ASH) view has information about waits and events based on the sessions that are occurring in the database.
The TIME_WAITED column shows the actual time waited for the event, and will be updated when the event is completed. The WAIT_TIME column information matches up with the v$session_wait view. When the wait time is shown as zero, then the session is currently waiting; nonzero values indicate the session’s last wait time.
- Library Cache for SQL Statements
Oracle will first look in the library cache for code that is to be executed against the database, so there is no additional load into memory if the code is already there. The plans are also available there, so it is beneficial to be able to reuse SQL that is available in the library cache.
The AWR reports show a library cache hit ratio to indicate how much of the code is found in the cache and available for reuse. One reason for not finding code in the library cache is that the cache is too small to hold all of the statements; if there are a lot of ad hoc statements, it might be hard to hold all of the statements. Another reason could be due to the use of literal values instead of bind variables in the code.
There is also a parameter that can help make code seem similar enough that it can be reused: CURSOR_SHARING. This parameter can be set to one of the following:
■ EXACT This makes the code match exactly. Using this value will result in either a large library cache/shared pool or a very low hit ratio of the library cache if literal values are used in the code and can’t be matched up.
■ FORCE This will force a substitute of a literal into a bind variable to reuse the code.
■ SIMILAR This will allow Oracle to decide what to bind, so that code can be reused.