Oracle Point, Oracle Life.

Oracle metalink note: 438452.1 offers a list of oracle performance tools. That’s real helpful for DBAs and Developers to optimze oracle performance. The list can be found at followings.

1. Query Tuning

• Enterprise Manager (SQL Tuning Advisor)
• AWR SQL Report
• SQLTXPLAIN
• TRCANLZR
• PL/SQ_ Profiler
• LTOM (Session Trace Collector)
• OPDG

2. OS Data

• OS_Watcher

3. Database Tuning

• Enterprise Manager ADDM
• ADDM Report
• STATSPACK
• AWR Report
• OPDG

4. Hang, Locking, and Transient Issues

• ASH Report
• LTOM (Hang Detector, Data Recorder)
• HangFG

5. Error/Crash Issues

• Stackx
• ORA-600/ORA-7445 Troubleshooter

6. RAC

• RDA

I’ve ever tried several of above tools, such as LTOM and OS_Watcher. I’m interested in trying all of them for my knowledge and experience. Clicking the name of tool will re-direct to my installation guide on that tool. The OEM components, such as ADDM report, will be ignored in this posting.

Popularity: 3% [?]

I’m recommending an article from Don Burleson titled “Oracle log_buffer sizing tips”. The article is available at here.
I’ve read this article and found that informative on point of relationship between log_buffer size and init parameter CPU_COUNT. The paramter CPU_COUNT affects the Oracle CBO through many calculated parameters that use CPU_COUNT as their basis and are considered every time that Oracle creates an execution plan for a SQL statement.

The latest dual cores technology of processor makes things getting complicated. Several hidden init parameters are all related to the setting of CPU_COUNT, such as default defree of parallelism.

In my posting of Tuning SGA with scripts (Part 4) - Redo Log Buffer, I concluded that maximum of Log Buffer is 512 KB or 128 KB * CPU_COUNT, whichever is greater in Oracle 9i. That means, with duel cores technology of processor, the size of log_buffer is not always good (if it’s good) by following my calculation offered.

Fortunately, We can heavily rely on AWR report (10g onwards) and Statspack (before 9i) to optimize this. Also, I often use the report offered in my posting (Is Oracle SQL Developer a DBA Tool?) to identify the optimization of log_buffer size. On that report, the log related waits show DBAs to identify if size of log_buffer is appropriate.

There is no alway good log_buffer size for different oracle database. Like our two oracle 9i databases, the size of log_buffer are quite different, one in 512K and one in 5M. It heavily depends on the application.

Popularity: 7% [?]