Category Archives: Performance Tunning

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 , , , , , | Leave a comment

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