Oraclue

Oracle internals, debugging and undocumented features

Category Archives: Performance

Run away from GUI DBA part 2.Script to report user locks for RAC

Very usefull  script to list all user locks in RAC database.

SELECT o.name object_name, u.name owner, lid.*
  FROM (SELECT
               s.inst_id, s.SID, s.serial#, p.spid,NVL (s.sql_id, 0), s.sql_hash_value,
               DECODE (l.TYPE,
                       ‘TM’, l.id1,
                       ‘TX’, DECODE (l.request,
                                     0, NVL (lo.object_id, -1),
                                     s.row_wait_obj#
                                    ),
                       -1
                      ) AS object_id,
                 l.TYPE lock_type,
               DECODE (l.lmode,
                       0, ‘NONE’,
                       1, ‘NULL’,
                       2, ‘ROW SHARE’,
                       3, ‘ROW EXCLUSIVE’,
                       4, ‘SHARE’,
                       5, ‘SHARE ROW EXCLUSIVE’,
                       6, ‘EXCLUSIVE’,
                       ‘?’
                      ) mode_held,
               DECODE (l.request,
                       0, ‘NONE’,
                       1, ‘NULL’,
                       2, ‘ROW SHARE’,
                       3, ‘ROW EXCLUSIVE’,
                       4, ‘SHARE’,
                       5, ‘SHARE ROW EXCLUSIVE’,
                       6, ‘EXCLUSIVE’,
                       ‘?’
                      ) mode_requested,
               l.id1, l.id2, l.ctime time_in_mode,s.row_wait_obj#, s.row_wait_block#,
               s.row_wait_row#, s.row_wait_file#
          FROM gv$lock l,
               gv$session s,
               gv$process p,
               (SELECT object_id, session_id, xidsqn
                  FROM gv$locked_object
                 WHERE xidsqn > 0) lo
         WHERE l.inst_id = s.inst_id
           AND s.inst_id = p.inst_id
           AND s.SID = l.SID
           AND p.addr = s.paddr
           AND l.SID = lo.session_id(+)
           AND l.id2 = lo.xidsqn(+)) lid,
       SYS.obj$ o,
       SYS.user$ u
 WHERE o.obj#(+) = lid.object_id
 AND o.owner# = u.user#(+)
 AND object_id <> -1

 

If you are lazy and preffer GUI you can get same result  using OEM performance page and selecting database blocks

And yes , you can run this script using Toad so you get formated result. 🙂

Advertisements

Debugging serializable transactions.. ORA-08177: can’t serialize access for this transaction

How to find SQL statements that use SERIALIZABLE isolation level?

I have used  v$sql_optimizer_env ( not x$ table to make it less complicated ) to get this information.
If you like x$ tables than this v$ is based on  x$kqlfsqce table.There are two additional tables x$qkscesys
and  x$qksceses which keep hidden Optimizer Environment.See CBO book from JL and Julian Dyke site for more info..

Doug also has excellent post on these views:

http://oracledoug.com/serendipity/index.php?/archives/907-10g-Optimiser-Environment-Views.html

 So to get sql_text I have joined v$sql_optimizer_env and v$sql table.In my case GV$ because of the RAC.
.

select distinct s.sql_id ,s.sql_text,s.parsing_schema_name,o.name,o.value,o.isdefault
from gv$sql  s,gv$sql_optimizer_env o
where s.parsing_schema_name=’ADMIN’
and s.sql_id=o.sql_id
and o.name=’transaction_isolation_level’
and isdefault <> ‘YES’

 Output looks like:

                                                                        
8a7fgfsr5gffgqgtfqnn  SELECT ‘INSERT’ FROM DUAL WHERE NOT EXISTS (SELECT ‘IN THE QUEUE’)..  ADMIN transaction_isolation_level serializable   NO
 
 
Now I know which queries are involved and focus on the code.

You can always turn on tracing using 10046 and 10053 events  or have dump errostack on error:

alter system set events ‘8177 trace name errorstack level 3’
From Oracle documentation default for isolation level is read commited but it can be changed using

set transaction or   alter session command..
Some notes on SERIALIZABLE isolation level

Oracle Database stores control information in each data block to manage access by concurrent transactions.
To use the SERIALIZABLE isolation level, you must use the INITRANS clause
of the CREATE TABLE or ALTER TABLE command to set aside storage for this control information.
To use serializable mode, INITRANS must be set to at least 3.

This must be done at the CREATE TABLE/CREATE INDEX time to ensure that it is set for all blocks of the object.
Also  rebuilding table/index should do the trick..
  Oracle keeps note of which rows are locked by which transaction in an area
  at the top of each data block known as the ‘interested transaction list’.
  The number of ITL slots in any block in an object is controlled by
  the INITRANS and MAXTRANS attributes. INITRANS is the number of slots
  initially created in a block when it is first used, while MAXTRANS places
  an upper bound on the number of entries allowed. Each transaction which
  wants to modify a block requires a slot in this ‘ITL’ list in the block.

  INITRANS provides a minimum guaranteed ‘per-block’ concurrency.

Oracle recored  ITL Waits statistics in v$segment_statistics :
SELECT t.OWNER, t.OBJECT_NAME, t.OBJECT_TYPE, t.STATISTIC_NAME, t.VALUE
     FROM v$segment_statistics t
     WHERE t.STATISTIC_NAME = ‘ITL waits’
     AND t.owner <> ‘SYS’
     AND t.VALUE > 0
     order by t.owner,t.object_name;

 
 Increasing  INITRANS for objects that will experience many transactions updating the same block will enable Oracle to allocate sufficient storage in each block to
 record the history of recent transactions that accessed the block.
 
 Or  change  SERIALIZABLE back to read commited..
 
 If you are using ODP.NET  TransactionIsolationLevel  Specifies the Oracle transaction isolation level. The default is ReadCommitted.

Run away from GUI DBA.. Script to report waiting sessions for RAC

Found this one in my old DBA folder.. It’s time to use more sqlplus command line than Toad and OEM ( GUI DBA )..

 

set serverout on size 999999
     declare
     begin
     dbms_output.put_line(‘ ‘);
     dbms_output.put_line(‘************* Start report for WAITING sessions with current SQL ***************’);
     for x in (select vs.inst_id, vs.sid || ‘,’ || vs.serial# sidser, vs.sql_address, vs.sql_hash_value,
     vs.last_call_et, vsw.seconds_in_wait, vsw.event, vsw.state
     from gv$session_wait vsw, gv$session vs
     where vsw.sid = vs.sid
     and vsw.inst_id = vs.inst_id
     and vs.type <> ‘BACKGROUND’
     and vsw.event NOT IN (‘rdbms ipc message’
     ,’smon timer’
     ,’pmon timer’
     ,’SQL*Net message from client’
     ,’lock manager wait for remote message’
     ,’ges remote message’
     ,’gcs remote message’
     ,’gcs for action’
     ,’client message’
     ,’pipe get’
     ,’Null event’
     ,’PX Idle Wait’
     ,’single-task message’
     ,’PX Deq: Execution Msg’
     ,’KXFQ: kxfqdeq – normal deqeue’
     ,’listen endpoint status’
     ,’slave wait’
     ,’wakeup time manager’))
     loop
     begin
     dbms_output.put_line(‘Event WaitState InstID SidSerial LastCallEt SecondsInWait’);
     dbms_output.put_line(‘************************* ******************** ****** *********** ********** *************’);
     dbms_output.put_line(rpad(x.event,25) ||’ ‘|| rpad(x.state,20) ||’ ‘|| lpad(x.inst_id,6) ||’ ‘|| lpad(x.sidser,11) ||’
     ‘|| lpad(x.last_call_et,10) ||’ ‘|| lpad(x.seconds_in_wait,13));
     dbms_output.put_line(‘ SQLText ‘);
     dbms_output.put_line(‘****************************************************************’);
     for y in (select sql_text
     from gv$sqltext
     where address = x.sql_address
     and hash_value = x.sql_hash_value
     and inst_id = x.inst_id
     order by piece)
     loop
     dbms_output.put_line(y.sql_text);
     end loop;
     end;
     end loop;
     dbms_output.put_line(‘************** End report for sessions waiting with current SQL ****************’);
     dbms_output.put_line(‘ ‘);
     end;
     /

Output look like: Read more of this post

Capturing binds.

Capturing bind information using v$sql_bind_capture, v$sql_bind_metadata

Starting with 10g view v$sql_bind_capture displays bind variable metadata and values for a SQL cursor .

Suppose to be faster than sql_trace or  event 10046 at level 4

Demo:

SQL> var test number

SQL> exec :test :=10

PL/SQL procedure successfully completed.
SQL> select * from scott.emp where deptno=:test;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
———- ———- ——— ———- ——— ———- ———- ———-
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

SQL>  alter system set  “_cursor_bind_capture_interval”=1;

System altered.
SQL> select * from v$sql_bind_metadata where bind_name = ‘TEST’;

ADDRESS            POSITION   DATATYPE MAX_LENGTH  ARRAY_LEN BIND_NAME
—————- ———- ———- ———- ———- ——————————
00000000FD78FA38          1          2         22          0 TEST
00000000FD34E3F0          1          2         22          0 TEST
00000000FD7CF7F0          1          2         22          0 TEST
SQL>  select sql_text from v$sql where child_address in (‘00000000FD34E3F0′,’00000000FD7CF7F0′,’00000000FD78FA38’);

SQL_TEXT
———————————————————————————————————————————————————————–
select * from scott.emp where deptno=:test
SELECT :test test FROM DUAL
BEGIN :test :=10; END;
select sql_text,name,value_string,datatype_string
    from v$sql_bind_capture join v$sql using (hash_value)
    where sql_text like
      ‘%select * from scott.emp where deptno=:test%’;

SQL_TEXT                                           NAME                 VALUE_STRING    DATATYPE_STR WAS LAST_CAPT
————————————————– ——————– ————— ———— — ———
select * from scott.emp where deptno=:test         :TEST                10              NUMBER       YES 09-JAN-09

 Demo  for v$sql_bind_metadata

SQL> alter system flush shared_pool;

System altered.

SQL> var test number
SQL> set linesize 167
SQL> select * from v$sql_bind_metadata where bind_name = ‘TEST’;

no rows selected

Now trick using PRINT

SQL> print test

      TEST
———-
SQL> select * from v$sql_bind_metadata where bind_name = ‘TEST’;

ADDRESS                         POSITION   DATATYPE   MAX_LENGTH  ARRAY_LEN  BIND_NAME
—————-                    ———- ———- ———- ———- ——————————
00000000FD34E3F0              1                                   2         22             0                                             TEST

or directly from x$ table

SQL> select * from x$kksbv where KKSBVNNAM=’TEST’;

ADDR                   INDX    INST_ID KGLHDADR           POSITION   KKSCBNDT   KKSCBNDL   KKSCBNDA KKSBVNNAM
—————- ———- ———- —————- ———- ———- ———- ———- ——————————
00002AAAAC18A558        489          1 00000000FD34E3F0          1          2         22          0 TEST

SQL> select sql_text from v$sql where child_address=’00000000FD34E3F0′;

SQL_TEXT
——————————————-
SELECT :test test FROM DUAL

PRINT  use SELECT FROM DUAL …. 

Oracle recommends use of bind variables for your applications versus non-shared SQL. However too much binds can cause  library latch contention  (bug 3517658).

To check if there are many bind variables being used in system, the following query can be run:

select avg(bind_count) AVG_NUM_BINDS  from
(select sql_id, count(*) bind_count
from v$sql_bind_capture
where child_number = 0
group by sql_id);

But there is catch: Read more of this post

Script to extract sql code from trace file : trimsql.sh

Script to extract sql code from trace file generated using event 10046 e.g.

SQL> oradebug setmypid

SQL> oradebug event 10046 trace name context forever, level 12

or

SQL> alter session set events ‘10046 trace name context forever, level 12’;

Find  a trace:

SQL> oradebug tracefile_name;
/ora-main/app/oracle/admin/trace/demo11g_ora_19736.trc

Then run script like: 

trimsql.sh  <tracefile_name>    Read more of this post