Oracle Online Features Golfing Instead of Working on Weekends - PowerPoint PPT Presentation

About This Presentation
Title:

Oracle Online Features Golfing Instead of Working on Weekends

Description:

... Instead of Working on Weekends. Oracle World 2003 Session 36769 ... You must work on the weekend. Well, you can still go golfing on the weekend because ... – PowerPoint PPT presentation

Number of Views:36
Avg rating:3.0/5.0
Slides: 35
Provided by: AGag1
Category:

less

Transcript and Presenter's Notes

Title: 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
Write a Comment
User Comments (0)
About PowerShow.com