Oracle Rant

Many DBAs are familiar with the 10046 event. It’s an excellent way to grab wait events for a specific session that may or may not be performing specific tasks. Some DBAs look at the raw output. Some write their own scripts to aggregate, format and look at it. There is also TKPROF to help you format it. Then, if you really want some detail, you can download the Oracle Trace Analyzer from Metalink (requires setup and configuration) or you could purchase a product like the Hotsos Profiler.

Well, a fellow Oracle DBA who runs a blog at http://oraclue.wordpress.com put up a post about something called OraSRP. It is very detailed, and I thought I would pass it on to any DBA who would like to look at profiling Oracle via 10046 event trace data and get some great analysis on their trace files.

You can even create Google Charts for it. Very cool!

Best of all, It’s FREE!

Enjoy : )

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

Read the rest of this entry »

Oracle has a table type of Global Temporay table that is unique to each session using it. You define the table as a Global Temporary table. What is useful about this type of table is that session A can insert rows into this table and session B can also insert rows into this table. Session A will never see Session B’s rows and vice versa. You can also define if the rows are preserved after a commit or deleted. Once a user disconnects they can never access those rows again. Temporary tables can work well in different types of environments where you want to code to use certain tables but not have the data mix with those of other sessions. The bad thing is that if you collect statistics, they may be wrong. For example, we found Oracle doing nested loops when it should have been doing a hash join.

What did we do to correct this? We deleted statistics on temporary tables and then locked the table stats. It is quite simple to do, and there are two commands that you can use.

exec dbms_stats.delete_table_stats('SCHEMA', 'TABLE_NAME');

exec dbms_stats.lock_table_stats('SCHEMA', 'TABLE_NAME);

That’s about all there is to it. Now when a query runs against this temporary table, Oracle will automatically do dynamic sampling. To change the level of the dynamic sampling, you can alter the parameter optimizer_dynamic_sampling. I believe the default is 2. For more information on the different levels of dynamic sampling, please visit the Oracle Performance Tuning Guide and the Oracle Database Reference.