1 / 34

Oracle Online Features: Golfing Instead of Working on Weekends

Learn about the online features of Oracle9i that allow for tasks like creating and rebuilding indexes while users are still working, dynamic SGA adjustments, and online table redefinition. Say goodbye to weekend work and hello to a round of golf!

burtd
Download Presentation

Oracle Online Features: Golfing Instead of Working on Weekends

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Oracle Online Features - Golfing Instead of Working on Weekends Oracle World 2003 – Session 36769 Sep 2003 Malesh Gummula ConsultantDatabase 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 can’t 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” > 0.2, or • If index node utilization < 60% (PCT_USED < 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 < 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 -->

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

  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? 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

  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 doesn’t 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 Malesh • Reference Sources • Experience • Oracle Metalink • Oracle Technet

More Related