Things to look for when performance Tuning an application
- A Look on the initialization parameters in Oracle
- Execution plan
- Indexing
- Joins used by the Optimizer
- Materialized View
- Hints if needed
- Application logic to avoid context switching
- Use of Bind Variables
- Avoid Sorting
- Partitioning
- Avoid Excessive use of triggers ( Row changes are performed in the SQL engine and triggers are executed in the PL/SQL engine. Once again, you encounter the dreaded context-switch problem )
- Database links
- Datatype conversion overhead like to_char , to_number
- Function Result Cache ( 11G Onwards)
- Automatic Segment Space Management
- Extent management Local
- Rebuilding Index if necessary
- Statistics are up to date
- Extented Statistics ( 11G onwards)
- Cusor Sharing parameter
- SQL Profiles
- ADDM
- Automatic SQL Tuning Advisor
- USE OF "NOT" CLAUSE WILL NEGATE THE USE OF INDEXES
- Scalar Sub Query Caching
- How Often Commit is issued inside an application
- Function Based indexes
- Row Chaining and Row Migration
- Bitmap , Btree and Functional Indexes
- NO LOGGING when its one time activity and no need of recovery.
- Dropping Un Used indexes
- Indexing a Foreign Key
- Caching SQL Query Results
- Making use of Stored packages so that they can be cached in the PLSQL Area
- Latches and Waits
- Executing SQL in Parallel
- Enabling Query Rewrite to make use of the materialized Views
- Index organized Tables
- Concatenated Indexes
- Reverse Key Indexes
- Histograms
- When Using an Alias, Hint the Alias, Not the Table
- Caching a Table in Memory
- Attempt to limit the calls to SYSDATE in iterative or recursive loops because overhead is associated with this variable.
- Pinning PLSQL Objects to memory
- High Water Mark on a Table
- Paging and Swapping
- Ratios : Possible Causes for Problems
- Wait Events
- Latches
- SQL Access Advisor
No comments:
Post a Comment