The scope of this article is to disable some features –mainly related to recoverability and logging, in order to speed up Test databases that are being used to testing application functionality or database features. What we are doing here is similar to what mechanics do when converting a regular car to a race car; they […]

This shell script will help you tune a SQL Statement in Oracle by doing the following functions: It will ask you for the SQL Statement sqlid, then it will do the following: 1- Display the SQL Statement Statistics like: Executions, ELAPSED/CPU time, Waits, I/O stats:   2- Display the SQL TEXT along with populating the […]

AWR report in 19c is packed up with many new sections. In this post I’ll highlight the new sections comparing with 11g’s AWR report. Starting with “Report Summary” sections: – Top ADDM Findings: – TOP 10 Foreground Wait Events: (Instead of TOP 5 in 11g) – Wait classes: (new table)   – IO Profile (new […]

Following the release of Oracle 19c –the long term supported member of the 12c family, many Oracle customers planned to migrate their older DBs especially the widely used version “11g” to the new version. Because of the restrictions on the direct upgrade from 11g to 19c, data pump method became the optimal method for this […]

Introduction: Oracle Resource Manager (available since 8i) is one of the underestimated Oracle features, because it’s not commonly used among DBAs. In this article I’ll try to explain it in a simple way to make you change your mind and start making use of it.   What is Resource Manager:   In short, it gives […]

Problem: I noticed “virtual circuit wait” wait event in the AWR report for one of the databases in the TOP 5 Wait Events: Analysis: Honestly I didn’t know what this event for, I googled it and landed on (Doc ID 1415999.1) which is discussing the same event on Shared Server architecture, but wait a second […]

I had a complaint from one of the readers that dbalarm script for monitoring the DB is taking very long time to run against one RAC DB [11.2.0.3] especially in the part of reporting locked sessions on the database, when I dug deeper found that most of time is getting consumed by this statement: select substr(s.INST_ID||’|’||s.OSUSER||’/’||s.USERNAME||’| ‘||s.sid||’,’||s.serial#||’ […]

I’ve introduced a new mode called “Paranoid” mode to the dbalarm script (which is used to monitor the Oracle database and listener logs along with monitoring locks, Space, and other events). When the paranoid  mode gets enabled, the script will monitor the following additional activities/events on the Oracle database: – EXPORT/IMPORT activities whenever they start. […]

In the previous post, I’ve shared a script reports long running sessions/queries. In this post I’ll explain another simple script that kills long running sessions connecting from specific programs e.g. SQL Developer The script can be tuned to target any specific programs, but I’m mainly defaulting the script to target SQL Developer, as it’s widely […]

In this post I’ll try to summarize all sorts of statistics in Oracle, I strongly recommend reading the full article, as it contains information you may find it valuable in understanding Oracle statistics. #####################################Database | Schema | Table | Index Statistics##################################### Gather Database Statistics:=======================SQL> EXEC DBMS_STATS.GATHER_DATABASE_STATS(     ESTIMATE_PERCENT=>100,METHOD_OPT=>’FOR ALL COLUMNS SIZE SKEWONLY’,     CASCADE => TRUE,    […]