Performance

How to Get Auto SQL Tuning Report

by Ed Chen
September 14, 2022September 20, 2022

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 Resolve ORA-20200: Database Instance does not exist

by Ed Chen
July 11, 2022July 12, 2022

ORA-20200 When we ran a STATSPACK report, we got error ORA-20200. SQL> @?/rdbms/admin/spreport.sql ... ORA-20200: Database/Instance 356760607/1 does not exist in STATS$DATABASE_INSTANCE ORA-06512: at line… Read More »

How to Increase PGA_AGGREGATE_LIMIT

by Ed Chen
June 16, 2022August 26, 2022

Whenever you need more PGA to service clients or saw error ORA-04030, you may consider to raise PGA size online. Let's see current settings. SQL>… Read More »

How to Resolve ORA-12850: Could not allocate slaves on all specified instances: 2 needed, 0 allocated

by Ed Chen
July 21, 2021March 17, 2022

JDBC ORA-12850 Some developers report ORA-12850 error in their JDBC log. ORA-12850: Could not allocate slaves on all specified instances: 2 needed, 0 allocated There… Read More »

How to Resolve ORA-01000: maximum open cursors exceeded

by Ed Chen
January 15, 2020October 25, 2021

ORA-01000 Saw an error ORA-01000 when executing a lot of statements. It seemed that the number of open cursors has reached the limitation. ORA-01000: maximum… Read More »

How to Read AWR CPU Utilization

by Ed Chen
May 4, 2018October 3, 2021

CPU Utilization We might have some doubts about OS CPU utilization when we read an AWR report like the following: We all may be confused… Read More »

Standard Edition AWR Report

by Ed Chen
April 28, 2018September 4, 2022

AWR for Standard Edition I was assigned to collect some performance data in an unfamiliar database. While I ran AWR report by executing @?/rdbms/admin/awrrpt.sql under… 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, 2018September 20, 2022
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 »

Who Cause Library Cache Lock?

by Ed Chen
February 11, 2017October 7, 2021

I found a session waiting for an event of Library Cache Lock. In the first place, I thought there could be blocking sessions that locked… Read More »