Applications Performance
Performance Troubleshooting Chart
Performance Troubleshooting Chart(<-- Click here for details & download)
This is a beta version of a troubleshooting flowchart. The intent is to provide a series of troubleshooting steps in a flowchart format for Performance troubleshooting. We are testing different deployment methods, so your feedback is encouraged in reference to ease of use and content. Please use the feedback link at the bottom of the chart for such comments.
Locks for given Session ID
bde_session_locks.sql(<-- Click here for details & download)
This script creates a report with locks for one session that is not responding (hanging), because of a Lock.
Validates and Rebuilds Fragmentated Indexes
bde_rebuild.sql(<-- Click here for details & download)
This script validates and proceeds to generate a dynamic script to rebuild them.
Analyze Indexes to determine Fragmentation
bde_analyze_indexes.sql(<-- Click here for details & download)
This script will analyze indexes to determine fragmentation
Current, Required and Recommended Apps 11i init.ora params
bde_chk_cbo.sql(<-- Click here for details & download)
This script displays the current value, the required value (if any), the recommended value (if any), and the default value (if any).
Simple Explain Plan for given SQL Statement
bde_x.sql(<-- Click here for details & download)
Script generates a Simple Explain Plan for one SQL statement, required to diagnose apps performance issues (transaction tuning).
Expensive SQL and Resources Utilization for a Given Session ID
bde_session.sql(<-- Click here for details & download)
This script creates a report with relevant information for one session that is either performing poorly or not responding (hanging).
Verifies Statistics for all Installed Apps Modules 11.5
bde_last_analyzed.sql(<-- Click here for details & download)
Script verifies statistics for all installed Apps modules 11.5
Changes CBO Stats Selectivity for a Given Index and Column
bde_chg_stats.sql(<-- Click here for details & download)
Script that changes CBO stats selectivity for a given index and column
Clone Views Across Instances for SQL Tuning Exercises
coe_view.sql(<-- Click here for details & download)
This script facilitates cloning views across instances for SQL tuning exercises
Trace Apps Online Transactions with Event 10046
coe_trace.sql(<-- Click here for details & download)
This script will Generates SQL Trace with bind variables and waits information for an Oracle Applications Form (equivalent of Event 10046 level 12)
Automate CBO Stats Gathering
coe_stats.sql(<-- Click here for details & download)
This script automates CBO stats gathering using FND_STATS and table sizes
Top 10 Expensive SQL from SQL Area
coe_sqlarea.sql(<-- Click here for details & download)
This script scans sql area and sql text v$ dynamic performance views and displays Top n SQL Statements in terms of resources utilization.
SQL Tracing online transactions using Event 10046
coe_event_10046.sql(<-- Click here for details & download)
Process and Session info for one Concurrent Request
Session and Serial# for Locked Rows
coe_locks.sql(<-- Click here for details & download)
This script displays all sessions holding a lock on a table or row.
Import CBO Stats from COE_STATTAB_XYZ
coe_import_stattab.sql(<-- Click here for details & download)
This script uploads data dictionary statistics from table COE_STATTAB_XYZ...
Enhanced Explain Plan for given SQL Statement
SQLTXPLAIN.sql(<-- Click here for details & download)
Given one SQL Statement as input, generate a comprehensive report.. (sql tuning).
Audit Trail
Active Users executing FORMs or Conc Programs
FNDFindActiveUsers115.sql(<-- Click here for details & download)
This script generates a list of active apps users with at least one open form, or executing at least one concurrent program.
Trace
Enhanced Explain Plan for given SQL Statement
bde_system_event_10046.sql(<-- Click here for details & download)
This script is used to turn SQL trace ON with LEVEL 12 for any concurrent program that starts its execution AFTER the EVENT 10046 is turned ON at the SYSTEM level.
TRCANLZR.sql(<-- Click here for details & download)
Trace Analyzer - Interpreting Raw SQL Traces generated by EVENT 10046
No comments:
Post a Comment