Oracle library cache tuning – A Real Case Study.

I have made a few comments in previous posts about the latch: library cache and playing with cursor parameters that can introduce surprises; Here is another one library cache tuning – A Real Case Study. Scenario : Users started complaining about very poor application performance, hung and incomplete transactions. Some severity one ticket for immediate […]

Read the rest of this entry »

Oracle Hanganalyze Performance Problems “sqlplus -prelim”

Hanganalyze / Librarycache contention Last week I was called up to  help to resolve one of the production database hung issue. This totally screwed the database by all means  [ Database connections were hanging, Connections on the server using SQL*Plus as sysdba was also hanging, EM grid was spinning ] Dammn totally stuck …. you […]

Read the rest of this entry »

Tips to Maximize performance and scalability of Oracle E-business Suite 11i

Application Tier: Forms Forms performance issues may arise when dead connection persists on the server and consuming the server resources. Enable the forms dead client connection can eliminate the dead connection. FORMS60_TIMEOUT=10 (recommended value and value specified in minutes) Do $ ps –ef| grep f60webmx and terminates f60webmx processes for dead clients. Enable Forms Abnormal […]

Read the rest of this entry »

Oracle Performance Tuning – How to Use AWR ?

Here are few silver bullets to use AWR for diagnosing performance issues ….. AWR – Automatic Workload Repository Detailed & comprehensive performance history Based on “snapshot” (time interval) Automatic Database Diagnostic Monitor (ADDM) About 79 DBA_HIST_* “tables” Defaults: Hourly snapshots Weekly retention (month is better) Overview of important AWR tables DBA_HIST_SNAPSHOT PK: SNAP_ID DBID, INSTANCE_NUMBER […]

Read the rest of this entry »

Tuning High CPU Consumption on Oracle server

For any performance tuning issue, our strategy in diagnosing the problem is to take a “top-down” approach and follow the trail of highest CPU consumption down to the offending processes, and eventually the offending sessions and query. Verify CPU Consumption on the server is the first step any DBA should do for a top-down approach […]

Read the rest of this entry »

Tuning indexes on RAC environments-4

Excessive undo block shipment and contention for undo buffers I have seen this scenario in one my projects where  Oracle EBS workload is high. When a select statement needs to read a block with active transactions, it has to cleanout the block and undo the changes to create a CR version. If the active transactions […]

Read the rest of this entry »

Tuning indexes on RAC environments-3

Reversing the index key This is accomplished by executing the following SQL statement: ALTER INDEX <index_name> REBUILD REVERSE; A reverse key index will invert the bytes of the index key column, so that values originally stored in the same block may be spread across multiple index leaf blocks. That will reduce buffer busy contention especially […]

Read the rest of this entry »

Tuning indexes on RAC environments-2

Globally busy buffers due to contention for index leaf and branch blocks As we discussed in previous sessions, because of batch INSERT high concurrency could occur for a relatively small set of index leaf and branch blocks, where index keys are populated with monotonically ascending values.The symptoms are increased frequencies and wait times for a […]

Read the rest of this entry »

Gather Schema Statistics with LASTRUN Option does not Clean FND_STATS_HIST Table

After running Gather Schema Statistics concurrent request with (LASTRUN) option to save only the history for last run, then run the concurrent request again for 2 times after that found that (FND_STATS_HIST) tables contains the history for all the 3 requests and it is not cleaned up to keep only last run. Sample steps to […]

Read the rest of this entry »

10g streams configuration

Unidirectional oracle streams setup from Source database Sup.world to Replicated database Rup.world Step 1)   Initialization parameter setup – Following init parameters need to be set. 1. global_names = true 2. job_queue_processes = 4 3. aq_tm_processes = 1 4. logmnr_max_persistent_sessions = 1 5. log_parallelism = 1 6. parallel_max_servers = 2 7. streams_pool_size = 200 min 8. […]

Read the rest of this entry »

Auditing SYS User

Why audit the SYS user, when SYS is super user? Since we deal with critical and confidential data it becomes highly necessary to monitor even the SYS user for data integrity. Data integrity refers to the validity of data; meaning data is consistent and correct. As the need to derive meaningful information from large sets […]

Read the rest of this entry »

Understand each sections of AWR

AWR report is broken into multiple parts. 1)Instance information:- This provides information the instance name , number,snapshot ids,total time the report was taken for and the database time during this elapsed time. Elapsed time= end snapshot time – start snapshot time Database time= Work done by database during this much elapsed time( CPU and I/o […]

Read the rest of this entry »