Ixora News - August 2000

Welcome to the August 2000 issue of "Ixora News".

In this issue ...

Our new-look Q&A section - It's been reorganized into topic areas so that you can find answers faster.
A comment about comments - How NOT to comment out code in SQL scripts.
Double trouble - Some performance problems with snapshots in Oracle 8.1.
More new APT scripts - A few more additions to our collection of advanced performance tuning scripts.
Going to OpenWorld? - Catch the Ixora performance tuning seminar too!

Our new-look Q&A section

The Question and Answer section of the Ixora web site has received a face lift. We found that the same old questions were being asked repeatedly because people were not able to find previous answers easily enough. To make previous answers easier to find (and to cut down on the number of repeat questions) we have reorganized our Q&A section into 27 topic areas. Now, whether you are looking for information about the shared pool, or rollback segments, or redo generation, or anything else, you can find all the information you need on a single web page. It is expected that this new format will also make it easier for us to add new answers with less duplication and thus to continue to build a valuable collection of Oracle internals and advanced performance tuning information. Look for more answers to be added over the next few weeks.

A comment about comments

Be careful how you comment out lines in SQL scripts. Consider the following example. Say you have been testing a script to make some complex bulk changes to the CUST_PROD_DISCOUNTS table, and the first step of each test has been to take of copy of the data from production as follows.
drop table cust_prod_discounts;
create table cust_prod_discounts nologging as select * from cust_prod_discounts@prod;
Now the script is ready to run in production, but watch what happens when these preliminary commands are commented out using the Unix script comment convention (#) instead of the correct Oracle comment syntax (REM, -- or /* */).
# Of course, we don't want to do this in production!
# drop table cust_prod_discounts;
# create table cust_prod_discounts nologging as select * from cust_prod_discounts@prod;
Strangely, Oracle does not return a syntax error as you might expect.
No, Oracle just ignores the # and executes the commands anyway!
SQL> # Of course, we don't want to do this in production!
SP2-0734: unknown command beginning "Of course,..." - rest of line ignored.
SQL> #
SQL> # drop table cust_prod_discounts;

Table dropped.

SQL> # create table cust_prod_discounts nologging as select * from cust_prod_discounts@prod;
 create table cust_prod_discounts nologging as select * from cust_prod_discounts@prod
ERROR at line 1:
ORA-02019: connection description for remote database not found
Now, where are those backup tapes?!

Double trouble

On two recent consulting engagements we have run into serious problems with snapshots (materialized views) under Oracle 8.1. When a snapshot is refreshed from a snapshot log in an Oracle 8i database an exclusive library cache lock is required against the master table. This exclusive lock causes two problems.

Firstly, the lock mode is incompatible with the shared locks that are required by many other database operations. For example, if the master table was being analyzed at the time that the snapshot refresh operation started, then its exclusive lock request must wait until the analyze operation has finished. Most importantly, all other shared lock requests have to queue behind the exclusive lock request. This effectively suspends all activity against the master table indefinitely. To check whether you have this problem, look for an accumulation of time against the library cache lock wait event in V$SYSTEM_EVENT.

Another side effect of this problem is that all SQL in the library cache that is dependent on the definition of the master table is invalidated as soon as the associated exclusive library cache pin is released. That means that all those SQL statements have to be reparsed, and if multiple sessions attempt to do so simultaneously, then they will each create their own versions of those SQL statements, leading to high version counts and the associated performance problems. To check whether you have this problem, use the version_count.sql script from the Ixora web site.

More new APT scripts

When the definition of a database object is changed (or might have been changed) all dependent SQL is invalidated. Invalidated SQL statements must be reparsed (reloaded) before they can be executed again. Invalidations are also one possible cause of high version counts. Therefore, invalidations should be eliminated if at all possible. To do so, it is necessary to determine which database objects were changed so as to trigger invalidations, how they were changed, and how that might be prevented in future. This script attempts to do the first part of the job, by attributing invalidations to changed database objects. Where an invalidated cursor is dependent on two or more objects for which the definition appears to have been changed, then the invalidations are distributed evenly between them, and shown in a second column (AND_MAYBE). If you have invalidations that cannot be attributed to the 8i snapshot refresh problem then this script may help you to work out where they are coming from.

Why would you want to drop the X_$ views? To install StatsPack! StatsPack also uses some X_$ views, but the StatsPack install scripts fail if these views already exist. Therefore, to install StatsPack, we recommend that you first drop all the X_$ views created for APT using this script, then install StatsPack (which will create a few of the X_$ views), and then restore the remaining X_$ views by re-running create_xviews.sql once StatsPack has been successfully installed.

We often see sites where very little has been done to mark important packages and cursors for keeping in the library cache. In part, the problem is that it is difficult to decide what to keep. Under version7, V$DB_OBJECT_CACHE had an execution count for packages, procedures and so on (although the statistics were known to be highly inaccurate under some circumstance). But Oracle8 no longer attempts to maintain executions counts for anything other than cursors. This has left many people in the dark as to which packages (and so on) to keep. The party line seems to be that customers should infer package execution counts from the cursor executions counts, but doing so is non-trivial because you have to navigate through a very obscure X$ table to do it - namely, X$KGLRD instead of the normal library cache dependency structure X$KGLDP. Although Ixora's recommendation remains that you should keep everything, this script at least allows you to attempt to follow the party line. Note that the results should be taken with a pinch of salt, because some packages do not execute any SQL directly, and some SQL statements may have been executed other than from the packages.

In our ongoing quest to encourage people to keep objects in the library cache more aggressively, the keep_schema.sql, keep_cursors.sql and keep_sys_objects.sql scripts have been removed from the Ixora web site, and replaced with something better, keeper.sql. This script is more intelligent than its predecessors in several ways. It only keeps object that are actually in use, whereas the previous scripts kept everything; it stops keeping cursors once the shared pool is 80% full to allow for literal SQL and small shared pools; and it avoids keeping cursors with version count or invalidation problems. This may not be the last word on the matter, but it is certainly a step in the right direction.

Going to OpenWorld?

If so, you have an opportunity to linger for a day in San Francisco to meet Steve Adams, Ixora's principle consultant, and to pick up all the otherwise unavailable advanced Oracle performance tuning information contained in his "SGA Internals and Advanced Instance Tuning" seminar. This seminar is Steve at his best, explaining what he knows best - how Oracle really works and how it should be tuned for the most demanding of applications. Details are available on the Ixora web site at http://www.ixora.com.au/seminars/index.htm. This seminar is limited to 50 people. Don't miss out!

Copyright Ixora Pty Ltd Send Email Home