DBA
Oracle Online Features – Golfing Instead of Working on Weekends
Denny Wong, CGI
Introduction
In today’s I.T. world, system requirements seem to be getting higher every day. 7x24 is one of the common terms you hear the most. In the past, requesting an outage for maintaining databases after normal business hours wasn’t too difficult. As business competition rises, getting an outage during weekdays is almost impossible. The DBA often has to sacrifice the sunny weekends to work instead of golfing or enjoying other activities. Even worse, the DBA must work in the middle of night on Saturday or Sunday. Fortunately, Oracle9i has many great features that allow the DBA to perform database maintenance online without affecting business operations.
First, we will discuss some of the features from earlier versions of Oracle enhanced in Oracle9i with the online capabilities. Second, we will discuss two new online features in Oracle9i – Dynamic SGA and Online Table Redefinition. The audience will be introduced to these new features by going through some examples.
Create/Rebuild Index Online
Prior to Oracle9i, the online feature can only be used on conventional indexes. The online capability has been enhanced a lot in Oracle9i. You can online create/rebuild reverse-key indexes, function-based indexes, key-compressed indexes and IOT secondary indexes. So you can make use of the different types of indexes without losing the benefit of the online capabilities. However, there are still some restrictions such as parallel DML is not supported while the index is being rebuilt. Bitmap and cluster indexes also can’t be rebuilt online.
Figure 1 – CREATE/REBUID INDEX ONLINE
CREATE INDEX EMP_FUNCTION_INDX … ONLINE ;ALTER INDEX EMP_FUNCTION_INDX REBUILD ONLINE … ;
Analyze Table Validate Structure Online
ANALYZE TABLE VALIDATE STRUCTURE ONLINE is another enhancement available in Oracle9i. You can analyze the structure of a table online while users are performing DML on the table. Oracle verifies the integrity of data blocks/rows, such as verifying rows belong to the correction partition.
Analyze Index Validate Structure Online
ANALYZE INDEX VALIDATE STRUCTURE ONLINE verifies the integrity of data blocks in an index, such as verifying block corruption. With the ONLINE option, users can still perform DML on the table while the table is being validated. Unfortunately, the online option is not available for checking if an index needs to be rebuilt or not. For example, when you run the “ANALYZE INDEX VALIDATE STRUCTURE” command (without ONLINE), Oracle populates the information about the index into a temporary table called INDEX_STATS. If the deleted entries in the index is greater then 20% (i.e. DEL_LF_ROWS/LF_ROWS > 0.2) then you might want to rebuild the index. However, if the ONLINE option is specified, Oracle will not populate the information into the INDEX_STATS table.
Figure 2 – ANALYZE TABLE/INDEX VALIDATE STRUCTURE ONLINE
ANALYZE TABLE EMP VALIDATE STRUCTURE ONLINE ;ANALYZE INDEX EMP_INDX VALIDATE STRUCTURE ONLINE ;
Alter index rebuild compute statistics online
Normally you would analyze the index after you rebuild it so that the optimizer has latest statistics to generate better execution plans. There are two ways to accomplish prior to Oracle9i. You can either run the “ALTER INDEX REBUILD ONLINE” command followed by the “ANALYZE INDEX” command, or run the “ALTER INDEX REBUILD COMPUTE STATISTICS”. However, the latter restricts users from performing any DML on the table. With Oracle9i, you can specify the ONLINE option in the “ALTER INDEX REBUILD COMPUTE STATISTICS” command to eliminate this restriction. So the users can still update the table while the index is being rebuilt and statistics is being collected.
Figure 3 – ANALYZE INDEX REBUILD COMPUTE STATISTICS ONLINE
ALTER INDEX EMP_INDX REBUILD COMPUTE STATISTICS ONLINE ;Dynamic SGA
We have discussed some of the enhancements introduced in Oracle9i. Now let’s take a look at some of the totally new online features in Oracle9i. In database performance tuning, we often have to adjust the size of buffer cache or shared pool. These changes require the instance to be restarted prior to Oracle9i. For 7x24 systems, that can be a real challenge. In addition, if you want to observe results from various buffer cache sizes or shared pool sizes, then you need to restart the instance more than once to observe the different outcomes. This is definitely not acceptable even on a non-7x24 system.
Oracle9i introduced something called Dynamic SGA. You can adjust the size of the buffer cache (DB_CACHE_SIZE) and shared pool (SHARED_POOL_SIZE) dynamically. As of Oracle9i Release 2, you can also adjust the large pool (LARGE_POOL_SIZE) dynamically. That means you do not have to restart the instance in order to effect changes in the database. For example, if you want to increase the buffer cache from 32M to 64M, run the “ALTER SYSTEM SET DB_CACHE_SIZE = 64M” command. Oracle will allocate the additional memory dynamically.
Figure 4 – Dynamic SGA
SQL> show sgaTotal System Global Area 219638280 bytes
Fixed Size 735752 bytes
Variable Size 184549376 bytes
Database Buffers 33554432 bytes
Redo Buffers 798720 bytes
SQL> alter system set db_cache_size = 64m;
System altered.
SQL> show sga
Total System Global Area 219638280 bytes
Fixed Size 735752 bytes
Variable Size 150994944 bytes
Database Buffers 67108864 bytes
Redo Buffers 798720 bytes
Sga_Max_size
The total size of SGA is basically made up of a number of memory components such as buffer cache, shared pool, large pool, etc. SGA_MAX_SIZE is one of the new parameters introduced in Oracle9i. This parameter limits the maximum size of the SGA that you can grow dynamically. For example, SGA_MAX_SIZE has been set to 208M, and currently the sum of all memory components is 180M. If you try to add another 32M to the existing shared pool; Oracle will raise the ORA-04033 error because you are trying to expand the SGA beyond 208M specified by SGA_MAX_SIZE.
Figure 5 – SGA_MAX_SIZE
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
In this case, you can either reduce the size of other dynamic SGA components (buffer cache or large pool) to make room for the shared pool to grow. Unfortunately you can’t increase the size of SGA_MAX_SIZE dynamically. You have to restart the instance in order to change it. So, make sure you define a reasonable size of SGA_MAX_SIZE in order to grow the three SGA components dynamically. On the other hand you should not set SGA_MAX_SIZE too high so that large amount of memory is wasted. It’s always a good practice to monitor if there is any unusual paging happening from the operating system level.
One last point about Dynamic SGA, a new term called ‘granule’ is introduced in Oracle9i. It refers to a piece of contiguous memory that Oracle allocates in the SGA. The size of a granule can be either 4M or 16M depending on the size of the SGA. If the SGA size is less than 128M, the granule will be 4M otherwise it will be 16M. Oracle allocates the space in the SGA by multiples of granules. For example assume the granule size is 16M, and you are trying to set DB_CACHE_SIZE to 30M. Oracle will actually allocate 32M instead of 30M for the buffer cache. In Oracle9i Release 2, there is a number of V$SGA views which show you the dynamic SGA information such as the SGA component name, granule size and free SGA.
· V$SGA_DYNAMIC_COMPONENTS – Information about each dynamic SGA component
· V$SGA_DYNAMIC_FREE_MEMORY – Amount of SGA memory can be grown dynamically
· V$SGA_CURRENT_RESIZE_OPS – Information about SGA resize operations currently in progress
· V$SGA_RESIZE_OPS – Information about the last 100 SGA resize operations
Oracle9i Online Table Redefinition
The ‘ALTER TABLE MOVE ONLINE’ command is only for index organized tables (IOT). You could run ALTER TABLE MOVE to reorganize a table, but the users won’t be able to update the table while the table is being moved. Actually, it’s just a fancy way to run the ‘CREATE TABLE AS SELECT * FROM…’ command. There wasn’t any concept that allowed you to online reorganize a heap (regular) table until Oracle9i.
With Oracle9i, a new feature is available, called Online Table Redefinition, which allows you to redefine the table structure while the users perform DML on the table. With this capability, you can online reorganize a table, drop and add columns, change a heap table to an IOT, change a non-partitioned table to a partitioned table, change storage parameters, and so on. However, it is not as simple as by running a single command. It involves a number of procedure calls from the DBMS_REDEFINITION PL/SQL package:
· CAN_REDEF_TABLE – Check if the table can be redefined
· START_REDEF_TABLE – Start the redefinition process
· SYNC_INTERIM_TABLE – Synchronize any data changes (Optional)
· FINISH_REDEF_TABLE – Finish the redefinition process
· ABORT_REDEF_TABLE -- Abort the redefinition process
Steps For -Redefining a Table ONLINE
There are a number of steps involved in redefining a table online. To help us to understand the process better, this paper depicts an example to show us how to redefine a table online. Assume we have a table called EMPLOYEE, and would like to reorganize the table and drop the OLD_SALARY column at the same time. The following steps explain how to achieve that.
Step 1) Verify The Table
There are a number of limitations of online table redefinition. For example, a table with LONG or LONG RAW data type can’t be redefined. Later in this paper, it lists all the restrictions. Also, the table must have a primary key in order to be redefined. As of Oracle9i Release 2, the table doesn’t have to have a primary key because there is an option that allows us to use ROWID to keep track of any data changes made during the redefinition process. The CAN_REDEF_TABLE procedure is used to verify if the table is valid to be redefined. If it can’t be redefined, the procedure will raise an error.
Figure 6 – Verify the Table
SQL> exec dbms_redefinition.can_redef_table ('HRMS','EMPLOYEE')PL/SQL procedure successfully completed.
Step 2) Create The Interim Table
As we verify in Figure 6 that the EMPLOYEE table satisfies all the requirements for online redefinition. The next step is to create an interim table. This table will eventually become the new table when the redefinition process is complete. All the desired table definitions must be defined on this interim table. However, at this point we only need to create the interim table with the primary key. We will create any indexes, constraints and triggers later on. Again, our object is to reorganize the EMPLOYEE table and drop the OLD_SALARY column.
Figure 7 – Create the Interim Table
SQL> desc hrms.employeeName Null? Type
------
EMPNO NOT NULL NUMBER
NAME VARCHAR2(30)
SALARY NUMBER
OLD_SALARY NUMBER
SQL> CREATE TABLE INTERIM
2 (EMPNO NUMBER PRIMARY KEY,
3 NAME VARCHAR2(30),
4 SALARY NUMBER);
Step 3) Start The Redefinition Process
After we create the interim table, the next step is to copy the data from the original table to the interim table by executing the START_REDEF_TABLE procedure. Once the procedure is started Oracle creates a materialized view and log, and then copies the data from the EMPLOYEE to INTERIM table. The purpose of the materialized view log is to record any data changes made to the EMPLOYEE table while the data is being copied to the INTERIM table. We create the INTERIM table without the OLD_SALARY column since we want to drop that column. Since the columns between the EMPLOYEE and INTERIM tables are different, we have to specific the column names when calling the START_REDEF_TABLE procedure.
Figure 8 – START_REDEF_TABLE
SQL> exec dbms_redefinition.start_redef_table('HRMS','EMPLOYEE','INTERIM','EMPNO EMPNO, NAME NAME, SALARY SALARY');
PL/SQL procedure successfully completed.
Step 4) Create Indexes, Constraints, Triggers and Grants on the Interim Table
The next step is to create any indexes, constraints, triggers and grants on the INTERIM table. These are the new definitions you would like to have on the new table when the redefinition process is complete. That is, what you define on the interim table is what you will ultimately see on the new table. Keep in mind that any foreign key constraints must be created in disabled state, and they will be enabled later in the redefinition process. Assume we have an index to be created on the INTERIM table.
Figure 9 – Create the Index
SQL> create index interim_indx on INTERIM (EMPNO, NAME);Index created.
Step 5) Complete the Redefinition Process
To complete the redefinition process, we execute the FINISH_REDEF_TABLE procedure as in Figure 10. This procedure first applies any data changes recorded in the materialized view log to the INTERIM table, so that no data will be lost. Second, it locks the EMPLOYEE table in exclusive mode for a short period of time to switch the names between the INTERIM and EMPLOYEE table. It also drops the materialized view and log. The online redefinition process is basically complete after the FINISH_REDEF_TABLE procedure is finished.
Figure 10 – Complete the Redefinition Process
SQL> exec dbms_redefinition.finish_redef_table('HRMS','EMPLOYEE','INTERIM');PL/SQL procedure successfully completed.
Step 6) The Final Step