Performance Tuning

Statspack is a performance tuning tool provided by Oracle. The way to use STATSPACK in Oracle is to bracket the performance problem by getting a… Read More »Using STATSPACK in Oracle 12c

Oracle OSWatcher Black Box (OSWbb) collects and archives operating system and network metrics that you can use to diagnose performance issues. OSWbb operates as a… Read More »Using Oracle OSWatcher Black Box

Using Oracle System State Dump. When the database encounters a serious performance problem or hang, sqlplus cannot connect to the server. In this case, if… Read More »Oracle System State Dump

Oracle SQLTXPLAIN, also known as SQLT, is a tool provided by Oracle Server Technologies Center of Expertise – ST CoE. SQLT inputs one SQL statement… Read More »Using Oracle SQLTXPLAIN (SQLT)

In Oracle Explain Plan a shared PLAN_TABLE is created by default, but you can still create a local version of the table using the “utlxplan.sql” script. Some… Read More »Oracle Explain Plan

Oracle SQL Access Advisor uses DBMS_ADVISOR package, in cojunction with the DBMS_SQLTUNE package, is used to create and execute any advisor tasks, including SQL Access Advisor tasks.  DECLARE taskname… Read More »Oracle SQL Access Advisor

Oracle SQL Tuning Advisor evaluates resource consumption in terms of CPU, I/O, memory and temporary space. The advisor receives one or more SQL statements as… Read More »Using Oracle SQL Tuning Advisor

Oracle SQL Trace the quickest way to capture the SQL being processed by a session is to switch on SQL trace or set the 10046… Read More »Oracle SQL Trace, trcsess and tkprof

This is the syntax to start and stop this SQL Tuning 10053 trace. The trace files are all created in the ‘BACKGROUND_DUMP_DEST’ location (init.ora setting).… Read More »Oracle SQL Tuning 10053 CBO Trace