Oracle DBA Best Practices

The Oracle’s DBA Best Practices Guide is intended to provide information regarding Oracle’s products. However, Oracle warns users that the guide does not represent any form of commitment from Oracle on the delivery or functionality or any other commitment. The guide should also not be used as a reference in order to make purchasing decisions as it is used solely for educational purposes.

Top 3 DBA Activities

The DBA best practices guide highlights that the top 3 DBA activities include performance diagnostics, SQL optimization, and space management. Each of these sectors is discussed separately in the guide to provide sufficient insight about each activity.

Performance Diagnostics

Performance diagnostics is nothing but the process of reducing the total DB time to minimum in an Oracle database. DB time is a measure of the time that is required to perform certain user level calls in the database. This is often expressed in microseconds. This includes the CPU time, IO time, and the non-idle wait time. So, if ‘n’ number of active sessions are being carried out on your database, the total DB time is nothing but the sum of all the DB times for each of the ‘n’ active sessions on your database.

Any session that is currently spending some time with respect to a database call is defined as an active session. The ratio of the DB time to the elapsed time is known as average active sessions. This was previously known as DB load. The document also highlights the use of ADDM or Automatic Database Diagnostic Monitor to analyze the performance throughout a database. The guide also addresses manual performance diagnostics for your database. While the ADDM runs autonomously whenever required, manual performance diagnostics requires you to observe the active sessions graph and determine the source of the problem by analyzing the peaks in the graph.

Apart from these functions, you can use targeted performance analysis using different dimensions. The various dimensions that could be used in targeted performance analysis include time, session ID, SQL ID, client ID, wait class, module, action, and service. This will generate a series of reports that you can use to analyze and arrive at the source of the problem that is increasing your DB time.

With ADDM you can also create a comparative type performance analysis. You can make use of the AWR or Automatic Workload Repository and create a baseline that can be used for the performance analysis. You can also set thresholds so that you will be alerted on time whenever the DB time increases. In addition, you can also use this method to monitor the performance, compare the reports, workload variations, and a good number of other metrics that can help you in effectively reducing the effort you would otherwise have to put into a performance diagnosis.

SQL Optimization

SQL statements are often used to retrieve data from the database. The same result can often be achieved through the use of SQL queries. These queries also play an important role in optimizing the database performance and it is essential to tune these queries according to your requirements. One of the highlights of the Oracle DBA is the ability to understand SQL execution using the real-time SQL monitoring tool. This tool shows the SQL processes taking place during an execution. It also allows you to automatically monitor SQL queries that have been running for a long time. The tool provides insights on SQL execution by monitoring statistics on the global level of execution, parallel level of execution, and on the operational plan level. The tools also provide reports that can help you to optimize the SQL queries for best performance.

The Oracle DBA contains yet another useful feature in SQL tuning advisor that can help you in tuning the SQL queries as per your requirements. You can run a comprehensive analysis of how the SQL queries are being executed on the basis of the statistics obtained from the tuning advisor. You can also access the structural analysis, design the SQL analysis, and plan the tuning using the tuning advisor’s recommendations.

The Oracle DBA can be set at two different modes of resource consumption; limited and resource consumption comprehensive. The limited mode, as the name suggests, consumes a minimal amount of resources as opposed to the comprehensive mode where the SQL profiling may consume even some non-trivial resources. Similarly, it also offers two different modes for tuning SQL remote and live. These modes can help you tune the SQL queries based on different situations.

The remote mode comes in handy during situations when the system is unable to spare any additional resources or when the resources consumed by the SQL statements and queries are quite significant. The live tuning mode, on the other hand, can be used in both situations. You can run it using the limited mode when the system cannot spare additional resources and the comprehensive mode in other circumstances. Oracle recommends the use of comprehensive mode along with Live Tuning.

Automatic SQL tuning is also available in the Oracle DBA and it is a highly intuitive tool. This can capture high load SQL automatically and can also tune the SQL queries and statements by creating SQL profiles. This process is highly useful as it does not modify the application in any way. In addition to this, the tool can also validate SQL profiles and implement SQL plans automatically. You can get an automatic analysis of reports and the tool also runs during the maintenance window making it one of the most innovative features in the DBA.

Space Management

Space management in a database management system such as Oracle is about understanding the storage mechanisms offered by the Oracle DBA and making use of the best option available to meet your storage demands. Oracle segregates this process into two main subdivisions known as permanent tablespace management and temporary tablespace management. Both these techniques optimize the space by either partially or completely eliminating fragmentation. The end result is optimized access to data and improved transaction performance of your DBA.

Permanent tablespace management takes place by making use of the bitmaps that can be found within the file headers using locally managed tablespace for extent management. Extent management can also be achieved by two methods – uniform allocation, where all extents are of the same size, and auto allocation, where the extent size varies. Auto allocation is the recommended approach toward extent management. You can also use permanent tablespace management for segment management using the automatic segment space management feature. This feature generates a segment free space that can be managed using bitmaps. This also leads to minimum internal fragmentation and superior performance.

Internal fragmentation in segment management is nothing but the fragmented space that often exists within a segment. This can have a significant impact on the performance as the fragmented space can slow down certain access paths. This problem can be solved by using an online segment shrink. The automatic segment advisor, on the other hand, evaluates the segments for fragmentation and provides the much required recommendations to help you get rid of any internal fragmentation you may come across.

Temporary tablespace management comes in handy when managing the space for temporary data in your DBA. This includes managing space for data that is generated by operations such as bitmap merges, creating bitmap indexes, sorting, hash joins, and so on. The data from such operations generally persists only for the duration of the session and as a result, media and instance recovery is not required. However, it is important to take a look at the concurrency of space management in these operations. Using locally managed temporary tablespace for temporary data allows for several advantages such as high concurrency in managing the space and also allows the space to be shrunk using commands such as SHRINK TEMPFILE or SHRINK SPACE.

Other than this, the guide also mentions and provides several highly useful and intuitive best practices for SQL optimization, permanent tablespace management, temporary tablespace management, tablespace management using RAC and much more. In addition to this, the guide also highlights how the database control or the grid control tool can be used to manage the entire database efficiently. Additionally, the guide also contains the schedules of the campground demos and recommended sessions to better understand database management in Oracle. These demos and sessions can be extremely useful when it comes to better understanding the features of the Oracle DBA. Oracle also highlights the top 10 best practices for DBA activities as follows:

1. Using enterprise manager for database management
2. Using locally managed tablespace for temporary purposes
3. Using auto allocate and automatic segment space management or ASSM for managing permanent tablespace
4. Using SQL performance analyzer
5. Automatic statistics collection
6. SQL tuning advisor to tune up SQL queries and statements
7. Real time monitoring to understand SQL execution
8. Making use of AWR baselines in performance analysis
9. Using ASH for targeted performance analysis
10. Using the ADDM for performance diagnostics

About the Author

+Seth Williams has been writing with Firebox Training http://www.fireboxtraining.com since July, 2011. He writes on many topics across IT programming, training and developments in the industry. Through spreading wisdom across forums and tech blogs, Seth has realized an open source approach to training professionals across the globe is the way to go.