Performance Tuning

How to Resolve ORA-27492: unable to run job: scheduler unavailable

by Ed Chen
January 7, 2018October 30, 2021
8 Comments

ORA-27492 Found error ORA-27492 when executed a tuning job. BEGIN SYS.DBMS_SCHEDULER.RUN_JOB (job_name => 'SYS.SQLTUNE_JOB_61535_2043' ,use_current_session … 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 »

How to Resolve ORA-27137 Error Message

by Ed Chen
December 16, 2016October 14, 2021
4 Comments

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

How to Raise SGA_TARGET or MEMORY_TARGET

by Ed Chen
December 12, 2016October 14, 2021

Tuning ASSM or ASM target can never be done within SCOPE=MEMORY or even SCOPE=BOTH, which is not allowed. SQL> alter system set sga_target=64g scope=both; alter… Read More »

How to Gather Statistics on Database Level for Various Situations

by Ed Chen
November 19, 2016December 14, 2021

DBMS_STATS.GATHER_DATABASE_STATS We usually gather statistics of the database globally after data migration. The following examples are very practical in post-migration phase. Default You can do… Read More »

What is Variable Size SGA

by Ed Chen
October 28, 2016March 29, 2018

SQL> show sgaTotal System Global Area 6.1189E+10 bytesFixed Size 830456 bytesVariable Size … Read More »

How High Estimate Percentage of Statistics is Sufficient Enough

by Ed Chen
October 1, 2016November 4, 2022

There're could be several tables and indexes over 100 million rows in the database, you may not want to consume precious resource on analyzing them… Read More »

How Much Benefit will be Gained after Increasing Database Buffer Cache

by Ed Chen
August 20, 2016March 28, 2018

For avoiding reading same blocks over and over again, Oracle database puts them in memory called database buffer cache (i.e. DB_CACHE_SIZE) which is for retaining… Read More »

How to Accelerate Queries on Big Tables

by Ed Chen
February 28, 2016December 17, 2022

Tables that have rows more than 100M in numbers may have a performance degradation on queries, especially when the queries must join multiple tables, which… Read More »

When to Enable Real-Time Database Operation Monitoring (SQL Monitoring)

by Ed Chen
September 28, 2015March 28, 2018

Real-Time Database Operation Monitoring is actually a synonymous term of SQL Monitoring for Enterprise Manager (either Cloud Control or Grid Control). Therefore, its features and… Read More »