Oracle Rant

I will talk about some of the Oracle Automatic Database Management enhancements in Oracle Database 10g. The main idea behind these is to make the DBA more productive. I have used some of these and some I have not. I guess you could say I have one foot in the future and the other in the past. As I share my studying with you, perhaps I will start using some of these myself.

The first enhancement is the ADDM (Automatic Diagnostic Monitor). Now in order to use ADDM, you must have an Oracle Diagnostic Pack license. Typically you use and access ADDM through your database control environment but you can also access it via packages and procedures. By default ADDM runs every hour. It is dependent upon the AWR (Automatic Workload Repository), which is like statspack on steroids and also runs every hour by default. You need an Oracle Diagnostic Pack license to look at AWR snapshots. If you want to look at an AWR report then just run awrrpt.sql which is located in your rdbms/admin directory. I’ll do a post about doing statspack in a previous post, but I only look at statspack for pre 10g databases. Also, there is a new background process, MMON, which attaches directly to the SGA and gathers statistics rather than querying the data dictionary. Back to ADDM. ADDM diagnoses many types of performance issues. Those include:

• Configuration issues
• Improper application usage
• Expensive SQL statements
• I/O performance issues
• Locking issues
• Excessive parsing
• CPU bottlenecks
• Undersized memory allocation
• Connection management issues

The thing with the ADDM is that is looks at actual DB time instead of a bunch of ratios. Where statspack can give you too much information, ADDM looks for items that exceed the threshold for database time. This gives you a much clearer picture into what is most likely causing you your performance issues. In order to use ADDM you must set TIMED_STATISTICS = TRUE and STATISTICS_LEVEL to TYPICAL or ALL. If STATISTICS_LEVEL is set to BASIC then you will have to manually run your AWR snapshot, which you can do by using the DBMS_WORKLOAD_REPOSITORY package. By default in 10g, this is already configured for you. You can also manually run an ADDM job in between snapshots if you choose. While you need a Diagnostic Pack license to use ADDM, if you have this setup at home and are using it for studying, I don’t think Oracle will have a problem with you analyzing, prototyping, and studying ADDM. To run an ADDM report, you can do it via Database Control / Grid Control or via command line by running @$ORACLE_HOME/rdbms/admin/addmrpt.sql from the sqlplus prompt. I would highly recommend the OCP Oracle Database 10g study guide by Sam Alapati for further detail on this topic and future ones in this post. This is merely a high level overview.

The next part to talk about is Automatic Shared Memory Management. The main idea behind this is that you set the SGA_MAX_SIZE and SGA_TARGET_SIZE and you leave the traditional SGA parameters like DB_CACHE_SIZE, SHARED_POOL_SIZE, LARGE_POOL_SIZE, JAVA_POOL_SIZE, and STREAMS_POOL_SIZE empty. Oracle will then manage the SGA. This is supposed to make the DBAs life somewhat easier because the database should know where it needs resources and make adjustments accordingly. Imagine you have a database that is hit during the day with small OLTP transactions and at night there are some large batch jobs. The database will be able to dynamically make the adjustments that it needs in order to maintain efficiency. There is a new background process, MMAN, that dynamically makes these adjustments. The gotchas with ASMM is that it really only auto tunes 4 parameters. They are DB_CACHE_SIZE, SHARED_POOL_SIZE, LARGE_POOL_SIZE, and JAVA_POOL_SIZE. However, it takes SGA_TARGET and subtracts what you have given to other SGA parameters that it does not auto tune and then use that for these parameters. If you want to set a minimum for these though, you can. For example, if I set my DB_CACHE_SIZE to 300MB then ASMM will ensure that as it auto tunes it will not reduce the amount of memory for DB_CACHE_SIZE below 300MB. As you can see, this gives the DBA a lot of power to set thresholds that ASMM must work within in automatically tuning your database. One other thing to keep in mind is that if you want to use ASMM then you should use an SPFILE over a pfile for your init parameters. If you have an init.ora file, then login to Oracle and issue this command from sqlplus: create spfile from pfile. Automatic Optimizer Statistics is another area to be aware of on the 10g OCP exam. By Automating the collection of statistics you can ensure that your stats will be up to date. This is one of the most critical areas when it comes to database optimization. If the optimizer is aware of how many rows are in a table, the cardinality etc… then the optimizer can make an intelligent, accurate decision as to the best and quickest way to perform a query. Ensure that you are up to speed on this method or it will come back to bite you come exam time and in the real world. The main thing you need to know is that in order to to ensure that Automatic Optimizer Statistics is enabled you need to set the parameter to STATISTICS_LEVEL = Typical or All. Typical is the default setting in 10g. By setting it to ALL you can also get extra information in your AWR reports such as latch information. The name of the job that runs is called Gather_Stats_job. The job only collects statistics if there is no stats for an object or if they are stale. Oracle marks a tables stats as stale when about 10% of the rows have changed. The DBMS_STATS package is the preferred method for gathering database stats. In 10g, they give you the option of restoring historical optimizer statistics. If you have any user defined statistics or any statistics you gather with the analyze command, you cannot restore those using the DBMS_STATS package.

So how are statistics collected automatically? Oracle by default creates a job named ‘GATHER_STATS_JOB’ which is scheduled to run during the maintenance windows of 10pm-6am M-F and Sat from 12am to Mon 12am. This can be changed or the job can be disabled entirely. The job calls the DBMS_STATS.GATHER_DATABASE_STATS to gather the optimizer statistics. This proc collects stats on objects that either have no statistics or the statistics are stale. When tables are volatile, statistics can be locked. Simply use DBMS_STATS.LOCK_TABLE_STATS (‘SCHEMA’ , ‘TABLE_NAME’); DELETE_TABLE_STATS can be run prior if the DBA wants to force dynamic sampling at query runtime which is great for temp tables and staging tables etc…By default Oracle will also keep statistics on all objects for 31 days. In order to restore these statistics one can use the RESTORE_*_STATS procedure in the DBMS_STATS package.

Oracle now automatically tunes undo retention. This is also referred to rollback. Oracle needs undo for several reasons such as read consistency, rollback or undo uncommitted changes, aid in DB recovery, help facilitate flashback etc… ORA-1555 caused many headaches for Oracle DBAs. But instead of assigning individual undo segments, Oracle is now in charge of the undo segment. Oracle Automatic Undo Management (AUM) can be enabled by setting UNDO_MANAGEMENT to AUTO. It is recommending that the DBA create a separate undo tablespace, otherwise Oracle will use the system tablespace. Finally, by setting UNDO_RETENTION, the DBA specifies how long data should be retained in seconds — 900 is the default. Oracle also provides an undo-advisor which can be accessed easily from database control and grid control.Oracle will also alert you if your queries may result in ORA-1555 (retention time not long enough) and if your undo tablespace begins to run out of free space. Last but not least, if the DBA wants to guarantee retention, the he or she can enable the retention guarantee option on the tablespace. This is especially critical if you are using the flashback features in Oracle database 10g.