当前位置: 代码迷 >> SQL >> SQLA的应用_查找top的SQL语句
  详细解决方案

SQLA的应用_查找top的SQL语句

热度:16   发布时间:2016-05-05 12:09:33.0
SQLA的使用__查找top的SQL语句
使用方法:
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.
  相关解决方案