Performance

How to Resolve ORA-01000: maximum open cursors exceeded

by Ed Chen
January 15, 2020January 17, 2020

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

How to Read CPU Utilization of OS Statistics in AWR

by Ed Chen
May 4, 2018May 4, 2018

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 »

How to Workaround Oracle AWR in Standard Edition

by Ed Chen
April 28, 2018November 20, 2018

Oracle AWR in Standard Edition I was assigned to collect performance data on an unfamiliar database. While I ran AWR report by executing @?/rdbms/admin/awrrpt.sql under… Read More »

How to Correct Wrong Execution Path

by Ed Chen
March 3, 2018March 27, 2018

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 SQL Tuning Report for Specific SQL ID Manually

by Ed Chen
January 13, 2018November 29, 2019
2 Comments

I know there could be more convenient to run SQL tuning reports in tools like Enterprise Manager or TOAD. But in this post, I will… Read More »

When No Explicit Session is Responsible for Library Cache Lock

by Ed Chen
February 11, 2017March 28, 2018

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 »

How to Fix Broken Statspack Snapshot Jobs

by Ed Chen
December 30, 2016November 20, 2018

Saw a broken statspack snapshot job stopped working for several days in an Oracle 9i database. I tried to run the job on the spot… Read More »

How to Resolve ORA-27137: unable to allocate large pages to create a shared memory segment

by Ed Chen
December 16, 2016April 25, 2018
4 Comments

ORA-27137 Tried to startup a database to nomount, but failed. SQL> startup nomount; ORA-27137: unable to allocate large pages to create a shared memory segment… Read More »

How to Raise SGA_TARGET or MEMORY_TARGET

by Ed Chen
December 12, 2016April 16, 2018

Tunning ASSM or ASM target can never be done within memory scope, which is not allowed.SQL> alter system set sga_target=64g scope=both;alter system set sga_target=64g scope=both*ERROR… Read More »

How to Gather Statistics on Database Level for Various Situations

by Ed Chen
November 19, 2016October 2, 2019

DBMS_STATS.GATHER_DATABASE_STATS You can do it without any indications, it will run as its own will. SQL> exec dbms_stats.gather_database_stats(); PL/SQL procedure successfully completed. Probably, you might… Read More »