Oracle Online Features Golfing Instead of Working on Weekends

1
Oracle Online Features - Golfing Instead of
Working on Weekends Oracle World 2003 Session
36769
Sep 2003
Denny WongConsultantDatabase Services
2
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 ...

3
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

4
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

5
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 cant be rebuilt
  online

6
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)

7
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 the index If Wasted gt 0.2, or
• If index node utilization lt 60 (PCT_USED lt 60)

8
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

9
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

10
Oracle9i (Dynamic SGA)

• E.g., increase the buffer cache size from 32M to
  64M dynamically

11
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

12
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

13
Oracle9i (Dynamic SGA)

• Granule
• A piece of contiguous memory that Oracle
  allocates in SGA
• If SGA lt 128M, the granule size will be 4M
  otherwise it will be 16M
• Oracle allocates space in SGA by multiples of
  granules
• VSGA views containing Dynamic SGA information
  next slide --gt

14
Oracle9i (Dynamic SGA)

• VSGA
• VSGA_DYNAMIC_COMPONENTS
• Dynamic SGA component name, size, granule size,
  etc
• VSGA_DYNAMIC_FREE_MEMORY
• Amount of SGA memory can be grown dynamically
• VSGA_CURRENT_RESIZE_OPS
• SGA resize operations currently in progress
• VSGA_RESIZE_OPS
• Last 100 SGA resize operations

15
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 ...

16
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

17
Oracle9i (Online Table Redefinition)

• Steps for Redefining a Table Online
• How to redefine a table online? Lets 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

18
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 have any materialized view defined on it
• must 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

19
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

20
Oracle9i (Online Table Redefinition)
21
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

22
Oracle9i (Online Table Redefinition)

• If the original and interim table have different
  columns
• Then, specify all the column names when calling
  the procedure

23
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

24
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 ...

25
Oracle9i (Online Table Redefinition)
26
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 ...

27
Oracle9i (Online Table Redefinition)

• Users are accessing the new EMPLOYEE table
  (without OLD_SALARY)
• The whole table redefinition process is now
  completed

28
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 ...

29
Oracle9i (Online Table Redefinition)

• This synchronization is part of
  FINISH_REDEF_TABLE
• Calling SYNC_INTERIM_TABLE doesnt affect the
  short period of time the original table is locked

30
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

31
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

32
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

33
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

34
Questions?

• Oracle Online Features Golfing Instead of
  Working on Weekends
• Session 36769
• Denny Wong
• Denny.Wong_at_cgi.com

• Reference Sources
• Experience
• Oracle Metalink
• Oracle Technet