Tip of the month – Magic for DBAs

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

4 thoughts on “Tip of the month – Magic for DBAs

  1. Dear Ricardo and Eduardo,

    I’m glad you enjoy it :) hope to see you guys when presenting at Sao Paulo (October and December)!
    You guys are doing a great work to all your peers in Brazil, please keep the great work and please let me know if I can help with something.

    Kind Regards,

    Francisco Munoz Alvarez

Leave a Reply