Optimizer_mode and statistics gathering in 10g

The OPTIMIZER_MODE parameter has a new default value of ALL_ROWS in OracleDatabase 10g.
In Oracle Database 10g, the Optimizer uses dynamic sampling to get statistics for these tables and uses Cost Based Optimizer(CBO). The other two possible values are FIRST_ROWS_N and FIRST_ROWS.
The CHOOSE and RULE values are no longer supported by Oracle.

In Oracle Database 10g an automatic statistics gathering job is enabledby default when a database is created .This is because of the default setting STATISTICS_LEVEL=TYPICAL.
This is also true when you do database upgrade. Oracle will automatically collect statistics for alldatabase objects which are missing statistics by running an Oracle Scheduler job(GATHER_STATS_JOB) during a predefined maintenance window.
Statistics on a table are considered stale when more than 10% of the rows are changed.
The maintenance window by default is opens every night from 10:00 pm to 6:00 am and all day on weekends.This can be changed.

In 9i to gather statistics automatically on tables, you have to set MONITORING on when creating the table or using the alter table command.
In 10g MONITORING and NOMONITORING is no longer in use.It has been depreciated.

Monitoring tracks the approximate number of DMLs (INSERT, UPDATE, and DELETE)operations for the table since the last time statistics were gathered. information about how many rows are affected is maintained in the SGA, until periodically (about every 15 minutes.This can be changed) after that the background process called SMON flush the data into the data dictionary.(another use of SMON )
This data dictionary information is made AVAILABLE through the tablesDBA_TAB_MODIFICATIONS, ALL_TAB_MODIFICATIONS or USER_TAB_MODIFICATIONS views.
Oracle uses these views to identify tables with stale statistics. Whenever there is 10% change in data in a table, Oracle considers the statistics to be stale.Then it will start to collect statiscts during the maintanence window (STATISTICS_LEVEL=TYPICAL. That is the default)
Since in 10g CBO is used, having up to date statistics is so important to generate good execution plans. Automatic statistics collection is the way to go in 10g.

Oracle Remote Diagnostic Agent (RDA)

Remote Diagnostic Agent (RDA) is a set of command-line diagnostic scripts that are executed by an engine written in the Perl programming language.
RDA is a good tool to gather detailed information about an Oracle environment. The data gathered or the output is used to aid in problem diagnosisand also also for seeing the overall system configuration.

Oracle Support encourages the use of RDA because it provides a detail picture of the customer's environment. . RDA is designed to be as unobtrusive as possible; it does not modify Oracle or OS systems in any way. It collects useful data for diagnostic or to see the overall picture.

OS Platforms that are supported
IBM AIX
Intel Linux (Oracle Enterprise, RedHat, and SuSE)
HP-UX (10.* and 11.*)
HP Tru64 Linux PPC
Mac OS X/Darwin
Sequent Dynix/Ptx
Sun Solaris (2.6 - 2.10)
Windows XP Professional
Windows 2000 Workstation and Server
Windows 2003 Server
HP OpenVMS Alpha 7.3-2 and above
HP OpenVMS Itanium
ZLinux


Oracle Products that are supported
Oracle RDBMS Server (Standard and Enterprise Editions)
Oracle RAC Cluster (Single/Multiple Nodes, Automatic Storage Management, Oracle Cluster File System, Data Guard)
Oracle Application Server (iAS 1.0.2.x/9.0.x/10.1.2.x/10.1.3.x,10.1.4.x,
HTTP Server,WebCache,J2EE/OC4J)
Oracle Oracle Data Integrator
Oracle Management Server and Intelligent Agent (Grid Server, Agent Server, DB Control) OLAP Products (Express Server, Financial Analyzer, and Demand Planning Server)
Oracle Developer (Forms and Reports)
Oracle JDBC/PRO *C/ODBC and other development client related information
Oracle Collaboration Suites (Email Server,Calendar,Discussions,Content Services,Workspaces,WebClient, and Wireless)
Oracle Identity Management (Internet Directory and COREid)
Oracle Networking products
Oracle Ebusiness Suite 11i and 12
Oracle Portal Software 7.0, 7.2, and 7.3
Oracle Retail (Retek)

for more information on how to download,install and run RDA,please see metalink Note: 330363.1

What a DBA shouldn't do....

Here is a list of things that a DBA shouldn't do.I got this info
from an Oracle expert Rachel Carmichael of Dragonfly Consulting LLC.She presented this topic in an event organized by International Oracle Users Group (IOUG).i think it will benefit you all.

1. Dont backup your database - This is one BIG MISTAKE you can
do.Database is slow or fast, but it is still running.When data is
gone, you are gone too.

2.Do not test the recovery features - Whe you do backup, please test
the recovery features, this is the best way to know whether you are
backing up properly.

3.If you do backup,you do not need to export - Sometimes export can
help you to recover from certain issues.A good dba must always think
ways to lower MTTR (Mean Time To Recover).

4.Do not monitor the database.Anything wrong user will complain- To
be a good DBA, always be PROACTIVE not REACTIVE.

5.Use cache hit ratios instead of wait events to monitor performance.- If older systems yes.9i and 10g BETTER use wait events.More accurate.

6.Got a performance problem? Increase your shared pool.Please investigate what the real issue is.Sometimes increasing memory can halt your system.

7.Index every single column in every single table just in case-the more index you have,the more upkeep Oracle has to do.Please always justify your index creation

8.Don't analyze tables or generate stats!- Oracle becomes more cleverer if you generate stats and also please test first.

9.Fix your space problems by turning autoextend on every datafile in every tablespace. -This is not the best way or the right way.You won't know when you are going to hit a big problem.

10.Go ahead -- apply every critical fix without testing them first.- Please test sometimes it can back fire.

11.When new releases come out, upgrade ASAP and use all the new features in each release.- Please know whether you need it or your application support it first.Always let others test first before you do it :-)

12.Grant everyone "connect," "resource" and "DBA" privileges.- This is the worse thing you can do.Give access to users who need it only.

Enjoy thinking.......