IOUG - Oracle
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 sga |
| |
|Total 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.employee |
| |
|Name 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
AT THIS POINT THE EMPLOYEE TABLE HAS ALREADY BEEN REDEFINED SUCCESSFULLY. THE USERS ARE NOW ACCESSING THE NEW EMPLOYEE TABLE THAT IS WITHOUT THE OLD_SALARY COLUMN. SINCE THE INTERIM TABLE IS NOW THE OLD COPY OF THE EMPLOYEE TABLE, WE CAN NOW DROP IT TO FREE UP SOME SPACE. WE MIGHT WANT TO RENAME ANY INDEX, CONSTRAINT OR TRIGGER NAMES ON THE NEW EMPLOYEE TABLE. IN OUR EXAMPLE, WE HAVE CREATED AN INDEX CALLED INTERIM_INDX ON THE INTERIM TABLE (FIGURE 9). THEREFORE, THE INDEX NAME INTERIM_INDX IS NOW USED ON THE NEW EMPLOYEE TABLE. THE INDEX NAME CAN BE RENAMED EASILY BY USING THE ‘ALTER INDEX RENAME…’ COMMAND. IN ADDITION, YOU CAN RENAME CONSTRAINTS AND TABLE COLUMNS IN ORACLE9I RELEASE 2; ANOTHER REASON TO UPGRADE TO ORACLE9I RELEASE 2.
Figure 14 – The New Redefined EMPLOYEE Table (Without the OLD_SALARY Column)
| |
|SQL> desc hrms.employee |
| |
|Name Null? Type |
|----------------------------------------- -------- ------------------------- |
|EMPNO NOT NULL NUMBER |
|NAME VARCHAR2(30) |
|SALARY NUMBER |
| |
Synchronize Data Changes (Optional)
THE MAIN IDEA OF ONLINE TABLE REDEFINITION IS THAT USERS CAN STILL UPDATE THE TABLE WHILE THE DATA IS BEING COPIED TO THE NEW TABLE. ORACLE USES THE MATERIALIZED VIEW LOG TO KEEP TRACK OF ANY DATA CHANGES; THEREFORE, ANY DATA CHANGES MADE DURING THE REDEFINITION PROCESS WON’T BE LOST. THE SYNCHRONIZATION WILL BE DONE AS PART OF THE FINISH_REDEF_TABLE PROCEDURE AS WE DISCUSSED IN STEP 6. OPTIONALLY WE CAN RUN THE SYNC_INTERIM_TABLE PROCEDURE TO SYNCHRONIZE ANY DATA CHANGES RECORDED IN THE MATERIALIZED VIEW LOG TO THE INTERIM TABLE BETWEEN THE START_REDEF_TABLE AND FINISH_REDEF_TABLE PROCEDURE CALLS. FOR INSTANCE, AFTER THE START_REDEF_TABLE PROCEDURE HAS COMPLETED, WE NOTICED THAT THE USERS HAVE INSERTED 5 MILLION RECORDS INTO THE EMPLOYEE TABLE DURING THE REDEFINITION PROCESS. NOW THERE ARE 5 MILLION RECORDS SITTING IN THE MATERIALIZED VIEW LOG WHICH HASN’T BE TRANSFERRED TO THE INTERIM TABLE. YOU HAVE THE OPTION TO RUN THE SYNC_INTERIM_TABLE PROCEDURE TO PROPAGATE THE DATA CHANGES FROM THE MATERIALIZED VIEW LOG TO THE INTERIM TABLE BEFORE CREATING THE INDEXES ON THE INTERIM TABLE. NOTE THE FINISH_REDEF_TABLE PROCEDURE ONLY LOCKS THE ORIGINAL TABLE FOR A SHORT PERIOD OF TIME WHEN IT SWITCHES THE NAMES BETWEEN THE ORIGINAL AND THE INTERIM TABLE. THAT IS, WHETHER YOU CALL THE OPTIONAL SYNC_INTERIM_TABLE PROCEDURE OR NOT, IT DOESN’T AFFECT THE SHORT PERIOD OF TIME THAT THE ORIGINAL TABLE IS LOCKED.
Figure 15 – SYNC_INTERIM_TABLE (Optional)
| |
|SQL> exec dbms_redefinition.sync_interim_table('HRMS','EMP','INTERIM'); |
| |
|PL/SQL procedure successfully completed. |
Abort the Redefinition Process
IN CASE YOU NEED TO TERMINATE THE REDEFINITION PROCESS, YOU CAN RUN THE ABORT_REDEF_TABLE PROCEDURE TO DO SO. FOR EXAMPLE, IF YOU HAVE MADE A TYPO IN ONE OF THE COLUMN NAMES OR THE TABLESPACE RAN OUT OF SPACE, YOU COULD TERMINATE THE PROCESS BY CALLING THE ABORT_REDEF_TABLE PROCEDURE. THIS PROCEDURE BASICALLY DROPS THE MATERIALIZED VIEW AND LOG. AFTER THAT, YOU CAN FIX WHATEVER THE PROBLEM WAS AND THEN RESTART THE REDEFINITION PROCESS BY CALLING THE START_REDEF_TABLE PROCEDURE AGAIN.
Restrictions of Online Table Redefinition
Although the online redefinition feature is handy, it has a number of restrictions that won’t let you redefine a table.
• The table must not contain any LONG and FILE columns
• Prior to Oracle9 Release 2, the table must have a primary key
• The table cannot have any materialized view defined on it
• The table must be redefined within the same schema
• The table can’t be clustered
Conclusion
Oracle9i has extended the online features further giving the DBA a lot more flexibility to administer the databases without directly affecting the users. The capabilities of ’ALTER INDEX REBUILD ONLINE’ have extended to reverse-key, function-based and compressed-key indexes. Dynamic SGA allows the DBA to adjust the buffer cache, shared pool and large pool without restarting the instance. With Oracle9i, DBAs can truly redefine/reorganize heap tables online. They can use this Online Table Redefinition feature to reorganize tables to reduce fragmentations, drop columns, and change a non-partitioned table to partitioned table, etc. without restricting the users from performing any DML on the tables.
In today’s competitive business world, many of our systems must be highly available. Thus, requesting a system outage can be a challenge. With these online features in our toolbox, we can reduce the number of scheduled outages creating a win-win situation for both the DBA and the users. We already have other stress in our daily life; we wouldn’t want to spend the gorgeous weekends at work. So, let’s go golfing instead of working on weekends.
About The Author:
Denny Wong is a Database Consultant with CGI, and has more than 9 years of experience in various I.T. areas including Sybase and Oracle database administration. Prior to joining CGI, Denny has participated in various mission-critical system development projects in both Oracle and Sybase, and administered the Oracle Financials databases for a few international companies. Currently he is the primary DBA supporting over 30 Oracle databases from Oracle8i to Oracle9i including a one-terabyte Oracle database of the SAP R/3 system. He also specializes in Oracle networking, such as Oracle Names and Oracle Internet Directory. He has given a number of presentations at various conferences including IOUG and Calgary Oracle User Group (denny.wong@).
................
................
In order to avoid copyright disputes, this page is only a partial summary.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related searches
- us steel oracle self service
- my oracle advantage solutions
- advantage oracle sign in
- oracle cloud revenue 2018
- financial analyst resume with oracle experience
- us steel oracle log in
- oracle employee self service portal
- city of memphis oracle payroll
- oracle payslip self service
- memphis oracle employee self service
- memphis oracle sign in
- oracle employee log in memphis