The goals of Oracle troubleshooting include:

  • Learn a troubleshooting methodology for use in analyzing any Oracle database
  • Understand ratio-based and bottleneck troubleshooting analysis
  • Learn troubleshooting techniques for monitoring and optimizing memory usage
  • Know how to quickly pinpoint and resolve I/O hotspots at the database, storage and object level
  • Using scripts to uncover session-related bottlenecks
  • Understand techniques for locating and fixing problem SQL

Oracle Stored Procedures Tips

1
2
-- prototype defines the syntax for compiling or decompiling a procedure.
Alter procedure <procedure_name> compile | decompile;

Monitor oracle performance by OEM

Proactive Database Tuning

Reactive Database Tuning

SQL Tuning

Resources:
https://docs.oracle.com/cd/B28359_01/server.111/b28275/toc.htm