Oracle10G Hidden Features Part I
"The Hidden Secrets of Oracle10G Blog Part I"
This set of blogs, (I feel so tekkie when I say stuff like that) is intended to be a little different than others you may read. They will not focus on some of the more popular features that are most often discussed by other authors. Because of space considerations, and to not turn this into the world's largest blog, the topics will be covered at a high-level. That's the cool thing about blogs; I'll have lots of time in the future to discuss each feature in depth. If you have a hidden feature you would like to highlight, please feel free to respond to this blog. That's what blogging is all about!
In Oracle10G Hidden Secrets Blog Part I, we'll take a look at database cloning, cross platform transportable tablespace, new external table definition, automatic SGA segment sizing, automatic statistics generation and server-generated alerts.
In Oracle10G Hidden Secrets Blog Part II, or "son of blog", I'll discuss some of the other hidden secrets that are destined to make Oracle10G easier to tune, recover and administer.
Clone Database
Duplicating a database is a common task for database administrators. Whether it is to create a mirror-image of a production environment on another platform, duplicate test databases for specific testing and debugging purposes or provide a standby database for Oracle Data Guard, cloning is a popular, yet time-consuming and error-prone task.
10G database administrators are able to use the Enterprise Manager Clone Database wizard to clone databases to another existing Oracle Home (Clone Home - get it?). The wizard steps users through the database clone operation, which provides the following benefits:
- Saves time and reduces the number of errors associated with duplicating an Oracle database
- Capability to clone database releases from 8.1.7 and later
- The source database can remain open during the cloning operation
- The cloning operation restores the source datafiles on the target and recovers them resulting in a clone database that is consistent with the source database up to the point in time which the archived logs were backed up
- Creates the initialization files, password files and network files
Just a quick note from your friendly blogger. If you are going to clone a production database for testing, you must then secure that new QA or test environment as if it were production. Hey, you have production data in there so you better treat it as production. If I were going to steal your data, I wouldn't start with your production databases because I know that they are the most tightly secured. I would be looking for databases with the letters "QA" in them, test databases, database export files, flat files in the database's UTIL output directory, database backups, etc… Rest assured I'm not giving anything away that hackers don't already know.
Cross-Platform Transportable Tablespace
Oracle8i introduced the transportable tablespace feature, which allowed DBAs to "unplug" datafiles associated with a tablespace, manually transport the datafiles to another environment and "plug" the datafiles (and data) into that environment. This enhancement greatly improved the performance and operational simplicity of transferring data from one system to another. In previous releases, the transportable tablespace feature could only be used to transfer data to databases running on the same operating system. In Oracle10G, Oracle has enhanced the transportable tablespace feature to allow the tablespace to be transferred to databases running on different operating systems.
The source and target systems must be on an O/S and hardware combination (see Oracle documentation) that supports cross-platform transportable tablespace and both databases must use the same character sets. In addition, both databases must have their COMPATIBLE initialization parameter set to 10.0.0 or higher before they can use the cross-platform transportable tablespace feature.
The list of platforms is kind of limited right now, but it's a start! The platforms my shop uses are on there, so I'm good to go.
New External Table Definition
Oracle9i introduced external tables, which provided a mechanism to view data stored in external sources as if it were a table in the database. This ability to read external data provided a more straightforward method of loading and transforming data from external sources. Administrators were no longer required to allocate space inside the database for staging tables or write external programs to transform the data outside of the database environment. By making it no longer necessary to stage data in the Oracle database, Oracle9i's external tables have essentially streamlined the ETL function by merging the transformation and loading processes.
In Oracle9i, the database was unable to populate external tables for use as input to other systems. The initial implementation's intention was to allow data created outside of the database environment to be used as input to populate tables inside the database. Oracle10G users are now able to populate data stored in flat files using SQL. The data can then be used as input to other applications that are able to use flat files as input.
Index creation is still not allowed on external tables and administrators must use the "CREATE TABLE AS SELECT" command to populate them. The external table must be created using the ORACLE_DATAPUMP access driver. Administrators loading external tables will not be able to use the "CREATE TABLE AS SELECT" command to populate tables that are defined using the original (ORACLE_LOADER) access driver.
Once the external table has been populated with the "CREATE TABLE AS SELECT" statement, the table's contents can not be modified by INSERT, UPDATE or DELETE statements. In addition, external tables can only be read by the ORACLE_DATAPUMP access driver.
This new external table specification will allow database administrators to pump large volumes of database data into flat files using the "CREATE TABLE AS SELECT STATEMENT. " Being able to activate parallelism increases the performance of the flat file data creation process. These flat files can then be used to propagate the data into other systems or used as an archival storage mechanism.
We intend to use this feature in our data warehouses to transport data to other data stores (both Oracle and non-Oracle). We have several in the multi-terabyte range. Saying that my shop likes data is like stating that the Titanic sprung a small leak. We have a common saying that we never met a piece of data we didn't like. I found that statement amusing until I met with one of the folks that use that data. I had a general understanding of how competitive our line-of-business was - or so I thought. After talking with this person, I now understand that the decisions they make using this data not only provide us with a competitive advantage; it is a requirement for our competitive survival. I knew this data was important, but I now have a better perspective. A suggestion to my fellow DBA managers, send your folks to meet with the business users (not business liaisons or business mangers but the end users). They will come back with a fresh understanding of how important the data is that they are charged with administering. I had just started with my company so I had an excuse. There is no excuse for DBAs not knowing how important the data is they work with.
Automatic SGA Segment Sizing
The automatic SGA segment sizing feature of Oracle10G simplifies administration of the various memory components that make up the System Global Area (SGA). In previous releases, database administrators were required to specify the amount of memory allocated to the buffer cache, shared pool, java pool and large pool. Setting, monitoring and adjusting these parameters was time-consuming and oftentimes guesswork. The process could be described as being more of an "art" than a "science." Sizing them incorrectly usually led to poor performance and/or out-of-memory conditions.
Oracle10G administrators are able to set a single, dynamic parameter, SGA_TARGET, to allocate memory to the entire Oracle SGA. Oracle10G uses the value contained in SGA_TARGET to determine how much memory to allocate to the buffer cache, shared pool, large pool and java pool. During normal database operations, the database engine periodically reviews memory usage and redistributes memory between the components accordingly. Setting the SGA_TARGET to 0 disables automatic SGA segment sizing and requires that the SHARED_POOL_SIZE, LARGE_POOL_SIZE, and JAVA_POOL_SIZE parameters be set. The parameters controlled by automatic segment SGA sizing are now referred to as auto-tuned SGA parameters and the segments not controlled (log buffer, streams pool, internal SGA allocations) are referred to as manually sized parameters. Automatic SGA sizing requires that the STATISTICS_LEVEL parameter be set to TYPICAL or ALL.
Automatic Statistics Generation
In releases prior to Oracle10G, administrators were required to schedule DBMS_STATS jobs on a regular basis to ensure that valid statistics were available to the query optimizer. The optimizer uses statistics as input to generate optimal SQL execution plans or "access paths" to the desired data. In addition, it was necessary to "guess" how much of the data changed to determine if statistics collection was necessary.
In Oracle9i, the GATHER AUTO option of the DBMS_STATS procedure could be used to help determine if statistics generation was required. Administrators ran the ALTER TABLE MONITOR command to begin tracking the number of rows affected by DML statements over a period of time. If more than 10% of the rows changed in the table since the last analyze was performed, the DBMS_STATS procedure (with the GATHER_AUTO option activated) analyzed the table.
By default, Oracle10G automates these tasks by evaluating the statistics for all of the tables in the database and running analyze when required. Oracle10G's default maintenance window is nightly from 10 PM to 6 AM and all day on weekends. During these time periods, statistics are automatically collected using the GATHER_STATS_JOB procedure. The maintenance window time-periods can be adjusted to tailor it to each individual application's business processing requirements.
Server Generated Alerts
Oracle10G provides early warning mechanisms to alert administrators of possible error conditions. The database itself collects numerous metrics that were previously collected by Oracle Enterprise Manager. Administrators are able to select from 161 server-generated alerts.
Server generated alerts, as opposed to their Oracle Enterprise Manager (OEM) counterparts, are generated by a new background process called MMON. MMON is able to access the SGA directly (OEM's monitoring daemon was unable to do this) and performs the metrics calculations and threshold monitoring. Administrators use the Database Control administration page to set up e-mail and pager notifications. In addition, all server-generated alerts are displayed on the Database Control home page. The following server-generated alerts are enabled by default: tablespace out-of-space, snapshot too old, recovery area low on freespace and resumable session suspended.
We are heavy users of Oracle's Oracle Enterprise Manager (OEM) product. We use OEM, and its associated packs, to administer and tune databases. We also use jobs and events to proactively monitor them. We have tested the server generated alert feature of Oracle10G. We are happy that we can still use OEM, the "SGT - Sissy GUI Tool" as I used to call it when I was an Oracle instructor, to administer the server generated alerts. What we have found is that there are a LOT more canned alerts available in 10G - and that is a good thing. I would rather wade through dozens of alerts I don't want to activate than spend ANY time coding an alert that wasn't provided by the toolset.
If you would like to learn about some additional features available in 10G, go to Part II of this series of blogs titled "Oracle10G Hidden Secrets Blog Part II". Thanks for reading.
