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 »

Oracle DBA Silver Bullets – Performance Queries

Just thought to share few queries from my repository. It may be useful for all new DBAs. Of course these are quite old ones and you can do most of them through Enterprise Manager, still we DBAs always like command line 🙂 Show sessions that are blocking each other : select ‘SID ‘ || l1.sid […]

Read the rest of this entry »

Oracle 8i to 11g – hidden features.

Every release contains many new features and enhancements that are not highlighted in the marketing material. Oracle assumes all customers will move on to new versions when they are released. Here are few examples of those – New  features failed to spot. DBMS_SYSTEM.KSDWRT undocumented package installed in all databases Used to write messages to trace […]

Read the rest of this entry »

How to change ASM SYS Password and Creating SYSASM user 11g.

Things tried: SQL> select INSTANCE_NAME from v$instance; INSTANCE_NAME —————- +ASM SQL> ALTER USER sys IDENTIFIED BY <new_password> REPLACE <old_password>; ALTER USER sys IDENTIFIED BY <new_password> REPLACE <old_password> * ERROR at line 1: ORA-01109: database not open We can not change the password for ASM databases via alter user command.Because passwords are managed through password file […]

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 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 »

Tuning indexes on RAC environments-1

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

Read the rest of this entry »

Index Performance Tuning Techniques for RAC

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

Read the rest of this entry »

Oracle Dictionary – DBA Views – Quick Reference

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 – […]

Read the rest of this entry »