oracle database performance tuning case study

Dynamic Oracle Performance Analytics pp 141–173 Cite as

Case Studies

• Roger Cornejo 2  
• First Online: 07 December 2018

417 Accesses

In the previous chapter, I provided a framework for making the many decisions necessary when implementing the DOPA process. The discussion focused upon how you would choose to run the analysis, the various parameters, and views you would choose. In this chapter, I’ll lead you through several real examples.

This is a preview of subscription content, log in via an institution .

Buying options

• Available as PDF
• Read on any device
• Instant download
• Own it forever
• Available as EPUB and PDF
• Compact, lightweight edition
• Dispatched in 3 to 5 business days
• Free shipping worldwide - see info

Tax calculation will be finalised at checkout

Purchases are for personal use only

Author information

Authors and affiliations.

Durham, NC, USA

Roger Cornejo

You can also search for this author in PubMed   Google Scholar

Rights and permissions

Reprints and permissions

Copyright information

© 2018 Roger Cornejo

About this chapter

Cite this chapter.

Cornejo, R. (2018). Case Studies. In: Dynamic Oracle Performance Analytics. Apress, Berkeley, CA. https://doi.org/10.1007/978-1-4842-4137-0_8

Download citation

DOI : https://doi.org/10.1007/978-1-4842-4137-0_8

Published : 07 December 2018

Publisher Name : Apress, Berkeley, CA

Print ISBN : 978-1-4842-4136-3

Online ISBN : 978-1-4842-4137-0

eBook Packages : Professional and Applied Computing Professional and Applied Computing (R0) Apress Access Books

Share this chapter

Anyone you share the following link with will be able to read this content:

Sorry, a shareable link is not currently available for this article.

Provided by the Springer Nature SharedIt content-sharing initiative

• Publish with us

Policies and ethics

• Find a journal
• Track your research

• Skip to content
• Accessibility Policy
• Oracle blogs
• Lorem ipsum dolor

Latest insights, trends and updates on multicloud observability and management

How Database Performance Tuning Best Practices Will Help You Perform Better

Most DBAs are painfully familiar with what can be an onerous task, database performance tuning. Using the automation and the integrated features of Enterprise Manager can help relieve that pain. In a recent webinar, Alfredo took us through how to use the Oracle Enterprise Manager Diagnostics and Tuning options to speed the troubleshooting process including recommended resolutions.  If you like what you see, you may appreciate the Deep Dive Series on Enterprise Manager . These webcasts have been popular and rewarding to be a part of. Attendance has been strong, and interactions on Q&A have been active, in-depth, technical topics, evidence to the value of having experts online answering your questions during the webinar. Download the presentation , view the YouTube recording  (56:07) of it and/or read the rest of this blog for a great summary.

Database Diagnostics Pack You can access Diagnostics and Tuning Packs functionality through Database Server APIs, SQL Developer, Enterprise Manager Express and maybe most frequently and most comprehensively, through the Enterprise Manager Cloud Control console where the workflow is intuitive and visual.

What you get is pre-built automation.

It’s real-time diagnostics and monitoring of all your subscribed databases. Yes, many features of these packs you can get in other ways, including writing your own scripts, which many do. The vast majority of those responsible for running many Oracle Database Enterprise Edition databases have found it’s more efficient and effective to use Enterprise Manager (EM). You may know Enterprise Manager is built in collaboration with the Database development team, which includes having core functionality built-in the Oracle Database kernel and exposed through the EM interface. Building on that deep information gathering is the efficiency of having the Diagnostics and Tuning Packs, and Real Application Testing integrated so they work together as one in a find, fix, and validate workflow.

A foundation for identifying relative performance is DB Time. Everything builds from there. The Automatic Workload Repository (AWR) provides a built-in, automatic performance statistics dataset. Typically you can compare a good period to a troubled period and compare the AWR reports. The AWR warehouse continues to keep AWR reports on a regular cadence. A very popular report most DBAs use AWR reports to get status and to do comparisons.  Alfredo goes through a few examples. Use Automatic Database Diagnostic Monitor (ADDM) to get recommendations. It’s designed using Oracle’s best tuning experts. While AWR covers the entire system, there’s Active Session History (ASH) which looks at an individual session. I’ll leave it to Alfredo to share his insights in the recorded session and you’ll see his slides as well. Suffice it to say the tools cover diagnostics for every database, including Exadata.

Database Tuning Pack

More Automation. And Integration.

The Database Tuning Pack provides automatic and deterministic SQL tuning and It’s core functionality is built into the Oracle Database kernel and exposed through the EM console. As I mentioned above, and it’s worth mentioning again, it’s seamlessly integrated with the Diagnostics Pack, Real Application Testing and Automatic Tuning Optimizer functionality. This integration is one of the strengths of EM and why these packs are almost always used together.

Comprehensive Toolset

Both the Diagnostics and Tuning packs have been a staple for DBAs for over a decade. That’s not to say they haven’t evolved quite a bit over the years. Some of these tools might look familiar, and some have updates and new ones. You’ll see more in the webinar on each feature in more detail where we go through a number of problems and how to use the tools to evaluate and tune both performance and storage space.

Integrated To Meet DBA Workflows Find, Fix, and Validate

I didn’t go into the Real Application Testing option, but it’s a good example of these tools integrated to simplify and speed the work entailed in finding, fixing, and validating fixes before pushing them live into production. This is shown below, each pane, another tool integrated together.

You can jump forward to the demo recording . It’s short but illustrative. 

Business Benefits

I like mentioning this new report , which did a great job of gathering metrics from a large number of our customers through a survey and in-depth interviews. You’ll find detailed analysis and quantified benefits laid out in a well-written report and charts. Like with any project, there is always someone who has to make tradeoffs on what to do and what not to. We’ve helped many companies go through an evaluation to help make a business case.

We have experts in the field who are versed in doing a consult on this very topic. If you are interested, contact your Oracle sales rep and let them know you are interested.

These are some of the resources made available in the presentation.

• Enterprise Manager Page on Oracle.com
• Database 2 Day + Performance Tuning Guide (Docs)
• Enterprise Manager Groundbreakers Community
• Enterprise Manager Cloud Control (Docs)
• Download the presentation

Join the community

Follow us on Twitter.

Subscribe to our YouTube channel

Timothy Mooney

Director, product marketing.

Experience across business development, product management, product marketing in computer hardware to cloud services.

Most DBAs are painfully familiar with what can be an onerous task, database performance tuning. Using the automation and the integrated features of Enterprise Manager can help relieve that pain. In a recent webinar, Alfredo took us through how to use the Oracle Enterprise Manager Diagnostics and Tuning options to speed the troubleshooting process including recommended resolutions.  If you like what you see, you may appreciate the Deep Dive Series on Enterprise Manager . These webcasts have been popular and rewarding to be a part of. Attendance has been strong, and interactions on Q&A have been active, in-depth, technical topics, evidence to the value of having experts online answering your questions during the webinar. Download the presentation , view the YouTube recording  (56:07) of it and/or read the rest of this blog for a great summary.

Database Diagnostics Pack \n You can access Diagnostics and Tuning Packs functionality through Database Server APIs, SQL Developer, Enterprise Manager Express and maybe most frequently and most comprehensively, through the Enterprise Manager Cloud Control console where the workflow is intuitive and visual.

It’s real-time diagnostics and monitoring of all your subscribed databases. Yes, many features of these packs you can get in other ways, including writing your own scripts, which many do. The vast majority of those responsible for running many Oracle Database Enterprise Edition databases have found it’s more efficient and effective to use Enterprise Manager (EM). You may know Enterprise Manager is built in collaboration with the Database development team, which includes having core functionality built-in the Oracle Database kernel and exposed through the EM interface. Building on that deep information gathering is the efficiency of having the Diagnostics and Tuning Packs, and Real Application Testing integrated so they work together as one in a find, fix, and validate workflow.

A foundation for identifying relative performance is DB Time. Everything builds from there. The Automatic Workload Repository (AWR) provides a built-in, automatic performance statistics dataset. Typically you can compare a good period to a troubled period and compare the AWR reports. The AWR warehouse continues to keep AWR reports on a regular cadence. A very popular report most DBAs use AWR reports to get status and to do comparisons.  Alfredo goes through a few examples. Use Automatic Database Diagnostic Monitor (ADDM) to get recommendations. It’s designed using Oracle’s best tuning experts. While AWR covers the entire system, there’s Active Session History (ASH) which looks at an individual session. I’ll leave it to Alfredo to share his insights in the recorded session and you’ll see his slides as well. Suffice it to say the tools cover diagnostics for every database, including Exadata.

The Database Tuning Pack provides automatic and deterministic SQL tuning and It’s core functionality is built into the Oracle Database kernel and exposed through the EM console. As I mentioned above, and it’s worth mentioning again, it’s seamlessly integrated with the Diagnostics Pack, Real Application Testing and Automatic Tuning Optimizer functionality. This integration is one of the strengths of EM and why these packs are almost always used together.

Both the Diagnostics and Tuning packs have been a staple for DBAs for over a decade. That’s not to say they haven’t evolved quite a bit over the years. Some of these tools might look familiar, and some have updates and new ones. You’ll see more in the webinar on each feature in more detail where we go through a number of problems and how to use the tools to evaluate and tune both performance and storage space.

I didn’t go into the Real Application Testing option, but it’s a good example of these tools integrated to simplify and speed the work entailed in finding, fixing, and validating fixes before pushing them live into production. This is shown below, each pane, another tool integrated together.

You can jump forward to the demo recording . It’s short but illustrative. 

I like mentioning this new report , which did a great job of gathering metrics from a large number of our customers through a survey and in-depth interviews. You’ll find detailed analysis and quantified benefits laid out in a well-written report and charts. Like with any project, there is always someone who has to make tradeoffs on what to do and what not to. We’ve helped many companies go through an evaluation to help make a business case.

Previous Post

How to Automatically Fix Tablespace Full Alerts Using Corrective Actions

Updated: announcing new enterprise manager workshops available on livelabs, resources for.

• Analyst Reports
• Cloud Economics
• Corporate Responsibility
• Diversity and Inclusion
• Security Practices
• What is Customer Service?
• What is ERP?
• What is Marketing Automation?
• What is Procurement?
• What is Talent Management?
• What is VM?
• Try Oracle Cloud Free Tier
• Oracle Sustainability
• Oracle COVID-19 Response
• Oracle and SailGP
• Oracle and Premier League
• Oracle and Red Bull Racing Honda
• US Sales 1.800.633.0738
• How can we help?
• Subscribe to Oracle Content
• © 2022 Oracle
• Privacy / Do Not Sell My Info

Beginning Performance Tuning: Diagnose the Past

Take the next step in diagnosing oracle database performance issues..

November/December 2012

In “ Beginning Performance Tuning ,” in the July/August 2012 issue of Oracle Magazine , I demonstrated the basics of diagnosing performance issues in Oracle Database. In this article, I will show how to take performance tuning to the next level—identifying patterns and measuring resource utilization. As was the case with the previous article, you can create this article’s examples by executing the setup script —setup.sql.

Recall from “Beginning Performance Tuning” that you can identify why a session is slow by looking at what event it is waiting for right now in the V$SESSION view. This technique is effective when you are able to peek into the view while the issue is occurring, but what if a user reports that there was an increase in the response time of a session but it is back to normal now ? Because the issue is no longer present, the session may appear to be working productively with the STATE column value of WAITED KNOWN TIME. What’s worse, the current wait event shown in the V$SESSION view may be something unrelated to what the user encountered earlier. To diagnose this issue, you need to know all the different events the session had to wait for—not just what it is waiting for now . How difficult is it to get that information?

Fortunately, it’s quite trivial. There is another view—V$SESSION_EVENT—that shows the details of all the wait events the session has ever waited for. Here are the important columns in that view:

SID: the session ID

EVENT: the name of the wait event experienced by the session

TOTAL_WAITS: the total number of times the session had to wait for that wait event

TIME_WAITED: the total length of time the session had to wait for that event, recorded in cs (centiseconds [hundredths of a second])

AVERAGE_WAIT: the average length of time (also in cs) the session waited for that event

MAX_WAIT: the maximum length of time (in cs) the session waited for that event

Let’s examine the usage of the view with an example. Make sure you have set up the example as described in “Test Case Setup.” Connect to the Oracle Database instance as ARUP, and execute the test1.sql script, also available in “ Test Case Setup .” (Note that the script will take some time to complete). While that script is running, connect as SYS from a different session and find the SID of the ARUP session as follows:

Now display the session events of SID 37 by executing the script in Listing 1 . Because the V$SESSION_EVENT columns report time in centiseconds, I have multiplied them by 10 to show them in milliseconds (ms), a unit of measure that is a bit more familiar. Study the output carefully—it shows the various events the session waited for earlier, not what it is experiencing now. You can see that the session has waited for one event in particular: “kfk: async disk IO.” The session has waited 904,818 times for this event, but it has waited for a total of only 3,050 ms. The average wait shows zero, but it is simply because the average wait was so short that it couldn’t be shown within the two digits after the decimal point. So although the total number of waits for this event was high, it added only 3,050 ms to the overall time of the session—an insignificant number. You can thus rule this wait event out as a cause of delay for the session.

Code Listing 1: History of wait events in a specific session

In this case, you should focus your attention on the event that caused the session to wait for the maximum time. From the output in Listing 1 , you can see that session 37 (SID = 37) waited 513,810 ms, or more than 8.5 minutes, for the “direct path read temp” event. Every time the session waited for this event, it waited 1.1 ms on average, so if you can reduce the time for this event, you can reduce the overall time for the session. Looking at the session event history enables you to identify the biggest contributors to the delay in the session, whether they are currently affecting the session or have already affected it.

Did you notice the column for the maximum time waited: “Max Wait (ms)”? Why is that information useful? You see, the average wait time does not tell the whole story. Consider the “SQL*Net message from client” event in the output in Listing 1 . The session waited 20 times with an average wait time of 481 ms for that event. Does that mean that the session waited for approximately 481 ms at each of the 20 occurrences or that the session waited a very short time for most of the event instances and a very long time for one event? The latter will skew the average to a high value but will indicate an isolated issue rather than a persistent one—the two possibilities lead to very different conclusions.

The “Max Wait (ms)” column shows the maximum time the session had to wait for one occurrence of this event. The value here is 9,619 ms, and because the total wait time was 9,620 ms, it appears that the session waited 9,619 ms on one occasion, leaving a 1 ms combined total for the other 19 occasions—a very small wait each time. Considering the single incidence of a large wait, this event should not be a general cause of concern. On the other hand, had you seen a maximum time close to the average time, you could surmise that all occurrences had to wait about the same amount of time. In such a case, reducing the time for this event would likely apply uniformly to all occurrences and consequently reduce the overall elapsed time.

Although the V$SESSION_EVENT view shows what the session waited for earlier, it doesn’t show when . That information is visible in another view—V$ACTIVE_SESSION_HISTORY (part of the extra-cost Oracle Diagnostics Pack)—which is beyond the scope of this article.

Although wait events are great for helping with understanding the speed bumps the sessions experience, they do not show another important attribute of sessions: the use of resources such as CPU, I/O, and memory. A resource-hogging session deprives other sessions of the same resources, thus causing performance issues. When the root of the problem is that the session is consuming too much CPU, you should look at resource consumption—not the events waited for—by a session. Fortunately, finding that information is quite simple: it’s visible in a view named V$SESSTAT, which has three columns:

STATISTIC#: the ID of the statistic being captured (You can get the name of the statistic from the NAME column in another view—V$STATNAME—by using this ID.)

VALUE: the value of the statistic

Let’s see how to use this information with a performance problem example.

Suppose you’ve heard from several users that the performance is terrible across the board. Further, the UNIX system administrator has reported that both CPU and memory consumption are very high on the server and most of the consumption is by Oracle Database–owned processes. Sound familiar? From my experience, it’s the second-most-common performance issue in Oracle Database systems. You can reproduce this problem by running the test1.sql script, available in this article’s example scripts, as user ARUP.

To diagnose this resource issue, review the top resource-consuming processes by issuing the top command at the UNIX command prompt. Listing 2 shows the output of the top command.

Code Listing 2: Output of the top command

In the output in Listing 2 , you can see that the process with ID 5946 consumes the most CPU (100 percent) and memory (10 percent) and therefore should be the focus of your attention. To find out more about the process, enter the following command at the UNIX prompt:

The output shows the entire description of the process, which is clearly an Oracle “server process”—a process that is created by Oracle Database when a session is established—and that the process has been running for 1 minute and 52 seconds. The next question, then, is which Oracle Database session this process was created for. For that, you should look into another view—V$PROCESS—where the SPID column shows the server process ID. However, this view does not show the session information, so you need to join this view with the familiar V$SESSION view, as follows:

Once you know the SID, you can get everything you need to know about the session—the user who established the session, the machine it came from, the operating system user, the SQL it is executing, and so on—from the V$SESSION view. This script for getting the information was described in Listing 5 in “ Beginning Performance Tuning ,” in the July/August 2012 issue of Oracle Magazine . To find the SQL being run by session 37, use this query:

Here is the output:

This SQL is performing multiple Cartesian joins, so it’s no wonder it’s consuming so much CPU and memory.

Now that you have found the culprit, should you just go ahead and kill the session to release the CPU consumption? Not so fast. First, you need to know if the CPU consumption was recent or if the session has been chewing it up since the beginning. This is where the V$SESSTAT view comes in very handy—it shows the resource consumption (CPU in this case) by a specific session. To find out the CPU used by session 37, you would use the following query:

The output shows the number of CPU “ticks” that have been consumed by this session since it started. Considering that this session has been running for about two minutes, the CPU consumption is pretty high, so it is likely that this session has been consuming CPU all the time. Again, checking the session’s other details, such as the SQL it is executing, makes it fairly easy to understand why this is the case: the session is performing a multiple-table Cartesian join, which is bound to consume a lot of CPU. At this point, you may decide to kill the session to stop the high CPU consumption, or you may decide to let it run for now and fix the SQL later to avoid the Cartesian product.

Let’s revisit the current problem by checking the CPU consumption once again with this:

Now the result—the CPU used—is 69,724. Note that this number is larger than the number the last time I checked CPU usage (47,379). This is because the statistic value increases over time. The first time I checked CPU usage, I surmised that the multitable Cartesian product was to blame for that CPU consumption—but can I prove it? The answer is a resounding “Yes, I can, using other statistics.”

Listing 3 shows the query for gathering all the statistics for session 37. In the output, note the “table scan rows gotten” statistic value: 1.0236E+10—about 10 billion rows! This is indeed a very high number of rows to be accessed by one session in two minutes. The value for the “consistent gets” statistic is 25,898,543—about 25.9 million blocks read from the buffer cache. The high number of buffer gets takes up a considerable amount of CPU.

Code Listing 3: All session statistics

Another cause of CPU consumption is the parsing of SQL statements. In the output in Listing 3 , note the “parse count (total)” statistic, a very high number at 143,292. In plain English, it means that the session had to parse—not just execute—SQL statements that many times in about two minutes, which is quite unusual. Examine the SQL statement executed by the session (shown in the script test2.sql script in “ Test Case Setup ”). You can see that it creates distinct literal SQL statements. Each literal SQL statement needs to be parsed, which is something you have confirmed by looking at the parse count. Therefore, you surmise from the Listing 3 output that there are two causes of high CPU usage for this session: a high number of buffer gets and a high number of parses.

In Listing 3 , also note two other sets of statistics: “session pga memory max” and “session uga memory max,” which indicate the total memory consumed by the session. The very high numbers explain the high memory consumption by the Oracle server process that you noticed in the output of the operating system top command earlier. If you want to reduce the CPU and memory consumption of the server, you need to make sure the session consumes fewer of these resources, by appropriately modifying the SQL statement it issues.

Occasionally you may have a performance issue that will not appear as clearly at the OS level as CPU and memory consumption. One such case is redo generation by the database instance, which, in turn, increases both the rapid switching of redo logs and the creation rate and number of archived logs. This may cause an increase in overall I/O on file systems (or Oracle Automatic Storage Management disk groups), causing a systemwide performance issue. To alleviate this type of issue, you need to locate the session or sessions that caused the generation of high amounts of redo, but looking at OS metrics will not provide any insights into the offending session. In this case, you need to look at the sessions responsible for most of the load: the sessions generating maximum redo. Again, this information is available quite easily in the same V$SESSTAT view. The following query shows the sessions generating the most redo:

It’s clear from the output that SID 13 produced most of the redo, followed by SID 10, and so on.

The Setup sidebar describes how to find the SQL statement that is generating the redo, provides a replacement SQL statement, and tests that new statement for redo generation.

physical reads: the number of database blocks retrieved from disk

db block changes: the number of database blocks changed in the session

bytes sent via SQL*Net to client: the bytes received from the client over the network, which is used to determine the data traffic from the client

These are just a few of the 604 such statistics available in the V$SESSTAT view. It is not possible to describe each one of them, but I hope you get the general idea of how to use statistics to see the various resources used by a session and focus on performance issues. (Note that there is another view—V$SYSSTAT—that shows the statistics of the entire instance.)

In this article, you learned about two very important sources of performance tuning information in Oracle Database: (1) the history of wait events experienced by sessions—visible in V$SESSION_EVENT, and (2) the resource consumption by session—visible in V$SESSTAT. From the history, you can find out why a session has waited in the past and for how long—very useful information for diagnosing performance issues when they are no longer present. The resource statistics show the consumption of various resources such as CPU, memory, and redo by a specific session—very useful for focusing on resource-hogging sessions. Using these two views and scripts mentioned in this article, you can resolve many performance issues encountered in Oracle Database instances. Happy tuning!

Arup Nanda has been an Oracle DBA since 1993, handling all aspects of database administration, from performance tuning to security and disaster recovery. He was Oracle Magazine’s DBA of the Year in 2003 and received an Oracle Excellence Award for Technologist of the Year in 2012.

DISCLAIMER: We've captured these popular historical magazine articles for your reference. Links etc contained within these article possibly won't work. These articles are provided as-is with no guarantee that the information within them is the best advice for current versions of the Oracle Database.

What is Performance Tuning in Oracle?

Performance Optimization

Performance Pulse Optimizer Pro Platinum Precision

Application Services

Application Development Service Application Modernization DevOps

Managed Services

Oracle Cloud Managed Services Database Managed Services Middleware Managed Services Operating System Support and Maintenance Application Managed Services

Cloud Transformation

Cloud Migration Cloud Assessment Cloud Strategy and Planning Cloud Security and Compliance Cloud Optimisation Oracle APEX Oracle BI Publisher OCI AWS

Professional Services

Cloud Database Middleware OS RPA

About Us Careers Life@SimpleLogic Blogs Case Studies Contact Us

Copyright © SIMPLE LOGIC 2009 - 2024 All Rights Reserved | Privacy Statement | Sitemap

• Performance Pulse
• Optimizer Pro
• Platinum Precision
• Oracle Cloud Managed Services
• Database Managed Services
• Middleware Managed Services
• Operating System Support and Maintenance
• Application Managed Services
• Cloud Migration
• Cloud Assessment
• Cloud Strategy and Planning
• Cloud Security and Compliance
• Cloud Optimisation
• Oracle Apex
• Oracle BI Publisher
• Application Development Service
• Application Modernization
• Case Studies
• Life@SimpleLogic

Max Upload Size:5mb - Types:pdf,doc

We use cookies to improve your experience on our website. By browsing this website, you agree to our use of cookies

• Engineering & Technology
• Computer Science

Top 7 Oracle Database Tuning Techniques and Case Studies

Related documents

Add this document to collection(s)

You can add this document to your study collection(s)

Add this document to saved

You can add this document to your saved list

Suggest us how to improve StudyLib

(For complaints, use another form )

Input it if you want to receive answer

How To Optimize Oracle Database Performance

Explore key strategies for optimizing Oracle Database performance in this concise guide. Learn about effective indexing, SQL query optimization, memory management, and more to enhance your database's efficiency and speed. Perfect for developers seeking practical, actionable tips.

💡 KEY INSIGHTS

• SQL Trace and TKPROF tools are pivotal for in-depth analysis and optimization of query performance.
• Effective memory management strategies, including SGA and PGA adjustments, are crucial for database efficiency.
• Indexing strategies should be tailored to specific data types and query patterns for maximum performance gains.
• Regular AWR reports provide invaluable insights for proactive system resource management and bottleneck identification.

Optimizing an Oracle Database can feel like navigating a complex labyrinth, but with the right strategies, it becomes a manageable task. This article offers practical tips and insights to enhance database performance, ensuring your applications run smoothly and efficiently. Whether you're a seasoned developer or new to Oracle, these guidelines will help streamline your database operations.

Identifying Performance Bottlenecks

Effective database tuning strategies, optimizing sql queries, memory management and allocation.

• Indexing For Performance

Monitoring And Managing System Resources

Workload distribution and balancing, frequently asked questions, analyzing slow queries, resource utilization, indexing efficiency, locks and latches.

Performance bottlenecks in an Oracle Database can drastically affect its efficiency. Identifying these bottlenecks is the first step towards optimization. Common areas to check include slow-running queries, inefficient database design, and resource contention.

Start by analyzing slow-running SQL queries. Tools like Oracle's SQL Trace and TKPROF can help. SQL Trace gathers detailed execution data, while TKPROF formats this data for analysis.

High CPU or memory usage often indicates a bottleneck. Oracle's Automatic Workload Repository (AWR) reports can be used to analyze resource utilization over time.

Inefficient indexing can slow down query performance. Reviewing the indexing strategy and ensuring indexes are properly used is crucial.

Database locks and latches contention can also be a bottleneck. Monitoring and resolving these contentions is essential for smooth operation.

Optimizing SQL Execution Plans

Memory management, table partitioning.

Database tuning is a critical aspect of maintaining optimal performance in Oracle Databases. It involves adjusting various parameters and structures to enhance efficiency.

One key area is the optimization of SQL execution plans. Using the EXPLAIN PLAN statement helps understand how Oracle executes a SQL query.

Proper memory management can significantly improve database performance. Adjusting the Oracle memory parameters like SGA and PGA is crucial.

Partitioning large tables can enhance query performance and manageability. It involves dividing a table into smaller, more manageable pieces.

Using Indexes Effectively

Avoiding full table scans, optimizing joins.

Optimizing SQL queries is a cornerstone of database performance tuning. Efficient queries reduce the load on the database and speed up response times.

Proper use of indexes can dramatically improve query performance. Ensure that your queries leverage indexes where appropriate.

Minimizing full table scans can significantly enhance performance. Use specific conditions in your queries to target indexed columns.

Efficient use of joins is crucial, especially in queries involving multiple tables. Opt for the most efficient join type based on your data.

Configuring SGA And PGA

Automatic memory management, monitoring memory usage.

Effective memory management is crucial for optimizing Oracle Database performance. Proper allocation and tuning of memory parameters can significantly enhance database operations.

The System Global Area (SGA) and Program Global Area (PGA) are key components of Oracle memory. Configuring these areas appropriately is vital.

Oracle's Automatic Memory Management (AMM) simplifies the configuration of SGA and PGA by automatically managing their sizes.

Regular monitoring of memory usage helps in identifying potential issues and tuning memory parameters effectively.

TIndexing For Performance

Creating indexes, choosing index types, monitoring index usage.

Proper indexing is a powerful tool for enhancing Oracle Database performance. Creating and using indexes effectively can drastically reduce query times.

Indexes should be created on columns frequently used in WHERE clauses or JOIN conditions. This improves retrieval speed.

Oracle offers several index types, including B-tree, bitmap, and hash indexes. Selecting the right type based on the data and query patterns is crucial.

Regularly monitoring index usage helps in identifying unused or inefficient indexes. Oracle provides views like v$object_usage for this purpose.

Using AWR Reports

Analyzing v$ views, tuning resource allocation.

Monitoring system resources is essential for maintaining optimal performance in Oracle Databases. Keeping track of CPU, memory, and I/O usage helps in identifying bottlenecks.

Automatic Workload Repository (AWR) reports are invaluable for monitoring database performance. They provide detailed information on system resource usage.

Oracle's V$ views, like V$SYSSTAT , V$SESSION , and V$SESSTAT , provide real-time data on system performance.

Based on the insights from monitoring, adjust resource allocation to optimize performance. This includes tuning memory, storage, and CPU resources.

Implementing Connection Pooling

Using oracle real application clusters (rac), load balancing queries.

Balancing the workload across an Oracle Database system is key to maintaining optimal performance. Proper distribution prevents any single resource from becoming a bottleneck.

Connection pooling is an effective way to manage database connections, especially in high-traffic environments. It allows for the reuse of connections, reducing overhead.

Oracle RAC allows multiple computers to run Oracle RDBMS software simultaneously. This setup provides high availability and scalability.

Distributing queries across multiple nodes or processors can significantly improve performance. Oracle offers various tools for query load balancing.

How often should I analyze and optimize my Oracle Database?

Regular analysis and optimization are crucial. Ideally, perform a review quarterly or after significant changes to your database or workload. Continuous monitoring helps identify issues early.

Can indexing slow down some database operations?

Yes, while indexes speed up query retrieval, they can slow down data modification operations like INSERT, UPDATE, and DELETE, as the indexes also need to be updated.

Is Automatic Memory Management (AMM) always the best choice for memory allocation?

AMM is beneficial for simplifying memory management, especially in environments with fluctuating

Let's test you

Which Strategy Is Most Effective for Reducing Query Retrieval Times in Oracle Database?

Continue learning with these oracle database guides.

• How To Install Oracle Database
• How To Create Oracle Sample Database
• How To Connect To Oracle Database Server
• How To Use PL/SQL In Oracle Database
• How To Implement Security In Oracle Database

Subscribe to our newsletter

Subscribe to be notified of new content on marketsplash..

Oracle Database 10g The Complete Reference by Kevin Loney

Get full access to Oracle Database 10g The Complete Reference and 60K+ other titles, with a free 10-day trial of O'Reilly.

There are also live events, courses curated by job role, and more.

CHAPTER 44Case Studies in Tuning

As described in Chapter 43 , performance tuning begins with design. In many cases, the core performance problem in a database application has been designed into the system. You may be able to identify the most resource-intensive parts of the application, focusing future tuning efforts on specific database parameters or SQL statements. In this chapter, you will see case studies related to different performance problems and their resolutions. The case studies present three common problem areas—environmental issues, problem queries, and application architecture—from an application tuner’s perspective.

Case Study 1: Waits, Waits, and More Waits

A large online transaction-processing system experienced significant performance ...

Get Oracle Database 10g The Complete Reference now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Don’t leave empty-handed

Get Mark Richards’s Software Architecture Patterns ebook to better understand how to design components—and how they should interact.

It’s yours, free.

Check it out now on O’Reilly

Dive in for free with a 10-day trial of the O’Reilly learning platform—then explore all the other resources our members count on to build skills and solve problems every day.

• Search Search Search …
• Search Search …

Unleashing the Power of Performance Tuning in Oracle: A Guide to Optimal Database Operation

In the dynamic world of databases, the performance of an Oracle database is critical to the smooth operation of applications and the overall efficiency of an organization. Performance tuning in Oracle is not just a task; it’s an art and a science that can unlock the true potential of your database.

The Significance of Performance Tuning in Oracle

Performance tuning in Oracle is the process of optimizing the database and SQL queries to ensure optimal response times and resource utilization. It goes beyond reactive fixes, focusing on proactively enhancing the efficiency and speed of database operations.

Key Components of Oracle Database Performance

1. sql query optimization.

Optimizing SQL queries involves fine-tuning the structure and execution plans to minimize response times and improve overall database performance.

2. Indexing Strategies

Effective indexing is crucial for fast data retrieval. Choosing the right indexes and periodically maintaining them is key to performance tuning.

3. Memory Management

Optimal memory allocation and management prevent resource contention, ensuring that the database operates smoothly even under heavy loads.

Common Performance Issues and Their Impact

1. slow query execution.

Slow queries can significantly impact application responsiveness and user experience.

2. Resource Contention

Contending for resources like CPU, memory, or disk I/O can lead to bottlenecks and degrade database performance.

3. Disk I/O Bottlenecks

Slow disk I/O operations can be a major hindrance to overall database speed, affecting read and write operations.

Strategies for Effective Performance Tuning

1. regular database monitoring.

Proactive monitoring helps identify performance issues before they become critical, allowing for timely intervention.

2. Query Rewriting and Refactoring

Optimizing poorly performing queries through rewriting or refactoring ensures efficient data retrieval.

3. Index Maintenance

Regularly reviewing and maintaining indexes prevents fragmentation and ensures their continued effectiveness.

Oracle Tools for Performance Tuning

1. oracle enterprise manager.

A comprehensive tool for monitoring and managing Oracle databases, providing insights into performance metrics.

2. SQL Tuning Advisor

Analyzes SQL statements and provides recommendations for improving their execution plans.

3. Automatic Workload Repository (AWR)

Captures a comprehensive set of performance-related data, enabling in-depth analysis and tuning.

Real-world Applications and Success Stories

1. enhanced application responsiveness.

Performance tuning has led to applications responding faster, resulting in improved user satisfaction.

2. Improved User Experience

Users experience faster load times and smoother interactions with applications, enhancing overall usability.

3. Cost Savings and Resource Optimization

Optimized performance translates to efficient resource utilization, leading to cost savings in hardware and maintenance.

Challenges in Performance Tuning and How to Overcome Them

1. balancing act between performance and resource utilization.

Finding the right balance between optimal performance and efficient resource utilization is a continuous challenge.

2. Managing Tuning in High-Transaction Environments

In high-transaction environments, tuning must be carefully managed to avoid disruptions to ongoing operations.

3. Adapting to Evolving Workloads

The dynamic nature of workloads requires constant adaptation in tuning strategies to ensure continued efficiency.

Future Trends in Oracle Performance Tuning

1. machine learning integration.

The integration of machine learning algorithms for predicting and optimizing performance is on the horizon.

2. Automated Performance Tuning

Automation will play a more significant role in identifying and implementing performance optimizations.

3. Cloud-Based Tuning Solutions

With the shift to cloud-based infrastructures, performance tuning solutions tailored for the cloud will become more prevalent.

User Testimonials

1. database administrator insights.

“Oracle performance tuning has become a cornerstone of our database management strategy. It’s a proactive approach that pays off in terms of reliability and speed.”

2. Developer Experiences

“As a developer, the impact of performance tuning is evident in the responsiveness of applications. It’s like giving our code a turbo boost.”

3. Business Impact

“The business impact of performance tuning goes beyond speed—it translates to cost savings, improved user satisfaction, and a competitive edge.”

Tips for DIY Performance Tuning

1. regular backups and rollbacks.

Before implementing changes, ensure you have backups and rollback plans to revert in case of unexpected issues.

2. Understanding Execution Plans

Develop a solid understanding of SQL execution plans to identify areas for improvement in query performance.

3. Utilizing Oracle’s Diagnostics and Tuning Packs

Take advantage of Oracle’s advanced diagnostic and tuning packs for in-depth analysis and recommendations.

Case Study: Transformational Impact of Performance Tuning

1. before tuning implementation.

Highlighting the challenges and inefficiencies faced by a company before adopting performance tuning strategies.

2. Post-Implementation Results

Detailing the positive changes, increased efficiency, and improved performance metrics post-implementation.

3. Key Takeaways

Summarize the key lessons learned and benefits gained from the performance tuning journey.

In conclusion, performance tuning in Oracle is not just about making your database faster; it’s about creating a foundation for reliable, efficient, and future-proof operations. Embrace performance tuning as a proactive strategy to ensure your Oracle database operates at its full potential.

Frequently Asked Questions (FAQs)

• The frequency depends on factors like workload changes and application updates, but it’s advisable to conduct regular reviews, at least quarterly.
• Yes, with careful planning and execution, performance tuning can be conducted with minimal impact on ongoing operations.
• No, performance tuning is relevant for databases of all sizes. Small databases can benefit from improved efficiency and faster response times.
• Indexing is crucial in performance tuning as it enhances data retrieval speed. Regularly reviewing and optimizing indexes is a key aspect of tuning.

You may be interested in:

Cloud ERP vs On-Premise ERP

SAP Vs Oracle:Which Is The Best ERP Program

Supply Chain Management

You may also like

SAP ECC Application

Introduction to SAP ECC Application In the fast-paced realm of business technology, SAP ECC Application stands as a beacon of efficiency and […]

APP Development Software

Introduction to APP Development Software In the ever-evolving landscape of technology, app development software stands as the cornerstone for businesses seeking to […]

SAP UI5 List: Your Guide to Building Efficient and Engaging User Interfaces

In the world of enterprise applications, user experience reigns supreme. And when it comes to displaying data in a clear and concise […]

How SAP Consulting Can Drive Organizational Success

In today’s highly competitive business landscape, organizations are constantly seeking ways to optimize their operations, enhance efficiency, and gain a competitive edge. […]

Title and Copyright Information

• Documentation Accessibility
• Related Documents
• Conventions

What's New in Oracle Performance?

Part i performance tuning, 1 performance tuning overview.

• 1.1.1 Performance Planning
• 1.1.2 Instance Tuning
• 1.1.3 SQL Tuning
• 1.2.1 Automatic Performance Tuning Features
• 1.2.2 Additional Oracle Tools

Part II Performance Planning

2 designing and developing for performance.

• 2.1 Oracle Methodology
• 2.2 Understanding Investment Options
• 2.3.1 What is Scalability?
• 2.3.2 System Scalability
• 2.3.3 Factors Preventing Scalability
• 2.4.1 Hardware and Software Components
• 2.4.2 Configuring the Right System Architecture for Your Requirements
• 2.5.1 Simplicity In Application Design
• 2.5.2 Data Modeling
• 2.5.3 Table and Index Design
• 2.5.4 Using Views
• 2.5.5 SQL Execution Efficiency
• 2.5.6 Implementing the Application
• 2.5.7 Trends in Application Development
• 2.6.1 Sizing Data
• 2.6.2 Estimating Workloads
• 2.6.3 Application Modeling
• 2.6.4 Testing, Debugging, and Validating a Design
• 2.7.1 Rollout Strategies
• 2.7.2 Performance Checklist

3 Performance Improvement Methods

• 3.1.1 Steps in The Oracle Performance Improvement Method
• 3.1.2 A Sample Decision Process for Performance Conceptual Modeling
• 3.1.3 Top Ten Mistakes Found in Oracle Systems
• 3.2.1 Steps in the Emergency Performance Method

Part III Optimizing Instance Performance

4 configuring a database for performance.

• 4.1.1 Initialization Parameters
• 4.1.2 Configuring Undo Space
• 4.1.3 Sizing Redo Log Files
• 4.1.4 Creating Subsequent Tablespaces
• 4.2.1 Table Compression
• 4.2.2 Reclaiming Unused Space
• 4.2.3 Indexing Data
• 4.3.1 Identifying Contention Using the Dispatcher-Specific Views
• 4.3.2 Identifying Contention for Shared Servers

5 Automatic Performance Statistics

• 5.1.1 Database Statistics
• 5.1.2 Operating System Statistics
• 5.1.3 Interpreting Statistics
• 5.2.1 Snapshots
• 5.2.2 Baselines
• 5.2.3 Space Consumption
• 5.3.1 Managing Snapshots
• 5.3.2 Managing Baselines
• 5.3.3 Transporting Automatic Workload Repository Data
• 5.3.4 Using Automatic Workload Repository Views
• 5.3.5 Generating Automatic Workload Repository Reports
• 5.3.6 Generating Active Session History Reports

6 Automatic Performance Diagnostics

• 6.1 Introduction to Database Diagnostic Monitoring
• 6.2.1 ADDM Analysis Results
• 6.2.2 An ADDM Example
• 6.2.3 Setting Up ADDM
• 6.2.4 Diagnosing Database Performance Issues with ADDM
• 6.2.5 Views with ADDM Information

7 Memory Configuration and Use

• 7.1.1 Oracle Memory Caches
• 7.1.2 Automatic Shared Memory Management
• 7.1.3 Dynamically Changing Cache Sizes
• 7.1.4 Application Considerations
• 7.1.5 Operating System Memory Use
• 7.1.6 Iteration During Configuration
• 7.2.1 Using the Buffer Cache Effectively
• 7.2.2 Sizing the Buffer Cache
• 7.2.3 Interpreting and Using the Buffer Cache Advisory Statistics
• 7.2.4 Considering Multiple Buffer Pools
• 7.2.5 Buffer Pool Data in V$DB_CACHE_ADVICE
• 7.2.6 Buffer Pool Hit Ratios
• 7.2.7 Determining Which Segments Have Many Buffers in the Pool
• 7.2.8 KEEP Pool
• 7.2.9 RECYCLE Pool
• 7.3.1 Shared Pool Concepts
• 7.3.2 Using the Shared Pool Effectively
• 7.3.3 Sizing the Shared Pool
• 7.3.4 Interpreting Shared Pool Statistics
• 7.3.5 Using the Large Pool
• 7.3.6 Using CURSOR_SPACE_FOR_TIME
• 7.3.7 Caching Session Cursors
• 7.3.8 Configuring the Reserved Pool
• 7.3.9 Keeping Large Objects to Prevent Aging
• 7.3.10 CURSOR_SHARING for Existing Applications
• 7.3.11 Maintaining Connections
• 7.4.1 Sizing the Log Buffer
• 7.4.2 Log Buffer Statistics
• 7.5.1 Configuring Automatic PGA Memory
• 7.5.2 Configuring OLAP_PAGE_POOL_SIZE

8 I/O Configuration and Design

• 8.1 Understanding I/O
• 8.2.1 Lay Out the Files Using Operating System or Hardware Striping
• 8.2.2 Manually Distributing I/O
• 8.2.3 When to Separate Files
• 8.2.4 Three Sample Configurations
• 8.2.5 Oracle-Managed Files
• 8.2.6 Choosing Data Block Size

9 Understanding Operating System Resources

• 9.1.1 Using Operating System Caches
• 9.1.2 Memory Usage
• 9.1.3 Using Operating System Resource Managers
• 9.2.1 Performance Hints on UNIX-Based Systems
• 9.2.2 Performance Hints on Windows Systems
• 9.2.3 Performance Hints on HP OpenVMS Systems
• 9.3 Understanding CPU
• 9.4.1 Checking Memory Management
• 9.4.2 Checking I/O Management
• 9.4.3 Checking Network Management
• 9.4.4 Checking Process Management

10 Instance Tuning Using Performance Views

• 10.1.1 Define the Problem
• 10.1.2 Examine the Host System
• 10.1.3 Examine the Oracle Statistics
• 10.1.4 Implement and Measure Change
• 10.2.1 Examine Load
• 10.2.2 Using Wait Event Statistics to Drill Down to Bottlenecks
• 10.2.3 Table of Wait Events and Potential Causes
• 10.2.4 Additional Statistics
• 10.3.1 SQL*Net Events
• 10.3.2 buffer busy waits
• 10.3.3 db file scattered read
• 10.3.4 db file sequential read
• 10.3.5 direct path read and direct path read temp
• 10.3.6 direct path write and direct path write temp
• 10.3.7 enqueue (enq:) waits
• 10.3.8 events in wait class other
• 10.3.9 free buffer waits
• 10.3.10 latch events
• 10.3.11 log file parallel write
• 10.3.12 library cache pin
• 10.3.13 library cache lock
• 10.3.14 log buffer space
• 10.3.15 log file switch
• 10.3.16 log file sync
• 10.3.17 rdbms ipc reply
• 10.4 Idle Wait Events

Part IV Optimizing SQL Statements

11 sql tuning overview.

• 11.1 Introduction to SQL Tuning
• 11.2.1 Reduce the Workload
• 11.2.2 Balance the Workload
• 11.2.3 Parallelize the Workload
• 11.3.1 Identifying Resource-Intensive SQL
• 11.3.2 Gathering Data on the SQL Identified
• 11.4 Automatic SQL Tuning Features
• 11.5.1 Verifying Optimizer Statistics
• 11.5.2 Reviewing the Execution Plan
• 11.5.3 Restructuring the SQL Statements
• 11.5.4 Controlling the Access Path and Join Order with Hints
• 11.5.5 Restructuring the Indexes
• 11.5.6 Modifying or Disabling Triggers and Constraints
• 11.5.7 Restructuring the Data
• 11.5.8 Maintaining Execution Plans Over Time
• 11.5.9 Visiting Data as Few Times as Possible

12 Automatic SQL Tuning

• 12.1.1 Query Optimizer Modes
• 12.1.2 Types of Tuning Analysis
• 12.2.1 Input Sources
• 12.2.2 Tuning Options
• 12.2.3 Advisor Output
• 12.2.4 Using SQL Tuning Advisor APIs
• 12.3.1 Creating a SQL Tuning Set
• 12.3.2 Loading a SQL Tuning Set
• 12.3.3 Displaying the Contents of a SQL Tuning Set
• 12.3.4 Modifying a SQL Tuning Set
• 12.3.5 Transporting a SQL Tuning Set
• 12.3.6 Dropping a SQL Tuning Set
• 12.3.7 Additional Operations on SQL Tuning Sets
• 12.4.1 Accepting a SQL Profile
• 12.4.2 Altering a SQL Profile
• 12.4.3 Dropping a SQL Profile
• 12.5 SQL Tuning Information Views

13 The Query Optimizer

• 13.1 Optimizer Operations
• 13.2.1 OPTIMIZER_MODE Initialization Parameter
• 13.2.2 Optimizer SQL Hints for Changing the Query Optimizer Goal
• 13.2.3 Query Optimizer Statistics in the Data Dictionary
• 13.3.1 Enabling Query Optimizer Features
• 13.3.2 Controlling the Behavior of the Query Optimizer
• 13.4.1 Components of the Query Optimizer
• 13.4.2 Reading and Understanding Execution Plans
• 13.5.1 Full Table Scans
• 13.5.2 Rowid Scans
• 13.5.3 Index Scans
• 13.5.4 Cluster Access
• 13.5.5 Hash Access
• 13.5.6 Sample Table Scans
• 13.5.7 How the Query Optimizer Chooses an Access Path
• 13.6.1 How the Query Optimizer Executes Join Statements
• 13.6.2 How the Query Optimizer Chooses Execution Plans for Joins
• 13.6.3 Nested Loop Joins
• 13.6.4 Hash Joins
• 13.6.5 Sort Merge Joins
• 13.6.6 Cartesian Joins
• 13.6.7 Outer Joins

14 Managing Optimizer Statistics

• 14.1 Understanding Statistics
• 14.2.1 GATHER_STATS_JOB
• 14.2.2 Enabling Automatic Statistics Gathering
• 14.2.3 Considerations When Gathering Statistics
• 14.3.1 Gathering Statistics with DBMS_STATS Procedures
• 14.3.2 When to Gather Statistics
• 14.4.1 Workload Statistics
• 14.4.2 Noworkload Statistics
• 14.5.1 Restoring Previous Versions of Statistics
• 14.5.2 Exporting and Importing Statistics
• 14.5.3 Restoring Statistics Versus Importing or Exporting Statistics
• 14.5.4 Locking Statistics for a Table or Schema
• 14.5.5 Setting Statistics
• 14.5.6 Estimating Statistics with Dynamic Sampling
• 14.5.7 Handling Missing Statistics
• 14.6.1 Statistics on Tables, Indexes and Columns
• 14.6.2 Viewing Histograms

15 Using Indexes and Clusters

• 15.1.1 Tuning the Logical Structure
• 15.1.2 Index Tuning using the SQLAccess Advisor
• 15.1.3 Choosing Columns and Expressions to Index
• 15.1.4 Choosing Composite Indexes
• 15.1.5 Writing Statements That Use Indexes
• 15.1.6 Writing Statements That Avoid Using Indexes
• 15.1.7 Re-creating Indexes
• 15.1.8 Compacting Indexes
• 15.1.9 Using Nonunique Indexes to Enforce Uniqueness
• 15.1.10 Using Enabled Novalidated Constraints
• 15.2 Using Function-based Indexes for Performance
• 15.3 Using Partitioned Indexes for Performance
• 15.4 Using Index-Organized Tables for Performance
• 15.5 Using Bitmap Indexes for Performance
• 15.6 Using Bitmap Join Indexes for Performance
• 15.7 Using Domain Indexes for Performance
• 15.8 Using Clusters for Performance
• 15.9 Using Hash Clusters for Performance

16 Using Optimizer Hints

• 16.1.1 Types of Hints
• 16.1.2 Hints by Category
• 16.2.1 Specifying a Full Set of Hints
• 16.2.2 Specifying a Query Block in a Hint
• 16.2.3 Specifying Global Table Hints
• 16.2.4 Specifying Complex Index Hints
• 16.3.1 Hints and Complex Views
• 16.3.2 Hints and Mergeable Views
• 16.3.3 Hints and Nonmergeable Views

17 SQL Access Advisor

• 17.1.1 Overview of Using the SQL Access Advisor
• 17.2.1 Steps for Using the SQL Access Advisor
• 17.2.2 Privileges Needed to Use the SQL Access Advisor
• 17.2.3 Setting Up Tasks and Templates
• 17.2.4 Managing Workloads
• 17.2.5 Working with Recommendations
• 17.2.6 Performing a Quick Tune
• 17.2.7 Managing Tasks
• 17.2.8 Using SQL Access Advisor Constants
• 17.2.9 Examples of Using the SQL Access Advisor
• 17.3.1 DBMS_ADVISOR.TUNE_MVIEW Procedure

18 Using Plan Stability

• 18.1.1 Using Hints with Plan Stability
• 18.1.2 Storing Outlines
• 18.1.3 Enabling Plan Stability
• 18.1.4 Using Supplied Packages to Manage Stored Outlines
• 18.1.5 Creating Outlines
• 18.1.6 Using Stored Outlines
• 18.1.7 Viewing Outline Data
• 18.1.8 Moving Outline Tables
• 18.2.1 Moving from RBO to the Query Optimizer
• 18.2.2 Moving to a New Oracle Release under the Query Optimizer

19 Using EXPLAIN PLAN

• 19.1.1 How Execution Plans Can Change
• 19.1.2 Minimizing Throw-Away
• 19.1.3 Looking Beyond Execution Plans
• 19.1.4 EXPLAIN PLAN Restrictions
• 19.2 The PLAN_TABLE Output Table
• 19.3.1 Identifying Statements for EXPLAIN PLAN
• 19.3.2 Specifying Different Tables for EXPLAIN PLAN
• 19.4.1 Customizing PLAN_TABLE Output
• 19.5 Reading EXPLAIN PLAN Output
• 19.6.1 Viewing Parallel Queries with EXPLAIN PLAN
• 19.7 Viewing Bitmap Indexes with EXPLAIN PLAN
• 19.8.1 Examples of Displaying Range and Hash Partitioning with EXPLAIN PLAN
• 19.8.2 Examples of Pruning Information with Composite Partitioned Objects
• 19.8.3 Examples of Partial Partition-wise Joins
• 19.8.4 Examples of Full Partition-wise Joins
• 19.8.5 Examples of INLIST ITERATOR and EXPLAIN PLAN
• 19.8.6 Example of Domain Indexes and EXPLAIN PLAN
• 19.9 PLAN_TABLE Columns

20 Using Application Tracing Tools

• 20.1.1 Enabling and Disabling Statistic Gathering for End to End Tracing
• 20.1.2 Viewing Gathered Statistics for End to End Application Tracing
• 20.1.3 Enabling and Disabling for End to End Tracing
• 20.1.4 Viewing Enabled Traces for End to End Tracing
• 20.2.1 Syntax for trcsess
• 20.2.2 Sample Output of trcsess
• 20.3.1 Understanding the SQL Trace Facility
• 20.3.2 Understanding TKPROF
• 20.4.1 Step 1: Setting Initialization Parameters for Trace File Management
• 20.4.2 Step 2: Enabling the SQL Trace Facility
• 20.4.3 Step 3: Formatting Trace Files with TKPROF
• 20.4.4 Step 4: Interpreting TKPROF Output
• 20.4.5 Step 5: Storing SQL Trace Facility Statistics
• 20.5.1 Avoiding the Argument Trap
• 20.5.2 Avoiding the Read Consistency Trap
• 20.5.3 Avoiding the Schema Trap
• 20.5.4 Avoiding the Time Trap
• 20.5.5 Avoiding the Trigger Trap
• 20.6.1 Sample TKPROF Header
• 20.6.2 Sample TKPROF Body
• 20.6.3 Sample TKPROF Summary

Part V Real Application Testing

21 database replay.

• 21.1.1 Workload Capture
• 21.1.2 Workload Preprocessing
• 21.1.3 Workload Replay
• 21.1.4 Analysis and Reporting
• 21.2.1 Enabling and Disabling Workload Capture
• 21.2.2 Prerequisites for Capturing a Database Workload
• 21.2.3 Workload Capture Options
• 21.2.4 Workload Capture Restrictions
• 21.2.5 Capturing a Database Workload Using Enterprise Manager
• 21.2.6 Monitoring Workload Capture Using Enterprise Manager
• 21.2.7 Capturing a Database Workload Using APIs
• 21.2.8 Monitoring Workload Capture Using Views
• 21.3.1 Generating a Workload Capture Report Using Enterprise Manager
• 21.3.2 Generating a Workload Capture Report Using APIs
• 21.3.3 Using a Workload Capture Report

22 SQL Performance Analyzer

• 22.1 Overview of SQL Performance Analyzer
• 22.2 Capturing the SQL Workload

Scripting on this page enhances content navigation, but does not change the content in any way.