Find waiter with Oradebug 11gR2

October 29, 2009 by oraclue 1 Comment

There is small addition to oradebug unit_test command in 11g R2 .

oradebug unit_test per_session_find_one_waiter

This command has four mandatory arguments and one optional.

First parameter is find_waiters_for which can have two different values : current_sess or all_local_sess .Self descriptive.
Second parameter is wait_event.This is name of the wait event which search is done.Third and fourth parameters are related to time and rusults.

For my simple test I will test this command on event enq TX -row lock contention for my own session.

Session 1:

create table test(a int primary key);

insert into test select rownum from dba_tables where rownum <9;
commit;
update test set a=9 where a=1;
update test set a=10 where a=2;

Session 2:

update test set a=11 where a=3;
update test set a=12 where a=4;

SQL> /

PID SPID                            SID USERNAME                          SERIAL# SQL_HASH_VALUE
———- ———————— ———- —————————— ———- ————–
22 1030                            190 SYS                                  1115     3889092034

Session 1 again:

SQL> /

PID SPID                    SID USERNAME                          SERIAL# SQL_HASH_VALUE
———- ———————— ———- —————————— ———- ————–
21 31360                   156 SYS                                 59123     3889092034

update test set a=25 where a=8;
update test set a=13 where a=3;

Now I got  event to test oradebug command:

oradebug unit_test per_session_find_one_waiter find_waiters_for=current_sess wait_event=”enq: TX – row lock contention” waiter_min_secs_blkd=300 min_results=1

SQL> oradebug unit_test per_session_find_one_waiter find_waiters_for=current_sess wait_event=”enq: TX – row lock contention” waiter_min_secs_blkd=1 min_results=1

Here is output:

WAITERS_FOUND_BEGIN
(inst=1, sid=190, osid=1030) is blocking (inst=1, sid=156) for at least 74 secs
WAITERS_FOUND_END

if I run same command again

SQL> oradebug unit_test per_session_find_one_waiter find_waiters_for=current_sess wait_event=”enq: TX – row lock contention” waiter_min_secs_blkd=1 min_results=1

WAITERS_FOUND_BEGIN
(inst=1, sid=190, osid=1030) is blocking (inst=1, sid=156) for at least 221 secs
WAITERS_FOUND_END

wait time will increase.

Let’s increase waiter_min_secs_blkd=300 and run command:

SQL> oradebug unit_test per_session_find_one_waiter find_waiters_for=current_sess wait_event=”enq: TX – row lock contention” waiter_min_secs_blkd=300 min_results=1
WAITERS_FOUND_BEGIN
(inst=1, sid=190, osid=1030) is blocking (inst=1, sid=156) for at least 301 secs
WAITERS_FOUND_END

and I got output after aprox 301 secs..

Filed under Internals

ORION (Oracle I/O Calibration Tool) included in 11g R2

October 27, 2009 by oraclue 4 Comments

Apparently Oracle  included this tool into 11g release 2 database.Orion binary is located under  $ORACLE_HOME/bin .

Quick run with help command will give very detail explanation how to use this tool.Also

Kevin Closson’s Oracle blog got post about Orion:

http://kevinclosson.wordpress.com/2006/12/11/a-tip-about-the-orion-io-generator-tool/

-bash-3.2$ orion -help
ORION: ORacle IO Numbers — Version 11.2.0.1.0

ORION runs IO performance tests that model Oracle RDBMS IO workloads.
It measures the performance of small (2-32K) IOs and large (128K+) IOs
at various load levels.
Read more of this post

Filed under Performance

Uncommitted transactions

October 20, 2009 by oraclue 6 Comments

Let say someone have an update,insert or delete statement that runs but has not been commited yet.So question is how to show uncommitted transactions ?

First some background about transaction management in Oracle.

A transaction is a logical unit of work that contains one or more SQL statements.

A transaction ends when any of the following occurs:

A user issues a COMMIT or ROLLBACK statement without a SAVEPOINT clause.
A user runs a DDL statement such as CREATE, DROP, RENAME, or ALTER. If the current transaction contains any DML statements,
Oracle first commits the transaction, and then runs and commits the DDL statement as a new, single statement transaction.
A user disconnects from Oracle. The current transaction is committed.
A user process terminates abnormally. The current transaction is rolled back.

When a transaction is committed, the following occurs:

1  The internal transaction table for the associated undo tablespace that the transaction has committed,
and the corresponding unique system change number (SCN) of the transaction is assigned and recorded in the table.

This can be seen using v$transaction view.To get more  info  I normally join v$transaction and v$session views.

e.g.

select t.start_time,s.sid,s.serial#,s.username,s.status
from v$transaction t, v$session s
where s.saddr = t.ses_addr

2. The log writer process (LGWR) writes redo log entries in the SGA’s redo log buffers to the redo log file.
It also writes the transaction’s SCN to the redo log file. Read more of this post

Filed under Troubleshooting

Blocking locks history

October 15, 2009 by oraclue 8 Comments

In my previous blogs I have already wrote script for   monitoring blocking locks in real time.

http://oraclue.com/2009/05/18/procedure-to-kill-blocking-session-in-rac-11g/

But what if I am accessing database next day or week later and trying to find out who caused blocking lock? I know there are trace files etc but this time I will use v$views.

Let me start with familiar  scenario to produce blocking lock (  I have use same code for deadlock scenario in previous blogs):

Session 1:

create table test(a int primary key);

insert into test select rownum from dba_tables where rownum <9;
commit;
update test set a=9 where a=1;
update test set a=10 where a=2;

Table created.
8 rows created.
Commit complete.
1 row updated.
1 row updated.

Session 2:

update test set a=11 where a=3;

update test set a=12 where a=4;
1 row updated.
1 row updated.

again session 1:

update test set a=25 where a=8;
update test set a=13 where a=3;

So to detect blocking lock that occurred in the past  I   use active session history or to be precise column blocking_session from view V$ACTIVE_SESSION_HISTORY. Note that  blocking_session column is populated only when the session was waiting for enqueues or a “buffer busy” wait.    

For mutex related waits, v$session.blocking_session is not populated in 10.2.

Use Metalink Note 786507.1

   

v$session.blocking_session is populated in 11g .

My  simple script  looks like: Read more of this post

Filed under Troubleshooting

Deffered Segment Creation

October 13, 2009 by oraclue 2 Comments

Oracle 11g release 2 introduced new initialization  parameter DEFERRED_SEGMENT_CREATION .

This parameter specifies the semantics of deferred segment creation. If set to true ( which is DEFAULT ) then segments for non-partitioned tables and their dependent objects (LOBs, indexes) will not be created until the first row is inserted into the table.

According to Oracle documentation  when you create a table with deferred segment creation (the default), the new table appears in the *_TABLES views, but no entry for it appears in the *_SEGMENTS views until you insert the first row.
There is a new SEGMENT_CREATED column in *_TABLES, *_INDEXES, and *_LOBS that can be used to verify deferred segment creation.

However I do not trust any new feature until I test it. I  will use simple example to test it.

I am running Oracle 11g version 2

SQL> select * from v$version;

BANNER
——————————————————————————–
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
PL/SQL Release 11.2.0.1.0 – Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 – Production
NLSRTL Version 11.2.0.1.0 – Production
and connected as SYS user

SQL> show user
USER is “SYS”

Check that tablespace is LOCALLY managed:

SQL> select  TABLESPACE_NAME ,EXTENT_MANAGEMENT,SEGMENT_SPACE_MANAGEMENT from dba_tablespaces;

TABLESPACE_NAME                EXTENT_MAN SEGMEN
—————————— ———- ——
SYSTEM                         LOCAL      MANUAL
SYSAUX                         LOCAL      AUTO
UNDOTBS1                       LOCAL      MANUAL
TEMP                           LOCAL      MANUAL
USERS                          LOCAL      AUTO

and I will create table test with only one column and stored in tablespace users:

SQL> create table test ( a number) tablespace users;

Table created.

Now according to Oracle documentation this table should be listed in _TABLES views but not in _SEGMENTS views!

SQL> SELECT segment_created
FROM user_tables
WHERE table_name = ‘TEST’;

SEG
—
YES

but segment_created= YES? Read more of this post

Filed under Bugs