applications Receive Updates For This Category
When would one be required to bounce (stop and restart) the Concurrent Manager?
When you modify the Printer Driver you have to restart the Manager which
runs the request which is attached to that Printer Driver, however,if you do
not know which manager then you have to restart the Internal manager because
the printer driver can be used by multiple managers and multiple requests.
If only a concurrent program definition is modified, running a verify on the
Internal Manager will pick up the changes without the need for bouncing the
manager.
Does the Internal manager schedule requests to be run or does it put requests
into queues to be run by other managers?
This is a very common misconception. The ICM really does not have any
such scheduling responsibilities. It has NOTHING to do with scheduling
requests, or deciding which manager will run a particular request.
Its function is only to run ‘queue control’ requests, which are
requests to startup or shutdown other managers. It is responsible for
startup and shutdown of the whole concurrent processing facility, and
it also monitors the other managers periodically, and restarts them if
they should go down. It can also take over the Conflict Resolution
manager’s job, and resolve incompatibilities.
If the ICM itself should go down, requests will continue to run
normally, except for ‘queue control’ requests. You can restart it with
‘startmgr’, you do not need to kill the other managers first.
How can I check to see if a concurrent manager is running?
One way to see if a manager is running is to use the ‘Administer
Concurrent Managers’ form. Navigate to Concurrent->Managers->Administer.
You will see two columns labeled ‘Actual’ and ‘Target’. The Target column
lists the number of processes that should be running for each manager
for this particular workshift. The Actual column lists the number of
processes that are actually running. If the Actual column is zero, there
are no processes running for this manager. If the Target column is zero,
then either a workshift has not been assigned to this manager, or the current
workshift does not specify any target processes. If the target column
is not zero, then the manager processes have either failed to start up,
or gone down. You should check the manager’s logfile and the ICM
logfile. You can also search for OS processes using the ‘ps’ command.
It is possible for the form to be inaccurate, i.e. it may show actual
processes even though they are not really running. When in doubt, check for
processes at the OS level. On NT, you can check to see if the Concurrent Manager
service is running using the Services control panel.
Where do concurrent request or manager logfiles and output files go?
The concurrent manager first looks for the environment variable
$APPLCSF. If this is set, it creates a path using two other
environment variables: $APPLLOG and $APPLOUT
It places log files in $APPLCSF/$APPLLOG, output files go in
$APPLCSF/$APPLOUT
So for example, if you have this environment set:
$APPLCSF = /u01/appl/common
$APPLLOG = log
$APPLOUT = out
The concurrent manager will place log files in /u01/appl/common/log,
and output files in /u01/appl/common/out
Note that $APPLCSF must be a full, absolute path, and the other two
are directory names.
If $APPLCSF is not set, it places the files under the product top of
the application associated with the request. For example, a PO report
would go under $PO_TOP/$APPLLOG and $PO_TOP/$APPLOUT
Logfiles go to: /u01/appl/po/9.0/log
Output files to: /u01/appl/po/9.0/out
All these directories must exist and have the correct permissions.
Note that all concurrent requests produce a log file, but not necessarily
an output file.
Concurrent manager logfiles follow the same convention, and will be
found in the $APPLLOG directory
What are the logfile and output file naming conventions?
Request logfiles: l.req
Output files: If $APPCPNAM is not set: .
If $APPCPNAM = REQID: o.out
If $APPCPNAM = USER: .out
Where: = The request id of the concurrent request
And: = The id of the user that submitted the request
Manager logfiles:
ICM logfile: Default is std.mgr, can be changed with the mgrname
startup parameter
Concurrent manager log: w.mgr
Transaction manager log: t.mgr
Conflict Resolution manager log: c.mgr
Where: is the concurrent process id of the manager
Can I delete a concurrent manager?
You can disable the manager by checking the ‘Enabled’ checkbox, or
you can simply Terminate the manager and it will not run again unless
you reactivate it.
If it is really necessary, you can query the manager in the
‘Define Manager’ form, and delete the row. (It is recommended that you
DO NOT do this)
What is the function of the ‘Conflict Resolution Manager’?
Concurrent managers read requests to start concurrent programs running. The
Conflict Resolution Manager checks concurrent program definitions for
incompatibility rules.
If a program is identified as Run Alone, then the Conflict Resolution Manager
prevents the concurrent managers from starting other programs in the same
conflict domain.
When a program lists other programs as being incompatible with it, the
Conflict Resolution Manager prevents the program from starting until any
incompatible programs in the same domain have completed running.
What is the ‘Internal Scheduler/Prereleaser’ manager?
The short name for this manager is FNDSCH. It is also known as the
Advanced Scheduler/Prereleaser Manager. This manager is intended
to implement Advanced Schedules. Its job is to determine when a
scheduled request is ready to run. Advanced Schedules were not fully
implemented in Release 11.0, they are implemented in Release 11.5,
but are not widely used by the various Apps products. General Ledger
uses FNDSCH for financial schedules based on different calendars and
period types. It is then possible to schedule AutoAllocation sets,
Recurring Journals, MassAllocations, Budget Formulas, and MassBudgets
to run according to the General Ledger schedules that have been
defined.
If financial schedules in GL are not being used then it is not a
problem to deactivate this manager.
What is the ‘Internal Monitor’ manager/service?
This manager/service is used to implement Distributed Concurrent Processing.
It monitors whether the ICM is still running, and if the ICM crashes,
it will restart it on another node.
You do not need to run this manager/service unless you are using Distributed
Concurrent Processing.
See the Installation manual and Sysadmin Guide for more info on DCP.
How do I check/set the PMON method?
To check the PMON method:
1) cd $FND_TOP/sql
2) sqlplus apps/apps @afimchk.sql
This will tell whether the internal manager is running, what the PMON
method is, and where the log file is.
To set the PMON method:
1) first shut the concurrent managers down
2) cd $FND_TOP/sql
3) sqlplus apps/apps @afimpmon.sql LOCK (or RDBMS)
How do I enable/disable the Conflict Resolution Manager?
Use the system profile option ‘Concurrent: Use ICM’.
Setting this to ‘No’ (which is the default) allows the CRM to be started.
Setting it to ‘Yes’ causes the CRM to be shutdown and the Internal
Manager (ICM) will take over the conflict resolution duties. If the CRM will
not start (it is started automatically by the ICM), check this profile option.
Note that using the ICM to resolve conflicts is not recommended.
The CRM’s sole purpose is to resolve conflicts, while the ICM has
other functions to perform as well. Only set this option to ‘YES’
if you have a good reason to do so.
How do I clean out the Concurrent Manager tables?
Cleaning out the tables is a useful method of making sure that there
are no invalid statuses that can prevent the managers from starting.
Previously, this has been done by truncating fnd_concurrent_processes
and/or fnd_concurrent_requests. Truncation of the tables is a little
drastic, and can cause problems later when trying to purge requests,
not to mention losing all of the request information.
Run the script, cmclean.sql, article Note 134007.1 CMCLEAN.SQL – Non
Destructive Script to Clean Concurrent Manager Tables
It will make sure the relevant status codes are valid without
deleting any information.
How do I tell concurrent manager processes apart at the OS level?
Use: pf -ef grep FNDLIBR
This will produce output like:
vd11 13703 13660 0 May 11 ? 0:01 FNDLIBR FND Concurrent_Processor
MANAGE OLOGIN=”APPS/94A491A1000000000000000000
n1070161 24936 24927 0 Apr 29 ? 0:05 FNDLIBR FND Concurrent_Processor
MANAGE OLOGIN=”APPS_APPDEMO/94C4B1C10000000000
n1070161 24938 24927 0 Apr 29 ? 0:06 FNDLIBR FND Concurrent_Processor
MANAGE OLOGIN=”APPS_APPDEMO/94C4B1C10000000000
n1070161 24927 24922 0 Apr 29 ? 2:03 FNDLIBR FND CPMGR FNDCPMBR sysmgr
=”" sleep=60 pmon=20 diag=N logfile=/u16/app
The last process, #24927, shows ‘FNDLIBR FND CPMGR’, this one is the
Internal Manager (ICM). Notice that it gives some of the parameters it
was started with, the other processes showing ‘Concurrent_Processor’
are Standard manager processes. Notice that the ICM process is the
parent process of the Standard managers. (processes 24936 and 24938)
Other managers will have the name of the executable, like ARLIBR or
INVLIBR:
$ ps -ef grep ARLIBR
vd11 13683 13660 0 May 11 ? 0:20 ARLIBR APPS/82A2A4940000000000000
000000000000000000000000000000000000000 AR ART
The Conflict Resolution manager will look like:
$ ps -ef grep FNDCRM
n1070161 24941 24927 0 Apr 29 ? 1:17 FNDCRM APPS_APPDEMO/84BFBEB900000
0000000000000000000000000000000000000000000000
What is the syntax for controlling the concurrent manager using startmgr and
concsub in NT?
On NT, the concurrent manager is run as an NT service, you start and
stop the managers using the Services control panel.
See the Applications Installation manual for NT, Appendix A for
details. See pg. 5-9 of this manual for instructions on creating the
concurrent manager service.
Why am I seeing pinging entries like this in the ICM logfile?
PING (0.0.0.0): 56 data bytes
64 bytes from 192.75.91.2: icmp_seq=0 ttl=255 time=0.705 ms
64 bytes from 192.75.91.2: icmp_seq=1 ttl=255 time=1.120 ms
Process monitor session ended : 29-FEB-2000 10:38:43
64 bytes from 192.75.91.2: icmp_seq=2 ttl=255 time=0.985 ms
64 bytes from 192.75.91.2: icmp_seq=3 ttl=255 time=1.006 ms
Pinging other machines is used in Distributed Concurrent Processing.
This means you have DCP turned on, using the environment variable
APPLDCP. Set APPLDCP to OFF and restart the managers.
I hit the Restart button to start the Standard manager, but it still did not
start?
Telling a manager to restart just sets the status to Restart. The ICM
will start it the next process monitor session or the next time the
ICM starts. Use Activate to start a manager immediately.
When a manager is deactivated manually, the ICM will not restart
it, you will need to set it to Restart, or activate it manually.
How many rows are in FND_CONCURRENT_REQUESTS and FND_CONCURRENT_PROCESSES
tables?
When tables reach above 3000-4000 rows, the performance begins to
diminish. You may want to run Purge Concurrent Request on a regular basis,
dependant on the amount of requests being run.
The Purge Concurrent Requests job can be used to purge:
Requests, Mgr logs, and All requests depending on what is chosen.
Use the following options: Enter = All, Mode = AGE, Mode Value = 15
The std.mgr log continuously grows where it may good to
archive it regularly.
Any processes pending in Internal or Conflict Resolution Manager?
Best course of action before starting the Concurrent Managers is to cancel
any “Deactivate” or “Verify” jobs pending in the Internal Manager and place
any other pending jobs on hold.
How do I turn on transaction manager diagnostics?
Set the profile option ‘Concurrent:Debug Flags’ to ‘TCTM1′ at the site
level. This will cause transactions to make debug entries in the
FND_CONCURRENT_DEBUG_INFO table. Truncate this table before running a
tranasction, then select the entries from the table.
Starting the managers with diag=Y will also produce more information
in the transaction manager logfile.
How do transaction managers work?
Briefly:
(See the server documentation for details on the DBMS_PIPE package)
1 ) A tranasction manager is started on the concurrent processing
server, and periodically reads the pipe for incoming transactions.
2 ) A client program (usually a form) calls the
FND_TRANSACTION.SYNCHRONOUS function.
3 ) This function writes a message into the pipe containing the program
to be run and its parameters.
4 ) FND_TRANSACTION.SYNCHRONOUS begins reading a return pipe for the
return status.
5 ) The manager sees the message in the pipe, retrieves the program id
and parameters.
6 ) The manager runs the program with the specified parameters. The
program will be of type ‘Immediate’, so there will not be a
separate concurrent request run.
7 ) The program completes, and the manager packs its return status into
the return pipe.
8 ) FND_TRANSACTION.SYNCHRONOUS reads the return value and passes it
back to its caller.
Note that these events take place essentially simultaneously on the
client and server. This is a synchronous transaction because the
client waits for the server to return, or times out waiting for it.
Problem….
When you try to submit a request like Active users or
Active responsibilities, request gets submitted.
When we view the help requests, you find that it is
inactive / nomanager.
Within 12 to 15 seconds, you refresh-it gets completed.
Initially, you could find only inactive and we look at
the diagnostic- the concurrent manager assigned is not
picking up.
There is no specialization rules in any managers except
the include program this source.
Solution….
Most often when this occurs where a request goes
“inactive/no manager” and is then processed a short time
later, the solution is to either increase the cache size
for your Standard manger, or increase the actual number of
Standard manager processes.
Cache Size is set on the CONCURRENT/MANAGER/DEFINE form. Basically,
this regulates how many requests a manager will pick up for each
sleep cycle.
How do I process more concurrent requests concurrently?
The Concurrent Manager parameters, (Query the concurrent manager by
Login as Sysadmin, navigate -> Concurrent -> Manager -> Define and Query for
the relevant concurrent manager), should be modified to handle more
concurrent requests concurrently, this can be done in two steps:
(i) Increase the Number of Target processes for the manager
(ii) Change the cache size of the concurrent manager as this determines
how many requests will be evaluated by a manager at a time and should match the target (process) value as set above.
RELATED DOCUMENTS
—————–
Note 1050938.6 What to Set $APPCPNAM for the Report Output File Naming Convention Format
Note 149600.1 FND_CONC_PP_ACTIONS and FND_RUN_REQ_PP_ACTIONS Growing Exponentially
Note 134007.1 CMCLEAN.SQL – Non Destructive Script to Clean Concurrent Manager Tables
Introduction
Oracle has a strict read-consistency model coupled with high concurrency that sets it apart from other database products such as Microsoft’s SQL Server. You can update rows in a table while I query those very same rows. Your updates will not get lost, my query results will not be corrupted, and neither of us will block the other from doing their work. The net result is that with Oracle we can run batch jobs, data loads, and reports all at the same time and never worry about things like “dirty reads” or “read locks.” However, this great functionality comes at a cost to performance—Oracle has to do more work to ensure read-consistency if one user is updating a table while another user is querying it. This raises the question: Just how much slower will a report run if a batch update job is running at the same time?
In this paper, we will first discuss briefly what is meant by read-consistency and how Oracle maintains it in a multi-user environment. Then we will briefly look at how Oracle’s read-consistency model might impact performance from a theoretical standpoint. Next, we’ll spend the bulk of this paper discussing in practical terms how to detect and measure performance degradation caused by concurrent activities that make Oracle work harder to maintain read-consistency. We will look at reproducible examples, TKPROF reports, and v$ views in order to measure how much slower a query runs when the tables being read are undergoing concurrent updates.
Would it be faster to run the update jobs and the report jobs sequentially instead of at the same time? What can we do if both jobs must run at the same time? How can we determine if the two are interacting with each other in a way that degrades performance? You will have the tools to answer questions like these after reading this paper.
A quick note about scope before we get started: This paper looks at the performance implications of Oracle’s read-consistency and concurrency mechanisms. We will not discuss the theoretical correctness of Oracle’s interpretation of the ANSI standards for transaction isolation levels or transaction serializability. Coding practices that developers must use in certain situations to maintain accurate results in a multi-user environment (such as SELECT FOR UPDATE) are also outside the scope of this paper.
Read-Consistency and Concurrency
In this section, we will explain what we mean by read-consistency. Then we’ll look at Oracle’s approach to read-consistency and concurrency, followed by other approaches used by other database vendors. This subject matter can get very complicated. We will keep the discussion at a pretty high level in order to present the concepts in enough detail to follow the rest of this paper, without being weighed down by all of the details of complex algorithms built into the Oracle engine.
We use the term “read-consistency” to mean the accurate retrieval of information. In particular, we want the results of a query to reflect data integrity and correctness as of a single point in time. By data integrity we mean that transactional boundaries must be preserved. For example, you should never see invoice lines without the invoice header—or vice versa—if they were created in the same transaction.
By correctness as of a single point in time, we mean that no matter how long a query takes to run, the result set from the very first row to the very last row must reflect the state of the data in the database as of one point in time. Suppose you have $100 in your checking account and $1,300 in your savings account and you go to your bank’s web site to transfer $500 from savings into checking. What if the bank was computing your daily combined balance right as you executed the funds transfer? Imagine the bank seeing your checking account balance as $100 (before the funds transfer) and your savings account balance as $800 (after the funds transfer). This would lead the bank to compute your combined balance as $900, and you would be hit with a service charge because you were below the $1,000 minimum combined balance. The bank would be wrong to assess this service charge, and their error would stem from the fact that they did not have a read-consistent view of your bank account balances.
Read-Consistency in Oracle
Oracle implements read-consistency in a way that maximizes concurrency. A query in Oracle is never blocked by write operations or other queries, and a write operation is never blocked by queries. That is, I can query a table while another user queries or even updates the same table, and I will not be stopped from proceeding with my query. If the other user writes to the table I am querying, they will never be blocked by my query.
All queries in Oracle give results that are based on the data that was in the database at one single point in time, something we will refer to as a query’s reference point. Thus, no matter how long a query takes to complete, the entire result set will be based on the data as of one exact point in time. Typically a query’s reference point is the moment the query began. Oracle also provides a facility known as read-only transactions where a whole set of queries can be based on the same reference point—the moment the read-only transaction began. This allows read-consistency to be maintained across queries.
Oracle uses a multi-versioning mechanism in order to offer such a high degree of concurrency while maintaining read-consistency. Multi-versioning was introduced way back in Oracle V6. When transactions insert, update, or delete data in tables, information is written to an undo segment (also known as a rollback segment) so that Oracle will be able to back out the change if the user decides to roll back their transaction instead of committing it. However, this undo information does more than allow for transaction rollback—it provides the foundation for the multi-versioning mechanism.
If a query comes across a data block that has been updated by another session but has not yet been committed, the query reads entries from the appropriate undo segment and recreates a version of the data block that looks like the data block did before the uncommitted transaction changed it. If a query comes across a data block that was updated and committed after the query began, then the query again reads undo entries and this time recreates a version of the data block that looks like the data block as of the query’s reference point.
In summary, Oracle uses the information in undo segments to allow the reconstruction of data blocks as they looked in the past—what we call multi-versioning. Oracle uses multi-versioning to enable queries to run concurrently with updates and other queries with no blocking required. This, in a nutshell, is how Oracle is able to ensure read-consistency while allowing a high degree of concurrency.
Other Approaches to Read-Consistency
Other database vendors take different approaches to read-consistency and concurrency. Some databases limit concurrency in order to avoid circumstances that could cause data integrity or accuracy issues. Others take the approach of allowing concurrency with the caveat that data integrity or accuracy could be compromised.
Using “read locks” and “write locks” it is possible to ensure read-consistency, but at the expense of concurrency. On such databases, users wait in line to access data in certain situations. A batch job might not be able to update data in a table, for example, because a report job is reading data from the same table. Or perhaps a user’s query will have to wait until an update transaction commits before the tables can be accessed.
Allowing concurrent access to data without having a sophisticated mechanism like multi-versioning to preserve read-consistency can lead to a variety of data anomalies. Some databases allow “dirty reads” to happen—a situation where the results of a query include uncommitted transactions. Imagine making a data entry error and rolling back the update, but a batch job that was running at the same time included the erroneous data in its final report! This can happen in databases that allow dirty reads. “Fuzzy reads,” meanwhile, can arise when a query’s result set is not accurate as of a single point in time—the bank funds transfer example discussed earlier is an example of this phenomenon.
The Theoretical Cost of Read-Consistency in Oracle
In this section, we will look at the performance implications of Oracle’s read-consistency mechanisms from a theoretical perspective. Again we will keep the discussion fairly high level and stick to concepts that will provide useful background for the rest of this paper.
Before Oracle can use the contents of a data block while processing a query, it must first check to see if the data block contains any updates made after the query’s reference point. This seems like a very inexpensive check, because Oracle records a timestamp of sorts (called the system change number or SCN) in data blocks whenever they are updated. Comparing the SCN of the query’s reference point to the data block’s SCN seems easy.
Oracle must also check to see if the data block contains uncommitted changes made by another session. This check seems inexpensive as well, because Oracle maintains in each data block a list of “interested transactions” (called the ITL) that are active in the data block. If no transactions are active in a data block, then Oracle knows there is no uncommitted work there.
Again, these two checks that Oracle performs when reading every data block during query processing seem like they should have little impact on overall performance. However, we need to consider the situation where one or both of these checks indicate that the contents of a data block are not suitable for use by the query as-is and an alternate version of the data block must be created. In this case, Oracle needs to read data from the undo segment (listed in the ITL) and reconstruct a copy of the data block as it would have looked before the recent or uncommitted transaction. This could require physical disk I/O if the needed undo blocks are not already in the buffer cache. An additional buffer in the buffer cache will be used to hold this altered data block, and additional CPU time will be required to allocate the buffer in the buffer cache, copy the data block, and apply the undo. Furthermore, there is the risk that the necessary undo information will no longer be available because that extent of the undo segment has been reused. In this situation the ORA-01555 “snapshot too old” error will occur and the query will fail.
To summarize: Oracle maintains a timestamp and list of interested transactions in every data block in order to ensure read-consistency. Maintaining this information in every data block does not seem like it should be very expensive. While processing a query, Oracle must check the SCN and ITL on every data block before looking at its contents. This seems like it should be very inexpensive. But if Oracle determines that it cannot use the data block as-is and needs to reconstruct an older version, then a possibly nontrivial performance price will need to be paid.
In general, these performance costs seem like a small price to pay in order to maintain data integrity and accuracy without sacrificing concurrency. Moreover, if we can detect when excessive multi-versioning is happening, quantify it, and figure out ways to avoid it or at least minimize its performance impacts, then we will be even better off.
Measuring the True Cost of Read-Consistency
In this section, we will set theory aside and move on to reproducible test cases. First we will set up a demo schema, and then we will run through two simulations. In each scenario we will measure resource consumption for a query both with and without other activities taking place that force Oracle to create alternate versions of data blocks. The difference in resource consumption in each simulation should give us some insight into the actual resource cost of multi-versioning.
I encourage you to try the simulations shown here on your Oracle system. The results I will present were collected on an Oracle 9i database running on Sun Solaris. I expect you would see similar results on any version of Oracle ranging from Oracle V6 up through Oracle 10g. Although the code was written for Oracle 9i, it should work as-is on Oracle 10g, and with minor modifications on Oracle 8i.
Schema Setup
We will create a few tables for a simulated banking application and populate them with pseudo-random data. By pseudo-random, I mean data values that are scattered over a spectrum in a predictable manner. The data is not truly random. If you drop the tables and reload them on the same database using the code shown here, the tables should end up with the exact same data on the second and subsequent loads. This allows you to repeat a simulation without changing the expected results.
First we create a separate tablespace just for this project. We’ll turn off Automatic Segment Space Management (ASSM) so that Oracle allocates data blocks within segments in a linear, easily predictable manner:
CREATE TABLESPACE test DATAFILE '/u03/oradata/dev920ee/test01.dbf' SIZE 200m EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT MANUAL;
Next we grant quota on this tablespace to the database user who will own the schema objects we are about to create:
ALTER USER rschrag QUOTA UNLIMITED ON test;
Now we are ready to create a bank_accounts table and load it with 1,000,000 records. Each row will contain information about one checking or savings account. About 90% of the accounts will be set as active and the other 10% will be inactive. We won’t create and populate a bank_customers table, but we will assign a customer_id to each of the bank accounts. We’ll occasionally repeat customer_ids so that some customers have multiple accounts. We’ll also include an other_stuff column in the table so that each row takes up about 120 bytes. The code to create and populate the bank_accounts table is as follows:
CREATE TABLE bank_accounts
(
account_id NUMBER,
account_number VARCHAR2(18),
customer_id NUMBER,
current_balance NUMBER,
last_activity_date DATE,
account_type VARCHAR2(10),
status VARCHAR2(10),
other_stuff VARCHAR2(100)
)
TABLESPACE test;
BEGIN
dbms_random.seed ('Set the random seed so that ' ||
'this script will be repeatable');
FOR i IN 0..9 LOOP
FOR j IN i * 100000..i * 100000 + 99999 LOOP
INSERT INTO bank_accounts
(
account_id, account_number, customer_id,
current_balance, last_activity_date,
account_type, status, other_stuff
)
VALUES
(
j,
LPAD (LTRIM (TO_CHAR (TRUNC (dbms_random.value * 1000000000000000000))),
15, '0'),
TRUNC (dbms_random.value * 700000),
TRUNC (dbms_random.value * 5000, 2) + 250.00,
TO_DATE ('12-31-2005 12:00:00', 'mm-dd-yyyy hh24:mi:ss') -
dbms_random.value * 30,
DECODE (TRUNC (dbms_random.value * 3),
1, 'SAVINGS', 'CHECKING'),
DECODE (TRUNC (dbms_random.value * 10),
1, 'INACTIVE', 'ACTIVE'),
'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'
);
END LOOP;
COMMIT;
END LOOP;
END;
/
To complete the setup of the bank_accounts table, we create a primary key and compute optimizer statistics for the table and primary key index:
ALTER TABLE bank_accounts ADD CONSTRAINT bank_accounts_pk PRIMARY KEY (account_id) USING INDEX TABLESPACE test; BEGIN dbms_stats.gather_table_stats (USER, 'BANK_ACCOUNTS', cascade=>TRUE); END; /
Next we create a bank_transactions table and load it with approximately 90,000 records. Each row will contain information about one deposit or withdrawal. This table will simulate a queue that is populated by ATMs as people deposit and withdraw money. We’ll set a processed flag to “n” on each row for now to show that the transaction has not been applied to the bank_accounts table yet. The code to create and populate the bank_transactions table is as follows:
CREATE TABLE bank_transactions
(
transaction_id NUMBER,
account_id NUMBER,
transaction_date DATE,
transaction_type VARCHAR2(10),
amount NUMBER,
processed VARCHAR2(1)
)
TABLESPACE test;
DECLARE
v_transaction_id NUMBER;
v_transaction_date DATE;
v_transaction_type VARCHAR2(10);
v_amount NUMBER;
BEGIN
v_transaction_id := 1;
v_transaction_date := TO_DATE ('01-01-2006 00:00:00',
'mm-dd-yyyy hh24:mi:ss');
FOR i IN 1..100000 LOOP
v_amount := TRUNC (dbms_random.value * 10) * 20 + 20;
IF TRUNC (dbms_random.value * 2) = 1 THEN
v_transaction_type := 'DEPOSIT';
ELSE
v_amount := 0 - v_amount;
v_transaction_type := 'WITHDRAWAL';
END IF;
INSERT INTO bank_transactions
(
transaction_id, account_id, transaction_date,
transaction_type, amount, processed
)
SELECT v_transaction_id, account_id, v_transaction_date,
v_transaction_type, v_amount, 'n'
FROM bank_accounts
WHERE account_id = TRUNC (dbms_random.value * 1000000)
AND status = 'ACTIVE';
v_transaction_id := v_transaction_id + SQL%ROWCOUNT;
v_transaction_date := v_transaction_date + (dbms_random.value / 5000);
END LOOP;
COMMIT;
END;
/
To complete the setup of the bank_transactions table, we create a primary key and compute optimizer statistics for the table and primary key index:
ALTER TABLE bank_transactions ADD CONSTRAINT bank_transactions_pk PRIMARY KEY (transaction_id) USING INDEX TABLESPACE test; BEGIN dbms_stats.gather_table_stats (USER, 'BANK_TRANSACTIONS', cascade=>TRUE); END; /
To complete the schema setup, we create a stored procedure that posts transactions to bank account balances by reading a specified number of bank_transactions rows and updating rows in the bank_accounts table accordingly. The stored procedure does the work in an autonomous transaction as follows:
CREATE OR REPLACE PROCEDURE post_transactions (p_record_count IN NUMBER)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
CURSOR c_bank_transactions IS
SELECT account_id, transaction_date, amount
FROM bank_transactions
WHERE processed = 'n'
ORDER BY transaction_id
FOR UPDATE;
v_record_count NUMBER;
BEGIN
v_record_count := 0;
FOR r IN c_bank_transactions LOOP
UPDATE bank_accounts
SET current_balance = current_balance + r.amount,
last_activity_date = r.transaction_date
WHERE account_id = r.account_id;
UPDATE bank_transactions
SET processed = 'y'
WHERE CURRENT OF c_bank_transactions;
v_record_count := v_record_count + 1;
EXIT WHEN v_record_count >= p_record_count;
END LOOP;
COMMIT;
END post_transactions;
/
Our schema is now complete, and we are ready to run the simulations.
Simulation #1: Querying a Bank Balance While Posting a Deposit to a Different Account
In our theoretical discussion we pointed out that if Oracle finds uncommitted work in a data block while processing a query, Oracle must reconstruct a version of the data block from before the uncommitted work was applied to the data block. While the test to see if uncommitted work is present does not seem expensive, creating an alternate version of the data block if necessary could take some effort. In this simulation, we will quantify that effort.
First we start SQL*Plus and log onto the database in a brand new session. Then we enable tracing at a highly detailed level with the following statements:
ALTER SESSION SET statistics_level = ALL; ALTER SESSION SET sql_trace = TRUE;
Next we run the following query to retrieve information about one specific bank account:
SELECT account_number, status, account_type,
TO_CHAR (last_activity_date, 'mm-dd-yyyy hh24:mi:ss') last_activity,
TO_CHAR (current_balance, '$999,990.00') current_balance
FROM bank_accounts
WHERE account_id = 2;
Depending on the contents of the buffer cache and the shared SQL area, this query may or may not incur physical disk reads and/or a hard parse. To eliminate these variables and establish a discernable pattern, we run the same query three more times. It is important to keep the query the exact same each time we run it—even small changes in white space will cause a new hard parse.
Now we start a second SQL*Plus session and update (but do not commit) one row in the bank_accounts table as follows:
UPDATE bank_accounts
SET last_activity_date =
TO_DATE ('01-03-2006 11:15:22', 'mm-dd-yyyy hh24:mi:ss'),
current_balance = current_balance + 20
WHERE account_id = 3;
Notice that this statement updates a different row in the bank_accounts table than the one that we have been querying. However, the two rows should reside in the same data block. (This is one of the reasons we created our test tablespace with ASSM turned off.)
Now we go back to the first SQL*Plus session and repeat our old query a fifth time:
SELECT account_number, status, account_type,
TO_CHAR (last_activity_date, 'mm-dd-yyyy hh24:mi:ss') last_activity,
TO_CHAR (current_balance, '$999,990.00') current_balance
FROM bank_accounts
WHERE account_id = 2;
In this SQL*Plus session we have now queried a row from a table five times—four times with no read-consistency conflicts and one time with an uncommitted update elsewhere in the same data block that will force Oracle to perform a multi-versioning operation.
We can now exit both SQL*Plus sessions and turn our attention to the trace file we have created. We fetch the trace file and run TKPROF with the aggregate=no and sys=no options:
tkprof simulation1.trc simulation1.prf aggregate=no sys=no
Our first execution of the query appears in the TKPROF report like this:
SELECT account_number, status, account_type,
TO_CHAR (last_activity_date, 'mm-dd-yyyy hh24:mi:ss') last_activity,
TO_CHAR (current_balance, '$999,990.00') current_balance
FROM bank_accounts
WHERE account_id = 2
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.03 0.05 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.02 4 4 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.03 0.08 4 4 0 1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 97
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID BANK_ACCOUNTS (cr=4 r=4 w=0 time=14008 us)
1 INDEX UNIQUE SCAN BANK_ACCOUNTS_PK (cr=3 r=3 w=0 time=13763 us)(object id 32144)
We can see that a hard parse took place as well as four physical disk reads. Since the query itself is very simple and only required four logical reads, the hard parse and physical reads overshadow the true work required to perform this query. Looking at the second execution of the query in the TKPROF report, we see:
SELECT account_number, status, account_type,
TO_CHAR (last_activity_date, 'mm-dd-yyyy hh24:mi:ss') last_activity,
TO_CHAR (current_balance, '$999,990.00') current_balance
FROM bank_accounts
WHERE account_id = 2
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 4 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 4 0 1
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 97
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID BANK_ACCOUNTS (cr=4 r=0 w=0 time=58 us)
1 INDEX UNIQUE SCAN BANK_ACCOUNTS_PK (cr=3 r=0 w=0 time=36 us)(object id 32144)
We can see that no hard parse or physical reads were necessary this time, now that the parse and necessary data blocks are cached in the SGA. We can also see that the execution plan and number of logical reads (four) for this execution are the same as before. Since the query runs so quickly, the CPU and elapsed time figures reported with a resolution of one hundredth of a second are not very useful. However, the top line of the Row Source Operation listing shows that the query ran in 58 microseconds.
We won’t show the TKPROF output for the third and fourth executions of the query here, but they were extremely similar to the second execution. In fact the only difference was that the third and fourth executions ran in 76 and 78 microseconds, respectively.
Now let’s look at the TKPROF output for the fifth execution of the query. Remember that this execution ran after another session had updated but not yet committed a row in the same data block we are accessing:
SELECT account_number, status, account_type,
TO_CHAR (last_activity_date, 'mm-dd-yyyy hh24:mi:ss') last_activity,
TO_CHAR (current_balance, '$999,990.00') current_balance
FROM bank_accounts
WHERE account_id = 2
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 6 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 6 0 1
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 97
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID BANK_ACCOUNTS (cr=6 r=0 w=0 time=538 us)
1 INDEX UNIQUE SCAN BANK_ACCOUNTS_PK (cr=3 r=0 w=0 time=64 us)(object id 32144)
This time Oracle performed six logical reads instead of four, and the query took 538 microseconds instead of something in the range of 58 to 78 microseconds. From the Row Source Operation Listing we can see that the extra logical reads and time were spent on the table access and not the index access. This is because when Oracle looked at the data block from the table, it found the uncommitted update from the second session and had to build an alternate version of the data block with the uncommitted work rolled back. This extra effort required two logical reads and took roughly 460 microseconds.
Should we really care about two extra logical reads and a couple hundred microseconds? Are we not splitting hairs here? In this simulation the performance penalty caused by the concurrent update would probably not cause anybody concern. However, there is a different way to look at this—we could say that the concurrent update caused this query to require 50% more logical reads and seven times as much execution time. This may not be a concern for one query as simple as the one we have run here. But clearly updates could cause significant performance degradation to concurrent queries if multi-versioning were necessary for a large number of data blocks. Also keep in mind that here we looked at the simplest multi-versioning scenario; what if there had been a dozen updates that were not committed or occurred after the query’s reference point? Creating a version of the data block usable by this query would have been much more expensive.
Another interesting fact this simulation points out is that multi-versioning will occur even if a session queries different rows from data blocks than those that are being updated by another session. That is, a session could query one set of rows while another session updates a totally different set of rows. But if any queried rows share a data block with updated rows, then multi-versioning will occur.
Simulation #2: Reporting Minimum Bank Balances While Posting Transactions
In the first simulation we looked at a very simple query so that we could pinpoint the performance cost of having Oracle build an alternate version of one data block in a simple case. In this simulation we will examine a situation in which a report is being run to determine which customers have combined bank account balances below the minimum while a batch job is posting withdrawals and deposits from ATMs into the bank_accounts table. In this simulation we will see widespread multi-versioning.
Like last time, we start SQL*Plus, log onto the database in a brand new session, and enable tracing:
ALTER SESSION SET statistics_level = ALL; ALTER SESSION SET sql_trace = TRUE;
Next, we run a query to display customers whose combined account balance is less than $1,000. To keep the report output brief, we will only look at a subset of customers:
VARIABLE low_balances REFCURSOR
BEGIN
OPEN :low_balances FOR
SELECT /*+ CACHE (bank_accounts) */
customer_id, COUNT (*) active_accounts,
SUM (current_balance) combined_balance,
MAX (last_activity_date) last_activity_date
FROM bank_accounts
WHERE status = 'ACTIVE'
AND customer_id BETWEEN 10000 AND 10999
GROUP BY customer_id
HAVING SUM (current_balance) < 1000
ORDER BY active_accounts, customer_id;
END;
/
SELECT b.value, a.name
FROM v$statname a, v$mystat b
WHERE a.name IN ('consistent gets', 'consistent changes')
AND b.statistic# = a.statistic#
ORDER BY a.statistic#;
PRINT low_balances
SELECT b.value, a.name
FROM v$statname a, v$mystat b
WHERE a.name IN ('consistent gets', 'consistent changes')
AND b.statistic# = a.statistic#
ORDER BY a.statistic#;
The CACHE hint in the query tells Oracle to keep data blocks read from the bank_accounts table in the buffer cache according to the LRU algorithm, something that usually does not happen during full table scans. This will allow us to eliminate the variability of physical disk reads from the mix when evaluating the TKPROF report.
Also note the statistics reporting before and after the query results are fetched. This allows us to see how many logical reads (in read-consistent mode) Oracle had to perform, as well as how many changes had to be rolled back in alternate versions of data blocks to ensure read-consistency.
As in the first simulation, the query may or may not incur physical disk reads and/or a hard parse the first time it is run. To eliminate these variables and establish a discernable pattern, we again run the same query three more times. Each execution will report about 16,680 consistent gets and zero consistent changes. The bank_accounts table segment is 16,668 blocks long, so the reported number of consistent gets makes intuitive sense. (Figure a few extra reads for overhead.)
Now we run the query a fifth time, except we call the post_transactions stored procedure after opening the cursor and before fetching the results from it. The post_transactions call will post 10,000 deposits and withdrawals from the bank_transactions table to the bank_accounts table in an autonomous transaction, simulating updates being performed in another session by a batch job while the minimum balance report is running. Since the updates occur after the query’s reference point (the opening of the cursor), the cursor output must be based on bank balances before the post_transactions call. The fifth query execution looks like this:
BEGIN
OPEN :low_balances FOR
SELECT /*+ CACHE (bank_accounts) */
customer_id, COUNT (*) active_accounts,
SUM (current_balance) combined_balance,
MAX (last_activity_date) last_activity_date
FROM bank_accounts
WHERE status = 'ACTIVE'
AND customer_id BETWEEN 10000 AND 10999
GROUP BY customer_id
HAVING SUM (current_balance) < 1000
ORDER BY active_accounts, customer_id;
END;
/
EXECUTE post_transactions (10000)
SELECT b.value, a.name
FROM v$statname a, v$mystat b
WHERE a.name IN ('consistent gets', 'consistent changes')
AND b.statistic# = a.statistic#
ORDER BY a.statistic#;
PRINT low_balances
SELECT b.value, a.name
FROM v$statname a, v$mystat b
WHERE a.name IN ('consistent gets', 'consistent changes')
AND b.statistic# = a.statistic#
ORDER BY a.statistic#;
This fifth execution of the query generated 26,691 consistent gets and 10,000 consistent changes. As we would expect, Oracle had to create alternate versions of data blocks as they would have appeared at the query’s reference point, and this required many extra logical reads.
We have now run the minimum balance report five times—four times with no read-consistency conflicts and one time with numerous committed updates that had to be rolled back in the name of read-consistency. We fetch the trace file and run TKPROF again with the aggregate=no and sys=no options:
tkprof simulation2.trc simulation2.prf aggregate=no sys=no
Our first execution of the minimum balance report looks like this:
SELECT /*+ CACHE (bank_accounts) */
customer_id, COUNT (*) active_accounts,
SUM (current_balance) combined_balance,
MAX (last_activity_date) last_activity_date
FROM bank_accounts
WHERE status = 'ACTIVE'
AND customer_id BETWEEN 10000 AND 10999
GROUP BY customer_id
HAVING SUM (current_balance) < 1000
ORDER BY active_accounts, customer_id
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.02 0.06 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 4 5.24 7.84 16669 16679 0 48
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6 5.26 7.91 16669 16679 0 48
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 97 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
48 SORT ORDER BY (cr=16679 r=16669 w=0 time=7846722 us)
48 FILTER (cr=16679 r=16669 w=0 time=7835555 us)
708 SORT GROUP BY (cr=16679 r=16669 w=0 time=7834846 us)
1281 TABLE ACCESS FULL BANK_ACCOUNTS (cr=16679 r=16669 w=0 time=7795324 us)
We can see that a hard parse took place, as well as numerous physical reads. Looking at the second execution of the query in the TKPROF report, we see:
SELECT /*+ CACHE (bank_accounts) */
customer_id, COUNT (*) active_accounts,
SUM (current_balance) combined_balance,
MAX (last_activity_date) last_activity_date
FROM bank_accounts
WHERE status = 'ACTIVE'
AND customer_id BETWEEN 10000 AND 10999
GROUP BY customer_id
HAVING SUM (current_balance) < 1000
ORDER BY active_accounts, customer_id
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 4 2.80 2.79 0 16679 0 48
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6 2.80 2.79 0 16679 0 48
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 97 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
48 SORT ORDER BY (cr=16679 r=0 w=0 time=2793933 us)
48 FILTER (cr=16679 r=0 w=0 time=2793371 us)
708 SORT GROUP BY (cr=16679 r=0 w=0 time=2792563 us)
1281 TABLE ACCESS FULL BANK_ACCOUNTS (cr=16679 r=0 w=0 time=2768765 us)
No hard parse or physical reads were necessary this time. As we would expect, the execution plan and number of logical reads (16,679) for this execution are the same as for the first execution. Moreover, the number of logical reads reported by TKPROF matches what we retrieved from v$mystat.
The third and fourth query executions appeared very similar to the second, but the fifth was again quite different:
SELECT /*+ CACHE (bank_accounts) */
customer_id, COUNT (*) active_accounts,
SUM (current_balance) combined_balance,
MAX (last_activity_date) last_activity_date
FROM bank_accounts
WHERE status = 'ACTIVE'
AND customer_id BETWEEN 10000 AND 10999
GROUP BY customer_id
HAVING SUM (current_balance) < 1000
ORDER BY active_accounts, customer_id
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 4 3.42 3.81 0 26691 0 48
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6 3.42 3.81 0 26691 0 48
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 97 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
48 SORT ORDER BY (cr=26691 r=0 w=0 time=3814002 us)
48 FILTER (cr=26691 r=0 w=0 time=3813425 us)
708 SORT GROUP BY (cr=26691 r=0 w=0 time=3812575 us)
1281 TABLE ACCESS FULL BANK_ACCOUNTS (cr=26691 r=0 w=0 time=3780240 us)
The number of logical reads climbed 60% to 26,691 (matching what we saw in v$mystat). CPU and elapsed time climbed by over 20% also. It is interesting to note that the Row Source Operation listing now shows that a full table scan of the bank_accounts table took 26,691 logical reads, when in fact the table has only 16,668 blocks below the highwater mark. This should be a clue to us that multi-versioning is happening. The 10,000 consistent changes reported from v$mystat confirms this.
An additional performance implication of the multi-versioning that is not explicitly reported by TKPROF is that Oracle had to allocate additional buffers in the buffer cache in order to build those 10,000 additional data block versions. On a busy database, multi-versioning could cause data blocks to get aged out of the buffer cache sooner. This could lead to additional physical reads when those data blocks are later needed.
In this exercise, we have simulated the concurrent running of a report and a batch job that updates the same data being read by the report. By looking at session statistics and the TKPROF report we can see that doing so caused Oracle to do significant multi-versioning, and this in turn caused a nontrivial performance penalty.
Lessons Learned
By understanding how concurrent updates impact queries in Oracle, we can better understand the performance implications of multi-versioning, detect when excessive multi-versioning is happening, and minimize performance degradation.
Interaction Between Writers and Readers
As advertised, writers do not block readers and readers do not block writers in Oracle. However, Oracle must resort to multi-versioning when users query data residing in data blocks that are being updated by other users at roughly the same time. The users could be working with different rows of data, but if the rows reside in the same data block then multi-versioning will be necessary.
Each time Oracle needs to create an alternate version of a data block, a performance penalty is paid in the form of extra logical reads, extra CPU time, allocation of an extra buffer in the buffer cache, possibility of extra physical reads, and the possibility that the undo information required to create the alternate version is no longer available (the ORA-01555 condition).
The impact of multi-versioning on overall database performance is usually not an issue. As we have seen, creating an alternate version of a data block can cost as little as two logical reads and a few hundred microseconds of elapsed time (or even less on a server with a faster CPU). However, the scheduling of batch jobs and reports can unintentionally lead to widespread multi-versioning. This can drag down system performance significantly.
Detecting Excessive Multi-versioning
How much multi-versioning should be considered excessive will vary from one environment to the next. Ultimately it becomes a judgment call based on the needs of the users and the system resources available. Oracle gives us a few system-wide and session-level statistics for measuring multi-versioning activity, and there are also a few clues we can look for in individual execution plan statistics.
The v$sysstat, v$sesstat, and v$mystat views show several statistics relating to multi-versioning, including:
- consistent gets: The number of logical reads in read-consistent mode.
- consistent changes: The number of changes that have been rolled back while performing consistent gets.
- no work – consistent read gets: The number of logical reads in read-consistent mode where no changes had to be rolled back and the ITL did not need cleanout (updating to reflect that a previously active transaction has since committed).
- cleanouts only – consistent read gets: The number of logical reads in read-consistent mode where the ITL needed cleanout.
- rollbacks only – consistent read gets: The number of logical reads in read-consistent mode where changes had to be rolled back.
- cleanouts and rollbacks – consistent read gets: The number of logical reads in read-consistent mode where both changes had to be rolled back and the ITL needed cleanout.
In theory, the bottom four statistics should add up to the value of the consistent gets statistic, but in fact they usually won’t. Steve Adams attributes this to a bug in the consistent gets statistic, which he discusses at http://www.ixora.com.au/q+a/0012/12093712.htm.
TKPROF, v$sql_plan_statistics, and v$sql_plan_statistics_all are all able to provide actual resource usage statistics for individual row source operations of a SQL statement’s execution plan. (Note the distinction between actual resource usage and projected usage as estimated by the cost-based optimizer.) You should suspect that multi-versioning is happening when you see either of the following in a row source operation:
- A table access by index ROWID that consumes more than one logical read per row accessed.
- A full table scan or index full scan that consumes more logical reads than there are blocks in the segment below the highwater mark (allowing for a few extra logical reads for overhead).
Reducing Performance Degradation
The key to reducing performance degradation from excessive multi-versioning is to reduce the amount of multi-versioning that takes place. This is usually achieved through schedule adjustment. For example, if the inventory report is running too slowly, then try not to run it at the same time the inventory update batch job runs.
If there is a strong desire to reduce the volume of multi-versioning but conflicting reports and update jobs must run concurrently, then in some cases there could conceivably be a benefit to storing fewer rows per data block by setting PCTFREE artificially high. This will cause segments to take up more space, and operations like full table scans will require the reading of more blocks. But in some cases this can reduce multi-versioning activity.
Finally, if multi-versioning cannot be avoided, then make sure that multi-versioning is not dragging down buffer cache efficiency by tying up multiple buffers with different versions of the same data blocks. In this situation it is possible that a larger buffer cache could help reduce the negative impact of multi-versioning on cache efficiency.
It should be emphasized that multi-versioning does not usually cause significant performance problems. You should not pursue the reduction of multi-versioning operations unless you have determined that this is indeed a root cause of performance issues in your environment and there is no lower hanging fruit available to go after first. If you do determine that excessive multi-versioning is a problem in your environment and you cannot address the issue through job schedule adjustment, then think carefully and run validation tests before adjusting PCTFREE settings or buffer cache sizes in production environments.
Conclusion
Oracle’s sophisticated multi-versioning mechanism allows a high degree of concurrency without compromising read-consistency. Users can query data while other users are updating the very same data, and there is no need to worry about data integrity or accuracy issues in the result set. The multi-versioning mechanism was introduced in Oracle V6, and today this part of the Oracle kernel is extremely mature and stable.
Just because we can run reports and batch update jobs against the same data at the same time doesn’t mean we necessarily should. If Oracle needs to create alternate versions of data blocks as they appeared at a time in the past in order to preserve data integrity and accuracy, extra system resources will be required. This is usually not a problem, but excessive multi-versioning can degrade performance to a noticeable degree.
Through the use of system- and session-level statistics and execution plan detailed statistics we can detect when multi-versioning is occurring. Through tracing and the use of TKPROF we can quantify the cost of multi-versioning. Although we don’t typically need to worry about the performance implications of multi-versioning, Oracle gives us the tools to detect when it is happening and to measure the impact on system performance. We can use this information to decide when it is necessary to reschedule conflicting batch jobs and reports, or take more drastic action.
Oracle Application Server 10g Release 2 (10.1.2) Installation On RedHat Advanced Server 3.0 (Update 3)
In this article I’ll describe the installation of Oracle Application Server 10g Release 2 (10.1.2), Oracle’s J2EE Application Server, on RedHat Advanced Server 3.0 (update 3). The article assumes you’ve performed the standard advanced server installation including the development tools.
- Download Software
- Unpack Files
- Hosts File
- Set Kernel Parameters
- Setup
- Installation
- Post Installation
Note. I’ve experienced issues with installations on systems that are updated beyond Update 3, which is the version certified by Oracle.
Download Software
Download the following software:
Unpack Files
Unpack the contents of the file:
cpio -idmv < as_linux_x86_core_101200.cpio
You should now have a directory (Disk1) containing installation files.
Hosts File
The /etc/hosts file must contain a fully qualified name for the server:
<IP-address> <fully-qualified-machine-name> <machine-name>
Set Kernel Parameters
Add the following lines to the /etc/sysctl.conf file:
kernel.shmall = 2097152 kernel.shmmax = 2147483648 kernel.shmmni = 4096 # semaphores: semmsl, semmns, semopm, semmni kernel.sem = 256 32000 100 142 fs.file-max = 131072 net.ipv4.ip_local_port_range = 10000 65000 kernel.msgmni = 2878 kernel.msgmax = 8192 kernel.msgmnb = 65535
Run the following command to change the current kernel parameters:
/sbin/sysctl -p
Add the following lines to the /etc/security/limits.conf file:
* soft nproc 2047 * hard nproc 16384 * soft nofile 2048 * hard nofile 65536
Add the following line to the /etc/pam.d/login file, if it does not already exist:
session required pam_limits.so
Setup
Install the following packages:
# From RedHat AS3 Disk 2
cd /mnt/cdrom/RedHat/RPMS
rpm -Uvh setarch-1.3-1.i386.rpm
rpm -Uvh sysstat-4.0.7-4.EL3.3.i386.rpm
# From RedHat AS3 Disk 3
cd /mnt/cdrom/RedHat/RPMS
rpm -Uvh openmotif21-2.1.30-8.i386.rpm
rpm -Uvh ORBit-0.5.17-10.4.i386.rpm
rpm -Uvh libpng10-1.0.13-15.i386.rpm
rpm -Uvh gnome-libs-1.4.1.2.90-34.1.i386.rpm
rpm -Uvh compat-glibc-7.x-2.2.4.32.6.i386.rpm
compat-gcc-7.3-2.96.128.i386.rpm
compat-gcc-c++-7.3-2.96.128.i386.rpm
compat-libstdc++-7.3-2.96.128.i386.rpm
compat-libstdc++-devel-7.3-2.96.128.i386.rpm
Create the new groups and users:
groupadd oinstall groupadd dba groupadd oper useradd -g oinstall -G dba -s /bin/ksh oracle passwd oracle
Create the directories in which the Oracle software will be installed:
mkdir -p /u01/app/oracle/product/j2ee_10_1_2 chown -R oracle.oinstall /u01
Login as root and issue the following command:
xhost +<machine-name>
Login as the oracle user and add the following lines at the end of the .profile file:
# Oracle Settings
TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/j2ee_10_1_2; export ORACLE_HOME
ORACLE_TERM=xterm; export ORACLE_TERM
PATH=/usr/sbin:$ORACLE_HOME/bin:$PATH; export PATH
PATH=$PATH:$ORACLE_HOME/dcm/bin:$ORACLE_HOME/opmn/bin; export PATH
PATH=$PATH:$ORACLE_HOME/Apache/Apache/bin; export PATH
if [ $USER = "oracle" ]; then
if [ $SHELL = "/bin/ksh" ]; then
ulimit -p 16384
ulimit -n 16384
else
ulimit -u 16384 -n 16384
fi
fi
PS1="`hostname`> "
set -o emacs
set filec
Installation
Log into the oracle user. If you are using X emulation then set the DISPLAY environmental variable:
DISPLAY=<machine-name>:0.0; export DISPLAY
Start the Oracle Universal Installer (OUI) by issuing the following command in the Disk1 directory:
./runInstaller
During the installation enter the appropriate ORACLE_HOME and name then continue with the installation.
Post Installation
With the installation complete you can perform any administration tasks using Enterprise Manager:
- Connect to the Enterprise Manager Website (http://<fully-qualified-machine-name>:1810) using the username “ias_admin” and the password you assigned during the installation. If EM is not available start it with the “emctl start iasconsole” command.
- Stop enterprise manager by issuing the “emctl stop iasconsole” command.
Oracle AS10g Automatic Startup/Shutdown
This article describes a method to make Oracle AS10g start and stop automatically during server startup and shutdown on Linux.
As the oracle user create a “dba” directory:
su - oracle mkdir $ORACLE_BASE/dba
Next create a file called “startup” in the dba directory with the following contents:
#!/bin/ksh dcmctl shutdown opmnctl stopall opmnctl start # ----------------------------------- # Adjust to start desired components. dcmctl start -ct ohs dcmctl start -co container1 dcmctl start -co container2 # ----------------------------------- emctl stop iasconsole emctl start iasconsole
The list of components to start should be altered to match your requirements.
Then create a file called “shutdown” in the dba directory with the following contents:
#!/bin/ksh dcmctl shutdown opmnctl stopall emctl stop iasconsole
Make sure both files are executable:
chmod u+x startup chmod u+x shutdown
As the root user create a file called “/etc/init.d/oracle” with the following contents:
#!/bin/sh
# chkconfig: 345 99 10
# description: Oracle auto start-stop script.
#
ORA_BASE=/u01/app/oracle
ORA_HOME=/u01/app/oracle/product/904_j2ee
ORA_OWNER=oracle
case "$1" in
'start')
# Start the Oracle databases:
su - $ORA_OWNER -c $ORA_BASE/dba/startup &
;;
'stop')
# Stop the Oracle databases:
# The following command assumes that the oracle login
# will not prompt the user for any values
su - $ORA_OWNER -c $ORA_BASE/dba/shutdown &
;;
esac
Use chmod to set the privileges to 750:
chmod 750 /etc/init.d/oracle
Associate the oracle service with the appropriate run levels and set it to auto-start:
chkconfig --level 345 dbora on
The relevant instances should now startup/shutdown automatically at system startup/shutdown.
December 21, 2011 Articles 0 0
Recompiling Invalid Schema Objects
Operations such as upgrades, patches and DDL changes can invalidate schema objects. Provided these changes don’t cause compilation failures the objects will be revalidated by on-demand automatic recompilation, but this can take an unacceptable time to complete, especially where complex dependencies are present. For this reason it makes sense to recompile invalid objects in advance of user calls. It also allows you to identify if any changes have broken your code base. This article presents several methods for recompiling invalid schema objects.
- Identifying Invalid Objects
- The Manual Approach
- Custom Script
- DBMS_UTILITY.compile_schema
- UTL_RECOMP
- utlrp.sql and utlprp.sql
Identifying Invalid Objects
The DBA_OBJECTS view can be used to identify invalid objects using the following query.
COLUMN object_name FORMAT A30
SELECT owner,
object_type,
object_name,
status
FROM dba_objects
WHERE status = 'INVALID'
ORDER BY owner, object_type, object_name;
With this information you can decide which of the following recompilation methods is suitable for you.
The Manual Approach
For small numbers of objects you may decide that a manual recompilation is sufficient. The following example shows the compile syntax for several object types.
ALTER PACKAGE my_package COMPILE; ALTER PACKAGE my_package COMPILE BODY; ALTER PROCEDURE my_procedure COMPILE; ALTER FUNCTION my_function COMPILE; ALTER TRIGGER my_trigger COMPILE; ALTER VIEW my_view COMPILE;
Notice that the package body is compiled in the same way as the package specification, with the addition of the word “BODY” at the end of the command.
An alternative approach is to use the DBMS_DDL package to perform the recompilations.
EXEC DBMS_DDL.alter_compile('PACKAGE', 'MY_SCHEMA', 'MY_PACKAGE');
EXEC DBMS_DDL.alter_compile('PACKAGE BODY', 'MY_SCHEMA', 'MY_PACKAGE');
EXEC DBMS_DDL.alter_compile('PROCEDURE', 'MY_SCHEMA', 'MY_PROCEDURE');
EXEC DBMS_DDL.alter_compile('FUNCTION', 'MY_SCHEMA', 'MY_FUNCTION');
EXEC DBMS_DDL.alter_compile('TRIGGER', 'MY_SCHEMA', 'MY_TRIGGER');
This method is limited to PL/SQL objects, so it is not applicable for views.
Custom Script
In some situations you may have to compile many invalid objects in one go. One approach is to write a custom script to identify and compile the invalid objects. The following example identifies and recompiles invalid packages and package bodies.
SET SERVEROUTPUT ON SIZE 1000000
BEGIN
FOR cur_rec IN (SELECT owner,
object_name,
object_type,
DECODE(object_type, 'PACKAGE', 1,
'PACKAGE BODY', 2, 2) AS recompile_order
FROM dba_objects
WHERE object_type IN ('PACKAGE', 'PACKAGE BODY')
AND status != 'VALID'
ORDER BY 4)
LOOP
BEGIN
IF cur_rec.object_type = 'PACKAGE' THEN
EXECUTE IMMEDIATE 'ALTER ' || cur_rec.object_type ||
' "' || cur_rec.owner || '"."' || cur_rec.object_name || '" COMPILE';
ElSE
EXECUTE IMMEDIATE 'ALTER PACKAGE "' || cur_rec.owner ||
'"."' || cur_rec.object_name || '" COMPILE BODY';
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line(cur_rec.object_type || ' : ' || cur_rec.owner ||
' : ' || cur_rec.object_name);
END;
END LOOP;
END;
/
This approach is fine if you have a specific task in mind, but be aware that you may end up compiling some objects multiple times depending on the order they are compiled in. It is probably a better idea to use one of the methods provided by Oracle since they take the code dependencies into account.
DBMS_UTILITY.compile_schema
The COMPILE_SCHEMA procedure in the DBMS_UTILITY package compiles all procedures, functions, packages, and triggers in the specified schema. The example below shows how it is called from SQL*Plus.
EXEC DBMS_UTILITY.compile_schema(schema => 'SCOTT');
UTL_RECOMP
The UTL_RECOMP package contains two procedures used to recompile invalid objects. As the names suggest, the RECOMP_SERIAL procedure recompiles all the invalid objects one at a time, while the RECOMP_PARALLEL procedure performs the same task in parallel using the specified number of threads. Their definitions are listed below.
PROCEDURE RECOMP_SERIAL( schema IN VARCHAR2 DEFAULT NULL, flags IN PLS_INTEGER DEFAULT 0); PROCEDURE RECOMP_PARALLEL( threads IN PLS_INTEGER DEFAULT NULL, schema IN VARCHAR2 DEFAULT NULL, flags IN PLS_INTEGER DEFAULT 0);
The usage notes for the parameters are listed below.
- schema – The schema whose invalid objects are to be recompiled. If NULL all invalid objects in the database are recompiled.
- threads – The number of threads used in a parallel operation. If NULL the value of the “job_queue_processes” parameter is used. Matching the number of available CPUs is generally a good starting point for this value.
- flags – Used for internal diagnostics and testing only.
The following examples show how these procedures are used.
-- Schema level.
EXEC UTL_RECOMP.recomp_serial('SCOTT');
EXEC UTL_RECOMP.recomp_parallel(4, 'SCOTT');
-- Database level.
EXEC UTL_RECOMP.recomp_serial();
EXEC UTL_RECOMP.recomp_parallel(4);
-- Using job_queue_processes value.
EXEC UTL_RECOMP.recomp_parallel();
EXEC UTL_RECOMP.recomp_parallel(NULL, 'SCOTT');
There are a number of restrictions associated with the use of this package including:
- Parallel execution is perfomed using the job queue. All existing jobs are marked as disabled until the operation is complete.
- The package must be run from SQL*Plus as the SYS user, or another user with SYSDBA.
- The package expects the
STANDARD,DBMS_STANDARD,DBMS_JOBandDBMS_RANDOMto be present and valid. - Runnig DDL operations at the same time as this package may result in deadlocks.
utlrp.sql and utlprp.sql
The utlrp.sql and utlprp.sql scripts are provided by Oracle to recompile all invalid objects in the database. They are typically run after major database changes such as upgrades or patches. They are located in the $ORACLE_HOME/rdbms/admin directory and provide a wrapper on the UTL_RECOMP package. The utlrp.sql script simply calls the utlprp.sql script with a command line parameter of “0″. The utlprp.sql accepts a single integer parameter that indicates the level of parallelism as follows.
- 0 – The level of parallelism is derived based on the CPU_COUNT parameter.
- 1 – The recompilation is run serially, one object at a time.
- N – The recompilation is run in parallel with “N” number of threads.
Both scripts must be run as the SYS user, or another user with SYSDBA, to work correctly.
Introduction
In this paper we’ll discuss an overview of the EXPLAIN PLAN and TKPROF functions built into the Oracle 8i server and learn how developers and DBAs use these tools to get the best performance out of their applications. We’ll look at how to invoke these tools both from the command line and from graphical development tools. In the remainder of the paper we’ll discuss how to read and interpret Oracle 8i execution plans and TKPROF reports. We’ll look at lots of examples so that you’ll come away with as much practical knowledge as possible.
An Overview of EXPLAIN PLAN and TKPROF
In this section we’ll take a high-level look at the EXPLAIN PLAN and TKPROF facilities: what they are, prerequisites for using them, and how to invoke them. We will also look at how these facilities help you tune your applications.
Execution Plans and the EXPLAIN PLAN Statement
Before the database server can execute a SQL statement, Oracle must first parse the statement and develop an execution plan. The execution plan is a task list of sorts that decomposes a potentially complex SQL operation into a series of basic data access operations. For example, a query against the dept table might have an execution plan that consists of an index lookup on the deptno index, followed by a table access by ROWID.
The EXPLAIN PLAN statement allows you to submit a SQL statement to Oracle and have the database prepare the execution plan for the statement without actually executing it. The execution plan is made available to you in the form of rows inserted into a special table called a plan table. You may query the rows in the plan table using ordinary SELECT statements in order to see the steps of the execution plan for the statement you explained. You may keep multiple execution plans in the plan table by assigning each a unique statement_id. Or you may choose to delete the rows from the plan table after you are finished looking at the execution plan. You can also roll back an EXPLAIN PLAN statement in order to remove the execution plan from the plan table.
The EXPLAIN PLAN statement runs very quickly, even if the statement being explained is a query that might run for hours. This is because the statement is simply parsed and its execution plan saved into the plan table. The actual statement is never executed by EXPLAIN PLAN. Along these same lines, if the statement being explained includes bind variables, the variables never need to actually be bound. The values that would be bound are not relevant since the statement is not actually executed.
You don’t need any special system privileges in order to use the EXPLAIN PLAN statement. However, you do need to have INSERT privileges on the plan table, and you must have sufficient privileges to execute the statement you are trying to explain. The one difference is that in order to explain a statement that involves views, you must have privileges on all of the tables that make up the view. If you don’t, you’ll get an “ORA-01039: insufficient privileges on underlying objects of the view” error.
The columns that make up the plan table are as follows:
Name Null? Type -------------------- -------- ------------- STATEMENT_ID VARCHAR2(30) TIMESTAMP DATE REMARKS VARCHAR2(80) OPERATION VARCHAR2(30) OPTIONS VARCHAR2(30) OBJECT_NODE VARCHAR2(128) OBJECT_OWNER VARCHAR2(30) OBJECT_NAME VARCHAR2(30) OBJECT_INSTANCE NUMBER(38) OBJECT_TYPE VARCHAR2(30) OPTIMIZER VARCHAR2(255) SEARCH_COLUMNS NUMBER ID NUMBER(38) PARENT_ID NUMBER(38) POSITION NUMBER(38) COST NUMBER(38) CARDINALITY NUMBER(38) BYTES NUMBER(38) OTHER_TAG VARCHAR2(255) PARTITION_START VARCHAR2(255) PARTITION_STOP VARCHAR2(255) PARTITION_ID NUMBER(38) OTHER LONG DISTRIBUTION VARCHAR2(30)
There are other ways to view execution plans besides issuing the EXPLAIN PLAN statement and querying the plan table. SQL*Plus can automatically display an execution plan after each statement is executed. Also, there are many GUI tools available that allow you to click on a SQL statement in the shared pool and view its execution plan. In addition, TKPROF can optionally include execution plans in its reports as well.
Trace Files and the TKPROF Utility
TKPROF is a utility that you invoke at the operating system level in order to analyze SQL trace files and generate reports that present the trace information in a readable form. Although the details of how you invoke TKPROF vary from one platform to the next, Oracle Corporation provides TKPROF with all releases of the database and the basic functionality is the same on all platforms.
The term trace file may be a bit confusing. More recent releases of the database offer a product called Oracle Trace Collection Services. Also, Net8 is capable of generating trace files. SQL trace files are entirely different. SQL trace is a facility that you enable or disable for individual database sessions or for the entire instance as a whole. When SQL trace is enabled for a database session, the Oracle server process handling that session writes detailed information about all database calls and operations to a trace file. Special database events may be set in order to cause Oracle to write even more specific information—such as the values of bind variables—into the trace file.
SQL trace files are text files that, strictly speaking, are human readable. However, they are extremely verbose, repetitive, and cryptic. For example, if an application opens a cursor and fetches 1000 rows from the cursor one row at a time, there will be over 1000 separate entries in the trace file.
TKPROF is a program that you invoke at the operating system command prompt in order to reformat the trace file into a format that is much easier to comprehend. Each SQL statement is displayed in the report, along with counts of how many times it was parsed, executed, and fetched. CPU time, elapsed time, logical reads, physical reads, and rows processed are also reported, along with information about recursion level and misses in the library cache. TKPROF can also optionally include the execution plan for each SQL statement in the report, along with counts of how many rows were processed at each step of the execution plan.
The SQL statements can be listed in a TKPROF report in the order of how much resource they used, if desired. Also, recursive SQL statements issued by the SYS user to manage the data dictionary can be included or excluded, and TKPROF can write SQL statements from the traced session into a spool file.
How EXPLAIN PLAN and TKPROF Aid in the Application Tuning Process
EXPLAIN PLAN and TKPROF are valuable tools in the tuning process. Tuning at the application level typically yields the most dramatic results, and these two tools can help with the tuning in many different ways.
EXPLAIN PLAN and TKPROF allow you to proactively tune an application while it is in development. It is relatively easy to enable SQL trace, run an application in a test environment, run TKPROF on the trace file, and review the output to determine if application or schema changes are called for. EXPLAIN PLAN is handy for evaluating individual SQL statements.
By reviewing execution plans, you can also validate the scalability of an application. If the database operations are dependent upon full table scans of tables that could grow quite large, then there may be scalability problems ahead. On the other hand, if large tables are accessed via selective indexes, then scalability may not be a problem.
EXPLAIN PLAN and TKPROF may also be used in an existing production environment in order to zero in on resource intensive operations and get insights into how the code may be optimized. TKPROF can further be used to quantify the resources required by specific database operations or application functions.
EXPLAIN PLAN is also handy for estimating resource requirements in advance. Suppose you have an ad hoc reporting request against a very large database. Running queries through EXPLAIN PLAN will let you determine in advance if the queries are feasible or if they will be resource intensive and will take unacceptably long to run.
Generating Execution Plans and TKPROF Reports
In this section we will discuss the details of how to generate execution plans (both with the EXPLAIN PLAN statement and other methods) and how to generate SQL trace files and create TKPROF reports.
Using the EXPLAIN PLAN Statement
Before you can use the EXPLAIN PLAN statement, you must have INSERT privileges on a plan table. The plan table can have any name you like, but the names and data types of the columns are not flexible. You will find a script called utlxplan.sql in $ORACLE_HOME/rdbms/admin that creates a plan table with the name plan_table in the local schema. If you use this script to create your plan table, you can be assured that the table will have the right definition for use with EXPLAIN PLAN.
Once you have access to a plan table, you are ready to run the EXPLAIN PLAN statement. The syntax is as follows:
EXPLAIN PLAN [SET STATEMENT_ID = <string in single quotes>] [INTO <plan table name>] FOR <SQL statement>;
If you do not specify the INTO clause, then Oracle assumes the name of the plan table is plan_table. You can use the SET clause to assign a name to the execution plan. This is useful if you want to be able to have multiple execution plans stored in the plan table at once—giving each execution plan a distinct name enables you to determine which rows in the plan table belong to which execution plan.
The EXPLAIN PLAN statement runs quickly because all Oracle has to do is parse the SQL statement being explained and store the execution plan in the plan table. The SQL statement can include bind variables, although the variables will not get bound and the values of the bind variables will be irrelevant.
If you issue the EXPLAIN PLAN statement from SQL*Plus, you will get back the feedback message “Explained.” At this point the execution plan for the explained SQL statement has been inserted into the plan table, and you can now query the plan table to examine the execution plan.
Execution plans are a hierarchical arrangement of simple data access operations. Because of the hierarchy, you need to use a CONNECT BY clause in your query from the plan table. Using the LPAD function, you can cause the output to be formatted in such a way that the indenting helps you traverse the hierarchy. There are many different ways to format the data retrieved from the plan table. No one query is the best, because the plan table holds a lot of detailed information. Different DBAs will find different aspects more useful in different situations.
A simple SQL*Plus script to retrieve an execution plan from the plan table is as follows:
REM
REM explain.sql
REM
SET VERIFY OFF
SET PAGESIZE 100
ACCEPT stmt_id CHAR PROMPT "Enter statement_id: "
COL id FORMAT 999
COL parent_id FORMAT 999 HEADING "PARENT"
COL operation FORMAT a35 TRUNCATE
COL object_name FORMAT a30
SELECT id, parent_id, LPAD (' ', LEVEL - 1) || operation || ' ' ||
options operation, object_name
FROM plan_table
WHERE statement_id = '&stmt_id'
START WITH id = 0
AND statement_id = '&stmt_id'
CONNECT BY PRIOR
id = parent_id
AND statement_id = '&stmt_id';
I have a simple query that we will use in a few examples. We’ll call this “the invoice item query.” The query is as follows:
SELECT a.customer_name, a.customer_number, b.invoice_number,
b.invoice_type, b.invoice_date, b.total_amount, c.line_number,
c.part_number, c.quantity, c.unit_cost
FROM customers a, invoices b, invoice_items c
WHERE c.invoice_id = :b1
AND c.line_number = :b2
AND b.invoice_id = c.invoice_id
AND a.customer_id = b.customer_id;
The explain.sql SQL*Plus script above displays the execution plan for the invoice item query as follows:
ID PARENT OPERATION OBJECT_NAME ---- ------ ----------------------------------- ------------------------------ 0 SELECT STATEMENT 1 0 NESTED LOOPS 2 1 NESTED LOOPS 3 2 TABLE ACCESS BY INDEX ROWID INVOICE_ITEMS 4 3 INDEX UNIQUE SCAN INVOICE_ITEMS_PK 5 2 TABLE ACCESS BY INDEX ROWID INVOICES 6 5 INDEX UNIQUE SCAN INVOICES_PK 7 1 TABLE ACCESS BY INDEX ROWID CUSTOMERS 8 7 INDEX UNIQUE SCAN CUSTOMERS_PK
The execution plan shows that Oracle is using nested loops joins to join three tables, and that accesses from all three tables are by unique index lookup. This is probably a very efficient query. We will look at how to read execution plans in greater detail in a later section.
The explain.sql script for displaying an execution plan is very basic in that it does not display a lot of the information contained in the plan table. Things left off of the display include optimizer estimated cost, cardinality, partition information (only relevant when accessing partitioned tables), and parallelism information (only relevant when executing parallel queries or parallel DML).
If you are using Oracle 8.1.5 or later, you can find two plan query scripts in $ORACLE_HOME/rdbms/admin. utlxpls.sql is intended for displaying execution plans of statements that do not involve parallel processing, while utlxplp.sql shows additional information pertaining to parallel processing. The output of the latter script is more confusing, so only use it when parallel query or DML come into play. The output from utlxpls.sql for the invoice item query is as follows:
Plan Table -------------------------------------------------------------------------------- | Operation | Name | Rows | Bytes| Cost | Pstart| Pstop | -------------------------------------------------------------------------------- | SELECT STATEMENT | | 1 | 39 | 4 | | | | NESTED LOOPS | | 1 | 39 | 4 | | | | NESTED LOOPS | | 1 | 27 | 3 | | | | TABLE ACCESS BY INDEX R|INVOICE_I | 1 | 15 | 2 | | | | INDEX UNIQUE SCAN |INVOICE_I | 2 | | 1 | | | | TABLE ACCESS BY INDEX R|INVOICES | 2 | 24 | 1 | | | | INDEX UNIQUE SCAN |INVOICES_ | 2 | | | | | | TABLE ACCESS BY INDEX RO|CUSTOMERS | 100 | 1K| 1 | | | | INDEX UNIQUE SCAN |CUSTOMERS | 100 | | | | | --------------------------------------------------------------------------------
When you no longer need an execution plan, you should delete it from the plan table. You can do this by rolling back the EXPLAIN PLAN statement (if you have not committed yet) or by deleting rows from the plan table. If you have multiple execution plans in the plan table, then you should delete selectively by statement_id. Note that if you explain two SQL statements and assign both the same statement_id, you will get an ugly cartesian product when you query the plan table!
The Autotrace Feature of SQL*Plus
SQL*Plus has an autotrace feature which allows you to automatically display execution plans and helpful statistics for each statement executed in a SQL*Plus session without having to use the EXPLAIN PLAN statement or query the plan table. You turn this feature on and off with the following SQL*Plus command:
SET AUTOTRACE OFF|ON|TRACEONLY [EXPLAIN] [STATISTICS]
When you turn on autotrace in SQL*Plus, the default behavior is for SQL*Plus to execute each statement and display the results in the normal fashion, followed by an execution plan listing and a listing of various server-side resources used to execute the statement. By using the TRACEONLY keyword, you can have SQL*Plus suppress the query results. By using the EXPLAIN or STATISTICS keywords, you can have SQL*Plus display just the execution plan without the resource statistics or just the statistics without the execution plan.
In order to have SQL*Plus display execution plans, you must have privileges on a plan table by the name of plan_table. In order to have SQL*Plus display the resource statistics, you must have SELECT privileges on v$sesstat, v$statname, and v$session. There is a script in $ORACLE_HOME/sqlplus/admin called plustrce.sql which creates a role with these three privileges in it, but this script is not run automatically by the Oracle installer.
The autotrace feature of SQL*Plus makes it extremely easy to generate and view execution plans, with resource statistics as an added bonus. One key drawback, however, is that the statement being explained must actually be executed by the database server before SQL*Plus will display the execution plan. This makes the tool unusable in the situation where you would like to predict how long an operation might take to complete.
A sample output from SQL*Plus for the invoice item query is as follows:
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=39)
1 0 NESTED LOOPS (Cost=4 Card=1 Bytes=39)
2 1 NESTED LOOPS (Cost=3 Card=1 Bytes=27)
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'INVOICE_ITEMS' (Cost
=2 Card=1 Bytes=15)
4 3 INDEX (UNIQUE SCAN) OF 'INVOICE_ITEMS_PK' (UNIQUE) (
Cost=1 Card=2)
5 2 TABLE ACCESS (BY INDEX ROWID) OF 'INVOICES' (Cost=1 Ca
rd=2 Bytes=24)
6 5 INDEX (UNIQUE SCAN) OF 'INVOICES_PK' (UNIQUE)
7 1 TABLE ACCESS (BY INDEX ROWID) OF 'CUSTOMERS' (Cost=1 Car
d=100 Bytes=1200)
8 7 INDEX (UNIQUE SCAN) OF 'CUSTOMERS_PK' (UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
517 bytes sent via SQL*Net to client
424 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
Although we haven’t discussed how to read an execution plan yet, you can see that the output from SQL*Plus provides the same basic information, with several additional details in the form of estimates from the query optimizer.
Using GUI Tools to View Execution Plans
There are many GUI tools available that allow you to view execution plans for SQL statements you specify or for statements already sitting in the shared pool of the database instance. Any comprehensive database management tool will offer this capability, but there are several free tools available for download on the internet that have this feature as well.
One tool in particular that I really like is TOAD (the Tool for Oracle Application Developers). Although TOAD was originally developed as a free tool, Quest Software now owns TOAD and it is available in both a free version (limited functionality) and an enhanced version that may be purchased (full feature set). You may download TOAD from Quest Software at http://www.toadsoft.com/downld.html. TOAD has lots of handy features. The one relevant to us here is the ability to click on any SQL statement in the shared pool and instantly view its execution plan.
As with the EXPLAIN PLAN statement and the autotrace facility in SQL*Plus, you will need to have access to a plan table. Here is TOAD’s rendition of the execution plan for the invoice item query we’ve been using:
You can see that the information displayed is almost identical to that from the autotrace facility in SQL*Plus. One nice feature of TOAD’s execution plan viewer is that you can collapse and expand the individual operations that make up the execution plan. Also, the vertical and horizontal lines connecting different steps help you keep track of the nesting and which child operations go with which parent operations in the hierarchy. The benefits of these features become more apparent when working with extremely complicated execution plans.
Unfortunately, when looking at execution plans for SQL statements that involve database links or parallelism, TOAD leaves out critical information that is present in the plan table and is reported by the autotrace feature of SQL*Plus. Perhaps this deficiency only exists in the free version of TOAD; I would like to think that if you pay for the full version of TOAD, you’ll get complete execution plans.
Generating a SQL Trace File
SQL trace may be enabled at the instance or session level. To enable SQL trace at the instance level, add the following parameter setting to the instance parameter file and restart the database instance:
sql_trace = true
When an Oracle instance starts up with the above parameter setting, every database session will run in SQL trace mode, meaning that all SQL operations for every database session will be written to trace files. Even the daemon processes like PMON and SMON will be traced! In practice, enabling SQL trace at the instance level is usually not very useful. It can be overpowering, sort of like using a fire hose to pour yourself a glass of water.
It is more typical to enable SQL trace in a specific session. You can turn SQL trace on and off as desired in order to trace just the operations that you wish to trace. If you have access to the database session you wish to trace, then use the ALTER SESSION statement as follows to enable and disable SQL trace:
ALTER SESSION SET sql_trace = TRUE|FALSE;
This technique works well if you have access to the application source code and can add in ALTER SESSION statements at will. It also works well when the application runs from SQL*Plus and you can execute ALTER SESSION statements at the SQL*Plus prompt before invoking the application.
In situations where you cannot invoke an ALTER SESSION command from the session you wish to trace—as with prepackaged applications, for example—you can connect to the database as a DBA user and invoke the dbms_system built-in package in order to turn on or off SQL trace in another session. You do this by querying v$session to find the SID and serial number of the session you wish to trace and then invoking the dbms_system package with a command of the form:
EXECUTE SYS.dbms_system.set_sql_trace_in_session (<SID>, <serial#>, TRUE|FALSE);
When you enable SQL trace in a session for the first time, the Oracle server process handling that session will create a trace file in the directory on the database server designated by the user_dump_dest initialization parameter. As the server is called by the application to perform database operations, the server process will append to the trace file.
Note that tracing a database session that is using multi-threaded server (MTS) is a bit complicated because each database request from the application could get picked up by a different server process. In this situation, each server process will create a trace file containing trace information about the operations performed by that process only. This means that you will potentially have to combine multiple trace files together to get the full picture of how the application interacted with the database. Furthermore, if multiple sessions are being traced at once, it will be hard to tell which operations in the trace file belong to which session. For these reasons, you should use dedicated server mode when tracing a database session with SQL trace.
SQL trace files contain detailed timing information. By default, Oracle does not track timing, so all timing figures in trace files will show as zero. If you would like to see legitimate timing information, then you need to enable timed statistics. You can do this at the instance level by setting the following parameter in the instance parameter file and restarting the instance:
timed_statistics = true
You can also dynamically enable or disable timed statistics collection at either the instance or the session level with the following commands:
ALTER SYSTEM SET timed_statistics = TRUE|FALSE; ALTER SESSION SET timed_statistics = TRUE|FALSE;
There is no known way to enable timed statistics collection for an individual session from another session (akin to the SYS.dbms_system.set_sql_trace_in_session built-in).
There is very high overhead associated with enabling SQL trace. Some DBAs believe the performance penalty could be over 25%. Another concern is that enabling SQL trace causes the generation of potentially large trace files. For these reasons, you should use SQL trace sparingly. Only trace what you need to trace and think very carefully before enabling SQL trace at the instance level.
On the other hand, there is little, if any, measurable performance penalty in enabling timed statistics collection. Many DBAs run production databases with timed statistics collection enabled at the system level so that various system statistics (more than just SQL trace files) will include detailed timing information. Note that Oracle 8.1.5 had some serious memory corruption bugs associated with enabling timed statistics collection at the instance level, but these seem to have been fixed in Oracle 8.1.6.
On Unix platforms, Oracle will typically set permissions so that only the oracle user and members of the dba Unix group can read the trace files. If you want anybody with a Unix login to be able to read the trace files, then you should set the following undocumented (but supported) initialization parameter in the parameter file:
_trace_files_public = true
If you trace a database session that makes a large number of calls to the database server, the trace file can get quite large. The initialization parameter max_dump_file_size allows you to set a maximum trace file size. On Unix platforms, this parameter is specified in units of 512 byte blocks. Thus a setting of 10240 will limit trace files to 5 Mb apiece. When a SQL trace file reaches the maximum size, the database server process stops writing trace information to the trace file. On Unix platforms there will be no limit on trace file size if you do not explicitly set the max_dump_file_size parameter.
If you are tracing a session and realize that the trace file is about to reach the limit set by max_dump_file_size, you can eliminate the limit dynamically so that you don’t lose trace information. To do this, query the PID column in v$process to find the Oracle PID of the process writing the trace file. Then execute the following statements in SQL*Plus:
CONNECT / AS SYSDBA ORADEBUG SETORAPID <pid> ORADEBUG UNLIMIT
Running TKPROF on a SQL Trace File
Before you can use TKPROF, you need to generate a trace file and locate it. Oracle writes trace files on the database server to the directory specified by the user_dump_dest initialization parameter. (Daemon processes such as PMON write their trace files to the directory specified by background_dump_dest.) On Unix platforms, the trace file will have a name that incorporates the operating system PID of the server process writing the trace file.
If there are a lot of trace files in the user_dump_dest directory, it could be tricky to find the one you want. One tactic is to examine the timestamps on the files. Another technique is to embed a comment in a SQL statement in the application that will make its way into the trace file. An example of this is as follows:
ALTER SESSION /* Module glpost.c */ SET sql_trace = TRUE;
Because TKPROF is a utility you invoke from the operating system and not from within a database session, there will naturally be some variation in the user interface from one operating system platform to another. On Unix platforms, you run TKPROF from the operating system prompt with a syntax as follows:
tkprof <trace file> <output file> [explain=<username/password>] [sys=n] \
[insert=<filename>] [record=<filename>] [sort=<keyword>]
If you invoke TKPROF with no arguments at all, you will get a help screen listing all of the options. This is especially helpful because TKPROF offers many sort capabilities, but you select the desired sort by specifying a cryptic keyword. The help screen identifies all of the sort keywords.
In its simplest form, you run TKPROF specifying the name of a SQL trace file and an output filename. TKPROF will read the trace file and generate a report file with the output filename you specified. TKPROF will not connect to the database, and the report will not include execution plans for the SQL statements. SQL statements that were executed by the SYS user recursively (to dynamically allocate an extent in a dictionary-managed tablespace, for example) will be included in the report, and the statements will appear in the report approximately in the order in which they were executed in the database session that was traced.
If you include the explain keyword, TKPROF will connect to the database and execute an EXPLAIN PLAN statement for each SQL statement found in the trace file. The execution plan results will be included in the report file. As we will see later, TKPROF merges valuable information from the trace file into the execution plan display, making this just about the most valuable way to display an execution plan. Note that the username you specify when running TKPROF should be the same as the username connected in the database session that was traced. You do not need to have a plan table in order to use the explain keyword—TKPROF will create and drop its own plan table if needed.
If you specify sys=n, TKPROF will exclude from the report SQL statements initiated by Oracle as the SYS user. This will make your report look tidier because it will only contain statements actually issued by your application. The theory is that Oracle internal SQL has already been fully optimized by the kernel developers at Oracle Corporation, so you should not have to deal with it. However, using sys=n will exclude potentially valuable information from the TKPROF report. Suppose the SGA is not properly sized on the instance and Oracle is spending a lot of time resolving dictionary cache misses. This would manifest itself in lots of time spent on recursive SQL statements initiated by the SYS user. Using sys=n would exclude this information from the report.
If you specify the insert keyword, TKPROF will generate a SQL script in addition to the regular report. This SQL script creates a table called tkprof_table and inserts one row for each SQL statement displayed on the report. The row will contain the text of the SQL statement traced and all of the statistics displayed in the report. You could use this feature to effectively load the TKPROF report into the database and use SQL to analyze and manipulate the statistics. I’ve never needed to use this feature, but I suppose it could be helpful in some situations.
If you specify the record keyword, TKPROF will generate another type of SQL script in addition to the regular report. This SQL script will contain a copy of each SQL statement issued by the application while tracing was enabled. You could get this same information from the TKPROF report itself, but this way could save some cutting and pasting.
The sort keyword is extremely useful. Typically, a TKPROF report may include hundreds of SQL statements, but you may only be interested in a few resource intensive queries. The sort keyword allows you to order the listing of the SQL statements so that you don’t have to scan the entire file looking for resource hogs. In some ways, the sort feature is too powerful for its own good. For example, you cannot sort statements by CPU time consumed—instead you sort by CPU time spent parsing, CPU time spent executing, or CPU time spent fetching.
A sample TKPROF report for the invoice item query we’ve been using so far is as follows:
TKPROF: Release 8.1.6.1.0 - Production on Wed Aug 9 19:06:36 2000
(c) Copyright 1999 Oracle Corporation. All rights reserved.
Trace file: example.trc
Sort options: default
********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************
ALTER SESSION /* TKPROF example */ SET sql_trace = TRUE
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer goal: CHOOSE
Parsing user id: 34 (RSCHRAG)
********************************************************************************
ALTER SESSION SET timed_statistics = TRUE
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 0 0 0
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 34 (RSCHRAG)
********************************************************************************
SELECT a.customer_name, a.customer_number, b.invoice_number,
b.invoice_type, b.invoice_date, b.total_amount, c.line_number,
c.part_number, c.quantity, c.unit_cost
FROM customers a, invoices b, invoice_items c
WHERE c.invoice_id = :b1
AND c.line_number = :b2
AND b.invoice_id = c.invoice_id
AND a.customer_id = b.customer_id
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.05 0.02 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 8 8 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.05 0.02 8 8 0 1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 34 (RSCHRAG)
Rows Row Source Operation
------- ---------------------------------------------------
1 NESTED LOOPS
1 NESTED LOOPS
1 TABLE ACCESS BY INDEX ROWID INVOICE_ITEMS
1 INDEX UNIQUE SCAN (object id 21892)
1 TABLE ACCESS BY INDEX ROWID INVOICES
1 INDEX UNIQUE SCAN (object id 21889)
1 TABLE ACCESS BY INDEX ROWID CUSTOMERS
1 INDEX UNIQUE SCAN (object id 21887)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
1 NESTED LOOPS
1 NESTED LOOPS
1 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'INVOICE_ITEMS'
1 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'INVOICE_ITEMS_PK'
(UNIQUE)
1 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'INVOICES'
1 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'INVOICES_PK'
(UNIQUE)
1 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'CUSTOMERS'
1 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'CUSTOMERS_PK'
(UNIQUE)
********************************************************************************
ALTER SESSION SET sql_trace = FALSE
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 0 0 0
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 34 (RSCHRAG)
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 3 0.05 0.02 0 0 0 0
Execute 4 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 8 8 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 9 0.05 0.02 8 8 0 1
Misses in library cache during parse: 3
Misses in library cache during execute: 1
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 24 0.02 0.04 1 0 1 0
Execute 62 0.01 0.05 0 0 0 0
Fetch 126 0.02 0.02 6 198 0 100
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 212 0.05 0.11 7 198 1 100
Misses in library cache during parse: 11
4 user SQL statements in session.
24 internal SQL statements in session.
28 SQL statements in session.
1 statement EXPLAINed in this session.
********************************************************************************
Trace file: example.trc
Trace file compatibility: 8.00.04
Sort options: default
1 session in tracefile.
4 user SQL statements in trace file.
24 internal SQL statements in trace file.
28 SQL statements in trace file.
15 unique SQL statements in trace file.
1 SQL statements EXPLAINed using schema:
RSCHRAG.prof$plan_table
Default table was used.
Table was created.
Table was dropped.
381 lines in trace file.
You can see that there is a lot going on in a TKPROF report. We will talk about how to read the report and interpret the different statistics in the next section.
Interpreting Execution Plans and TKPROF Reports
In this section we will discuss how to read and interpret execution plans and TKPROF reports. While generating an execution plan listing or creating a TKPROF report file is usually a straightforward process, analyzing the data and reaching the correct conclusions can be more of an art. We’ll look at lots of examples along the way.
Understanding Execution Plans
An execution plan is a hierarchical structure somewhat like an inverted tree. The SQL statement being examined can be thought of as the root of the tree. This will be the first line on an execution plan listing, the line that is least indented. This statement can be thought of as the result of one or more subordinate operations. Each of these subordinate operations can possibly be decomposed further. This decomposition process continues repeatedly until eventually even the most complex SQL statement is broken down into a set of basic data access operations.
Consider the following simple query and execution plan:
SELECT customer_id, customer_number, customer_name FROM customers WHERE UPPER (customer_name) LIKE 'ACME%' ORDER BY customer_name; ID PARENT OPERATION OBJECT_NAME ---- ------ ----------------------------------- ------------------------------ 0 SELECT STATEMENT 1 0 SORT ORDER BY 2 1 TABLE ACCESS FULL CUSTOMERS
The root operation—that which we explained—is a SELECT statement. The output of the statement will be the results of a sort operation (for the purposes of satisfying the ORDER BY clause). The input to the sort will be the results of a full table scan of the customers table. Stated more clearly, the database server will execute this query by checking every row in the customers table for a criteria match and sorting the results. Perhaps the developer expected Oracle to use an index on the customer_name column to avoid a full table scan, but the use of the UPPER function defeated the index. (A function-based index could be deployed to make this query more efficient.)
Consider the following query and execution plan:
SELECT a.customer_name, b.invoice_number, b.invoice_date FROM customers a, invoices b WHERE b.invoice_date > TRUNC (SYSDATE - 1) AND a.customer_id = b.customer_id; ID PARENT OPERATION OBJECT_NAME ---- ------ ----------------------------------- ------------------------------ 0 SELECT STATEMENT 1 0 NESTED LOOPS 2 1 TABLE ACCESS BY INDEX ROWID INVOICES 3 2 INDEX RANGE SCAN INVOICES_DATE 4 1 TABLE ACCESS BY INDEX ROWID CUSTOMERS 5 4 INDEX UNIQUE SCAN CUSTOMERS_PK
Again, the root operation is a SELECT statement. This time, the SELECT statement gets its input from the results of a nested loops join operation. The nested loops operation takes as input the results of accesses to the invoices and customers tables. (You can tell from the indenting that accesses to both tables feed directly into the nested loops operation.) The invoices table is accessed by a range scan of the invoices_date index, while the customers table is accessed by a unique scan of the customers_pk index.
In plainer language, here is how Oracle will execute this query: Oracle will perform a range scan on the invoices_date index to find the ROWIDs of all rows in the invoices table that have an invoice date matching the query criteria. For each ROWID found, Oracle will fetch the corresponding row from the invoices table, look up the customer_id from the invoices record in the customers_pk index, and use the ROWID found in the customers_pk index entry to fetch the correct customer record. This, in effect, joins the rows fetched from the invoices table with their corresponding matches in the customers table. The results of the nested loops join operation are returned as the query results.
Consider the following query and execution plan:
SELECT a.customer_name, COUNT (DISTINCT b.invoice_id) "Open Invoices",
COUNT (c.invoice_id) "Open Invoice Items"
FROM customers a, invoices b, invoice_items c
WHERE b.invoice_status = 'OPEN'
AND a.customer_id = b.customer_id
AND c.invoice_id (+) = b.invoice_id
GROUP BY a.customer_name;
ID PARENT OPERATION OBJECT_NAME
---- ------ ----------------------------------- ------------------------------
0 SELECT STATEMENT
1 0 SORT GROUP BY
2 1 NESTED LOOPS OUTER
3 2 HASH JOIN
4 3 TABLE ACCESS BY INDEX ROWID INVOICES
5 4 INDEX RANGE SCAN INVOICES_STATUS
6 3 TABLE ACCESS FULL CUSTOMERS
7 2 INDEX RANGE SCAN INVOICE_ITEMS_PK
This execution plan is more complex than the previous two, and here you can start to get a feel for the way in which complex operations get broken down into simpler subordinate operations. To execute this query, the database server will do the following: First Oracle will perform a range scan on the invoices_status index to get the ROWIDs of all rows in the invoices table with the desired status. For each ROWID found, the record from the invoices table will be fetched.
This set of invoice records will be set aside for a moment while the focus turns to the customers table. Here, Oracle will fetch all customers records with a full table scan. To perform a hash join between the invoices and customers tables, Oracle will build a hash from the customer records and use the invoice records to probe the customer hash.
Next, a nested loops join will be performed between the results of the hash join and the invoice_items_pk index. For each row resulting from the hash join, Oracle will perform a unique scan of the invoice_items_pk index to find index entries for matching invoice items. Note that Oracle gets everything it needs from the index and doesn’t even need to access the invoice_items table at all. Also note that the nested loops operation is an outer join. A sort operation for the purposes of grouping is performed on the results of the nested loops operation in order to complete the SELECT statement.
It is interesting to note that Oracle chose to use a hash join and a full table scan on the customers table instead of the more traditional nested loops join. In this database there are many invoices and a relatively small number of customers, making a full table scan of the customers table less expensive than repeated index lookups on the customers_pk index. But suppose the customers table was enormous and the relative number of invoices was quite small. In that scenario a nested loops join might be better than a hash join. Examining the execution plan allows you to see which join method Oracle is using. You could then apply optimizer hints to coerce Oracle to use alternate methods and compare the performance.
You may wonder how I got that whole detailed explanation out of the eight line execution plan listing shown above. Did I read anything into the execution plan? No! It’s all there! Understanding the standard inputs and outputs of each type of operation and coupling this with the indenting is key to reading an execution plan.
A nested loops join operation always takes two inputs: For every row coming from the first input, the second input is executed once to find matching rows. A hash join operation also takes two inputs: The second input is read completely once and used to build a hash. For each row coming from the first input, one probe is performed against this hash. Sorting operations, meanwhile, take in one input. When the entire input has been read, the rows are sorted and output in the desired order.
Now let’s look at a query with a more complicated execution plan:
SELECT customer_name
FROM customers a
WHERE EXISTS
(
SELECT 1
FROM invoices_view b
WHERE b.customer_id = a.customer_id
AND number_of_lines > 100
)
ORDER BY customer_name;
ID PARENT OPERATION OBJECT_NAME
---- ------ ----------------------------------- ------------------------------
0 SELECT STATEMENT
1 0 SORT ORDER BY
2 1 FILTER
3 2 TABLE ACCESS FULL CUSTOMERS
4 2 VIEW INVOICES_VIEW
5 4 FILTER
6 5 SORT GROUP BY
7 6 NESTED LOOPS
8 7 TABLE ACCESS BY INDEX ROWID INVOICES
9 8 INDEX RANGE SCAN INVOICES_CUSTOMER_ID
10 7 INDEX RANGE SCAN INVOICE_ITEMS_PK
This execution plan is somewhat complex because the query includes a subquery that the optimizer could not rewrite as a simple join, and a view whose definition could not be merged into the query. The definition of the invoices_view view is as follows:
CREATE OR REPLACE VIEW invoices_view
AS
SELECT a.invoice_id, a.customer_id, a.invoice_date, a.invoice_status,
a.invoice_number, a.invoice_type, a.total_amount,
COUNT(*) number_of_lines
FROM invoices a, invoice_items b
WHERE b.invoice_id = a.invoice_id
GROUP BY a.invoice_id, a.customer_id, a.invoice_date, a.invoice_status,
a.invoice_number, a.invoice_type, a.total_amount;
Here is what this execution plan says: Oracle will execute this query by reading all rows from the customers table with a full table scan. For each customer record, the invoices_view view will be assembled as a filter and the relevant contents of the view will be examined to determine whether the customer should be part of the result set or not.
Oracle will assemble the view by performing an index range scan on the invoices_customer_id index and fetching the rows from the invoices table containing one specific customer_id. For each invoice record found, the invoice_items_pk index will be range scanned to get a nested loops join of invoices to their invoice_items records. The results of the join are sorted for grouping, and then groups with 100 or fewer invoice_items records are filtered out.
What is left at the step with ID 4 is a list of invoices for one specific customer that have more than 100 invoice_items records associated. If at least one such invoice exists, then the customer passes the filter at the step with ID 2. Finally, all customer records passing this filter are sorted for correct ordering and the results are complete.
Note that queries involving simple views will not result in a “view” operation in the execution plan. This is because Oracle can often merge a view definition into the query referencing the view so that the table accesses required to implement the view just become part of the regular execution plan. In this example, the GROUP BY clause embedded in the view foiled Oracle’s ability to merge the view into the query, making a separate “view” operation necessary in order to execute the query.
Also note that the filter operation can take on a few different forms. In general, a filter operation is where Oracle looks at a set of candidate rows and eliminates some based on certain criteria. This criteria could involve a simple test such as number_of_lines > 100 or it could be an elaborate subquery.
In this example, the filter at step ID 5 takes only one input. Here Oracle evaluates each row from the input one at a time and either adds the row to the output or discards it as appropriate. Meanwhile, the filter at step ID 2 takes two inputs. When a filter takes two inputs, Oracle reads the rows from the first input one at a time and executes the second input once for each row. Based on the results of the second input, the row from the first input is either added to the output or discarded.
Oracle is able to perform simple filtering operations while performing a full table scan. Therefore, a separate filter operation will not appear in the execution plan when Oracle performs a full table scan and throws out rows that don’t satisfy a WHERE clause. Filter operations with one input commonly appear in queries with view operations or HAVING clauses, while filter operations with multiple inputs will appear in queries with EXISTS clauses.
An important note about execution plans and subqueries: When a SQL statement involves subqueries, Oracle tries to merge the subquery into the main statement by using a join. If this is not feasible and the subquery does not have any dependencies or references to the main query, then Oracle will treat the subquery as a completely separate statement from the standpoint of developing an execution plan—almost as if two separate SQL statements were sent to the database server. When you generate an execution plan for a statement that includes a fully autonomous subquery, the execution plan may not include the operations for the subquery. In this situation, you need to generate an execution plan for the subquery separately.
Other Columns in the Plan Table
Although the plan table contains 24 columns, so far we have only been using six of them in our execution plan listings. These six will get you very far in the tuning process, but some of the other columns can be mildly interesting at times. Still other columns can be very relevant in specific situations.
The optimizer column in the plan table shows the mode (such as RULE or CHOOSE) used by the optimizer to generate the execution plan. The timestamp column shows the date and time that the execution plan was generated. The remarks column is an 80 byte field where you may put your own comments about each step of the execution plan. You can populate the remarks column by using an ordinary UPDATE statement against the plan table.
The object_owner, object_node, and object_instance columns can help you further distinguish the database object involved in the operation. You might look at the object_owner column, for example, if objects in multiple schemas have the same name and you are not sure which one is being referenced in the execution plan. The object_node is relevant in distributed queries or transactions. It indicates the database link name to the object if the object resides in a remote database. The object_instance column is helpful in situations such as a self-join where multiple instances of the same object are used in one SQL statement.
The partition_start, partition_stop, and partition_id columns offer additional information when a partitioned table is involved in the execution plan. The distribution column gives information about how the multiple Oracle processes involved in a parallel query or parallel DML operation interact with each other.
The cost, cardinality, and bytes columns show estimates made by the cost-based optimizer as to how expensive an operation will be. Remember that the execution plan is inserted into the plan table without actually executing the SQL statement. Therefore, these columns reflect Oracle’s estimates and not the actual resources used. While it can be amusing to look at the optimizer’s predictions, sometimes you need to take them with a grain of salt. Later we’ll see that TKPROF reports can include specific information about actual resources used at each step of the execution plan.
The “other” column in the plan table is a wild card where Oracle can store any sort of textual information about each step of an execution plan. The other_tag column gives an indication of what has been placed in the “other” column. This column will contain valuable information during parallel queries and distributed operations.
Consider the following distributed query and output from the SQL*Plus autotrace facility:
SELECT /*+ RULE */
a.customer_number, a.customer_name, b.contact_id, b.contact_name
FROM customers a, contacts@sales.acme.com b
WHERE UPPER (b.contact_name) = UPPER (a.customer_name)
ORDER BY a.customer_number, b.contact_id;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: RULE
1 0 SORT (ORDER BY)
2 1 MERGE JOIN
3 2 SORT (JOIN)
4 3 REMOTE* SALES.ACME.COM
5 2 SORT (JOIN)
6 5 TABLE ACCESS (FULL) OF 'CUSTOMERS'
4 SERIAL_FROM_REMOTE SELECT "CONTACT_ID","CONTACT_NAME" FROM "CONTACTS" "B”
In the execution plan hierarchy, the step with ID 4 is displayed as a remote operation through the sales.acme.com database link. At the bottom of the execution plan you can see the actual SQL statement that the local database server sends to sales.acme.com to perform the remote operation. This information came from the “other” and other_tag columns of the plan table.
Here is how to read this execution plan: Oracle observed a hint and used the RULE optimizer mode in order to develop the execution plan. First, a remote query will be sent to sales.acme.com to fetch the contact_ids and names from a remote table. These fetched rows will be sorted for joining purposes and temporarily set aside. Next, Oracle will fetch all records from the customers table with a full table scan and sort them for joining purposes. Next, the set of contacts and the set of customers will be joined using the merge join algorithm. Finally, the results of the merge join will be sorted for proper ordering and the results will be returned.
The merge join operation always takes two inputs, with the prerequisite that each input has already been sorted on the join column or columns. The merge join operation reads both inputs in their entirety at one time and outputs the results of the join. Merge joins and hash joins are usually more efficient than nested loops joins when remote tables are involved, because these types of joins will almost always involve fewer network roundtrips. Hash joins are not supported when rule-based optimization is used. Because of the RULE hint, Oracle chose a merge join.
Reading TKPROF Reports
Every TKPROF report starts with a header that lists the TKPROF version, the date and time the report was generated, the name of the trace file, the sort option used, and a brief definition of the column headings in the report. Every report ends with a series of summary statistics. You can see the heading and summary statistics on the sample TKPROF report shown earlier in this paper.
The main body of the TKPROF report consists of one entry for each distinct SQL statement that was executed by the database server while SQL trace was enabled. There are a few subtleties at play in the previous sentence. If an application queries the customers table 50 times, each time specifying a different customer_id as a literal, then there will be 50 separate entries in the TKPROF report. If however, the application specifies the customer_id as a bind variable, then there will be only one entry in the report with an indication that the statement was executed 50 times. Furthermore, the report will also include SQL statements initiated by the database server itself in order to perform so-called “recursive operations” such as manage the data dictionary and dictionary cache.
The entries for each SQL statement in the TKPROF report are separated by a row of asterisks. The first part of each entry lists the SQL statement and statistics pertaining to the parsing, execution, and fetching of the SQL statement. Consider the following example:
******************************************************************************** SELECT table_name FROM user_tables ORDER BY table_name call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.01 0.02 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 14 0.59 0.99 0 33633 0 194 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 16 0.60 1.01 0 33633 0 194 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: RSCHRAG [recursive depth: 0]
This may not seem like a useful example because it is simply a query against a dictionary view and does not involve application tables. However, this query actually serves the purpose well from the standpoint of highlighting the elements of a TKPROF report.
Reading across, we see that while SQL trace was enabled, the application called on the database server to parse this statement once. 0.01 CPU seconds over a period of 0.02 elapsed seconds were used on the parse call, although no physical disk I/Os or even any buffer gets were required. (We can infer that all dictionary data required to parse the statement were already in the dictionary cache in the SGA.)
The next line shows that the application called on Oracle to execute the query once, with less than 0.01 seconds of CPU time and elapsed time being used on the execute call. Again, no physical disk I/Os or buffer gets were required. The fact that almost no resources were used on the execute call might seem strange, but it makes perfect sense when you consider that Oracle defers all work on most SELECT statements until the first row is fetched.
The next line indicates that the application performed 14 fetch calls, retrieving a total of 194 rows. The 14 calls used a total of 0.59 CPU seconds and 0.99 seconds of elapsed time. Although no physical disk I/Os were performed, 33,633 buffers were gotten in consistent mode (consistent gets). In other words, there were 33,633 hits in the buffer cache and no misses. I ran this query from SQL*Plus, and we can see here that SQL*Plus uses an array interface to fetch multiple rows on one fetch call. We can also see that, although no disk I/Os were necessary, it took quite a bit of processing to complete this query.
The remaining lines on the first part of the entry for this SQL statement show that there was a miss in the library cache (the SQL statement was not already in the shared pool), the CHOOSE optimizer goal was used to develop the execution plan, and the parsing was performed in the RSCHRAG schema.
Notice the text in square brackets concerning recursive depth. This did not actually appear on the report—I added it for effect. The fact that the report did not mention recursive depth for this statement indicates that it was executed at the top level. In other words, the application issued this statement directly to the database server. When recursion is involved, the TKPROF report will indicate the depth of the recursion next to the parsing user.
There are two primary ways in which recursion occurs. Data dictionary operations can cause recursive SQL operations. When a query references a schema object that is missing from the dictionary cache, a recursive query is executed in order to fetch the object definition into the dictionary cache. For example, a query from a view whose definition is not in the dictionary cache will cause a recursive query against view$ to be parsed in the SYS schema. Also, dynamic space allocations in dictionary-managed tablespaces will cause recursive updates against uet$ and fet$ in the SYS schema.
Use of database triggers and stored procedures can also cause recursion. Suppose an application inserts a row into a table that has a database trigger. When the trigger fires, its statements run at a recursion depth of one. If the trigger invokes a stored procedure, the recursion depth could increase to two. This could continue through any number of levels.
So far we have been looking at the top part of the SQL statement entry in the TKPROF report. The remainder of the entry consists of a row source operation list and optionally an execution plan display. (If the explain keyword was not used when the TKPROF report was generated, then the execution plan display will be omitted.) Consider the following example, which is the rest of the entry shown above:
Rows Row Source Operation
------- ---------------------------------------------------
194 SORT ORDER BY
194 NESTED LOOPS
195 NESTED LOOPS OUTER
195 NESTED LOOPS OUTER
195 NESTED LOOPS
11146 TABLE ACCESS BY INDEX ROWID OBJ$
11146 INDEX RANGE SCAN (object id 34)
11339 TABLE ACCESS CLUSTER TAB$
12665 INDEX UNIQUE SCAN (object id 3)
33 INDEX UNIQUE SCAN (object id 33)
193 TABLE ACCESS CLUSTER SEG$
387 INDEX UNIQUE SCAN (object id 9)
194 TABLE ACCESS CLUSTER TS$
388 INDEX UNIQUE SCAN (object id 7)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
194 SORT (ORDER BY)
194 NESTED LOOPS
195 NESTED LOOPS (OUTER)
195 NESTED LOOPS (OUTER)
195 NESTED LOOPS
11146 TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$'
11146 INDEX (RANGE SCAN) OF 'I_OBJ2' (UNIQUE)
11339 TABLE ACCESS (CLUSTER) OF 'TAB$'
12665 INDEX (UNIQUE SCAN) OF 'I_OBJ#' (NON-UNIQUE)
33 INDEX (UNIQUE SCAN) OF 'I_OBJ1' (UNIQUE)
193 TABLE ACCESS (CLUSTER) OF 'SEG$'
387 INDEX (UNIQUE SCAN) OF 'I_FILE#_BLOCK#' (NON-UNIQUE)
194 TABLE ACCESS (CLUSTER) OF 'TS$'
388 INDEX (UNIQUE SCAN) OF 'I_TS#' (NON-UNIQUE)
The row source operation listing looks very much like an execution plan. It is based on data collected from the SQL trace file and can be thought of as a “poor man’s execution plan”. It is close, but not complete.
The execution plan shows the same basic information you could get from the autotrace facility of SQL*Plus or by querying the plan table after an EXPLAIN PLAN statement—with one key difference. The rows column along the left side of the execution plan contains a count of how many rows of data Oracle processed at each step during the execution of the statement. This is not an estimate from the optimizer, but rather actual counts based on the contents of the SQL trace file.
Although the query in this example goes against a dictionary view and is not terribly interesting, you can see that Oracle did a lot of work to get the 194 rows in the result: 11,146 range scans were performed against the i_obj2 index, followed by 11,146 accesses on the obj$ table. This led to 12,665 non-unique lookups on the i_obj# index, 11,339 accesses on the tab$ table, and so on.
In situations where it is feasible to actually execute the SQL statement you wish to explain (as opposed to merely parsing it as with the EXPLAIN PLAN statement), I believe TKPROF offers the best execution plan display. GUI tools such as TOAD will give you results with much less effort, but the display you get from TOAD is not 100% complete and in certain situations critical information is missing. (Again, my experience is with the free version!) Meanwhile, simple plan table query scripts like my explain.sql presented earlier in this paper or utlxpls.sql display very incomplete information. TKPROF gives the most relevant detail, and the actual row counts on each operation can be very useful in diagnosing performance problems. Autotrace in SQL*Plus gives you most of the information and is easy to use, so I give it a close second place.
TKPROF Reports: More Than Just Execution Plans
The information displayed in a TKPROF report can be extremely valuable in the application tuning process. Of course the execution plan listing will give you insights into how Oracle executes the SQL statements that make up the application, and ways to potentially improve performance. However, the other elements of the TKPROF report can be helpful as well.
Looking at the repetition of SQL statements and the library cache miss statistics, you can determine if the application is making appropriate use of Oracle’s shared SQL facility. Are bind variables being used, or is every query a unique statement that must be parsed from scratch?
From the counts of parse, execute, and fetch calls, you can see if applications are making appropriate use of Oracle’s APIs. Is the application fetching rows one at a time? Is the application reparsing the same cursor thousands of times instead of holding it open and avoiding subsequent parses? Is the application submitting large numbers of simple SQL statements instead of bulking them into PL/SQL blocks or perhaps using array binds?
Looking at the CPU and I/O statistics, you can see which statements consume the most system resources. Could some statements be tuned so as to be less CPU intensive or less I/O intensive? Would shaving just a few buffer gets off of a statement’s execution plan have a big impact because the statement gets executed so frequently?
The row counts on the individual operations in an execution plan display can help identify inefficiencies. Are tables being joined in the wrong order, causing large numbers of rows to be joined and eliminated only at the very end? Are large numbers of duplicate rows being fed into sorts for uniqueness when perhaps the duplicates could have been weeded out earlier on?
TKPROF reports may seem long and complicated, but nothing in the report is without purpose. (Well, okay, the row source operation listing sometimes isn’t very useful!) You can learn volumes about how your application interacts with the database server by generating and reading a TKPROF report.
Conclusion
In this paper we have discussed how to generate execution plans and TKPROF reports, and how to interpret them. We’ve walked through several examples in order to clarify the techniques presented. When you have a firm understanding of how the Oracle database server executes your SQL statements and what resources are required each step of the way, you have the ability to find bottlenecks and tune your applications for peak performance. EXPLAIN PLAN and TKPROF give you the information you need for this process.
When is a full table scan better than an index range scan? When is a nested loops join better than a hash join? In which order should tables be joined? These are all questions without universal answers. In reality, there are many factors that contribute to determining which join method is better or which join order is optimal.
In this paper we have looked at the tools that give you the information you need to make tuning decisions. How to translate an execution plan or TKPROF report into an action plan to achieve better performance is not something that can be taught in one paper. You will need to read several papers or books in order to give yourself some background on the subject, and then you will need to try potential solutions in a test environment and evaluate them. If you do enough application tuning, you will develop an intuition for spotting performance problems and potential solutions. This intuition comes from lots of experience, and you can’t gain it solely from reading papers or books.
