使用方法：
Execution Environment:
SQL*Plus
Access Privileges:
Requires to access some V$ dynamic views. If used on an Apps instance, connect as APPS.
In general, connect into SQL*Plus as main application schema owner, or as SYSTEM.
If application schema owner (APPS or any other) does not have access to data dictionary
dynamic views, use included script SQLAGRNT.SQL to grant access to required views.
Usage:
sqlplus apps/<pwd>
SQL>START SQLAREAT;
Instructions:
1. Unzip file SQLA.zip into dedicated directory on db server preserving case
on all scripts (UPPER CASE). Example: SQLAREAT.SQL
2. If you are using script SQLAREAT.SQL for the first time, connect as main
application user (APPS if using Oracle Apps) and execute:
# sqlplus apps/<pwd>
SQL> START SQLACREA.SQL;
This step creates a staging repository that is used by SQLAREAT.SQL
If not sure if the staging repository has been created or not, simply
execute SQLACREA.SQL and it will re-create it.
If you get PLS-00201 errors, execute SQLAGRNT.SQL as SYSTEM, SYS or
INTERNAL
3. Once the staging repository is created, execute:
# sqlplus apps/<pwd>
SQL> START SQLAREAT.SQL;
4. SQLAREAT.SQL creates an HTML spool file with most expensive SQL.
Execute this script manually or within a cron job. Suggested frequency
is every 15 minutes during peak time (high system load window).
5. If SQLAREAT.SQL is used over a period of time (i.e. peak hours), use
included SQLAREAR.SQL to extract most expensive SQL observed during a
range of snapshots captured previously by SQLAREAT.SQL:
# sqlplus apps/<pwd>
SQL> START SQLAREAR.SQL <p_process_type> <p_snap_id_from> <p_snap_id_to>;
SQL> START SQLAREAR.SQL LR 1 4;
Where p_process_type is LR for logical reads or PR for physical reads
6. In addition to SQLAREAT.SQL and SQLAREAR.SQL, use the SQLAREAS.SQL to
report additional statistics.
# sqlplus apps/<pwd>
SQL> START SQLAREAS.SQL;
7. If you need to uninstall this tool, execute commands below and remove
scripts SQLA* from dedicated directory
# sqlplus apps/<pwd>
SQL> START SQLADROP.SQL
If you ever executed SQLAGRNT.SQL, use SQLAREVK.SQL when uninstalling
8. For feedback, email author [email protected]
PROOFREAD THIS SCRIPT BEFORE USING IT! Due to differences in the way text
editors, e-mail packages, and operating systems handle text formatting (spaces,
tabs, and carriage returns), this script may not be in an executable state
when you first receive it. Check over the script to ensure that errors of
this type are corrected. The script will produce an output file named
SQLAREATnnnn.HTML. This file can be viewed in a browser or uploaded for support
analysis.