OraChat – Oracle DBAs

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 [...]

DB Console issue after cloning 11.5.10.2 environment Issue: – 1 – On Applications 11.5.10.2 with RDBMS 11.1.0.7: After cloning dbconsole cannot start. When Checking the $ORACLE_HOME/bin directory and the following emagent, emdctl programs are missing. Step by Step Solution to start and discover DB target to DB Console in Stat and elabs Environments: 1)     Source [...]

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 [...]

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 [...]

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 [...]

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 [...]

Introduction and Significance of tuning indexes in RAC environments. Oracle E-Business environment with financial modules like AP and GL having large scale payment invoices transaction. Let me take example of one my public sector clients, they do process of 3 million payment instructions every night. Such most insert intensive applications running on RAC the frequency [...]

It’s been quite some time since my readers are requesting to see some posts on RAC performance tuning concepts and practices. In the process of testing some of the tuning techniques, as well as discussing and interpreting the results, I am preparing a four-day in depth session to understand index performance tuning techniques for RAC. [...]

SQL> SELECT * FROM DICT WHERE TABLE_NAME LIKE ‘DBA_%’ ORDER BY TABLE_NAME; TABLE_NAME COMMENTS —————————— —————————————————————————————————- DBA_2PC_NEIGHBORS – information about incoming and outgoing connections for pending transactions DBA_2PC_PENDING – info about distributed transactions awaiting recovery DBA_ADVISOR_ACTIONS – DBA_ADVISOR_COMMANDS – DBA_ADVISOR_DEFINITIONS – DBA_ADVISOR_DEF_PARAMETERS – DBA_ADVISOR_DIRECTIVES – DBA_ADVISOR_FINDINGS – DBA_ADVISOR_JOURNAL – DBA_ADVISOR_LOG – DBA_ADVISOR_OBJECTS – DBA_ADVISOR_OBJECT_TYPES – [...]

If you conducted an import or export of an Oracle10g Release 2 or 11g Release 1 database prior to September 11, 2009, you should follow the instructions in My Oracle Support Knowledge Document 1055539.1 to verify the privileges in your database. This is because a missing step in several OracleMetaLink notes published before 11 September [...]

An ASM Regression issue has been reported with 11.1.0.7.1 PSU. A Mandatory patch is being recommended to resolve the ASM regression issue. There is very high possibility of running into the ASM regression issue with 11.1.0.7.1 PSU. No action is required if ASM is not being used in the environment Some of the symptoms are: [...]

Every DBA needs to know how to do their work under pressure, while facing crisis after crisis. Our most important contribution is to keep the database running. It’s an on-call function; you never know what might happen. Half the branch was up all night last night restoring a database because of a failure. Data recovery [...]