Performance and Tuning in Oracle
Monitoring for performance issues and doing regular maintenance of databases will keep them tuned and performing better.
Better-performing Systems
Planning the initial database design, monitoring the performance, and maintaining it are the proactive steps to achieving better-performing systems. This applies to any database environment. Along with proactive monitoring and tuning for performance, DBAs need to deal with performance issues that arise at times.
Oracle has a view of the sessions and a way to see the current statements that are running against the database. Looking at the queries running and validating the statistics that are currently on the tables are the beginning 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 alerts, 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 the statistics.
Indexes
Indexes exist to help speed up queries. Having proper columns indexed can reduce the logical I/Os for queries. There are costs associated with data changes when indexes are involved. Maintenance requirements should also be considered. The performance gains of adding an index should be more than the cost of maintaining it. Also, it is important to know that too many indexes can add to performance issues instead of resolving them. Hence, indexes should be used selectively and their usage should be monitored appropriately.
Index Monitoring
By enabling index monitoring, we can see which indexes are being used in Oracle. The owner of an index can alter the index to enable monitoring and leave it on for a set period. The v$object_usage table will show whether an index is used.
SQLPLUS> alter index IDX1_EMP_DEPT monitoring usage;
With this, the index is altered.
SQLPLUS> select empno from emp where deptno=10;
The rows are returned.
SQLPLUS> select index_name,used, monitoring
from v$object_usage;
INDEX_NAME USED MONITORING
—————————– ————- ————–
IDX1_EMP_DEPT YES YES
The v$object_usage table has two other columns that show the time when the monitoring is started and when it is stopped. Meanwhile, to end the monitoring of an index, we have to use this statement:
alter index index_name no monitoring usage
Index monitoring will not track how many times an 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 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 various index types affect data changes and improve SELECT statements will help us decide if the benefits of an index outweigh the costs of retaining it in place.
- Primary Key Indexes: The primary key index is created for a table when a constraint is added, and we 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. They are useful for large tables even with simple functions like UPPER to do string comparisons.
- Indexes for Views: Views use indexes on their associated tables to build information, but there might be a need for an index for selecting from a view. Oracle has materialized views, similar to views, but are a snapshot of the data. These 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: They are used as a nice little trick to spread out index blocks for a sequenced column. Reversing the numbers will allow 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 we 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 tables, such as LIST, HASH, RANGE, and COMPOSITE. The partition key is how the table is partitioned. We can create partitioned indexes for these tables. An index can be a locally partitioned index based on the partition key and setup used 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 an index.
Locking
Holding locks on a database object will also cause another concurrent session to wait. Waiting to acquire a lock or perform a transaction could even cause blocking, depending on the locks required to perform a select transaction.
A deadlock is when two or more users are waiting to access data locked by each other. When the deadlock occurs, Oracle chooses the victim, 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 we should check for performance unless 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, Oracle Database would 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.
Current Activity Views
Oracle has various system views that provide the current session and wait for 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. We can join this with another view to see all the queries that a session is running.
Activity Monitors
There are statistics gathered as part of Automatic Workload Repository (AWR) to provide reports for analyzing the health of the database and look for performance issues. Historical views are based on snapshots that have been gathered. Viewing these areas of activity can help us troubleshoot performance issues by pointing to an area that might be responding slowly or 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.
Waits
Another area to check into Oracle for performance issues is waiting for events. This information will be available in OEM views. The SQL*Net message from the 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 the data from the database.
Automatic Workload Repository
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 it in AWR metadata tables (WRM$_) and historical statistics tables (WRH$_).
In Oracle Database 11g, these reports and information are part of Oracle Diagnostic Pack, which provides an automated gathering of the information and ways to pull the information out of the workload and also history tables for review and evaluation of performance issues. We can also create baseline templates to compare the information.
AWR Reports
AWR reports have information about different waits. The reports list the top waits, providing a quick way to determine the areas where to start looking for bottlenecks.
AWR reports can be viewed in OEM. The reports are based on snapshot times. If different intervals are needed, different reports can be generated. In OEM, we can view the details in the list or see the reports in the 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 an 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; non-zero values indicate the session’s last wait time.
Library Cache for SQL Statements
Oracle will first look in the library cache for code 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.
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 the code in the library cache is that the cache is too small to hold all statements; if there are a lot of ad-hoc statements, it might be hard to hold all of them. Another reason for it could be due to the use of literal values instead of bind variables in the code.
There is also a parameter that can help us make the code seem similar enough so 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 the code can be reused.