|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!|
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 /* */).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.# 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;
Now, where are those backup tapes?!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
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.
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.
|Copyright © Ixora Pty Ltd||