Oracle Performance Tuning

Oracle Performance Tuning is a very important part. It is not just the Oracle DBA’s responsibility, it is also the responsibility of Oracle developers. Performance tuning should start before Design and should be continuously tested. We need to have good Knowledge of how Oracle Software works. Many times problems occur as we are not aware of the working of the Oracle

In this section, We are presenting the Oracle performance tuning articles which will help you solve the problems quickly

Tuning Tools

1. Oracle Explain Plan
2. Autotrace in Oracle
3. Oracle Performance :sql_trace parameter and its impact
4. How to turn on the trace in database and Format the trace file
5. Sql tuning advisor

Performance Terms explained

1. What is Logical I/O and Physical I/O in Oracle Database
2. how to find session generating lots of redo
3. What is IOPS and How to calculate it in oracle
4. What is DB time and Average Active sessions, Active session in oracle
5. Bind Variable in Oracle
6. Automatic Database Diagnostic Monitor (ADDM)
7. Active Session History(ASH)
8. Automatic Workload Repository(AWR)
9. Hanganalyze and system state dump
10. Oracle waits events
11. How to find which sid is doing full table scan

Optimizer

1. New Optimizer Feature with 11g
2. Optimizer hints
3. find optimizer/database underscore parameter
4. Optimizer Mode
5. Join Methods in Oracle

Statistics

1. Find indexes and assigned columns for a table
2. How Table Monitoring and STATISTICS_LEVEL parameter are connected from 10g onwards
3. How to check Stale statistics
4. How to find table where statistics are locked
5. How to set statistics for table in oracle
6. How to delete statistics from Table in oracle
7. compare statistics using stattab
8. How to compare statistics using history for a table in Oracle
9. How to transfer statistics between databases in Oracle
10. How to import statistics in Oracle
11. How to export statistics in Oracle
12. How to create stat table in Oracle
13. How to check automatic statistics collection in Oracle
14. How to restore optimizer statistics
15. How to delete Optimizer preference
16. How to gather Statistics with DBMS_STATS Procedures
17. How to set table level preference setting
18. How to set Schema level preference setting
19. How to check optimizer statistics preferences at the table level
20. Optimizer statistics preferences in Oracle
21. How to check column statistics in Oracle
22. How to check Index statistics in Oracle
23. How to check gather stats on a table

General Performance topics

1. sql tuning in Oracle
2. Nested Loop Join in Oracle
3. Hash join in Oracle
4. Oracle performance & tuning Quiz
5. Oracle Index clustering factor
6. How to turn on the SQL trace, 10046 event in Oracle database and trcsess, tkprof utility
7. Incremental Statistics Gathering in 11g
8. Useful scripts for Oracle Database
9. Oracle table locks
10. All Useful 11g Dynamic Performance Views
11. How to find the waitevent History of the Oracle session
12. Virtual Index in Oracle
13. Oracle Performance tuning Glossary

SQL Profile

• how to check sql profile in oracle
• how to find if the sql is using the sql profile
• How to move the sql profiles in Oracle
• How to implement custom Sql Profile in Oracle

SQLPLAN Management

• Sql plan Management in Oracle
• How to replace sql plan by another sql id sql plan
• Queries on Sqlplan Management in oracle
• Drop the sql baseline in Oracle
• Move sql baselines to another database
• create sql baseline in oracle 19c
• How to create sql baseline from cursor cache in Oracle
• how to create sql baseline from Sql tuning set in Oracle
• create sql baseline from AWR repository without STS

We will keep adding the Knowledge article for Oracle Performance tuning. Please subscribe to the blog to get the latest articles. Also Please do provide the Feedback