PL/SQL Profiler

 1) verify if package DBMS_PROFILER is installed

SYS@DB> desc sys.dbms_profiler

if it’s not yet installed, connect as sysdba and run script

SYS@DB> @?/rdbms/admin/profload.sql

2) verify if profiler repository tables are created

connect as application user (the one who runs pl/sql for analysis)

USER@DB> desc PLSQL_PROFILER_RUNS
USER@DB> desc PLSQL_PROFILER_UNITS
USER@DB> desc PLSQL_PROFILER_DATA

if they are not installed yet, install them by running script

USER@DB> @proftab.sql

the script can be downloaded as PROF.zip from metalink note: ID 243755.1

3. profile a pl/sql

To profile a PL/SQL Library (package, procedure, function or trigger), include in its body
the two calls to actually start and complete the profiling.  Use the example below on any
PL/SQL Library to profile.

USER@DB>
BEGIN
DBMS_PROFILER.START_PROFILER('pl-sql profiling run1');

...    -- PL/SQL goes here

DBMS_PROFILER.STOP_PROFILER;
EXCEPTION -- this line may exist in your code
...
END;
/

4. profiler.sql –  Reporting PL/SQL Profiler data generated by DBMS_PROFILER

USER@DB> @profiler.sql <runid>

the script profiler.sql can be found in PROF.zip from metalink note: ID 243755.1

it requires one input ‘runid’, we get the last pl/sql profiler run id by

USER@DB> select runid from plsql_profiler_runs where run_comment = 'pl-sql profiling run1';

RUNID
--------
1

USER@DB> @profiler.sql 1

it will generate a report in html format.

Finding bind variable values

In 10g, you can use data dictionary view v$sql_bind_capture or AWR view dba_hist_sqlbind (historical version of v$sql_bind_capture) to find bind variables’ values. However it has some significant limitations:

• Captured periodically (_cursor_bind_capture_interval=900 seconds by default), not at real time.
• Captured under maximum size(_cursor_bind_capture_area_size=400)
• Only bind variables in WHERE clause are captured (e.g bind variables passed to function are not captured !)

select name, position, datatype_string, value_string from v$sql_bind_capture where sql_id = '<sql_id>';

But there is a bug related to v$sql_bind_capture as reported in note 444551.1

V$SQL_BIND_CAPTURE Does Not Show The Value For Binds Of Type TIMESTAMP [ID 444551.1]

as a workaround you can get value of TIMESTAMP bind variable by

select name, position, datatype_string, was_captured, value_string,
anydata.accesstimestamp(value_anydata) from v$sql_bind_capture where sql_id = '<sql_id>';

it’s fixed in 11.2.

Oracle Diagnostic Tools

Diagnostic Tools Catalog [ID 559339.1]
		Modified 27-APR-2011     Type REFERENCE     Status PUBLISHED

	Diagnostic Tools – Database And Fusion Middleware
	Getting Started With RDA 4	Getting Started with RDA for Rdb	Guardian Resource Center
	Health Check/Validation Engine User Guide	ORA-600/ORA-7445 Error Look-up Tool	Training – RDA 4
	Diagnostic Tools – Database Performance and Tuning Scripts
	Core / Stack Trace Extraction Tool (Stackx) User Guide	HANGFG User Guide	Implementing and Using the PL/SQL Profiler
	LTOM User Guide	ORA-600/ORA-7445 Troubleshooter	Oracle Performance Diagnostic Guide (OPDG)
	OSWatcher User Guide	Performance Tools Quick Reference Guide	SQLTXPLAIN.SQL – Enhanced Explain Plan and related diagnostic info for one SQL statement
	Trace Analyzer TRCANLZR – Interpreting Raw SQL Traces with Binds and/or Waits generated by EVENT 10046

Oracle Performance Tuning on AIX

1) Depending on your version, you may need to set filesystemio_options=setall (in 10g and 9i)

2) Make sure mount file system of data file, control file and redo logs with option "-o cio"

3) Create the filesystems for redo logs with -o cio and agblksize=512

4) Plan on increasing your db_cache_size to compensate for the loss of file
system buffering at the OS level

5) set lock_sga=true

6) use AIX large memory pages (e.g. 16M with vmo -p -o lgpg_regions=2048 lgpg_size=16777216)

Fixing Execution Plan by SQL Profile

After reading Kerry Osborne’s presentation Controlling Execution Plans, I decide to sum up what I learned about SQL Profile and to make some experiments of my own.

To tune a sql in case that we CAN NOT change the code, some tuning methods are available, but keep in mind they’re not specific solution comparing with sql profile:

• gathering or updating objects’ statistics
• adding or removing accessing paths (e.g. indexes, mv, etc.)
• adjust init parameters (e.g. in session level)

SQL Profile will take advantage of other tuning methods in such conditions:

• to fix a bad execution plan of one particular SQL (it is not a general systematic problem affecting a lot of others)
• we can’t change the code ( we don’t own the code; we’re in emergency and have no time to change the code; we need to follow a company rule to make changes in production, etc.)
• the same sql executes well in test environment but has very bad performance in production
• the same sql executes well in the past but has very bad performance today

Description about SQL Profile by Tom Kyte:

So, a SQL profile is sort of like gathering statistics on A QUERY which involves many tables, columns and the like….

In fact – it is just like gathering statistics for a query, it stores additional information in the dictionary which the optimizer uses at optimization time to determine the correct plan. The SQL Profile is not “locking a plan in place”, but rather giving the optimizer yet more bits of information it can use to get the right plan.

There are several ways to create SQL Profiles

• Using SQL Tuning Adviser from EM or via DBMS_SQLTUNE package
• Attaching a SQL Profile to a different SQL (e.g. update existing execution plan and attach it to a different SQL using KO’s scripts, see demo)
• Moving a SQL Profile from one database to another database ( using script )

Create SQL Profile by SQL Tuning Adviser

1. find sql with

lock_sga on AIX 5L

AIX 5.3 ORA-27126: unable to lock shared memory segment in core

when setting lock_sga=true

This doesn’t appear to be documented many places, so I’ll put it here. There can be a tangible performance increase to ‘locking’ SGA to pinned memory. Paging spaces are, after all, on a slower medium (disk). In AIX 5.3 you also need to ‘pin’ shared memory. (SGA is shared memory)

First, you’ll need to set the ‘vmo’ option v_pinshm to 1. This tells AIX to ‘pin’ shared memory into RAM.

Second, you’ll need to give the ‘oracle’ user (or whatever user the database runs as) the following capabilities:

CAP_BYPASS_RAC_VMM and CAP_PROPAGATE

The commands to do this (as root on the system) are as follows:

***To set v_pinshm***
# vmo -p -o v_pinshm=1

***To give the "oracle" user the necessary capabilities***
# chuser capabilities=CAP_BYPASS_RAC_VMM,CAP_PROPAGATE oracle

large memory pages may also help too

Articles from Kerry Osborne

Index

Creating Test Scripts With Bind Variables

GATHER_PLAN_STATISTICS

+++++Oracle Support Sanctions Manually Created SQL Profiles!

1. move sql profile from test to production 2) fix a good exec plan by sql_id and plan_hash_value

How to Attach a SQL Profile to a Different Statement – Take 2

1.move an execution plan to a different sql

 

Article on execution plan

Troubleshooting Bad Execution Plans

November 21, 2007

By Greg Rahn

One of the most common performance issues DBAs encounter are bad execution plans. Many try to resolve bad executions plans by setting optimizer related parameters or even hidden underscore parameters. Some even try to decipher a long and complex 10053 trace in hopes to find an answer. While changing parameters or analyzing a 10053 trace might be useful for debugging at some point, I feel there is a much more simple way to start to troubleshoot bad execution plans.

Verify The Query Matches The Business Question

This seems like an obvious thing to do, but I’ve seen numerous cases where the SQL query does not match the business question being asked. Do a quick sanity check verifying things like: join columns, group by, subqueries, etc. The last thing you want to do is consume time trying to debug a bad plan for an improperly written SQL query. Frequently I’ve found that this is the case for many of those “I’ve never got it to run to completion” queries.

What Influences The Execution Plan

I think it’s important to understand what variables influence the Optimizer in order to focus the debugging effort. There are quite a number of variables, but frequently the cause of the problem ones are: (1) non-default optimizer parameters and (2) non-representative object/system statistics. Based on my observations I would say that the most abused Optimizer parameters are:

• OPTIMIZER_INDEX_CACHING
• OPTIMIZER_INDEX_COST_ADJ
• DB_FILE_MULTIBLOCK_READ_COUNT

Many see setting these as a solution to get the Optimizer to choose an index plan over a table scan plan, but this is problematic in several ways:

1. This is a global change to a local problem
2. Although it appears to solve one problem, it is unknown how many bad execution plans resulted from this change
3. The root cause of why the index plan was not chosen is unknown, just that tweaking parameters gave the desired result
4. Using non-default parameters makes it almost impossible to correctly and effectively troubleshoot the root cause

Object and system statistics can have a large influence on execution plans, but few actually take the time to sanity check them during triage. These statistics exist in views like:

• ALL_TAB_COL_STATISTICS
• ALL_PART_COL_STATISTICS
• ALL_INDEXES
• SYS.AUX_STATS$

Using GATHER_PLAN_STATISTICS With DBMS_XPLAN.DISPLAY_CURSOR

As a first step of triage, I would suggest executing the query with a GATHER_PLAN_STATISTICS hint followed by a call to DBMS_XPLAN.DISPLAY_CURSOR. The GATHER_PLAN_STATISTICS hint allows for the collection of extra metrics during the execution of the query. Specifically, it shows us the Optimizer’s estimated number of rows (E-Rows) and the actual number of rows (A-Rows) for each row source. If the estimates are vastly different from the actual, one probably needs to investigate why. For example: In the below plan, look at line 8. The Optimizer estimates 5,899 rows and the row source actually returns 5,479,000 rows. If the estimate is off by three orders of magnitude (1000), chances are the plan will be sub-optimal. Do note that with Nested Loop Joins you need to multiply the Starts column by the E-Rows column to get the A-Rows values (see line 10).

?

01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18

select /*+ gather_plan_statistics */ ... from ... ; select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST')); ------------------------------------------------------------------------------------------ |  Id | Operation                              | Name | Starts | E-Rows | A-Rows | ------------------------------------------------------------------------------------------ |   1 | SORT GROUP BY |              |     1  |      1 | 1      | |*  2 |  FILTER                                |              |     1  |        | 1728K  | |   3 |   NESTED LOOPS                         |              |     1  |      1 | 1728K  | |*  4 |    HASH JOIN |              |     1  |      1 | 1728K  | |   5 |     PARTITION LIST SINGLE              |              |     1  |   6844 | 3029   | |*  6 | INDEX RANGE SCAN                  | PROV_IX13    |     1  |   6844 | 3029   | |   7 |     PARTITION LIST SINGLE              |              |     1  |   5899 | 5479K  | |*  8 | TABLE ACCESS BY LOCAL INDEX ROWID | SERVICE      |     1  |   5899 | 5479K  | |*  9 | INDEX SKIP SCAN                  | SERVICE_IX8  |     1  |   4934 | 5479K  | |  10 |    PARTITION LIST SINGLE               |              |  1728K |      1 | 1728K  | |* 11 | INDEX RANGE SCAN                   | CLAIM_IX7    |  1728K |      1 | 1728K  | ------------------------------------------------------------------------------------------

Using The CARDINALITY Hint

Now that I’ve demonstrated how to compare the cardinality estimates to the actual number of rows, what are the debugging options? If one asserts that the Optimizer will choose the optimal plan if it can accurately estimate the number of rows, one can test using the not so well (un)documented CARDINALITY hint. The CARDINALITY hint tells the Optimizer how many rows are coming out of a row source. The hint is generally used like such:

?

1 2 3 4 5 6 7 8

select /*+ cardinality(a 100) */ * from dual a; -------------------------------------------------------------------------- | Id  | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- |   0 | SELECT STATEMENT  |      |   100 |   200 |     2   (0)| 00:00:01 | |   1 | TABLE ACCESS FULL| DUAL |   100 |   200 |     2   (0)| 00:00:01 | --------------------------------------------------------------------------

In this case I told the Optimizer that DUAL would return 100 rows (when in reality it returns 1 row) as seen in the Rows column from the autotrace output. The CARDINALITY hint is one tool one can use to give the Optimizer accurate information. I usually find this the best way to triage a bad plan as it is not a global change, it only effects a single execution of a statement in my session. If luck has it that using a CARDINALITY hint yields an optimal plan, one can move on to debugging where the cardinality is being miscalculated. Generally the bad cardinality is the result of non-representative table/column stats, but it also may be due to data correlation or other factors. This is where it pays off to know and understand the size and shape of the data. If the Optimizer still chooses a bad plan even with the correct cardinality estimates, it’s time to place a call to Oracle Support as more in-depth debugging is likely required.

Where Cardinality Can Go Wrong

There are several common scenarios that can lead to inaccurate cardinality estimates. Some of those on the list are:

1. Data skew: Is the NDV inaccurate due to data skew and a poor dbms_stats sample?
2. Data correlation: Are two or more predicates related to each other?
3. Out-of-range values: Is the predicate within the range of known values?
4. Use of functions in predicates: Is the 5% cardinality guess for functions accurate?
5. Stats gathering strategies: Is your stats gathering strategy yielding representative stats?

Some possible solutions to these issues are:

1. Data skew: Choose a sample size that yields accurate NDV. Use DBMS_STATS.AUTO_SAMPLE_SIZE in 11g.
2. Data correlation: Use Extended Stats in 11g. If <= 10.2.0.3 use a CARDINALITY hint if possible.
3. Out-of-range values: Gather or manually set the statistics.
4. Use of functions in predicates: Use a CARDINALITY hint where possible.
5. Stats gathering strategies: Use AUTO_SAMPLE_SIZE. Adjust only where necessary. Be mindful of tables with skewed data.

How To Best Work With Oracle Support

If you are unable to get to the root cause on your own, it is likely that you will be in contact with Oracle Support. To best assist the support analyst I would recommend you gather the following in addition to the query text:

1. Output from the GATHER_PLAN_STATISTICS and DBMS_XPLAN.DISPLAY_CURSOR
2. SQLTXPLAN output. See Metalink Note 215187.1
3. 10053 trace output. See Metalink Note 225598.1
4. DDL for all objects used (and dependencies) in the query. This is best gotten as a expdp (data pump) using CONTENT=METADATA_ONLY. This will also include the object statistics.
5. Output from: select pname, pval1 from sys.aux_stats$ where sname='SYSSTATS_MAIN';
6. A copy of your init.ora

Having this data ready before you even make the call (or create the SR on-line) should give you a jump on getting a quick(er) resolution.

Summary

While this blog post is not meant to be a comprehensive troubleshooting guide for bad execution plans, I do hope that it does help point you in the right direction the next time you encounter one. Many of the Optimizer issues I’ve seen are due to incorrect cardinality estimates, quite often due to inaccurate NDV or the result of data correlation. I believe that if you use a systematic approach you will find that debugging bad execution plans may be as easy as just getting the cardinality estimate correct.

WARNING:Could not lower the asynch I/O limit to 165 for SQL direct I/O. It is set to -1

The trace files in $BDUMP continue to show warning messages like “WARNING:Could not lower the asynch I/O limit to 165 for SQL direct I/O. It is set to -1”.

After searching possible causes of the problem in metalink, it turns out that’s related to a bug (confirmed to affect 10.2.0.5 on general platforms)

9772888: WARNING:COULD NOT LOWER THE ASYNCH I/O LIMIT TO 160 FOR SQL DIRECT I/O.

Fix:

Applying patch 9772888

or

Applying patchset 10.2.0.5.2

 

Reference:

Applies to:

Oracle Server – Enterprise Edition – Version: 10.2.0.5 and later   [Release: 10.2 and later ]
Information in this document applies to any platform.

Symptoms

Warning below appears after upgrading to 10.2.0.5

*** SESSION ID:(538.48) 2010-07-15 08:41:30.649

WARNING:Could not lower the asynch I/O limit to 224 for SQL direct I/O. It is set to -1
WARNING:Could not lower the asynch I/O limit to 192 for SQL direct I/O. It is set to -1
WARNING:Could not lower the asynch I/O limit to 224 for SQL direct I/O. It is set to -1
WARNING:Could not lower the asynch I/O limit to 192 for SQL direct I/O. It is set to -1
WARNING:Could not lower the asynch I/O limit to 160 for SQL direct I/O. It is set to -1

Changes

Recently upgraded to 10.2.0.5.

Cause

This seems due to bug 9772888 10.2.0.5 RDBMS 10.2.0.5 BUFFER CACHE PRODID-5 PORTID-23 Abstract: WARNING:COULD NOT LOWER THE ASYNCH I/O LIMIT TO 160 FOR SQL DIRECT I/O. IT IS SE
Same behavior appears and on the same platform 10.2.0.5

Solution

1. Upgrade to Oracle 11.2 and install on top patchset 11.2.0.2.0 (or higher version patchset) where issue is fixed

OR

2. Install one-off patch on top of 10.2.0.5.0 if available to your platform.

Using Async IO on AIX 5L

This article describes steps to verify if you have allocated enough aioserver processes for AIO on AIX 5L.

AIX 5L supports asynchronous I/O (AIO) for database files created both on file system partitions and on raw devices.

AIO on raw devices is implemented fully into the AIX kernel, and does not require database processes to service the AIO requests.

When using AIO on file systems, the kernel database processes (aioserver) control each request from the time a request is taken off the queue to the time it is completed. The number of aioserver servers determines the number of AIO requests that can be processed in the system concurrently. So, it is important to tune the number of aioserver processes when using file systems to store Oracle Database data files.

1) Find out how many aioservers are configured:

smitty -> Devices-> Asynchronous I/O-> Change/Show Characteristics of Asynchronous I/O-> Asynchronous I/O (Legacy)

MINIMUM number of servers                          [10]
MAXIMUM number of servers per cpu                  [20]
Maximum number of REQUESTS                         [4096]
Server PRIORITY                                    [39]
STATE to be configured at system restart            available
State of fast path                                  enable

Note:

Starting with AIX 5L version 5.2, there are two AIO subsystems available. Oracle Database 10g uses Legacy AIO (aio0), even though the Oracle preinstallation script enables Legacy AIO (aio0) and POSIX AIO (posix_aio0). Both AIO subsystems have the same performance characteristics.

An easier way to find setting on legacy AIO(aio0):

$ lsattr -El aio0

autoconfig available STATE to be configured at system restart True
fastpath   enable    State of fast path                       True
kprocprio  39        Server PRIORITY                          True
maxreqs    4096      Maximum number of REQUESTS               True
maxservers 20        MAXIMUM number of servers per cpu        True
minservers 10        MINIMUM number of servers                True

The ‘minservers’ setting (10) is the total number of aioserver kernel threads that start at boot – this is not a per CPU or per logical CPU value.

The ‘maxservers’ setting (20) is the maximum number of aioserver  per CPU or per logical CPU value.

The ‘maxreqs’ setting (4096) is the maximum number of outstanding or pending requests that the kernel will allow at any one time.

The default value for the minimum number of servers is 1. The default value for the maximum number of servers is 10. These values are usually too low to run Oracle Database on large systems with 4 CPUs or more, if you are not using kernelized AIO( for raw devices).

2) Check how many CPUs (or logical CPUs if SMT is enabled) are provided on system

$ lparstat

System configuration: type=Shared mode=Uncapped smt=On lcpu=8 mem=16384 psize=4 ent=3.00

%user  %sys  %wait  %idle physc %entc  lbusy   app  vcsw phint
----- ----- ------ ------ ----- ----- ------   --- ----- -----
 4.2   0.4    2.1   93.2  0.14   4.7   12.9  3.27 2541549655 92497349

The maximum aioserver kernel threads can reach 20*8=160. When aioserver kernel thread starts, it never disappears. Let’s check how many aioserver threads have been started since system boot.

$ ps -ek | grep aioserver | grep -v posix_aioserver | wc -l
 160

The ‘-k’ argument to the ps command lists kernel threads (also known as kprocs) in addition to user-space processes.

It appears that all 160 aioservers have been started, so sometime between when the system booted and this very instant, something needed all of them – and possibly more.

If the value of the maxservers or maxreqs parameter is set too low, then the following warning messages are repeatedly displayed in trace files in directory $BDUMP:

Warning: lio_listio returned EAGAINPerformance degradation may be seen.

3) increase number of maxservers and minservers

chdev -l aio0 -a maxservers='m' -a minservers='n'

You can change the AIO parameters for your system with the chdev command on the aio0 pseudo-device, or by running smitty aio on AIX 5.3 systems – you can even use the poorly documented aioo command. An important thing to remember though – is that you can set the values online, with no interruption of service – BUT – they will not take affect until the next time the kernel is booted.

4) Check CPU time of each aioserver

Another test is to see how much CPU time each of the aioserver. If all of the aioserver threads have a very similar amount of CPU time, and if the amount of CPU time consumed is significant – chances are you need to configure more of them. Depending on how long the OS has been running, you may need to add quite a few more.

$ ps -ek | grep aioserver | more
118880      –  1:31 aioserver
127062      –  1:32 aioserver

References:

Asynchronous disk I/O performance tuning

Asynchronous I/O tunable parameters

http://abstractinitiative.com/PerformanceJungle/2008/11/aix-53-aio-asynchronous-io-tuning/