| 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@pdbdev
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@pdbdev
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@pdbdev
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 sys@pdbdev as sysdba
GRANT create trigger TO ebradmin;
conn ebradmin/ebradmin@pdbdev
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@pdbdev
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@pdbdev
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. |
|
