Solving common Oracle Wait Events for performance tunning

Note: One of our visitors and my friend Kavita Yadav  asked this question by posting a comment. Thanks KAvita for your contribution. Keep visiting/commenting! As there are over 800 wait events but but frequently you may come across very few. As working on performance tuning since more than 4 yrs there are very few wait events. [...]

Read Full Post »

How to write / tune SQL Queries for better performance

Note: One of our visitors and my friend Kavita Yadav  asked this question by posting a comment. Thanks KAvita for your contribution. Keep visiting/commenting! Performance of the SQL queries of an application often play a big role in the overall performance of the underlying application. The response time may at times be really irritating for [...]

Read Full Post »

Wait event Read by other session or Buffer busy

Dear friends,      Today I saw AWR report Top 5 time wait events. I saw one wait event “read by other session “. Read by other session wait event & Buffer busy wait event are same. Oracle 9i we called buffer busy wait event and oracle 10g/later we called “read by other session” About “Read [...]

Read Full Post »

Tuning PGA memory in Oracle10g

Checking PGA for each sessions You can check session level PGA using V$SESSTAT and V$SESSION view and also you can check the username, who is using that memory. SELECT s.value,s.sid,a.username FROM V$SESSTAT S, V$STATNAME N, V$SESSION A WHERE n.STATISTIC# = s.STATISTIC# and name = ‘session pga memory’ AND s.sid=a.sid ORDER BY s.value; VALUE SID USERNAME [...]

Read Full Post »

Wait Statistics in Oracle 10g

When a SQL is submitted by a user to Oracle database, it never happens that Oracle will execute the SQL continuously at one go. Oracle process never get to work on the execution of statement without any interruptions. Often the process has to pause or wait for some event or some other resource to be [...]

Read Full Post »

Oracle Database 11g new feature – Automatic Memory Management

Automatic Memory Management was a new feature introduced in 10g. With 10g release oracle has come up with anew parameter called sga_target which was used to automatically manage the memeory inside SGA. The components which were managed by sga_target are db_cache_size, shared_pool_size, large_pool_size, java_pool_size and streams_pool_size With 11g, Oracle went a step further to manage [...]

Read Full Post »

How to adjust the high watermark in ORACLE 10g – ALTER TABLE SHRINK

Yesterday my junior team member was confused about fragmentation and High water mark concepts.Also there was good comment on my fragmentation post, so it inspire me to write something about the High Watermark and the Oracle 10gR1 New Feature SEGMENT SHRINKING.About fragmentation I have already disscused in my previous post. The High Watermark is the [...]

Read Full Post »

What and when Index Scans is used

In case of index scan a row is retrieved by traversing the index, using the indexed column values specified by the statement. An index scan retrieves data from an index based on the value of one or more columns in the index. To perform an index scan, Oracle searches the index for the indexed column [...]

Read Full Post »