Category Archives: Performance Tunning

PARALLEL_MIN_SERVERS Parameter Behaviours In 12.2.0.1

By Default, when 12.2.0.1 instance starts up, there are more than necessary parallel processes created. In this test case, there are 133 parallel processes created when instance starts up. SQL>select * from v$px_process_sysstat where statistic like ‘%Server%’ STATISTIC VALUE CON_ID … Continue reading

Posted in 12c new features, Performance Tunning | Tagged , , , , , | Leave a comment

High Swap Usage On Oracle Database Server

Situation: When investigating one client’s Oracle database server high swap memory usage, we find the swap space usage is constantly very high on this Linux server, which causes Oracle database a big performance issue. OS: RHEL 7.3 DB: Oracle 12.2.0.1 … Continue reading

Posted in Linux, Performance Tunning | Tagged , , , , , , , , , , , , , , | Leave a comment

“SELECT MEDIA FROM V$BACKUP_PIECE_DETAILS” Query Takes Long Time and Uses Huge Temp Space after Upgrading to OEM 13.2

The below query from OEM agent keeps failing, after upgrading Enterprise Manager Cloud Control and a monitoring agent to 13.2, this query is run in the 11.2.0.4 target database: SELECT MEDIA FROM V$BACKUP_PIECE_DETAILS WHERE SESSION_KEY=:B3 AND SESSION_RECID=:B2 AND SESSION_STAMP=:B1 AND … Continue reading

Posted in OEM, OEM13c, Performance Tunning, RMAN Restore and Recovery | Tagged , , , , , | 2 Comments

How To Flush an Object Out The Library Cache by Using DBMS_SHARED_POOL Package

We know how to flash all shared pool by : SQL>alter system flush shared_pool; Sometime, we only want to flush certain objects, like a cursor for testing purpose to want the cursor to have different execution plan. ¬†DBMS_SHARED_POOL can do … Continue reading

Posted in Performance Tunning, SQL Scripts For DBA | Tagged , , , , , | Leave a comment

ORA-12751: cpu time or run time policy violation

There is an overnight auto-purge job failure every day by throwing “ORA-12751: cpu time or run time policy violation” error. Extracted trace file from slave process ( m001 ) of ¬†Manageability Monitor Process ( MMON) : *** 2017-02-01 01:11:39.393 *** … Continue reading

Posted in AWR, Performance Tunning | Tagged , , , , , , , , | Leave a comment

CREATE INDEX COMPUTE STATISTICS

When creating an index, “COMPUTE STATISTICS” option is no longer required for 10g onwards databases. Here is a quick demonstration: Create a table and insert a couple of sample records: SQL> create table test ( id number, name varchar(20)); Table … Continue reading

Posted in Performance Tunning, SQL Scripts For DBA | Tagged , , , , , , | Leave a comment

Active Session History (ASH) Performed an Emergency Flush

We see alert log with below information : “Active Session History (ASH) performed an emergency flush. This may mean that ASH is undersized. If emergency flushes are a recurring issue, you may consider increasing ASH size by setting the value … Continue reading

Posted in Performance Tunning | Tagged , , | Leave a comment