Transcript Oracle Online Features - Golfing Instead of Working on Weekends

Sep 2003
Oracle Online Features - Golfing Instead of
Working on Weekends
Oracle World 2003 – Session 36769
Denny Wong
Consultant
Database Services
Introduction
Take a system outage at 7pm on a weekday?
No, because …
 Internet applications
 Nightly batch jobs
 Users from other countries
 7x24 retail systems
 Users work late during month-end
 so on ...
Introduction
As a Result ...

You must work on the weekend

Well, you can still go golfing on the weekend because …

The regular system outage is between 1am - 5am on
Saturday
Introduction
Oracle9i Online Features

Create/Rebuild Indexes Online

ALTER TABLE/INDEX VALIDATE STRUCTURE
ONLINE

ALTER INDEX REBUILD COMPUTE STATISTICS
ONLINE

Dynamic SGA

Online Table Redefinition
Oracle9i (Enhancements)
Create/Rebuild Indexes Online

Oracle extended these capabilities in 9i
 Reverse-key index
 Function-based index
 Key-compressed index
 IOT secondary index

Limitations
 Parallel DML not supported while the index is being rebuilt
 Bitmap and Cluster indexes can’t be rebuilt online
Oracle9i (Enhancements)
ANALYZE TABLE VALIDATE STRUCTURE ONLINE

Users can still perform DML on the table

ANALYZE TABLE VALIDATE STRUCTURE ONLINE
 Verify the integrity of data blocks/rows (e.g. rows belong to the
correct partition)
Oracle9i (Enhancements)
ANALYZE INDEX VALIDATE STRUCTURE ONLINE

Verify the structure of the index (e.g. Check block corruption)

When to rebuild an index? Cannot use the ONLINE option
 SELECT
DEL_LF_ROWS / LF_ROWS “Wasted” FROM
INDEX_STATS
 Rebuild
 If
the index If “Wasted” > 0.2, or
index node utilization < 60% (PCT_USED < 60)
Oracle9i (Enhancements)
ALTER INDEX REBUILD COMPUTE STATISTICS ONLINE

An index should be analyzed after it has been rebuilt.
 “ALTER INDEX REBUILD ONLINE” followed by “ANALYZE INDEX”
 “ALTER INDEX REBUILD COMPUTE STATISTICS”
 But,
users cannot perform DML on the table
 “ALTER INDEX REBUILD COMPUTE STATISTICS ONLINE” is
available in Oracle9i
 Users
can perform DML on the table
Oracle9i (New Features)
Dynamic SGA

In database tuning, it may require adjusting the buffer cache or
shared pool size

Requires an instance restart, challenging for 7x24 systems

Dynamic SGA components
 Buffer cache (DB_CACHE_SIZE)
 Shared pool (SHARED_POOL_SIZE)
 Large pool (LARGE_POOL_SIZE) in Oracle9i Release 2
Oracle9i (Dynamic SGA)

E.g., increase the buffer cache size from 32M to 64M dynamically
SQL> show sga
Total System Global Area
Fixed Size
Variable Size
Database Buffers
Redo Buffers
219638280
735752
184549376
33554432
798720
bytes
bytes
bytes
bytes
bytes
SQL> alter system set db_cache_size = 64m;
System altered.
SQL> show sga
Total System Global Area
Fixed Size
Variable Size
Database Buffers
Redo Buffers
219638280
735752
150994944
67108864
798720
bytes
bytes
bytes
bytes
bytes
Oracle9i (Dynamic SGA)
SGA_MAX_SIZE

SGA is basically made up of memory components, such as buffer
cache, shared pool, large pool, java pool, etc ...

SGA_MAX_SIZE limits the maximum size the SGA can grow
dynamically
Oracle9i (Dynamic SGA)
SGA_MAX_SIZE

Example
 SGA_MAX_SIZE = 208M
 Sum of all memory components = 200M
 Increase the shared pool by another 16M will fail because there is
only 8M (208M - 200M) available
SQL> alter system set shared_pool_size=32m;
alter system set shared_pool_size=32m
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified
value is invalid
ORA-04033: Insufficient memory to grow pool
Oracle9i (Dynamic SGA)
Granule

A piece of contiguous memory that Oracle allocates in SGA

If SGA < 128M, the granule size will be 4M otherwise it will be 16M

Oracle allocates space in SGA by multiples of granules

V$SGA views containing Dynamic SGA information … next slide -->
Oracle9i (Dynamic SGA)
V$SGA
 V$SGA_DYNAMIC_COMPONENTS
 Dynamic
SGA component name, size, granule size, etc …
 V$SGA_DYNAMIC_FREE_MEMORY
 Amount
of SGA memory can be grown dynamically
 V$SGA_CURRENT_RESIZE_OPS
 SGA resize
operations currently in progress
 V$SGA_RESIZE_OPS
 Last
100 SGA resize operations
Oracle9i (Online Table Redefinition)
Online Table Redefinition

Allows you to redefine a table structure while users are performing
DML on the table

Online Table Redefinition Capabilities
 Reorganize a table
 Drop/add columns
 Change a heap (regular) table to an IOT
 Change a non-partitioned table to a partition table
 So on ...
Oracle9i (Online Table Redefinition)
Online Table Redefinition

The whole redefinition process involves a number of
DBMS_REDEFINITION procedure calls
 CAN_REDEF_TABLE
– Check if the table can be redefined
 START_REDEF_TABLE
– Start the redefinition process
 SYNC_INTERIM_TABLE – Synchronize data modifications (Optional)
 FINISH_REDEF_TABLE
– Finish the redefinition process
 ABORT_REDEF_TABLE – Abort the redefinition process
Oracle9i (Online Table Redefinition)
Steps for Redefining a Table Online

How to redefine a table online? Let’s walk through an example …

Reorganize table EMPLOYEE and drop column OLD_SALARY
 Step 1) Verify the Table
 Step 2) Create the Interim Table
 Step 3) Start the Redefinition Process
 Step 4) Create Indexes, Constraints, Triggers and Grants on the
Interim Table
 Step 5) Complete the Redefinition Process
 Step 6) The Final Step
Oracle9i (Online Table Redefinition)

Step 1) Verify the Table
 Limitations of Online Table Redefinition. The table ...
 must
have a primary key (prior to Oracle9i Release 2)
 must
not contain any LONG or FILE columns
 cannot
 must
have any materialized view defined on it
be defined within the same schema
 Execute the CAN_REDEF_TABLE procedure to verify
 If
the table cannot be redefined, it will raise an error
SQL> exec dbms_redefinition.can_redef_table
('HRMS','EMPLOYEE')
PL/SQL procedure successfully completed.
Oracle9i (Online Table Redefinition)

Step 2) Create the Interim Table
 The interim table will ultimately become the new table
 All desired table definitions must be defined on the interim table
 However
indexes, constraints and triggers will be created
later in step 4
 In
our example, we will reorganize table EMPLOYEE and
drop column OLD_SALARY
continue …
Oracle9i (Online Table Redefinition)
SQL> desc hrms.employee
Name
Null?
--------------- ----------EMPNO
NOT NULL
NAME
SALARY
OLD_SALARY
Type
----------------------NUMBER
VARCHAR2(30)
NUMBER
NUMBER
SQL> CREATE TABLE INTERIM
2 (EMPNO
NUMBER
PRIMARY KEY,
3
NAME
VARCHAR2(30),
4
SALARY
NUMBER);
Oracle9i (Online Table Redefinition)

Step 3) Start the Redefinition Process
 Execute the START_REDEF_TABLE procedure
 It
will create a materialized view and log
– The log keeps track of any data modifications made by users
 Then,
it starts copying the data from EMPLOYEE to INTERIM
continue …
Oracle9i (Online Table Redefinition)
 If the original and interim table have different columns
 Then,
specify all the column names when calling the
procedure
SQL> exec dbms_redefinition.start_redef_table
(‘HRMS','EMPLOYEE','INTERIM',
'EMPNO EMPNO, NAME NAME, SALARY SALARY');
PL/SQL procedure successfully completed.
Oracle9i (Online Table Redefinition)

Step 4) Create Indexes, Constraints, Triggers and
Grants on the Interim Table
 Create after START_REDEF_TABLE is completed
 What
we define on the interim table will ultimately belong to
the new table
 Foreign
key constraints must be created in disabled state
SQL> create index interim_indx on INTERIM(EMPNO, NAME);
Index created.
Oracle9i (Online Table Redefinition)

Step 5) Complete the Redefinition Process
 Execute the FINISH_REDEF_TABLE procedure
 Data
Synchronization
– Any data modifications recorded in the materialized log
will be transferred to INTERIM
 Switch
Tables
– EMPLOYEE will be locked
– Switch the table names of EMPLOYEE and INTERIM
– Drop the materialized view and log
continue ...
Oracle9i (Online Table Redefinition)
SQL> exec dbms_redefinition.finish_redef_table
('HRMS','EMPLOYEE','INTERIM');
PL/SQL procedure successfully completed.
Oracle9i (Online Table Redefinition)

Step 6) The Final Step
 EMPLOYEE has been redefined successfully
 INTERIM
is now the old copy of EMPLOYEE
– Drop it to free up the space
 Rename
indexes, constraints and triggers on the new table
– Rename index INTERIM_INDX created in step 4
– In Oracle9i Release 2, constraints and column names can
be renamed
continue ...
Oracle9i (Online Table Redefinition)
 Users
are accessing the new EMPLOYEE table (without
OLD_SALARY)
 The
whole table redefinition process is now completed
SQL> ALTER INDEX INTERIM_INDX RENAME EMPLOYEE_INDX ;
Index Altered.
SQL> desc hrms.employee
Name
Null?
----------------------------------------- -------EMPNO
NOT NULL
NAME
SALARY
Type
-----------------NUMBER
VARCHAR2(30)
NUMBER
Oracle9i (Online Table Redefinition)

Synchronize Data Modifications (Optional)
 Execute the SYNC_INTERIM_TABLE procedure
 This
procedure can be executed many times between
START_REDEF_TABLE and FINISH_REDEF_TABLE
 It
propagates data modifications recorded in the materialized
view log to the interim table
– e.g. Execute SYNC_INTERIM_TABLE to propagate 5
million records to the interim table before creating the
index
continue ...
Oracle9i (Online Table Redefinition)
 This synchronization is part of FINISH_REDEF_TABLE
 Calling SYNC_INTERIM_TABLE doesn’t affect the short period
of time the original table is locked
TEST> exec dbms_redefinition.sync_interim_table(
'HRMS','EMPLOYEE','INTERIM');
PL/SQL procedure successfully completed.
Oracle9i (Online Table Redefinition)

Abort the Redefinition Process (if necessary)
 E.g. You made a typo in the column name, or the tablespace
ran out of space
 Execute the ABORT_REDEF_TABLE procedure
 The
procedure will drop the materialized view and log
 Re-start the redefinition process when the problem is fixed
Conclusion

Oracle9i extended these online capabilities
 Create/Rebuild Indexes Online
 Reverse-key,
function-based and key-compressed indexes
 ALTER TABLE/INDEX VALIDATE STRUCTURE ONLINE
 Examine
the table/index without locking the table
 ALTER INDEX REBUILD COMPUTE STATICS ONLINE
 Rebuild
the index and collect statistics without locking the table
Conclusion

Dynamic SGA
 Adjust buffer cache, shared pool and large pool dynamically
 SGA_MAX_SIZE limits the maximum size the SGA can grow
dynamically
 Oracle allocates space in SGA by multiples of granules

Online Table Redefinition
 Online capabilities
 Reorganize
a table
 Change
a heap table to IOT
 Change
a non-partitioned table to a partitioned table …
Conclusion

Online Table Redefinition
 A number of procedure calls to redefine a table online
 CAN_REDEF_TABLE
 START_REDEF_TABLE
 SYNC_INTERIM_TABLE
 FINISH_REDEF_TABLE
 Create indexes, constraints and triggers on the interim table
 Rename indexes, constraints and triggers on the new table
Questions?
Oracle Online Features – Golfing Instead of
Working on Weekends
Session 36769
Denny Wong
[email protected]
Reference Sources
•Experience
•Oracle Metalink
•Oracle Technet