Oracle Edition Based Redefinition Demo 5
Version 11.2.0.3
 
Edition Basics 19: Create Pre-Upgrade Infrastructure
This demo is based on Demo 1 and does not depend on having run either Demo 2, Demo 3, or Demo 4

The business problem I want to address in this short demo is how to deal with a situation where a decision has been made to use EBR to perform a zero downtime upgrade ... but ... changes to one or more tables in the pre-upgrade application involve creating new indexes. Indexes that could potentially affect the pre-upgrade application if the indexes were used by the cost based optimizer. Or it might be a situation where pre-upgrade indexes could negatively affect the post-upgrade application.

The solution to both of these possibilities is to make all indexes invisible indexes and then turn-on index visibility by the application at logon. This demo will show how that can be done.
conn ebradmin/ebradmin

SELECT sys_context('USERENV', 'CURRENT_EDITION_NAME')
FROM dual;

CREATE TABLE mobile_net(
tower_id  NUMBER(5),
latitude  FLOAT(20),
longitude FLOAT(20));

ALTER TABLE mobile_net
ADD CONSTRAINT pk_mobile_net
PRIMARY KEY (tower_id);

CREATE INDEX ix_mobile_net_loc
ON mobile_net (latitude, longitude);

INSERT INTO mobile_net VALUES (1, 32.9806, -117.2567);
INSERT INTO mobile_net VALUES (2, 32.6956, -117.1261);
INSERT INTO mobile_net VALUES (3, 32.5525, -117.0488);
INSERT INTO mobile_net VALUES (4, 32.7297, -117.1722);
INSERT INTO mobile_net VALUES (5, 32.7573, -117.1603);
COMMIT;

SELECT * FROM mobile_net;

-- a couple of application queries explained
EXPLAIN PLAN FOR
SELECT *
FROM mobile_net
WHERE latitude = 32.9806;

SELECT * FROM TABLE(dbms_xplan.display);

-----------------------------------------------------------------------------------------------
| Id | Operation                   | Name              | Rows | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT            |                   |    1 |    39 |     1   (0)| 00:00:01 |
|  1 |  TABLE ACCESS BY INDEX ROWID| MOBILE_NET        |    1 |    39 |     1   (0)| 00:00:01 |
|* 2 |   INDEX RANGE SCAN          | IX_MOBILE_NET_LOC |    1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("LATITUDE"=32.9806)

EXPLAIN PLAN FOR
SELECT *
FROM mobile_net
WHERE longitude = -117.2567;

SELECT * FROM TABLE(dbms_xplan.display);

------------------------------------------------------------------------------------------
| Id | Operation              | Name              | Rows | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT       |                   |    1 |    39 |     1 (100)| 00:00:01 |
|  1 |  VIEW                  | index$_join$_001  |    1 |    39 |     1 (100)| 00:00:01 |
|* 2 |   HASH JOIN            |                   |      |       |            |          |
|* 3 |    INDEX FAST FULL SCAN| IX_MOBILE_NET_LOC |    1 |    39 |     0   (0)| 00:00:01 |
|  4 |    INDEX FAST FULL SCAN| PK_MOBILE_NET     |    1 |    39 |     0   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(ROWID=ROWID)
3 - filter("LONGITUDE"=(-117.2567))

-- both of these queries use IX_MOBILE_NET_LOC
 
Edition Basics 20: Create Child Edition and Editioning View Infrastructure
We now perform an editioning setup similar to what we did in Demo 2. We rename the table, create an editioning view, create a new edition, move into the new edition, alter our table to reflect the post-upgrade design and add the proposed post-upgrade indexes. This part, of course, is straight forward.

Where it gets more interesting is when we run the original query and a proposed new query. What we discover is that the new query uses the new index ... but so does the pre-upgrade application. This is precisely what we do not want. We do not want our upgrade to change the behavior of the currently running application.
conn ebradmin/ebradmin

SELECT sys_context('USERENV', 'CURRENT_EDITION_NAME')
FROM dual;

RENAME mobile_net TO mobile_net_tab;

-- replace direct table access with an editioning view API
CREATE OR REPLACE EDITIONING VIEW mobile_net AS
SELECT tower_id, latitude, longitude
FROM mobile_net_tab;

desc mobile_net_tab

desc mobile_net

-- create a child edition and make it current
CREATE EDITION demo_ed;

ALTER SESSION SET EDITION=demo_ed;

SELECT sys_context('USERENV', 'CURRENT_EDITION_NAME')
FROM dual;

-- all changes must be done in the child edition so as to to not affect the parent
-- obviously the parent edition view should have been done as part of an adoption
-- editioning long before this exercise began.

ALTER TABLE mobile_net_tab
ADD (tower_level NUMBER);

-- replace direct table access with an editioning view API
CREATE OR REPLACE EDITIONING VIEW mobile_net AS
SELECT tower_id, latitude, longitude, tower_level
FROM mobile_net_tab;

desc mobile_net_tab

desc mobile_net

-- update the data. In the real-world use DBMS_PARALLEL_EXECUTE
-- as shown in Demo 4.

UPDATE mobile_net SET tower_level = 1;

INSERT INTO mobile_net VALUES (6, 32.9806, -117.2567, 2);
INSERT INTO mobile_net VALUES (7, 32.6956, -117.1261, 2);
INSERT INTO mobile_net VALUES (8, 32.5525, -117.0488, 2);
INSERT INTO mobile_net VALUES (9, 32.7297, -117.1722, 2);
INSERT INTO mobile_net VALUES (10, 32.7573, -117.1603, 2);
COMMIT;

SELECT * FROM mobile_net;

-- create the three indexes for the new application
CREATE INDEX ix_mobile_net_lat
ON mobile_net_tab(latitude);

CREATE INDEX ix_mobile_net_lon
ON mobile_net_tab(longitude);

CREATE INDEX ix_mobile_net_lvl
ON mobile_net_tab (tower_level);

SELECT object_name, object_type
FROM user_objects
ORDER BY 2,1;

SELECT object_name, object_type, edition_name
FROM user_objects_ae
ORDER BY 2,1;

-- a query uses the new index no matter which edition it is in which means
-- our new index will change execution plans in the pre-upgrade application

EXPLAIN PLAN FOR
SELECT mn.*
FROM mobile_net mn
WHERE mn.longitude = -117.1722;

SELECT * FROM TABLE(dbms_xplan.display);

-----------------------------------------------------------------------------------------------
| Id | Operation                   | Name              | Rows | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT            |                   |    2 |   104 |     3   (0)| 00:00:01 |
|  1 |  TABLE ACCESS BY INDEX ROWID| MOBILE_NET_TAB    |    2 |   104 |     3   (0)| 00:00:01 |
|* 2 |   INDEX RANGE SCAN          | IX_MOBILE_NET_LON |    2 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
 2 - access("MN"."LONGITUDE"=(-117.1722))

-- note the new edition is using an index from the old edition that we intend to drop
EXPLAIN PLAN FOR
SELECT mn.*
FROM mobile_net mn
WHERE mn.latitude = 32.7573;

SELECT * FROM TABLE(dbms_xplan.display);

-- the plan has changed
-----------------------------------------------------------------------------------------------
| Id | Operation                   | Name              | Rows | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT            |                   |    2 |   104 |     1   (0)| 00:00:01 |
|  1 |  TABLE ACCESS BY INDEX ROWID| MOBILE_NET_TAB    |    2 |   104 |     2   (0)| 00:00:01 |
|* 2 |   INDEX RANGE SCAN          | IX_MOBILE_NET_LOC |    2 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("LATITUDE"=32.7573)
 
Edition Basics 21: Invisible Indexes
The way we can keep the pre-upgrade application from seeing our new indexes is to make them invisible. The CBO will ignore them unless the session is altered.
conn ebradmin/ebradmin

SELECT sys_context('USERENV', 'CURRENT_EDITION_NAME')
FROM dual;

-- drop the post upgrade indexes
DROP INDEX ix_mobile_net_lat;
DROP INDEX ix_mobile_net_lon;
DROP INDEX ix_mobile_net_lvl;

-- recreate the three indexes for the new application
CREATE INDEX ix_mobile_net_lat
ON mobile_net_tab(latitude)
INVISIBLE;

CREATE INDEX ix_mobile_net_lon
ON mobile_net_tab(longitude)
INVISIBLE;

CREATE INDEX ix_mobile_net_lvl
ON mobile_net_tab (tower_level)
INVISIBLE;

-- now the original query retains its original plan

EXPLAIN PLAN FOR
SELECT *
FROM mobile_net
WHERE latitude = 32.9806;

SELECT * FROM TABLE(dbms_xplan.display);

-- the plan has changed
-----------------------------------------------------------------------------------------------
| Id | Operation                   | Name              | Rows | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT            |                   |    2 |   104 |     1   (0)| 00:00:01 |
|  1 |  TABLE ACCESS BY INDEX ROWID| MOBILE_NET_TAB    |    2 |   104 |     1   (0)| 00:00:01 |
|* 2 |   INDEX RANGE SCAN          | IX_MOBILE_NET_LOC |    2 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("LATITUDE"=32.9806)

-- and the new query sees the index we intend to drop in the pre-upgrade version
EXPLAIN PLAN FOR
SELECT mn.*
FROM mobile_net mn
WHERE mn.tower_level = 2
AND mn.longitude = -117.1261;

SELECT * FROM TABLE(dbms_xplan.display);
----------------------------------------------------------------------------------
| Id | Operation         | Name           | Rows | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|  0 | SELECT STATEMENT  |                |    1 |    52 |     9   (0)| 00:00:01 |
|* 1 |  TABLE ACCESS FULL| MOBILE_NET_TAB |    1 |    52 |     9   (0)| 00:00:01 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("MN"."TOWER_LEVEL"=2 AND "MN"."LONGITUDE"=(-117.1261))

-- until we enable invisible indexes within the current session
ALTER SESSION SET "optimizer_use_invisible_indexes" = TRUE;

EXPLAIN PLAN FOR
SELECT mn.*
FROM mobile_net mn
WHERE mn.tower_level = 2
AND mn.longitude = -117.1261;

SELECT * FROM TABLE(dbms_xplan.display);

-----------------------------------------------------------------------------------------------
| Id | Operation                   | Name              | Rows | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT            |                   |    1 |    52 |     2   (0)| 00:00:01 |
|* 1 |  TABLE ACCESS BY INDEX ROWID| MOBILE_NET_TAB    |    1 |    52 |     2   (0)| 00:00:01 |
|* 2 |   INDEX RANGE SCAN          | IX_MOBILE_NET_LVL |    5 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("MN"."LONGITUDE"=(-117.1261))
2 - access("MN"."TOWER_LEVEL"=2)
 
Edition Basics 22: Enabling OPTIMIZER_USE_INVISIBLE_INDEXES
So we have the required technology but the issue is how we can seamlessly enable invisible indexes in the post-upgrade edition while not enabling them in the pre-upgrade edition. We are going to do that with a form of System Event Trigger called an AFTER LOGON trigger.
conn / as sysdba

GRANT create trigger TO ebradmin;

conn ebradmin/ebradmin

SELECT sys_context('USERENV', 'CURRENT_EDITION_NAME') FROM dual;

SELECT * FROM all_editions;

CREATE OR REPLACE TRIGGER enable_invisible_indexes
AFTER LOGON ON SCHEMA
DECLARE
 parent_edition all_editions.edition_name%TYPE;
BEGIN
  SELECT parent_edition_name
  INTO parent_edition
  FROM all_editions
  WHERE edition_name = (
  SELECT sys_context('USERENV', 'CURRENT_EDITION_NAME') FROM dual);

  IF parent_edition IS NOT NULL THEN
    execute immediate 'ALTER SESSION SET "optimizer_use_invisible_indexes" = TRUE';
    dbms_output.put_line('enabled');
  END IF;
END enable_invisible_indexes;
/

SELECT object_name, object_type, edition_name
FROM user_objects_ae
ORDER BY 2,1;

conn ebradmin/ebradmin

ALTER SESSION SET EDITION=ora$base;

SELECT sys_context('USERENV', 'CURRENT_EDITION_NAME') FROM dual;

EXPLAIN PLAN FOR
SELECT mn.*
FROM mobile_net mn
WHERE mn.latitude = 32.9806;

SELECT * FROM TABLE(dbms_xplan.display);

conn ebradmin/ebradmin

ALTER SESSION SET EDITION=demo_ed;

SELECT sys_context('USERENV', 'CURRENT_EDITION_NAME') FROM dual;

EXPLAIN PLAN FOR
SELECT mn.*
FROM mobile_net mn
WHERE mn.latitude = 32.9806
AND mn.tower_level = 2;

SELECT * FROM TABLE(dbms_xplan.display);

-- the invisible index is visible to the CBO
This concludes Demo 5. When I come up with new functionality it will appear in Demo 6.
 
 
Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: © 2012 Daniel A. Morgan All Rights Reserved