Tuesday, October 09, 2012

Beginning Performance Tuning By Arup Nanda - Artical from ORACLE MAGAZINE

Extract of Beginning Performance Tuning By Arup Nanda - Artical from ORACLE MAGAZINE
http://www.oracle.com/technetwork/issue-archive/2012/12-jul/o42dba-1566567.html

Query for displaying sessions, session state, and events

select sid, state, event from v$session where username = 'ARUP';

SID   STATE              EVENT
————— —————————————————  ————————————————————————————
2832  WAITED KNOWN TIME  SQL*Net message from client
3346  WAITING            enq: TX - row lock contention


Query for displaying sessions, session state, and wait details


col "Description" format a50
select sid,decode(state, 'WAITING','Waiting','Working') state,
        decode(state,'WAITING','So far '||seconds_in_wait,'Last waited '||wait_time/100)||' secs for '||event "Description"
from v$session where username = 'ARUP';

Output:

SID   STATE       Description
————— ——————————  ———————————————————————————————————————————————————————
2832  Working     Last waited 2029 secs for SQL*Net message from client
3346  Waiting     So far 743 secs for enq: TX - row lock contention
4208  Waiting     So far 5498 secs for SQL*Net message from client



You can find out the blocking session and instance by issuing the following SQL statement:

select blocking_session B_SID,blocking_instance B_Inst from v$session
where sid = 3346;

B_SID   B_INST
——————  ———————
 2832      1


Getting row lock information

select row_wait_obj#,row_wait_file#,row_wait_block#,row_wait_row#
from v$session where sid = 3346;

ROW_WAIT_OBJ#  ROW_WAIT_FILE#  ROW_WAIT_BLOCK#  ROW_WAIT_ROW#
—————————————  ——————————————  ———————————————— ——————————————
241876         1024            2307623          0



To get the object information:

select owner, object_type, object_name, data_object_id from dba_objects
where object_id = 241876;

OWNER  OBJECT_TYPE  OBJECT_NAME   DATA_OBJECT_ID
—————  ———————————— ————————————  ——————————————
ARUP   TABLE        T1                    241877



Finding the row information

REM Filename: rowinfo.sql
REM This shows the row from the table when the
REM components of ROWID are passed. Pass the
REM following in this exact order
REM  1. owner
REM  2. table name
REM  3. data_object_id
REM  4. relative file ID
REM  5. block ID
REM  6. row Number
REM
select *
from &1..&2
where rowid =
        dbms_rowid.rowid_create (
                rowid_type      =>  1,
                object_number   => &3,
                relative_fno    => &4,
                block_number    => &5,
                row_number      => &6
        )
/

SQL> @rowinfo ARUP T1 241877 1024 2307623 0

COL1  C
————— —
  1   x


Sessions from a specific user

select SID, osuser, machine, terminal, service_name,
       logon_time, last_call_et
from v$session
where username = 'ARUP';

SID   OSUSER  MACHINE   TERMINAL  SERVICE_NAME  LOGON_TIME LAST_CALL_ET
————— ——————  ———————   ————————  ————————————  —————————— ————————————
3346  oradb   prodb1    pts/5     SYS$USERS     05-FEB-12          6848
2832  oradb   prodb1    pts/6     SERV1         05-FEB-12          7616
4408  ANANDA  ANLAP     ANLAP     ADHOC         05-FEB-12             0



Session waits for a specific machine

col username format a5
col program format a10
col state format a10
col last_call_et head 'Called|secs ago' format 999999
col seconds_in_wait head 'Waiting|for secs' format 999999
col event format a50
select sid, username, program,
        decode(state, 'WAITING', 'Waiting',
                'Working') state,
last_call_et, seconds_in_wait, event
from v$session
where machine = 'appsvr1'
/



Getting the SQL
Another key piece of performance tuning information is the SQL statement a session is executing, which will provide more insights into the workings of the session. The same V$SESSION view also shows the SQL statement information. The SQL_ID column in the V$SESSION view shows the ID of the last SQL statement executed. You can get the text of that SQL statement from the V$SQL view, using the SQL_ID value. Here is an example of how I have identified the SQL statement executed by the session that appears slow to the user.



select sql_id from v$session
where sid = 3089;

SQL_ID
—————————————————
g0uubmuvk4uax

set long 99999
select sql_fulltext
from v$sql
where sql_id = 'g0uubmuvk4uax';
SQL_FULLTEXT
————————————————————————————————————————
update t1 set col2 = 'y' where col1 = 1



Data Access Issues
I have used row-level locking as the cause of a slowdown in this article. Although locking-related contention is a very common cause, it is not the only cause of performance problems. Another major cause of contention is disk I/O. When a session retrieves data from the database data files on disk to the buffer cache, it has to wait until the disk sends the data. This wait shows up for that session as “db file sequential read” (for index scans) or “db file scattered read” (for full-table scans) in the EVENT column, as shown below:



select event from v$session where sid = 3011;

EVENT
—————————————————————————
db file sequential read

16 comments:

Anonymous said...

If you want to get a good deal from this post then you
have to apply these methods to your won webpage.

Look into my web site: make money free online

Anonymous said...

Heya i'm for the primary time here. I found this board and I in finding It really useful & it helped me out much. I hope to provide something back and aid others such as you aided me.

Feel free to surf to my homepage - how to work online

Anonymous said...

This page truly has all of the info I wanted concerning this subject and didn't know who to ask.

my web page :: should i get a home equity loan

Anonymous said...

My brother recommended I might like this web site.
He was totally right. This post actually made my day.
You cann't imagine simply how much time I had spent for this info! Thanks!

Also visit my blog post ... dirndl outlet online

Anonymous said...

Hi to all, how is all, I think every one is getting more from this website, and your views
are pleasant in favor of new users.

my blog post ... free web hosting no ads

Anonymous said...

It's very straightforward to find out any topic on net as compared to textbooks, as I found this piece of writing at this site.

Here is my web-site - home equity loans information

Anonymous said...

Hello i am kavin, its my first occasion to commenting anyplace, when i read this article i thought i could also
make comment due to this good paragraph.

Here is my webpage :: private familienkrankenversicherung vergleich

Anonymous said...

May I simply just say what a comfort to find an individual who actually understands what they're discussing on the net. You definitely understand how to bring an issue to light and make it important. More and more people ought to read this and understand this side of your story. I can't
believe you're not more popular given that you surely possess the gift.

Here is my page Private Krankenversicherung Ohne Gesundheitspr├╝Fung

Anonymous said...

Thank you for sharing your info. I really appreciate your efforts
and I am waiting for your further write ups thanks once again.



Feel free to visit my web page - search engine optimization keyword

Anonymous said...

Hi there Dear, are you really visiting this site regularly, if so afterward
you will without doubt take pleasant knowledge.

Also visit my web blog; studeny loans

Anonymous said...

Hi, i think that i saw you visited my web site thus i came to “return the favor”.
I'm trying to find things to improve my site!I suppose its ok to use some of your ideas!!

Feel free to visit my blog :: 2013 at 4:36 pm

Anonymous said...

Howdy! Do you use Twitter? I'd like to follow you if that would be ok. I'm absolutely enjoying your blog and look forward to new posts.


My webpage; south padre Island vacation rentals

Anonymous said...

There is certainly manifestly a good deal to
know about this. I think you made some superb tips in
Functions also.

Look at my blog post ... reseller domain Registration

Anonymous said...

I'm very involved in this matter. Exactly where can I get more entropy about this? Or, if you do not pursue this additional here.

Look into my website :: krankversicherung f├╝r studenten

Anonymous said...

vapor cigarette, electronic cigarette, smokeless cigarettes, smokeless cigarette, e cigarette, electronic cigarettes

oracle said...

Thanks for sharing