SCRIPT: How to Determine the Number of Disk Sorts vs Memory Sorts

Subject:	SCRIPT: How to Determine the Number of Disk Sorts vs Memory Sorts
	Doc ID:	Note:100013.1	Type:	SCRIPT
	Last Revision Date:	21-MAY-2006	Status:	PUBLISHED

Abstract Script to determine the Number of Disk Sorts vs. Memory Sorts.   Product Name, Product Version Oracle Server, 7.3.x to 9.x Platform Platform Independent Date Created 30-Apr-2001   Instructions Execution Environment:        Access Privileges:      Requires DBA privileges.   Usage:      sqlplus / @[SCRIPTFILE]   Instructions:      Run the script from SQL*PLus connected as a user with DBA access privileges.   PROOFREAD THIS SCRIPT BEFORE USING IT! Due to differences in the way text editors, e-mail packages, and operating systems handle text formatting (spaces, tabs, and carriage returns), this script may not be in an executable state when you first receive it. Check over the script to ensure that errors of this type are corrected.   Description Many times a customer will want to know the number of memory sorts compared to disk sorts. This information is useful in tuning database performance and the SORT_AREA_SIZE. By using information from V$STATNAME and V$SYSSTAT, a DBA can determine whether or not more sorts are being performed on disk than is desired. A large number of disk sorts can adversely affect overall performance of the database. If more disk sorts are being performed than desired, then increase the value of SORT_AREA_SIZE. Any changes made to the SORT_AREA_SIZE parameter will have a like effect on the size of the SGA. Measurements have shown that large sort areas (over 10 Meg)do not perform much better than 1 Meg sort area unless a disk sort is avoided altogether. Sample Output ============= 'INIT.ORASORT_AREA_SIZE:'||VALUE -------------------------------------------------------------------------------- INIT.ORA sort_area_size: 524288     NAME VALUE ---------------------------------------------------------------- ------------ sorts (memory) 448,726 sorts (disk) 0 sorts (rows) 921,405   References <Note:1019592.6> TFTS: MSTAT -- SYSTEMSTATISTICS/TUNING SCRIPT. <Note:102339.1> Temporary Segments: What Happens When a Sort Occurs

SCRIPT: Calculating Buffer Cache Hit Ratio without Inputing Parameters

Subject:	SCRIPT: Calculating Buffer Cache Hit Ratio without Inputing Parameters
	Doc ID:	Note:1039290.6	Type:	SCRIPT
	Last Revision Date:	06-JAN-2003	Status:	PUBLISHED

Abstract This script calculates the database cache hit ratio through the use of PL/SQL.   Product Name, Product Version Oracle Server, 7.3 to 9.2 Platform Platform Independent Date Created 29-NOV-1997   Instructions Execution Environment:      SQL, SQL*Plus   Access Privileges:      Requires DBA access privileges to be executed.   Usage:      sqlplus sys/   Instructions:      Run the script in SQL*Plus to create the required procedure.                     After creating the procedure, use the following select statement          to retrieve current results.        SQL> select  global_name, :r "Hit Ratio" from  global_name ;             GLOBAL_NAME   Hit Ratio           -----------   ----------           V920.WORLD    .995129671     PROOFREAD THIS SCRIPT BEFORE USING IT! Due to differences in the way text editors, e-mail packages, and operating systems handle text formatting (spaces, tabs, and carriage returns), this script may not be in an executable state when you first receive it. Check over the script to ensure that errors of this type are corrected.   Description This script calculates the current database cache hit ratio.   References                        Script rem rem  This script uses PL/SQL. rem  le hit ratio  rem    col global_name for A20  variable d number  variable c number  variable r number  begin    select value into :c    from v$sysstat     where name = 'consistent gets';    select value into :d    from v$sysstat     where name = 'db block gets';    select (1- value/( :c + :d)) into :r      from v$sysstat    where name in ( 'physical reads');     end;  /   After creating the procedure, use the following select statement to retrieve current results.   SQL> select  global_name, :r "Hit Ratio" from  global_name ;        GLOBAL_NAME   Hit Ratio      -----------   ----------      V920.WORLD    .995129671

Getting Good Advice About Access

New SQL Access Advisor in Oracle 10g guides you in choosing indexes and materialized views.
It's not unusual to spend hours tuning SQL statements in hopes of getting better SQL performance, often relying on methods that are time-consuming at best or, worse, border on guesswork or wizardry. However, with the new SQL tuning tools available in Oracle Database 10g—specifically, SQL Tuning Advisor and SQL Access Advisor—tuning SQL is more science than sorcery.

We've looked at SQL Tuning Advisor (see "Advise and Consent,") and discussed how to use that particular tool to fix poorly structured SQL statements, detect stale statistics, or implement a SQL profile so that packaged applications can run optimally. During its analyses, SQL Tuning Advisor also performs an access-path analysis that's limited in scope—it assesses whether individual SQL statements can benefit from an index—often recommending that you run SQL Access Advisor in a separate step.

In addition, however, you can run SQL Access Advisor separately at any time to evaluate a complete SQL workload and obtain advice about indexes, materialized views, and materialized view logs. As every DBA knows, in data warehouse, decision support, and other environments in which complex, data-intensive queries are the norm, the appropriate use of indexes and materialized views can dramatically improve access paths to data. With the right materialized view on hand (and query rewrite enabled), the Oracle cost-based optimizer (CBO) can take a faster path to the data. Figuring out the right indexes and materialized views is streamlined when you use SQL Access Advisor, because even beginning DBAs who know little about these structures can obtain guidance.

Using the SQL Access Advisor
Like the other advisors in Oracle Database 10g, SQL Access Advisor is available from Enterprise Manager Database Control, from Advisor Central. Clicking on the SQL Access Advisor link launches the SQL Access Advisor wizard, which basically comprises a four-page process that starts by letting you select a SQL workload and then configure the schedule and several other options. When you finish stepping through the wizard, a task associated with the selected workload is created and becomes available from the Results table displayed on Advisor Central; if you configured the task to run immediately, it will do so.

Running the task invokes the tuning mode of the CBO, which analyzes the SQL workload and generates recommendations. This has an impact on performance—someting to keep in mind as you're stepping through the wizard, whose scheduling page lets you configure whether to run immediately, later at a specified time, or in the context of a maintenance window.

Various Types of Workloads
The workload can come from one of several sources, including SQL Tuning Sets that are available by default or that you've created from AWR (automatic workload repository) snapshots. For example, the Top SQL statements (statements using the largest percentage of system resources) comprise SQL Tuning Sets that are available by default, so you can select among them as you're stepping through the wizard. If you want to choose from AWR snapshots, you'll have to create these SQL Tuning Sets in advance, before launching SQL 
Access Advisor.

You can also select the cache of current and recent SQL activity as the workload. By default, all activity will be captured, but you can filter this initial capture (by selecting Show Advanced Options in the lower portion of the page) to include only statements belonging to a particular user.

Another option for creating a workload is the so-called hypothetical workload (an invaluable tool at design time), which is derived from the schema and table structures you identify. This option works only if the schema comprises dimensions and if the tables you identify have primary key and foreign key constraints defined.

You can also run SQL Access Advisor against a user-defined workload, a table containing SQL statements and the name of the user that executes each statement. Minimally, your table must include a column for the username and a column for the text of the SQL statement:

CREATE TABLE user_workload(username varchar2(30), sql_text clob);

So, for example, you can create a table containing all the SQL statements from a variety of applications that might be running concurrently and model different runtime scenarios, using various user-defined workload tables.

Once you've identified the workload you want to run through SQL Access Advisor, the wizard gives you options for the type of recommendations to obtain about the workload, specifically whether you want recommendations about indexes only, about materialized views only, or about both. In addition, you can choose to focus only on the highest-cost SQL statements.

The Cost-Based Optimized and Running a Task
Again, keep in mind that SQL Access Advisor invokes the tuning mode of the CBO, so running the task can have an impact on your system's performance. Obtaining advice about materialized views has a bigger performance impact than simply obtaining advice about indexes. Also, depending on the complexity of your SQL workload and other load factors, the comprehensive mode has a far greater impact than the limited mode.

In general, if you're obtaining advice about materialized views or if you're selecting comprehensive mode on the Recommendations Options page, you should schedule (on the third page of the wizard) the task to run during off-hours or during a scheduled maintenance window, such as WEEKEND_WINDOW.

Finally, when you've configured all your choices, you review all the settings you've made and then submit them. The process of submitting creates the task; if you've configured it to run immediately, it invokes the CBO and processes the workload to obtain the recommendations.

When this process is complete, you have a task associated with a workload, but until you run the task and generate recommendations, nothing happens. You can create multiple tasks to analyze a given workload in numerous ways; for example, you can create one task that looks at a workload in terms of indexes, another task that evaluates the same workload in terms of materialized views, another that performs both, and so on.

Because the CBO isn't invoked until you actually run the task, if you haven't scheduled the task to run immediately, there's no immediate impact on your system. Likewise, the performance improvements projected by SQL Access Advisor will not take effect until you actually implement a recommendation (or recommendations). In any case, you should always review all recommendations before implementing them.

Implementing Recommendations
You can view the expected impact of various recommendations in terms of overall performance impact relative to other recommendations (see Figure 1) or across all SQL statements. To implement the recommendation, you simply click on the Schedule Implementation button or save the SQL script that implements the recommendations to run later. As you might expect, implementing the recommendation creates the indexes and materialized views or performs other actions as detailed in the recommendation. (Although multiple recommendations can share the same action with other recommendations—you may see the same CREATE INDEX action in several different recommendations, for example—the implementation script [or the wizard's implement button] implements the action only once.)

Performing a QuickTune
Although Enterprise Manager Database Control supports almost all SQL Access Advisor functionality, you need to use the DBMS_ADVISOR package to perform a QuickTune on a single SQL statement or to perform several other activities, such as importing Oracle9i Summary Advisor workloads.

The QuickTune procedure is great for quickly getting materialized view and index advice about a single SQL statement. The procedure analyzes the SQL statement you pass it and generates recommendations, saving the results as a task of whatever name you pass it, as follows:

EXECUTE DBMS_ADVISOR.QUICK_TUNE(

DBMS_ADVISOR.SQLACCESS_ADVISOR, 

'KF QuickTune', 'SELECT avg(amount_sold) 

FROM sh.sales 

WHERE cust_id=3234'); 

When you refresh the Enterprise Manager display (for Advisor Central), you'll see the QuickTune task listed in the Results table. Click on the link (the name of the task), and the Recommendations for the task appear. You can execute the task by clicking on the Schedule Implementation button or saving the SQL script.

Conclusion
SQL Access Advisor provides yet another time-saving tool for DBAs. With even limited tuning experience, you can get started with Enterprise Manager Database Control. You can also easily start using the API, and you can find examples of using DBMS_ADVISOR procedures in the aadvdemo.sql file (you have to install the Oracle Database Server 10g Companion CD software to obtain it).

REFERENCES
Getting Good Advice About Access By Kimberly Floss

Tuning: People, processes and technology

All too often, organizations attempt to resolve performance issues with a quick fix or magic bullet to keep the business moving ahead of the competition. The perception is that there is no time for planning, designing or testing a solution; you have to move from problem identification to immediately implementing the solution. Unfortunately, the reality is that accurate solutions derived in this manner are few and far between. Quick fixes typically become long-term nightmares that no one wants to work on. It's only a matter of time before the IT team knows how fragile the system is and what is really required. To top it off, the quick fix is typically some other piece of software that "can just be integrated" and forgotten. In most cases, this is simply not possible. It has been common knowledge for years that every IT solution consists of people, processes and technology. But when it comes to building a solution, those first two components are left in the dust by the idea that a single piece of technology is the solution. However, what is really necessary is to have your team and management support all three components and avoid shooting from the hip. In this article, we will explore the components for properly leveraging people, processes and technology for performance tuning. People: Trained, motivated and performance-tuned In my opinion, people are the most important aspect of the triad because with good people, the correct processes and technology can be developed, tested and implemented. Without knowledgeable professionals focused on the issues, performance will continue to suffer. As such, I recommend focusing on the following: Team -- Motivate a strong team to address performance-tuning needs. At a minimum, it should consist of developers, database administrators, network administrators, desktop technicians, testers and users. Training -- Make sure the team is properly trained on the technology required to support the application from the front-end application all the way to the storage subsystems. Training in team building and knowledge sharing yields high-performance team members who are well aware of the challenges faced by other team members. Time -- Our scarcest resource is time, but with proper time management we can achieve momentous results. Without the ability to manage your time well, you face insufficient time leading to pressure and stress. Vigilant time management gives you ample time to manage your workload. You do not want to be so busy that at the eleventh hour you realize an issue that was brought up two months earlier had been ignored because you only have brain cycles for the work that is right in front of you, thanks to ever-mounting demands. Processes include well-known and lesser-known components Building a process that meets your team's skill set and comfort level with performance-tuning needs is easier said then done. That's why I believe most "solutions" lack processes that address things like implementation, maintenance and support, upgrades, testing and troubleshooting. Do not make processes too complex. Break the work down into small manageable steps that can be distributed among team members. Project management -- Address performance-tuning needs as a project, with a set of goals, start and end dates and, most importantly, managerial support for the project in terms of time and the members of the team. Most of the time, performance tuning is considered a side project to be worked on as time permits. Break this habit! Legitimize these needs. Communication -- First and foremost, communicate your needs for completing the project. Identify, document and test the processes, implement them and learn from the experience and share the knowledge so the same performance-tuning issues are not re-created in the future. Simplicity -- Build accurate and efficient processes and reuse them with the goal of distributing the work load among the team members. Don't risk having all of the knowledge in one person's head. This, too, is easier said then done. But after building a few processes, you will recognize opportunities to reuse processes and streamline the overall project. Technology, remember, is one of three components Do not pass by the first two components and proceed directly to a tool to solve your performance problems. A tool may help identify a performance bottleneck but will typically not be able to correct and validate the technical issue in your environment. Components -- Keep in mind that the tool is a third component of the equation, not the overall solution. Once you find the right tool for the issue, the team may have to learn something new and processes may change. Evaluation -- Make sure the technology meets your needs conceptually and practically. Bring the technology in-house and validate that it will meet user and IT expectations. Discuss the short- and long-term potential for the technology to be sure the plans and your expectations align and that support will be available when you need it. Once you allocate the proper time for evaluation, training, and planning, then you can implement the solution in a timely manner -- while the information is fresh and the team can focus on the issues at hand. Then begin to reap the benefits of your team's efforts in terms of high-performing systems.

REFERENCES

Tuning: People, processes and technology by Jeremy Kadlec

Surveying Diagnostics

Tools in Oracle Database 10g store diagnostic information and provide answers.

Oracle Database 10g introduced Automatic Database Diagnostic Monitor (ADDM), which enables the database to self-analyze its performance. The database can identify potential bottlenecks and fix them automatically or recommend a solution to an administrator. To make solutions or recommendations, ADDM uses the statistical data saved in Automatic Workload Repository (AWR). Oracle Database 10g also provides several advisors that guide DBAs in managing and tuning key components of the database.

This column focuses on how AWR, ADDM, and the advisors work to make performance tuning easy. It presents sample questions of the type that you may encounter when taking the Oracle Database 10g Administration Workshop I exam. The Oracle Database 10g Administration Workshop I exam enables you to earn the Oracle Certified Associate (OCA) level of certification. Note that the sample question format has been adjusted for presentation in this column.

Automatic Workload Repository

AWR is a built-in repository in Oracle Database 10g. At regular intervals, the Oracle database takes a snapshot of all its vital statistics as well as workload information and stores the snapshots in AWR. You can display the gathered data in both reports and views. By default, the AWR snapshots are captured once every hour and are retained in the database for seven days.

You need to change the collection interval and retention period of AWR snapshots for your database. Which two options could you use to change these settings?

A. Oracle Enterprise Manager 10g
B. Procedures in the DBMS_WORKLOAD_REPOSITORY package
C. An ALTER DATABASE SQL command
D. Procedures in the DBMS_ADVISOR package

The correct answers are A and B. To access AWR through Oracle Enterprise Manager 10g, select the Automatic Workload Repository link under Workload on the Administration page. Click on the Edit button on the Automatic Workload Repository page to open the Edit Settings page. Use this page to modify AWR snapshot settings. Alternatively, you can use the MODIFY_SNAPSHOT_SETTINGS procedure in the DBMS_WORKLOAD_REPOSITORY package to modify AWR snapshot settings. Answers C and D are incorrect because Oracle Database 10g does not support direct SQL access to repository settings, and you cannot use the procedures in the DBMS_ADVISOR package to modify AWR settings.

Automatic Database Diagnostic Monitor

ADDM is a self-diagnostic engine that analyzes AWR data on a regular basis. It then identifies the root causes of any performance issues and provides recommendations for resolving them. You can view the results of the ADDM analysis by using Oracle Enterprise Manager 10g, as shown in Figure 1. Alternatively, you can also run ADDM manually by using the addmrpt.sql script and DBMS_ADVISOR package application- programming interfaces (APIs).

An ADDM analysis is performed automatically each time ______. (Choose all that apply.)

A. A datafile is added to any of the tablespaces
B. A new tablespace is added to the database
C. A new user is added to the database
D. An AWR snapshot is taken

The correct answer is D. An ADDM analysis is automatically performed every time an AWR snapshot is taken, and the analysis results are saved in the database. Answers A, B, and C are incorrect because an ADDM analysis is not performed when a datafile, a tablespace, or a user is added to the database.

Which two statements are correct regarding ADDM?

A. ADDM presents information for only those areas in a database that have performance issues.
B. ADDM does not target the tuning of individual user response times.
C. The STATISTICS_LEVEL parameter should be set to TYPICAL or ALL to enable automatic database diagnostic monitoring.
D. The results of each ADDM analysis are stored in the SYSTEM tablespace.

The correct answers are B and C. ADDM does not target the tuning of individual response times. You can use user-tracing techniques to tune for individual response times. ADDM is enabled by default and controlled by the STATISTICS_LEVEL initialization parameter. The STATISTICS_LEVEL parameter should be set to TYPICAL or ALL to enable automatic database diagnostic monitoring. Setting STATISTICS_LEVEL to BASIC disables automatic database diagnostic monitoring. Answer A is incorrect because ADDM also documents areas of the database that have no performance issues. (This information allows you to quickly identify the areas in which there is little to be gained by performing actions and, therefore, saves you time and effort.) Answer D is incorrect because results of each ADDM analysis are stored in AWR, and AWR is stored in the SYSAUX tablespace.

Users complain that database performance was slow between 9 p.m. and 11 p.m. the previous night. AWR snapshots are being taken every hour, and the snapshot retention period is set to seven days. You plan to use the ADDM findings to identify the cause of performance issues and take suitable action to avoid such issues in the future.

What would you do?

A. Use the latest ADDM report.
B. Check the alert log entries for the ADDM recommendations.
C. Create and run a custom ADDM task.
D. Modify the AWR settings so that the snapshots are taken every two hours.

The correct answer is C. You can create a custom ADDM task to invoke an ADDM analysis on the AWR snapshots that are taken during the specific time. For example, you can choose the snapshot that was taken before or at 9 p.m. and the snapshot that was taken after or at 11 p.m. the previous night. The simplest way to run an ADDM analysis over a specific time period is with Oracle Enterprise Manager 10g, as shown in Figure 2. Answer A is incorrect because the latest ADDM report would provide findings for the last hour. It would not be a clear indication of the performance issues during the previous night. Answer B is incorrect because alert log entries do not provide ADDM recommendations. Answer D is incorrect because modifying the AWR snapshot interval setting applies to future snapshots only and will not help in identifying the cause of performance issues that occurred the previous night.

Advisor Framework

Oracle Database 10g includes a number of advisors for different subsystems in the database to automatically determine how the operation of the corresponding subcomponents could be further optimized. Typically, advisors are run either directly or in response to an ADDM performance finding. The advisor output provides recommendations that help you in tuning subsystems of the database, such as memory and undo.

Your ADDM analysis report (in Figure 1) shows the following finding:

SQL statements consuming significant database time were found.

ADDM recommends that you run SQL Tuning Advisor. SQL Tuning Advisor recommendations would help you in _____.

A. Shrinking appropriate segments
B. Setting the undo retention suitably
C. Setting the buffer cache size appropriately
D. Improving the execution plan of the SQL statements

The correct answer is D. The SQL Tuning Advisor provides recommendations that improve the execution plan of SQL statements. The SQL Tuning Advisor takes one or more SQL statements as input and invokes the Automatic Tuning Optimizer to perform SQL tuning on the statements. The output of the SQL Tuning Advisor is in the form of recommendations, along with a rationale for each recommendation and its expected benefit.

Conclusion

In Oracle Database 10g, the statistical data needed for the accurate diagnosis of a problem is saved in AWR. ADDM is a diagnostic tool that automatically and proactively analyzes the data in AWR to determine whether there are major issues with the database system. ADDM also provides recommendations to resolve performance issues. ADDM recommendations may suggest that you run an advisor to optimize performance, and you can use recommendations from the advisors to resolve performance issues and/or improve performance.

REFERENCES

Surveying Diagnostics By Aradhana Puri

Response-Time Analysis Made Easy in Oracle Database 10g

With Oracle Database 10g, many previously difficult-to-attain response-time metrics are now at your fingertips.

Historically, in trying to achieve maximum database performance, Oracle DBAs and performance analysts have fought an uphill battle to obtain solid response time metrics for system as well as user session activity. The problem facing DBAs has always had two facets: first, determining exactly "where" the database or user sessions have been spending their time; and second, determining the objective nature of the user experience.

Given all the possible activities and interactions within the database, these tasks are far from trivial. The Oracle Wait Interface, which was introduced many versions ago, has been a great jump-start for admins who know how to use it, but even it has lacked the ideal ability to tell a DBA how well the system or user sessions have efficiently processed transactions or queries. Enabling and poring over trace files can garner this level of detail, but for most overworked DBAs who have to manage large database farms, this exercise is a luxury.

Fortunately, those Oracle DBAs who have upgraded to Oracle Database 10g will find major response-time enhancements that allow a much better picture to be presented about system and session-level response-time metrics. Most significantly, the Oracle Automatic Database Diagnostic Monitor (ADDM) provides insights into response times and much more by automatically analyzing collected statistics, identifying problem areas, and even providing recommendations through the Oracle Enterprise Manager Grid Control GUI.

Furthermore, and most relevant to our discussion here, historical mechanisms in Oracle Database 10g allow DBAs to look back in time to perform their own response-time trend analysis, which helps them determine peak and nonpeak transaction/system times as well as locate rogue processes and SQL that elongate batch cycles or ETL jobs.

In this article, I'll explore the use of some of these historical mechanisms at the system, session, and SQL levels. For more information about ADDM, see the Oracle documentation as well as the "ADDM and SQL Tuning Advisor" installment of Arup Nanda's "Oracle Database 10g: Top 20 Features for DBAs."

System-Level Response-Time Analysis

Starting at the global or system level, DBAs typically want answers to these questions:

• In general, how well is my database running? What defines efficiency?
• What average response time are my users experiencing?
• Which activities affect overall response time the most?

The answers to these questions have been fairly elusive for DBAs before Oracle Database 10g, but now such metrics can be somewhat easy to capture if you happen to be using the latest and greatest Oracle database.

First of all, part of the answer to how well, in general, a database is running can be obtained by issuing this query in Oracle Database 10g:

select  METRIC_NAME,

        VALUE

from    SYS.V_$SYSMETRIC

where   METRIC_NAME IN ('Database CPU Time Ratio',

                        'Database Wait Time Ratio') AND

        INTSIZE_CSEC = 

        (select max(INTSIZE_CSEC) from SYS.V_$SYSMETRIC); 

METRIC_NAME                         VALUE

------------------------------ ----------

Database Wait Time Ratio                6

Database CPU Time Ratio                94

The Oracle Database 10g V$SYSMETRIC view contains several excellent response-time metrics, two of which are the Database Wait Time Ratio and Database CPU Time Ratio. The query above shows the latest snapshot of these two statistics, which help you determine if your database is currently experiencing a high percentage of waits/bottlenecks vs. smoothly running operations. The Database CPU Time Ratio is calculated by dividing the amount of CPU expended in the database by the amount of "database time," which is defined as the time spent by the database on user-level calls (with instance background process activity being excluded). High values (90-95+ percent) are good and indicate few wait/bottleneck actions, but take this threshold only as a general rule of thumb because every system is different.

You can also take a quick look over the last hour to see if the database has experienced any dips in overall performance by using this query:

select  end_time,

        value

from    sys.v_$sysmetric_history

where   metric_name = 'Database CPU Time Ratio'

order by 1;

END_TIME                  VALUE

-------------------- ----------

22-NOV-2004 10:00:38         98

22-NOV-2004 10:01:39         96

22-NOV-2004 10:02:37         99

22-NOV-2004 10:03:38        100

22-NOV-2004 10:04:37         99

22-NOV-2004 10:05:38         77

22-NOV-2004 10:06:36        100

22-NOV-2004 10:07:37         96

22-NOV-2004 10:08:39        100

.

.

And, you can get a good idea of the minimum, maximum, and average values of overall database efficiency by querying the V$SYSMETRIC_SUMMARY view with a query such as this:

select  CASE METRIC_NAME

            WHEN 'SQL Service Response Time' then 'SQL Service Response Time (secs)'

            WHEN 'Response Time Per Txn' then 'Response Time Per Txn (secs)'

            ELSE METRIC_NAME

            END METRIC_NAME,

                    CASE METRIC_NAME

            WHEN 'SQL Service Response Time' then ROUND((MINVAL / 100),2)

            WHEN 'Response Time Per Txn' then ROUND((MINVAL / 100),2)

            ELSE MINVAL

            END MININUM,

                    CASE METRIC_NAME

            WHEN 'SQL Service Response Time' then ROUND((MAXVAL / 100),2)

            WHEN 'Response Time Per Txn' then ROUND((MAXVAL / 100),2)

            ELSE MAXVAL

            END MAXIMUM,

                    CASE METRIC_NAME

            WHEN 'SQL Service Response Time' then ROUND((AVERAGE / 100),2)

            WHEN 'Response Time Per Txn' then ROUND((AVERAGE / 100),2)

            ELSE AVERAGE

            END AVERAGE

from    SYS.V_$SYSMETRIC_SUMMARY 

where   METRIC_NAME in ('CPU Usage Per Sec',

                      'CPU Usage Per Txn',

                      'Database CPU Time Ratio',

                      'Database Wait Time Ratio',

                      'Executions Per Sec',

                      'Executions Per Txn',

                      'Response Time Per Txn',

                      'SQL Service Response Time',

                      'User Transaction Per Sec')

ORDER BY 1

METRIC_NAME                       MINIMUM    MAXIMUM    AVERAGE

------------------------------ ---------- ---------- ----------

CPU Usage Per Sec                       0          7          1

CPU Usage Per Txn                       1         29          8

Database CPU Time Ratio                61        100         94

Database Wait Time Ratio                0         39          5

Executions Per Sec                      2         60          8

Executions Per Txn                     16        164         41

Response Time Per Txn (secs)            0        .28        .08

SQL Service Response Time (sec          0          0          0

User Transaction Per Sec                0          1          0

The query above contains more response-time metrics than simply the Database CPU and Wait Time Ratios (we'll cover those later), but you can see the benefit in being able to acquire this information. For this particular instance, the average Database CPU Time Ratio is 94, which is well within our acceptable limits.

The next question DBAs pose at the system level involves the average level of response time that their user community is experiencing. (Prior to Oracle Database 10g this type of data was difficult to capture, but not anymore.) The query shown above that interrogates the V$SYSMETRIC_SUMMARY view tells us what we need to know. If complaints of unacceptable response times are mounting from users, the DBA can check the Response Time Per Txn and SQL Service Response Time metrics to see if a database issue exists. For example, the statistics shown above report that the maximum response time per user transaction has been only .28 second, with the average response time being a blazing .08 second. Oracle certainly wouldn't be to blame in this case.

If, however, response times are longer than desired, the DBA will then want to know what types of user activities are responsible for making the database work so hard. Again, before Oracle Database 10g, this information was more difficult to acquire, but now the answer is only a query away:

select  case db_stat_name

            when 'parse time elapsed' then 

                'soft parse time'

            else db_stat_name

            end db_stat_name,

        case db_stat_name

            when 'sql execute elapsed time' then 

                time_secs - plsql_time 

            when 'parse time elapsed' then 

                time_secs - hard_parse_time

            else time_secs

            end time_secs,

        case db_stat_name

            when 'sql execute elapsed time' then 

                round(100 * (time_secs - plsql_time) / db_time,2)

            when 'parse time elapsed' then 

                round(100 * (time_secs - hard_parse_time) / db_time,2)  

            else round(100 * time_secs / db_time,2)  

            end pct_time

from

(select stat_name db_stat_name,

        round((value / 1000000),3) time_secs

    from sys.v_$sys_time_model

    where stat_name not in('DB time','background elapsed time',

                            'background cpu time','DB CPU')),

(select round((value / 1000000),3) db_time 

    from sys.v_$sys_time_model 

    where stat_name = 'DB time'),

(select round((value / 1000000),3) plsql_time 

    from sys.v_$sys_time_model 

    where stat_name = 'PL/SQL execution elapsed time'),

(select round((value / 1000000),3) hard_parse_time 

    from sys.v_$sys_time_model 

    where stat_name = 'hard parse elapsed time')

order by 2 desc;

DB_STAT                          TIME_SECS       PCT_TIME

-----------------------------    ---------       --------

sql execute elapsed time         13263.707       45.84                                 

PL/SQL execution elapsed time    13234.738       45.74                                 

hard parse elapsed time           1943.687        6.72                                  

soft parse time                    520.584         1.8

.

.

The example output above shows a database that has spent the vast majority of its time handling SQL and PL/SQL requests. Complete descriptions of all the statistics supported by the V$SYS_TIME_MODEL view can be found here.

In addition to active time, a DBA will want to know the global wait times as well. Prior to Oracle Database 10g, a DBA had to view individual wait events to understand waits and bottlenecks, but now Oracle provides a summary/rollup mechanism for waits via wait classes:

select  WAIT_CLASS,

        TOTAL_WAITS,

        round(100 * (TOTAL_WAITS / SUM_WAITS),2) PCT_WAITS,

        ROUND((TIME_WAITED / 100),2) TIME_WAITED_SECS,

        round(100 * (TIME_WAITED / SUM_TIME),2) PCT_TIME

from

(select WAIT_CLASS,

        TOTAL_WAITS,

        TIME_WAITED

from    V$SYSTEM_WAIT_CLASS

where   WAIT_CLASS != 'Idle'),

(select  sum(TOTAL_WAITS) SUM_WAITS,

        sum(TIME_WAITED) SUM_TIME

from    V$SYSTEM_WAIT_CLASS

where   WAIT_CLASS != 'Idle')

order by 5 desc;

WAIT_CLASS      TOTAL_WAITS  PCT_WAITS TIME_WAITED_SECS   PCT_TIME

--------------- ----------- ---------- ---------------- ----------

User I/O            2245204       7.48          4839.43      54.39

System I/O          2438387       8.12          2486.21      27.94

Application          920385       3.07           513.56       5.77

Other                 39962        .13           422.36       4.75

Commit               200872        .67           284.76        3.2

Network            24133213      80.38           162.26       1.82

Concurrency            6867        .02           102.63       1.15

Configuration         39377        .13            86.21        .97

It's much easier to tell now that the bulk of overall wait time is due, for example, to user I/O waits than to try to tally individual wait events to get a global picture. As with response-time metrics, you can also look back in time over the last hour with a query like this one:

select  to_char(a.end_time,'DD-MON-YYYY HH:MI:SS') end_time,

        b.wait_class,

        round((a.time_waited / 100),2) time_waited 

from    sys.v_$waitclassmetric_history a,

        sys.v_$system_wait_class b

where   a.wait_class# = b.wait_class# and

        b.wait_class != 'Idle'

order by 1,2;

END_TIME             WAIT_CLASS      TIME_WAITED

-------------------- --------------- -----------

22-NOV-2004 11:28:37 Application               0

22-NOV-2004 11:28:37 Commit                  .02

22-NOV-2004 11:28:37 Concurrency               0

22-NOV-2004 11:28:37 Configuration             0

22-NOV-2004 11:28:37 Network                 .01

22-NOV-2004 11:28:37 Other                     0

22-NOV-2004 11:28:37 System I/O              .05

22-NOV-2004 11:28:37 User I/O                  0

.

.

You can, of course, just focus on a single SID with the V$SESS_TIME_MODEL view and obtain data for all statistical areas of a session. You can also view current session wait activity using the new wait classes using the following query:

select  a.sid,

        b.username,

        a.wait_class,

        a.total_waits,

        round((a.time_waited / 100),2) time_waited_secs

from    sys.v_$session_wait_class a,

        sys.v_$session b

where   b.sid = a.sid and

        b.username is not null and

        a.wait_class != 'Idle'

order by 5 desc;

SID USERNAME   WAIT_CLASS      TOTAL_WAITS TIME_WAITED_SECS

--- ---------- --------------- ----------- ----------------

257 SYSMAN     Application          356104            75.22

255 SYSMAN     Commit                14508            25.76

257 SYSMAN     Commit                25026            22.02

257 SYSMAN     User I/O              11924            19.98

.

.

.

After this stage, you can check the standard individual wait events as you've been able to do in earlier versions of Oracle with V$SESSION_WAIT and V$SESSION_EVENT. You'll also find the new wait classes in these two modified views with Oracle Database 10g.

If you need to look back in time to discover what sessions were logged on and consuming the most resources, you can use the following query. In the example below, we're looking at activity from midnight to 5 a.m. on November 21, 2004, that involved user I/O waits:

select  sess_id,

        username,

        program,

        wait_event,

        sess_time,

        round(100 * (sess_time / total_time),2) pct_time_waited

from

(select a.session_id sess_id,

        decode(session_type,'background',session_type,c.username) username,

        a.program program,

        b.name wait_event,

        sum(a.time_waited) sess_time

from    sys.v_$active_session_history a,

        sys.v_$event_name b,

        sys.dba_users c

where   a.event# = b.event# and

        a.user_id = c.user_id and

        sample_time > '21-NOV-04 12:00:00 AM' and 

        sample_time < '21-NOV-04 05:00:00 AM' and

        b.wait_class = 'User I/O'

group by a.session_id,

        decode(session_type,'background',session_type,c.username),

        a.program,

        b.name),

(select sum(a.time_waited) total_time

from    sys.v_$active_session_history a,

        sys.v_$event_name b

where   a.event# = b.event# and

        sample_time > '21-NOV-04 12:00:00 AM' and 

        sample_time < '21-NOV-04 05:00:00 AM' and

        b.wait_class = 'User I/O')

order by 6 desc;

SESS_ID USERNAME PROGRAM    WAIT_EVENT                SESS_TIME PCT_TIME_WAITED

------- -------- ---------- ------------------------- ---------- -------------

    242 SYS      exp@RHAT9K db file scattered read       3502978         33.49

    242 SYS      oracle@RHA db file sequential read      2368153         22.64

    242 SYS      oracle@RHA db file scattered read       1113896         10.65

    243 SYS      oracle@RHA db file sequential read       992168          9.49

The Oracle Database 10g V$ACTIVE_SESSION_HISTORY view comes into play here to provide an insightful look back in time at session experiences for a given time period. This view gives you a lot of excellent information without the need for laborious tracing functions. We'll see more use of it in the next section, which deals with analyzing the response times of SQL statements.

SQL Response-Time Analysis

Examining the response time of SQL statements became easier in Oracle9i, and with Oracle Database 10g, DBAs have many tools at their disposal to help them track inefficient database code.

Historically the applicable V$ view here has been V$SQLAREA. Starting with Oracle9i, Oracle added the ELAPSED_TIME and CPU_TIME columns, which have been a huge help in determining the actual end user experience of a SQL statement execution (at least, when dividing them by the EXECUTIONS column, which produces the average amount of time per execution).

In Oracle Database 10g, six new wait-related and timing columns have been added to V$SQLAREA:

• APPLICATION_WAIT_TIME
• CONCURRENCY_WAIT_TIME
• CLUSTER_WAIT_TIME
• USER_IO_WAIT_TIME
• PLSQL_EXEC_TIME
• JAVA_EXEC_TIME

The new columns are helpful in determining, for example, the amount of time that a procedure spends in PL/SQL code vs. standard SQL execution, and if a SQL statement has experienced any particular user I/O waits. For example, a query you could use to find the top five SQL statements with the highest user I/O waits would be:

select *

from

(select sql_text,

        sql_id,

        elapsed_time,

        cpu_time,

        user_io_wait_time

from    sys.v_$sqlarea

order by 5 desc)

where rownum < 6;

SQL_TEXT                  SQL_ID       ELAPSED_TIME CPU_TIME  USER_IO_WAIT_TIME

------------------------- ------------ ------------ ---------- ---------------

select /*+ rule */ bucket db78fxqxwxt7     47815369   19000939            3423

SELECT :"SYS_B_0" FROM SY agdpzr94rf6v     36182205   10170226            2649

select obj#,type#,ctime,m 04xtrk7uyhkn     28815527   16768040            1345

select grantee#,privilege 2q93zsrvbdw4     28565755   19619114             803

select /*+ rule */ bucket 96g93hntrzjt      9411028    3754542             606

Of course, getting the SQL statements with the highest elapsed time or wait time is good, but you need more detail to get to the heart of the matter—which is where the V$ACTIVE_SESSION_HISTORY view again comes into play. With this view, you can find out what actual wait events delayed the SQL statement along with the actual files, objects, and object blocks that caused the waits (where applicable).

For example, let's say you've found a particular SQL statement that appears to be extremely deficient in terms of user I/O wait time. You can issue the following query to get the individual wait events associated with the query along with the corresponding wait times, files, and objects that were the source of those waits:

select event,

        time_waited,

        owner,

        object_name,

        current_file#,

        current_block# 

from    sys.v_$active_session_history a,

        sys.dba_objects b 

where   sql_id = '6gvch1xu9ca3g' and

        a.current_obj# = b.object_id and

        time_waited <> 0;

EVENT                     TIME_WAITED OWNER  OBJECT_NAME           file  block

------------------------- ----------- ------ --------------------- ---- ------

db file sequential read         27665 SYSMAN MGMT_METRICS_1HOUR_PK    3  29438

db file sequential read          3985 SYSMAN SEVERITY_PRIMARY_KEY     3  52877

Of course, you can use V$ACTIVE_SESSION_HISTORY in a historical fashion to narrow down unoptimized SQL statements for a particular time period. The point is that Oracle Database 10g makes it a lot easier to conduct response-time analysis on SQL statements with simplified data dictionary views, as opposed to the time-consuming trace-and-digest method.

Conclusion

DBAs and performance analysts who manage the performance of Oracle Database 10g will find many of the response-time metrics they've yearned for over the years now at their fingertips in the latest release of Oracle's flagship database. Such statistics will help accelerate the process of finding the proverbial needle in the haystack of large and complex database performance situations.

REFERENCES

Response-Time Analysis Made Easy in Oracle Database 10g by Robin Schumacher