Oracle Performance Tuning Online training

Oracle Performance tuning

SQL Performance Tuning

1. Reduce the Workload
2. Balance the Workload
3. Parallelize the Workload
4. Identifying High-Load SQL
5. Automatic Database Diagnostic Monitor
6. Automatic Workload Repository
7. V$SQL view
8. Custom Workload
9. SQL Trace
10. Identifying Resource-Intensive SQL
11. Tuning a Specific Program – (kind of only one sql)
12. Tuning an Application / Reducing Load – (you dont know what is running)
13. Buffer gets (V$SQLSTATS.BUFFER_GETS, for high CPU using statements)
14. Disk reads (V$SQLSTATS.DISK_READS, for high I/O statements)
15. Sorts (V$SQLSTATS.SORTS, for many sorts)
16. Gathering Data on the SQL Identified
17. Information to Gather During Tuning
18. Complete SQL text from V$SQLTEXT
19. Structure of the tables referenced in the SQL statement, usually by describing the table in SQL*Plus
20. Definitions of any indexes (columns, column orderings),and whether the indexes are unique or non-unique
21. Optimizer statistics for the segments (including the number of rows each table, selectivity of the index columns), including the date when the segments were last analyzed
22. Views
23. V$SQL_PLAN, TKPROF output)
24. Previous optimizer plans for that SQL statement
25. Developing Efficient SQL Statements
26. Verifying Optimizer Statistics
27. Reviewing the Execution Plan for best filter.
28. Restructuring SQL Statements
29. Restructuring Indexes
30. Modifying or Disabling Triggers and Constraints
31. Restructuring Data
32. Maintaining Execution Plans Over Time
33. Visiting Data as Few Times as Possible
34. SQL Best practices
35. Compose Predicates Using AND
36. Avoid Transformed Columns in the WHERE Clause
37. Write Separate SQL Statements for Specific Tasks
38. Use of EXISTS versus IN for Subqueries based on selective predicates
39. Controlling the Access Path and Join Order with Hints
40. Index best practices
41. Nonselective indexes
42. Critical access paths.
43. Reordering columns.
44. Add columns to the index to improve selectivity
45. Best practices.

1. Interpreting EXPLAIN PLAN.
2. Verifying the state of the objects.
3. Determining cardinality.
4. Creating advanced indexes.

a. Global index
b. Partitioned local index
c. Composite index
d. Context indexes
e. Bitmap index
f. Binary index
g. Clustered index

5. Various wait events – How to tackle them?
6. UNDO space performance tuning.
7. Profiling and Tracing PL/SQL Programs
8. Using DYNAMIC VIEWS
9. Gather statistics
10. Reducing Loop Overhead for DML Statements and Queries (FORALL, BULK COLLECT)
11. Writing Computation-Intensive Programs in PL/SQL
12. Tuning Dynamic SQL with EXECUTE IMMEDIATE and Cursor Variables
13. Tuning PL/SQL Procedure Calls with the NOCOPY Compiler Hint
14. Compiling PL/SQL Code for Native Execution
15. Overview of Table Functions
16. Snap shot too old error.