SQL Tuning

How to Resolve ORA-13780: SQL statement does not exist

by Ed Chen
February 7, 2023February 8, 2023

ORA-13780 Tried to create a SQL tuning task for specific SQL ID, but it failed with ORA-13780. SQL> select DBMS_SQLTUNE.CREATE_TUNING_TASK ( sql_id => '21zq47mj49f7w', scope… Read More »

How to Enable AUTOTRACE

by Ed Chen
October 26, 2022March 17, 2023

Enable AUTOTRACE To get full AUTOTRACE feature in your session, you should take the following steps. Need PLAN_TABLE table to be accessed in user's schema.… Read More »

How to Install PLUSTRACE Role

by Ed Chen
October 25, 2022March 17, 2023

PLUSTRACE Before enabling AUTOTRACE feature in your session, you not only need to create PLAN_TABLE table, but also need PLUSTRACE role, which is not native… Read More »

How to Install PLAN_TABLE Table

by Ed Chen
October 24, 2022March 17, 2023

PLAN_TABLE Before enabling AUTOTRACE feature in your session, you not only need PLUSTRACE role to be installed, but also need to create PLAN_TABLE table, which… Read More »

SYS_AUTO_SQL_TUNING_TASK Report

by Ed Chen
September 14, 2022March 24, 2023

SYS_AUTO_SQL_TUNING_TASK Sometimes, you don't have to run SQL tuning report for a specific statement, because the automatic SQL tuning job (SYS_AUTO_SQL_TUNING_TASK) could have identified long-running… Read More »

How to Improve SQL Performance

by Ed Chen
March 3, 2018November 14, 2021

SQL statements including queries and DML sometimes execute with the different index because of the following reasons: Missing index The target index might be dropped… Read More »

How to Run DBMS_SQLTUNE for Specific SQL

by Ed Chen
January 13, 2018February 7, 2023
4 Comments

SQL Tuning Advisor I know there could be more convenient to run SQL tuning reports by DBMS_SQLTUNE in tools like Enterprise Manager or TOAD. But… Read More »

How to Find out Badly Written Statements for Tuning

by Ed Chen
December 16, 2012October 10, 2021

When wait events like LIBRARY CACHE or LATCH FREE surges in business hours and the database seems to hang, you can check the free memory… Read More »

How to Get the Execution Plan of a Specific Statement

by Ed Chen
November 10, 2012March 1, 2021

Sometime, there is no OEM around to check the explain plan of a specific statement. We need a bare way to do the job. Here… Read More »