Oracle DBA (12c/19c) Articles and Scripts

 TIP#1 – Use only what you need It sounds very logical when you read it, but I’m sure we all had times when it was just easier to write SELECT s.* FROM sales s; then to list all fields we need from some Sales table. Don’t be like that. Unused columns that are needlessly fetched increase I/O on your database and add to network traffic. TIP#2 – Avoid the Cartesian product It’s obvious we’re still warming up, because this one is self-evident, but throughout many years in development, I the had urge to stress this one just on the safe side – we all get this. So here it comes one more time, avoid using the Cartesian product. Except when one of your two tables that you join consist of one row 😊. I know it sounds silly, but in everyday development you see everything. TIP#3 – Avoid modifying indexed columns with functions Do not modify indexed columns using Oracle built-in functions like TO_DATE, TRIM, LOWER etc. Using those functions on an indexed column will disable the optimizer from i...

  Primary Database : TIGGER  DBNAME : TIGGER  DB UNIQUE NAME : TIGGER  Instances : TIGGER on dgprm.racattack  Oracle Home: /u01/app/oracle/product/12.1.0/dbhome_1  GRID Homes:  /u01/app/oracle/product/12.1.0/grid Standby Database : TIGGER_STBY  DBNAME : TIGGER  DB UNIQUE NAME : TIGGER_STBY  Instances : TIGGER on dgstby.racattack  Oracle Home: /u01/app/oracle/product/12.1.0/dbhome_1  GRID Homes:  /u01/app/oracle/product/12.1.0/grid --************************************************* -- Information about Primary Database: --************************************************* set linesize 1000 column host_name format a15 select host_name, instance_name, INSTANCE_ROLE, archiver, version, status, database_status  from v$instance / HOST_NAME       INSTANCE_NAME    INSTANCE_ROLE      ARCHIVE VERSION           STATUS       DATABASE_STATUS ----------...

  Note:  Proof read any scripts before using. Always try scripts on a test instance first. This Blog is not responsible for any damage. Listed below are some SQL queries which are very useful for performance tuning. these are based on the ACTIVE SESSION HISTORY V$ VIEW to get a current perspective of performance and the DBA_HIST_* AWR HISTORY TABLES for obtaining performance data pertaining to a period of time in the past. -- TOP RECENT WAIT EVENTS SET LINESIZE 132 PAGESIZE 60 COL EVENT FORMAT A60 SELECT * FROM (    SELECT ACTIVE_SESSION_HISTORY.EVENT,           SUM(ACTIVE_SESSION_HISTORY.WAIT_TIME +               ACTIVE_SESSION_HISTORY.TIME_WAITED) TTL_WAIT_TIME    FROM V$ACTIVE_SESSION_HISTORY ACTIVE_SESSION_HISTORY    WHERE ACTIVE_SESSION_HISTORY.EVENT IS NOT NULL    GROUP BY ACTIVE_SESSION_HISTORY.EVENT    ORDER BY ...