Oracle NZ - Francisco Munoz Alvarez

Who never had a problem with a SQL that is killing your Database Performance and you can’t fix it because it’s running from an external closed application that you or your developers can’t touch?

Since Oracle version 10 this is a problem of the past, now you can easy solve this kind of problem using the DBMS_ADVANCED_REWRITE package, which allow you to transform/customize queries on the fly, changing for example one query with bad explain plan for another one with a good explain plan.

Before you become too excited, please remember the following restrictions: 

• It does not work with bind variables.(Alternative solution at Metalink Doc ID. 392214.1)
• Only works for the SELECT statement.
• Does not work when the base table is modified through DML.

To see how it works, first we will need to grant execute privileges on the package to our user called test and allow it to create materialized views.

CONN sys/password AS SYSDBA

GRANT EXECUTE ON DBMS_ADVANCED_REWRITE TO test;

GRANT CREATE MATERIALIZED VIEW TO test;

Now let’s create and populate our objects for test purpose:

CONN test/test

CREATE TABLE students (

  student_id               NUMBER(10),

  student_name             VARCHAR2(45),

  student_status           VARCHAR2(1),

  student_year             number(2),

  student_address          varchar2(45),

  student_city             varchar2(16),

  student_zip              number(6),

  student_social_security  number(10))

/

ALTER TABLE students

ADD CONSTRAINT pk_student PRIMARY KEY (student_id)

USING INDEX

PCTFREE 0;

BEGIN

  INSERT INTO STUDENTS VALUES (1,'PAUL COOK','Y',9,'5 Main Road','Auckland',2031,100101000);

  INSERT INTO STUDENTS VALUES (2,'HORACIO MIRANDA','Y',8,'15 Main Road','Auckland',2031,100101001);

  INSERT INTO STUDENTS VALUES (3,'SCOTT PEDERSEN','Y',7,'13 Main Road','Auckland',2031,100101002);

  INSERT INTO STUDENTS VALUES (5,'SETH PICKERING','Y',9,'12 Main Road','Auckland',2031,100101003);

  INSERT INTO STUDENTS VALUES (6,'FRANCISCO ALVAREZ','Y',11,'11 Main Road','Auckland',2031,100101004);

  INSERT INTO STUDENTS VALUES (7,'ALTMAAR VISSER','Y',9,'16 Main Road','Auckland',2031,100101005);

  INSERT INTO STUDENTS VALUES (9,'REYNALDO OCFEMIA','Y',6,'25 Main Road','Auckland',2031,100101006);

  INSERT INTO STUDENTS VALUES (15,'CAMERON PITCHES','Y',12,'31 Main Road','Auckland',2031,100101007);

  INSERT INTO STUDENTS VALUES (18,'MONIQUE GENNIP','Y',8,'71 Main Road','Auckland',2031,100101008);

  INSERT INTO STUDENTS VALUES (99,'TERRENCE LO','Y',6,'17 Main Road','Auckland',2031,100101009);

  INSERT INTO STUDENTS VALUES (100,'KIM FONG','Y',11,'16 Main Road','Auckland',2031,100101010);

  INSERT INTO STUDENTS VALUES (103,'CHRIS OPPERMAN','Y',12,'7 Main Road','Auckland',2031,100101011);

  INSERT INTO STUDENTS VALUES (104,'SCOTT TIGER','Y',6,'62 Main Road','Auckland',2031,100101012);

  INSERT INTO STUDENTS VALUES (105,'EVELYN AGHEMIO','Y',11,'32 Main Road','Auckland',2031,100101013);

  INSERT INTO STUDENTS VALUES (106,'TOMAS MUNOZ','Y',11,'18 Main Road','Auckland',2031,100101014);

  INSERT INTO STUDENTS VALUES (107,'GONZALO TORRES','Y',10,'14 Principal Road','Auckland',2031,100101015);

  INSERT INTO STUDENTS VALUES (108,'JOHN KEY','Y',10,'12 Principal Road','Auckland',2031,100101016);

  INSERT INTO STUDENTS VALUES (109,'JOHN A','Y',7,'21 Principal Road','Auckland',2031,100101017);

  INSERT INTO STUDENTS VALUES (111,'JOHN B','Y',9,'121 Principal Road','Auckland',2031,100101018);

  INSERT INTO STUDENTS VALUES (112,'JOHN C','Y',8,'321 Principal Road','Auckland',2031,100101019);

  INSERT INTO STUDENTS VALUES (113,'JOHN D','Y',6,'35 Principal Road','Auckland',2031,100101020);

  INSERT INTO STUDENTS VALUES (114,'JOHN E','Y',12,'41 Principal Road','Auckland',2031,100101021);

  INSERT INTO STUDENTS VALUES (116,'JOHN F','Y',8,'161 Principal Road','Auckland',2031,100101022);

  INSERT INTO STUDENTS VALUES (10,'JOHN G','Y',7,'171 Principal Road','Auckland',2031,100101023);

  INSERT INTO STUDENTS VALUES (311,'JOHN H','Y',11,'353 Principal Road','Auckland',2031,100101024);

  INSERT INTO STUDENTS VALUES (312,'JOHN I','Y',7,'351 Principal Road','Auckland',2031,100101025);

  INSERT INTO STUDENTS VALUES (319,'JOHN K','Y',9,'352 Principal Road','Auckland',2031,100101026);

  INSERT INTO STUDENTS VALUES (322,'JOHN L','Y',6,'353 Principal Road','Auckland',2031,100101027);

  INSERT INTO STUDENTS VALUES (333,'JOHN M','Y',11,'354 Principal Road','Auckland',2031,100101028);

  INSERT INTO STUDENTS VALUES (343,'JOHN N','Y',6,'355 Principal Road','Auckland',2031,100101029);

  INSERT INTO STUDENTS VALUES (344,'JOHN O','Y',7,'356 Principal Road','Auckland',2031,100101030);

  INSERT INTO STUDENTS VALUES (345,'JOHN P','Y',8,'357 Principal Road','Auckland',2031,100101031);

  INSERT INTO STUDENTS VALUES (346,'JOHN Q','Y',9,'358 Principal Road','Auckland',2031,100101032);

  INSERT INTO STUDENTS VALUES (347,'JOHN R','Y',10,'359 Principal Road','Auckland',2031,100101033);

  INSERT INTO STUDENTS VALUES (350,'JOHN S','Y',11,'360 Principal Road','Auckland',2031,100101034);

  INSERT INTO STUDENTS VALUES (530,'JOHN T','Y',12,'361 Principal Road','Auckland',2031,100101035);

  INSERT INTO STUDENTS VALUES (531,'JOHN U','Y',13,'362 Principal Road','Auckland',2031,100101036);

  INSERT INTO STUDENTS VALUES (533,'JOHN V','N',6,'35 Principal Road','Auckland',2031,100101037);

  INSERT INTO STUDENTS VALUES (534,'JOHN X','N',8,'13 Principal Road','Auckland',2031,100101038);

  INSERT INTO STUDENTS VALUES (535,'JOHN Z','N',7,'135 Principal Road','Auckland',2031,100101039);

  INSERT INTO STUDENTS VALUES (536,'JOHN Y','N',11,'435 Principal Road','Auckland',2031,100101040);

  INSERT INTO STUDENTS VALUES (537,'JOHN W','Y',8,'635 Principal Road','Auckland',2031,100101041);

  INSERT INTO STUDENTS VALUES (539,'ARTUR JOHNES','Y',6,'22 Secondary Road','Auckland',2031,100101042);

  INSERT INTO STUDENTS VALUES (540,'KING PANTHER','Y',7,'22 Secondary Road','Auckland',2031,100101043);

  INSERT INTO STUDENTS VALUES (541,'PINK PANTHER','Y',8,'22 Secondary Road','Auckland',2031,100101044);

  INSERT INTO STUDENTS VALUES (542,'HAROLD ROBINS','Y',9,'221 Secondary Road','Auckland',2031,100101045);

  INSERT INTO STUDENTS VALUES (543,'CHRIS BONES','Y',8,'222 Secondary Road','Auckland',2031,100101046);

  INSERT INTO STUDENTS VALUES (545,'TIM TOM','Y',9,'223 Secondary Road','Auckland',2031,100101047);

  INSERT INTO STUDENTS VALUES (546,'TIM JONES','Y',10,'223 Secondary Road','Auckland',2031,100101048);

  INSERT INTO STUDENTS VALUES (547,'MICHAEL JONES','Y',11,'224 Secondary Road','Auckland',2031,100101049);

  INSERT INTO STUDENTS VALUES (548,'ANN SMITH','Y',12,'225 Secondary Road','Auckland',2031,100101050);

  INSERT INTO STUDENTS VALUES (549,'JOHN SMITH','Y',13,'226 Secondary Road','Auckland',2031,100101051);

  INSERT INTO STUDENTS VALUES (551,'PAUL STONE','Y',6,'227 Secondary Road','Auckland',2031,100101052);

  INSERT INTO STUDENTS VALUES (552,'CARL SMITH','Y',7,'228 Secondary Road','Auckland',2031,100101053);

  INSERT INTO STUDENTS VALUES (553,'TEST','Y',8,'229 Secondary Road','Auckland',2031,100101054);

  COMMIT;

END;

/

CREATE TABLE grades (

  student_id       NUMBER(10),

  grade   NUMBER(6,2),

  grade_subject    VARCHAR2(4),

  grade_date       DATE,

  grade_note      VARCHAR2(60))

/

ALTER TABLE grades

ADD CONSTRAINT pk_GRADES PRIMARY KEY (student_id, grade, grade_subject,grade_date)

USING INDEX

PCTFREE 0;

ALTER TABLE grades

ADD CONSTRAINT fk_students

FOREIGN KEY (student_id)

REFERENCES students(student_id);

BEGIN

  INSERT INTO GRADES VALUES (553,100.0,'ENGL',sysdate,null);

  INSERT INTO GRADES VALUES (552,95.0,'ENGL',sysdate,null);

  INSERT INTO GRADES VALUES (551,87.0,'ENGL',sysdate,null);

  INSERT INTO GRADES VALUES (549,87.5,'ENGL',sysdate,null);

  INSERT INTO GRADES VALUES (548,90.0,'ENGL',sysdate,null);

  INSERT INTO GRADES VALUES (547,64.7,'ENGL',sysdate,null);

  INSERT INTO GRADES VALUES (546,85.0,'ENGL',sysdate,null);

  INSERT INTO GRADES VALUES (545,88.0,'ENGL',sysdate,null);

  INSERT INTO GRADES VALUES (543,98.0,'ENGL',sysdate,null);

  INSERT INTO GRADES VALUES (542,95.0,'ENGL',sysdate,null);

  INSERT INTO GRADES VALUES (541,94.0,'ENGL',sysdate,null);

  INSERT INTO GRADES VALUES (540,94.0,'ENGL',sysdate,null);

  INSERT INTO GRADES VALUES (539,95.0,'ENGL',sysdate,null);

  INSERT INTO GRADES VALUES (1,88.0,'ENGL',sysdate,null);

  INSERT INTO GRADES VALUES (2,98.0,'ENGL',sysdate,null);

  INSERT INTO GRADES VALUES (3,98.7,'ENGL',sysdate,null);

  INSERT INTO GRADES VALUES (5,96.0,'ENGL',sysdate,null);

  INSERT INTO GRADES VALUES (6,97.0,'ENGL',sysdate,null);

  INSERT INTO GRADES VALUES (7,90.0,'ENGL',sysdate,null);

  INSERT INTO GRADES VALUES (9,91.0,'ENGL',sysdate,null);

  INSERT INTO GRADES VALUES (15,92.0,'ENGL',sysdate,null);

  INSERT INTO GRADES VALUES (18,93.0,'ENGL',sysdate,null);

  INSERT INTO GRADES VALUES (99,94.0,'ENGL',sysdate,null);

  INSERT INTO GRADES VALUES (100,95.0,'ENGL',sysdate,null);

  INSERT INTO GRADES VALUES (533,98.0,'ENGL',sysdate,null);

  INSERT INTO GRADES VALUES (534,100.0,'ENGL',sysdate,null);

  INSERT INTO GRADES VALUES (535,100.0,'ENGL',sysdate,null);

  INSERT INTO GRADES VALUES (536,99.0,'ENGL',sysdate,null);

  INSERT INTO GRADES VALUES (537,88.0,'ENGL',sysdate,null);

  INSERT INTO GRADES VALUES (530,88.0,'ENGL',sysdate,null);

  INSERT INTO GRADES VALUES (531,88.0,'ENGL',sysdate,null);

  INSERT INTO GRADES VALUES (103,67.0,'ENGL',sysdate,null);

  INSERT INTO GRADES VALUES (104,56.0,'ENGL',sysdate,null);

  INSERT INTO GRADES VALUES (105,93.0,'ENGL',sysdate,null);

  INSERT INTO GRADES VALUES (106,88.0,'ENGL',sysdate,null);

  INSERT INTO GRADES VALUES (107,72.0,'ENGL',sysdate,null);

  INSERT INTO GRADES VALUES (108,71.0,'ENGL',sysdate,null);

  INSERT INTO GRADES VALUES (109,68.0,'ENGL',sysdate,null);

  INSERT INTO GRADES VALUES (111,77.0,'ENGL',sysdate,null);

  INSERT INTO GRADES VALUES (112,87.0,'ENGL',sysdate,null);

  INSERT INTO GRADES VALUES (113,65.5,'ENGL',sysdate,null);

  INSERT INTO GRADES VALUES (114,34.0,'ENGL',sysdate,null);

  INSERT INTO GRADES VALUES (116,91.0,'ENGL',sysdate,null);

  INSERT INTO GRADES VALUES (10,98.0,'ENGL',sysdate,null);

  INSERT INTO GRADES VALUES (311,78.0,'ENGL',sysdate,null);

  INSERT INTO GRADES VALUES (312,88.0,'ENGL',sysdate,null);

  INSERT INTO GRADES VALUES (319,67.0,'ENGL',sysdate,null);

  INSERT INTO GRADES VALUES (322,89.0,'ENGL',sysdate,null);

  INSERT INTO GRADES VALUES (333,95.0,'ENGL',sysdate,null);

  INSERT INTO GRADES VALUES (343,91.0,'ENGL',sysdate,null);

  INSERT INTO GRADES VALUES (344,98.0,'ENGL',sysdate,null);

  INSERT INTO GRADES VALUES (345,87.0,'ENGL',sysdate,null);

  INSERT INTO GRADES VALUES (346,93.0,'ENGL',sysdate,null);

  INSERT INTO GRADES VALUES (347,99.0,'ENGL',sysdate,null);

  COMMIT;

  INSERT INTO GRADES VALUES (553,100.0,'MATH',sysdate-7,null);

  INSERT INTO GRADES VALUES (552,95.0,'MATH',sysdate-7,null);

  INSERT INTO GRADES VALUES (551,87.0,'MATH',sysdate-7,null);

  INSERT INTO GRADES VALUES (549,87.5,'MATH',sysdate-7,null);

  INSERT INTO GRADES VALUES (548,90.0,'MATH',sysdate-7,null);

  INSERT INTO GRADES VALUES (547,64.7,'MATH',sysdate-7,null);

  INSERT INTO GRADES VALUES (546,85.0,'MATH',sysdate-7,null);

  INSERT INTO GRADES VALUES (545,88.0,'MATH',sysdate-7,null);

  INSERT INTO GRADES VALUES (543,98.0,'MATH',sysdate-7,null);

  INSERT INTO GRADES VALUES (542,95.0,'MATH',sysdate-7,null);

  INSERT INTO GRADES VALUES (541,94.0,'MATH',sysdate-7,null);

  INSERT INTO GRADES VALUES (540,94.0,'MATH',sysdate-7,null);

  INSERT INTO GRADES VALUES (539,95.0,'MATH',sysdate-7,null);

  INSERT INTO GRADES VALUES (1,88.0,'MATH',sysdate-7,null);

  INSERT INTO GRADES VALUES (2,98.0,'MATH',sysdate-7,null);

  INSERT INTO GRADES VALUES (3,98.7,'MATH',sysdate-7,null);

  INSERT INTO GRADES VALUES (5,96.0,'MATH',sysdate-7,null);

  INSERT INTO GRADES VALUES (6,97.0,'MATH',sysdate-7,null);

  INSERT INTO GRADES VALUES (7,90.0,'MATH',sysdate-7,null);

  INSERT INTO GRADES VALUES (9,91.0,'MATH',sysdate-7,null);

  INSERT INTO GRADES VALUES (15,92.0,'MATH',sysdate-7,null);

  INSERT INTO GRADES VALUES (18,93.0,'MATH',sysdate-7,null);

  INSERT INTO GRADES VALUES (99,94.0,'MATH',sysdate-7,null);

  INSERT INTO GRADES VALUES (100,95.0,'MATH',sysdate-7,null);

  INSERT INTO GRADES VALUES (533,98.0,'MATH',sysdate-7,null);

  INSERT INTO GRADES VALUES (534,100.0,'MATH',sysdate-7,null);

  INSERT INTO GRADES VALUES (535,100.0,'MATH',sysdate-7,null);

  INSERT INTO GRADES VALUES (536,99.0,'MATH',sysdate-7,null);

  INSERT INTO GRADES VALUES (537,88.0,'MATH',sysdate-7,null);

  INSERT INTO GRADES VALUES (530,88.0,'MATH',sysdate-7,null);

  INSERT INTO GRADES VALUES (531,88.0,'MATH',sysdate-7,null);

  INSERT INTO GRADES VALUES (103,67.0,'MATH',sysdate-7,null);

  INSERT INTO GRADES VALUES (104,56.0,'MATH',sysdate-7,null);

  INSERT INTO GRADES VALUES (105,93.0,'MATH',sysdate-7,null);

  INSERT INTO GRADES VALUES (106,88.0,'MATH',sysdate-7,null);

  INSERT INTO GRADES VALUES (107,72.0,'MATH',sysdate-7,null);

  INSERT INTO GRADES VALUES (108,71.0,'MATH',sysdate-7,null);

  INSERT INTO GRADES VALUES (109,68.0,'MATH',sysdate-7,null);

  INSERT INTO GRADES VALUES (111,77.0,'MATH',sysdate-7,null);

  INSERT INTO GRADES VALUES (112,87.0,'MATH',sysdate-7,null);

  INSERT INTO GRADES VALUES (113,65.5,'MATH',sysdate-7,null);

  INSERT INTO GRADES VALUES (114,34.0,'MATH',sysdate-7,null);

  INSERT INTO GRADES VALUES (116,91.0,'MATH',sysdate-7,null);

  INSERT INTO GRADES VALUES (10,98.0,'MATH',sysdate-7,null);

  INSERT INTO GRADES VALUES (311,78.0,'MATH',sysdate-7,null);

  INSERT INTO GRADES VALUES (312,88.0,'MATH',sysdate-7,null);

  INSERT INTO GRADES VALUES (319,67.0,'MATH',sysdate-7,null);

  INSERT INTO GRADES VALUES (322,89.0,'MATH',sysdate-7,null);

  INSERT INTO GRADES VALUES (333,95.0,'MATH',sysdate-7,null);

  INSERT INTO GRADES VALUES (343,91.0,'MATH',sysdate-7,null);

  INSERT INTO GRADES VALUES (344,98.0,'MATH',sysdate-7,null);

  INSERT INTO GRADES VALUES (345,87.0,'MATH',sysdate-7,null);

  INSERT INTO GRADES VALUES (346,93.0,'MATH',sysdate-7,null);

  INSERT INTO GRADES VALUES (347,99.0,'MATH',sysdate-7,null);

  COMMIT;

  INSERT INTO GRADES VALUES (553,100.0,'BIOL',sysdate,null);

  INSERT INTO GRADES VALUES (552,95.0,'BIOL',sysdate,null);

  INSERT INTO GRADES VALUES (551,87.0,'BIOL',sysdate,null);

  INSERT INTO GRADES VALUES (549,87.5,'BIOL',sysdate,null);

  INSERT INTO GRADES VALUES (548,90.0,'BIOL',sysdate,null);

  INSERT INTO GRADES VALUES (547,64.7,'BIOL',sysdate,null);

  INSERT INTO GRADES VALUES (546,85.0,'BIOL',sysdate,null);

  INSERT INTO GRADES VALUES (545,88.0,'BIOL',sysdate,null);

  INSERT INTO GRADES VALUES (543,98.0,'BIOL',sysdate,null);

  INSERT INTO GRADES VALUES (542,95.0,'BIOL',sysdate,null);

  INSERT INTO GRADES VALUES (541,94.0,'BIOL',sysdate,null);

  INSERT INTO GRADES VALUES (540,94.0,'BIOL',sysdate,null);

  INSERT INTO GRADES VALUES (539,95.0,'BIOL',sysdate,null);

  INSERT INTO GRADES VALUES (1,88.0,'BIOL',sysdate,null);

  INSERT INTO GRADES VALUES (2,98.0,'BIOL',sysdate,null);

  INSERT INTO GRADES VALUES (3,98.7,'BIOL',sysdate,null);

  INSERT INTO GRADES VALUES (5,96.0,'BIOL',sysdate,null);

  INSERT INTO GRADES VALUES (6,97.0,'BIOL',sysdate,null);

  INSERT INTO GRADES VALUES (7,90.0,'BIOL',sysdate,null);

  INSERT INTO GRADES VALUES (9,91.0,'BIOL',sysdate,null);

  INSERT INTO GRADES VALUES (15,92.0,'BIOL',sysdate,null);

  INSERT INTO GRADES VALUES (18,93.0,'BIOL',sysdate,null);

  INSERT INTO GRADES VALUES (99,94.0,'BIOL',sysdate,null);

  INSERT INTO GRADES VALUES (100,95.0,'BIOL',sysdate,null);

  INSERT INTO GRADES VALUES (533,98.0,'BIOL',sysdate,null);

  INSERT INTO GRADES VALUES (534,100.0,'BIOL',sysdate,null);

  INSERT INTO GRADES VALUES (535,100.0,'BIOL',sysdate,null);

  INSERT INTO GRADES VALUES (536,99.0,'BIOL',sysdate,null);

  INSERT INTO GRADES VALUES (537,88.0,'BIOL',sysdate,null);

  INSERT INTO GRADES VALUES (530,88.0,'BIOL',sysdate,null);

  INSERT INTO GRADES VALUES (531,88.0,'BIOL',sysdate,null);

  INSERT INTO GRADES VALUES (103,67.0,'BIOL',sysdate,null);

  INSERT INTO GRADES VALUES (104,56.0,'BIOL',sysdate,null);

  INSERT INTO GRADES VALUES (105,93.0,'BIOL',sysdate,null);

  INSERT INTO GRADES VALUES (106,88.0,'BIOL',sysdate,null);

  INSERT INTO GRADES VALUES (107,72.0,'BIOL',sysdate,null);

  INSERT INTO GRADES VALUES (108,71.0,'BIOL',sysdate,null);

  INSERT INTO GRADES VALUES (109,68.0,'BIOL',sysdate,null);

  INSERT INTO GRADES VALUES (111,77.0,'BIOL',sysdate,null);

  INSERT INTO GRADES VALUES (112,87.0,'BIOL',sysdate,null);

  INSERT INTO GRADES VALUES (113,65.5,'BIOL',sysdate,null);

  INSERT INTO GRADES VALUES (114,34.0,'BIOL',sysdate,null);

  INSERT INTO GRADES VALUES (116,91.0,'BIOL',sysdate,null);

  INSERT INTO GRADES VALUES (10,98.0,'BIOL',sysdate,null);

  INSERT INTO GRADES VALUES (311,78.0,'BIOL',sysdate,null);

  INSERT INTO GRADES VALUES (312,88.0,'BIOL',sysdate,null);

  INSERT INTO GRADES VALUES (319,67.0,'BIOL',sysdate,null);

  INSERT INTO GRADES VALUES (322,89.0,'BIOL',sysdate,null);

  INSERT INTO GRADES VALUES (333,95.0,'BIOL',sysdate,null);

  INSERT INTO GRADES VALUES (343,91.0,'BIOL',sysdate,null);

  INSERT INTO GRADES VALUES (344,98.0,'BIOL',sysdate,null);

  INSERT INTO GRADES VALUES (345,87.0,'BIOL',sysdate,null);

  INSERT INTO GRADES VALUES (346,93.0,'BIOL',sysdate,null);

  INSERT INTO GRADES VALUES (347,99.0,'BIOL',sysdate,null);

  COMMIT;

  INSERT INTO GRADES VALUES (553,100.0,'ARTS',sysdate,null);

  INSERT INTO GRADES VALUES (552,95.0,'ARTS',sysdate,null);

  INSERT INTO GRADES VALUES (551,87.0,'ARTS',sysdate,null);

  INSERT INTO GRADES VALUES (549,87.5,'ARTS',sysdate,null);

  INSERT INTO GRADES VALUES (548,90.0,'ARTS',sysdate,null);

  INSERT INTO GRADES VALUES (547,64.7,'ARTS',sysdate,null);

  INSERT INTO GRADES VALUES (546,85.0,'ARTS',sysdate,null);

  INSERT INTO GRADES VALUES (545,88.0,'ARTS',sysdate,null);

  INSERT INTO GRADES VALUES (543,98.0,'ARTS',sysdate,null);

  INSERT INTO GRADES VALUES (542,95.0,'ARTS',sysdate,null);

  INSERT INTO GRADES VALUES (541,94.0,'ARTS',sysdate,null);

  INSERT INTO GRADES VALUES (540,94.0,'ARTS',sysdate,null);

  INSERT INTO GRADES VALUES (539,95.0,'ARTS',sysdate,null);

  INSERT INTO GRADES VALUES (1,88.0,'ARTS',sysdate,null);

  INSERT INTO GRADES VALUES (2,98.0,'ARTS',sysdate,null);

  INSERT INTO GRADES VALUES (3,98.7,'ARTS',sysdate,null);

  INSERT INTO GRADES VALUES (5,96.0,'ARTS',sysdate,null);

  INSERT INTO GRADES VALUES (6,97.0,'ARTS',sysdate,null);

  INSERT INTO GRADES VALUES (7,90.0,'ARTS',sysdate,null);

  INSERT INTO GRADES VALUES (9,91.0,'ARTS',sysdate,null);

  INSERT INTO GRADES VALUES (15,92.0,'ARTS',sysdate,null);

  INSERT INTO GRADES VALUES (18,93.0,'ARTS',sysdate,null);

  INSERT INTO GRADES VALUES (99,94.0,'ARTS',sysdate,null);

  INSERT INTO GRADES VALUES (100,95.0,'ARTS',sysdate,null);

  INSERT INTO GRADES VALUES (533,98.0,'ARTS',sysdate,null);

  INSERT INTO GRADES VALUES (534,100.0,'ARTS',sysdate,null);

  INSERT INTO GRADES VALUES (535,100.0,'ARTS',sysdate,null);

  INSERT INTO GRADES VALUES (536,99.0,'ARTS',sysdate,null);

  INSERT INTO GRADES VALUES (537,88.0,'ARTS',sysdate,null);

  INSERT INTO GRADES VALUES (530,88.0,'ARTS',sysdate,null);

  INSERT INTO GRADES VALUES (531,88.0,'ARTS',sysdate,null);

  INSERT INTO GRADES VALUES (103,67.0,'ARTS',sysdate,null);

  INSERT INTO GRADES VALUES (104,56.0,'ARTS',sysdate,null);

  INSERT INTO GRADES VALUES (105,93.0,'ARTS',sysdate,null);

  INSERT INTO GRADES VALUES (106,88.0,'ARTS',sysdate,null);

  INSERT INTO GRADES VALUES (107,72.0,'ARTS',sysdate,null);

  INSERT INTO GRADES VALUES (108,71.0,'ARTS',sysdate,null);

  INSERT INTO GRADES VALUES (109,68.0,'ARTS',sysdate,null);

  INSERT INTO GRADES VALUES (111,77.0,'ARTS',sysdate,null);

  INSERT INTO GRADES VALUES (112,87.0,'ARTS',sysdate,null);

  INSERT INTO GRADES VALUES (113,65.5,'ARTS',sysdate,null);

  INSERT INTO GRADES VALUES (114,34.0,'ARTS',sysdate,null);

  INSERT INTO GRADES VALUES (116,91.0,'ARTS',sysdate,null);

  INSERT INTO GRADES VALUES (10,98.0,'ARTS',sysdate,null);

  INSERT INTO GRADES VALUES (311,78.0,'ARTS',sysdate,null);

  INSERT INTO GRADES VALUES (312,88.0,'ARTS',sysdate,null);

  INSERT INTO GRADES VALUES (319,67.0,'ARTS',sysdate,null);

  INSERT INTO GRADES VALUES (322,89.0,'ARTS',sysdate,null);

  INSERT INTO GRADES VALUES (333,95.0,'ARTS',sysdate,null);

  INSERT INTO GRADES VALUES (343,91.0,'ARTS',sysdate,null);

  INSERT INTO GRADES VALUES (344,98.0,'ARTS',sysdate,null);

  INSERT INTO GRADES VALUES (345,87.0,'ARTS',sysdate,null);

  INSERT INTO GRADES VALUES (346,93.0,'ARTS',sysdate,null);

  INSERT INTO GRADES VALUES (347,99.0,'ARTS',sysdate,null);

  COMMIT;

END;

/

Now let simulate that you found the SQL bellow running in your Database:

SQL> Explain plan for

select student_name,avg(a.grade) from grades a, students b

where b.student_social_security = 100101016

and   b.student_id = a.student_id

group by student_name

/

STUDENT_NAME                                  AVG(A.GRADE)

--------------------------------------------- ------------

JOHN KEY                                                71

SQL> SELECT * FROM TABLE(dbms_xplan.display);

Plan hash value: 3187331965

---------------------------------------------------------------------------------

| Id  | Operation           | Name      | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------------

|   0 | SELECT STATEMENT    |           |     4 |   304 |     5  (20)| 00:00:01 |

|   1 |  HASH GROUP BY      |           |     4 |   304 |     5  (20)| 00:00:01 |

|   2 |   NESTED LOOPS      |           |     4 |   304 |     4   (0)| 00:00:01 |

|*  3 |    TABLE ACCESS FULL| STUDENTS  |     1 |    50 |     3   (0)| 00:00:01 |

|*  4 |    INDEX RANGE SCAN | PK_GRADES |     4 |   104 |     1   (0)| 00:00:01 |

---------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   3 - filter("B“.”STUDENT_SOCIAL_SECURITY“=100101016)

   4 - access("B“.”STUDENT_ID“=”A“.”STUDENT_ID“)

Note

-----

   - dynamic sampling used for this statement

You can see that the SQL above is doing a full scan to the students table, after a few modifications we have another SQL, a little more efficient, and it will be:

SQL> Explain plan for

select student_name,avg(a.grade) from grades a, students b

where b.student_id = 108

and   b.student_id = a.student_id

group by student_name

/

STUDENT_NAME                                  AVG(A.GRADE)

--------------------------------------------- ------------

JOHN KEY                                                71

Execution Plan

----------------------------------------------------------

Plan hash value: 3300694555

--------------------------------------------------------------------------------------------

| Id  | Operation                     | Name       | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT              |            |     4 |   252 |     2   (0)| 00:00:01 |

|   1 |  HASH GROUP BY                |            |     4 |   252 |     2   (0)| 00:00:01 |

|   2 |   NESTED LOOPS                |            |     4 |   252 |     2   (0)| 00:00:01 |

|   3 |    TABLE ACCESS BY INDEX ROWID| STUDENTS   |     1 |    37 |     1   (0)| 00:00:01 |

|*  4 |     INDEX UNIQUE SCAN         | PK_STUDENT |     1 |       |     1   (0)| 00:00:01 |

|*  5 |    INDEX RANGE SCAN           | PK_GRADES  |     4 |   104 |     1   (0)| 00:00:01 |

--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   4 - access("B“.”STUDENT_ID“=108)

   5 - access("A“.”STUDENT_ID“=108)

Note

-----

   - dynamic sampling used for this statement

You can see that the second SQL is more efficient that the first one, and for that reason we will order Oracle to replace the bad SQL for the good one every time the bad SQL is executed, how we can do it? Easily, the magic will be:

SQL> ALTER SESSION SET QUERY_REWRITE_INTEGRITY = TRUSTED;

SQL> BEGIN

       sys.dbms_advanced_rewrite.declare_rewrite_equivalence (

       name              => 'test_rw1',

       source_stmt =>

       'select student_name,avg(a.grade)

       from grades a, students b

       where b.student_social_security = 100101016

       and b.student_id = a.student_id

       group by student_name',

       destination_stmt =>

       'select student_name,avg(a.grade)

       from grades a, students b

       where b.student_id = 108

       and   b.student_id = a.student_id

       group by student_name',

       validate  => false,

       rewrite_mode      => 'text_match');

     END;

     /

Let’s now see if the magic really works:

SQL> Explain plan for

select student_name,avg(a.grade) from grades a, students b

where b.student_social_security = 100101016

and   b.student_id = a.student_id

group by student_name

/

STUDENT_NAME                                  AVG(A.GRADE)

--------------------------------------------- ------------

JOHN KEY                                                71

SQL> SELECT * FROM TABLE(dbms_xplan.display);

Execution Plan

----------------------------------------------------------

Plan hash value: 3300694555

--------------------------------------------------------------------------------------------

| Id  | Operation                     | Name       | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT              |            |     4 |   252 |     2   (0)| 00:00:01 |

|   1 |  HASH GROUP BY                |            |     4 |   252 |     2   (0)| 00:00:01 |

|   2 |   NESTED LOOPS                |            |     4 |   252 |     2   (0)| 00:00:01 |

|   3 |    TABLE ACCESS BY INDEX ROWID| STUDENTS   |     1 |    37 |     1   (0)| 00:00:01 |

|*  4 |     INDEX UNIQUE SCAN         | PK_STUDENT |     1 |       |     1   (0)| 00:00:01 |

|*  5 |    INDEX RANGE SCAN           | PK_GRADES  |     4 |   104 |     1   (0)| 00:00:01 |

--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   4 - access("B“.”STUDENT_ID“=108)

   5 - access("A“.”STUDENT_ID“=108)

Note

-----

   - dynamic sampling used for this statement

The magic is done, now every time the source_stmt is execute it will be replaced by the destination_stmt with a better execution plan

Enjoy this trick!

Kind Regards,

Francisco Munoz Alvarez

By Francisco Munoz Alvarez

How to find Sessions Generating Lots of Redo

To find sessions generating lots of redo, you can use either of the following methods. Both methods examine the amount of undo generated. When a transaction generates undo, it will automatically generate redo as well.

The methods are:

1) Query V$SESS_IO. This view contains the column BLOCK_CHANGES which indicates how much blocks have been changed by the session. High values indicate a session generating lots of redo.

The query you can use is:

SQL> SELECT s.sid, s.serial#, s.username, s.program,

2 i.block_changes

3 FROM v$session s, v$sess_io i

4 WHERE s.sid = i.sid

5 ORDER BY 5 desc, 1, 2, 3, 4;

Run the query multiple times and examine the delta between each occurrence of BLOCK_CHANGES. Large deltas indicate high redo generation by the session.

2) Query V$TRANSACTION. These view contains information about the amount of undo blocks and undo records accessed by the transaction (as found in the USED_UBLK and USED_UREC columns).

The query you can use is:

SQL> SELECT s.sid, s.serial#, s.username, s.program,

2 t.used_ublk, t.used_urec

3 FROM v$session s, v$transaction t

4 WHERE s.taddr = t.addr

5 ORDER BY 5 desc, 6 desc, 1, 2, 3, 4;

Run the query multiple times and examine the delta between each occurrence of USED_UBLK and USED_UREC. Large deltas indicate high redo generation by the session.

You use the first query when you need to check for programs generating lots of redo when these programs activate more than one transaction. The latter query can be used to find out which particular transactions are generating redo.

Useful Scripts

To see the redo generated since instance started:

col name format a30 heading ‘Statistic|Name’

col value heading ‘Statistic|Value’

start title80 “Redo Log Statistics”

spool rep_out\&db\red_stat

SELECT name, value

FROM v$sysstat

WHERE name like ‘%redo%’

order by name

/

spool off

pause Press enter to continue

ttitle off

The redo generated during my session since the session started:

select value redo_size

from v$mystat, v$statname

where v$mystat.STATISTIC# = v$statname.STATISTIC#

and name = ‘redo size’

/

The redo generated by current user sessions:

select v$session.sid, username, value redo_size

from v$sesstat, v$statname, v$session

where v$sesstat.STATISTIC# = v$statname.STATISTIC#

and v$session.sid = v$sesstat.sid

and name = ‘redo size’

and value > 0

and username is not null

order by value

/

Provide a current status for redo logs:

column first_change# format 999,999,999 heading Change#

column group# format 9,999 heading Grp#

column thread# format 999 heading Th#

column sequence# format 999,999 heading Seq#

column members format 999 heading Mem

column archived format a4 heading Arc?

column first_time format a25 heading First|Time

break on thread#

set pages 60 lines 132 feedback off

start title132 ‘Current Redo Log Status’

spool rep_out\&db\log_stat

select thread#, group#, sequence#,

bytes, members,archived,status,first_change#,

to_char(first_time,’dd-mon-yyyy hh24:mi’) first_time

from sys.v_$log

order by thread#, group#;

spool off

pause Press Enter to continue

set pages 22 lines 80 feedback on

clear breaks

clear columns

ttitle off

/

Provide redo log groups and log switch (archive generation) information:

set echo on

set linesize 150

set pagesize 500

column day format a16 heading ‘Dia’

column d_0 format a3 heading ‘00′

column d_1 format a3 heading ‘01′

column d_2 format a3 heading ‘02′

column d_3 format a3 heading ‘03′

column d_4 format a3 heading ‘04′

column d_5 format a3 heading ‘05′

column d_6 format a3 heading ‘06′

column d_7 format a3 heading ‘07′

column d_8 format a3 heading ‘08′

column d_9 format a3 heading ‘09′

column d_10 format a3 heading ‘10′

column d_11 format a3 heading ‘11′

column d_12 format a3 heading ‘12′

column d_13 format a3 heading ‘13′

column d_14 format a3 heading ‘14′

column d_15 format a3 heading ‘15′

column d_16 format a3 heading ‘16′

column d_17 format a3 heading ‘17′

column d_18 format a3 heading ‘18′

column d_19 format a3 heading ‘19′

column d_20 format a3 heading ‘20′

column d_21 format a3 heading ‘21′

column d_22 format a3 heading ‘22′

column d_23 format a3 heading ‘23′

column Total format 9999

column status format a8

column member format a40

column archived heading ‘Archived’ format a8

column bytes heading ‘Bytes|(MB)’ format 9999

Ttitle ‘Log Info’ skip 2

select l.group#,f.member,l.archived,l.bytes/1078576 bytes,l.status,f.type

from v$log l, v$logfile f

where l.group# = f.group#

/

Ttitle off

prompt =========================================================================================================================

Ttitle ‘Log Switch on hour basis’ skip 2

select to_char(FIRST_TIME,’DY, DD-MON-YYYY’) dia,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’00′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’00′,1,0))) d_0,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’01′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’01′,1,0))) d_1,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’02′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’02′,1,0))) d_2,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’03′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’03′,1,0))) d_3,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’04′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’04′,1,0))) d_4,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’05′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’05′,1,0))) d_5,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’06′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’06′,1,0))) d_6,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’07′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’07′,1,0))) d_7,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’08′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’08′,1,0))) d_5,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’09′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’09′,1,0))) d_9,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’10′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’10′,1,0))) d_10,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’11′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’11′,1,0))) d_11,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’12′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’12′,1,0))) d_12,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’13′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’13′,1,0))) d_13,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’14′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’14′,1,0))) d_14,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’15′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’15′,1,0))) d_15,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’16′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’16′,1,0))) d_16,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’17′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’17′,1,0))) d_17,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’18′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’18′,1,0))) d_18,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’19′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’19′,1,0))) d_19,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’20′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’20′,1,0))) d_20,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’21′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’21′,1,0))) d_21,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’22′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’22′,1,0))) d_22,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’23′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’23′,1,0))) d_23,

count(trunc(FIRST_TIME)) Total

from v$log_history

group by to_char(FIRST_TIME,’DY, DD-MON-YYYY’)

order by to_date(substr(to_char(FIRST_TIME,’DY, DD-MON-YYYY’),5,15) )

/

Ttitle off

How to check for LOGGING/NOLOGGING objects in the DB:

Two example methods of querying the database for this information:

select owner , table_name, index_name
from dba_indexes
where logging=’NO’;

select tablespace_name, logging
from dba_tablespaces

/

Kind Regards,

Francisco Munoz Alvarez

 

Let start this New Year with a new mentality or a new resolution if you prefer call this way, let stop being reactive and start being proactive. Being proactive will reduce your database administration costs and increase your level of efficiency, and best of all, save your hours of sleep when you are on call.

Why check the problems only when they are critical, or when is too late and the database is down, or the users are screaming?

Being proactive is the best approach to keep your DB healthy and to show your company, or your clients that you really care about them.

Many DBA’s expend most of their time being firefighters only, fixing problems and working on user’s requests all the time. They don’t do any proactive work; this mentality only will cause an overload of work to them, thousands of dollars of overtime, several hours without access to the data to the users,  poor performance to the applications, and what is worse of all, several unhappy users thinking that you doesn’t have the knowledge needed to take care of their data.

Let’s mention a small example, you have the archive log area alert set to fire when it is 95% full, and this happens in the middle of the night, some DBA’s will take seriously the alert and solve the problem quickly, others will wait until the next day to take care of it because they are tired, or sleeping, or they are in a place without internet access at the moment the alert arrived. Will be a lot easier if they set a proactive alert to be fire when 75% or 85%, or even better ,take a look in the general health status of the DB before leave their work shift, to try to detect and solve any possible problem before be a real problem and be awake in the middle of the night or during the weekend (Remember how important is your personal and family time). I’ll always recommend to DBA’s to run 2 checklists daily, one in the start of their shift and other before they leave their shift.

I know several DBA’s that complain all the time that they got so many calls when they are on call, but they don’t do anything to solve the root problem, they only expend their time to solve the symptoms.

Here you can find an Oracle checklist script that will help to make your life a little easier (This is not my complete script, but will be a good start for you). This script is a compilation of several normal checklists and you can setup them with your own requirement and thresholds and always remember to have a baseline to compare. This script will not only help you to detect future or current problems, but also will help you to detect possible tuning requirement.

Here is an example of the script first phase outcome:

– ———————————————————————– –
– Oracle Instance Information
– ———————————————————————– –
Cpu_Count                                    4   |      Host_Name             OLIVER
Instance_Name                      prod   |      Database_Status    ACTIVE
Status                                     OPEN   |      Startup_Time        10-01-2009 19:50
Version                            11.1.0.7.0   |      Instance_Role        PRIMARY_INSTANCE
Database Space (Mb)       36604    |     SGA (Mb)                 511
Nb. Datafiles                                 43    |    Nb. Tempfiles          1

Archive destination LOCATION=E:\oracle\oradata\prod\archive
Database log mode ARCHIVELOG
Background Dump Dest d:\oracle\diag\rdbms\prod\prod\trace
Spfile D:\ORACLE\PRODUCT\11.1\PROD\DATABASE\SPFILEPROD.ORA
Redo size (Kb) 102400

– ———————————————————————– –
– Instance CheckList –
– ———————————————————————– –
Instance Status                                  OK    |      Listener Status      OK
– ———————————————————————– –
– Performance Memory CheckList –
– ———————————————————————– –
Total Sessions < 700                           OK - 19
Active sessions number <15               OK - 9
Data Buffer Hit Ratio > 80                   OK - 97
L.Buffer Reload Pin Ratio > 99         OK - 99
Row Cache Miss Ratio < 0.015         NO - 1.351
Dict.Buffer Hit Ratio > 80                     OK - 99
Log Buffer Waits = 0                             NO - 110
Log Buffer Retries < 0.0010               OK - 0
Switch number (Daily Avg) < 5          OK - 1
Jobs Broken = 0                                     OK 0
Shared_Pool Failure = 0                    OK - 0
– ———————————————————————– –
– Storage CheckList                                            –
– ———————————————————————– –
Dba_Tablespaces Status                   OK   |       V$Log Status             OK
V$Datafile Status                                  OK   |       V$Tempfile Status   OK
V$Recover_File                                    OK   |       V$Recovery_Log      OK
Tablespace in Backup Mode = 0     OK - 0
Tablespace < 95%                               OK- 0
Objects Invalid = 0                                NO - 147
Indexes unusable = 0                          OK - 0
Trigger Disabled = 0                            NO- 5
Constraint Disabled = 0                       NO - 2
Objects close max extents = 0           OK - 0
Objects can not extent = 0                  NO - 552
User Objects on Systems = 0              NO - 26
FK Without Index = 0                           NO - 138
– ———————————————————————– –
– Datagard CheckList                                          –
– ———————————————————————– –
Datagard Errors = 0                               OK- 0
Datagard Gap = 0                                OK - 0
Archives not Aplied < 5                       OK - 2
– ———————————————————————- –
– Installed options :
– ———————————————————————- –
- Objects option
- Connection multiplexing option
- Connection pooling option
- Database queuing option
- Incremental backup and recovery option
- Instead-of triggers option
- Parallel load option
- Proxy authentication/authorization option
- Plan Stability option
- Coalesce Index option
- Transparent Application Failover option
- Sample Scan option
- Java option
- OLAP Window Functions option

You also have several tools available in the market that can help you to monitor and setup your DB alerts, and help you with the proactive monitoring like: Grid Control, Enterprise Manager, Insider (FourthElephant), Spotlight (Quest) or if you prefer, your own scripts. The idea is to use them always on a proactive way, never reactive.

Let’s change our mentality, let stop being a firefighter and start to be a real hero!

Cheers,

Francisco Munoz Alvarez