VARCHAR2 column length in a Multi Byte Characterset Database

When migrating data from a single-byte characterset database into a multi-byte characterset database, you will very likely encounter problems with column lenghts.

Oracle uses by default BYTE length sematics for VARCHAR2 columns.

As many characters need more than one byte in a multi-byte characterset database, the field lengths in BYTE will not be sufficient in many cases.

It will therefore be preferable in most cases to use CHAR length semantics in your multi-byte characterset database. This can be configured either on system or session level:

ALTER SYSTEM SET NLS_LENGTH_SEMANTICS=CHAR;
ALTER SESSION SET NLS_LENGTH_SEMANTICS=CHAR;

This way, you do not have to specify CHAR in each and every VARCHAR2 column definition.

SQL Monitoring in 11g

With Oracle 11g, especially R2, Oracle is making it very easy to get an overview of the SQL statements (both running and finished) and how they perform. See all cursors:

select dbms_sqltune.report_sql_monitor_list(type=>'HTML', report_level=>'ALL') from dual;

Show detailed information for any given SQL ID:

select dbms_sqltune.report_sql_monitor(type=>'ACTIVE', sql_id=>'SQL ID') monitor_report from dual;

There are many options available, such as reporting on the last statement executed in the session, etc.

select dbms_sqltune.report_sql_monitor(type=>'ACTIVE') monitor_report from dual;

--set echo off timing off pagesize 0 linesize 1000 trimspool on trim on
--set long 2000000 longchunksize 2000000
select dbms_sqltune.report_sql_monitor
       (session_id=>sys_context('userenv','sid')
       ,session_serial=>dbms_debug_jdwp.current_session_serial
       ,report_level=>'ALL'
       ,type=>'TEXT'
       ) as monitor_report
from   dual
;

Oracle Exadata Database Machine Tutorials

Our education partner, Skillbuilders, is pleased to announce that their first series of Oracle Exadata Tutorials are now available. The series consists of the following eleven sessions, all delivered by SkillBuilders Oracle Certified Master John Watson:

• About the Exadata Hardware
• What Makes the DB Machine Special
• Smart Scan in Theory
• How Smart Scan Functions - Demo 
• Smart Scan in Practice 
• Making Smart Scan Work - Demo
• HCC in Theory 
• HCC performance and Compression Ratio - Demo 
• HCC Limitations and Best Practices 
• HCC Compression Degradation Issue - Demo 
• Exadata is Good But Not Easy

11g Optimizer Statistics

Earlier this year, Oracle posted two excellent white papers about Optimizer Statistics:

• Understanding Optimizer Statistics (January 2012)
• Best Practices for Gathering Optimizer Statistics (April 2012)

They are worth reading!

Generate Optimizer Trace Files

After starting this post about generating optimizer trace files, I found that Greg Rahn has already written a perfect overview of all methods to achieve this here.

Upgrading to 11g: Optimizer Changes

When migrating to Oracle to a newer version, there is and will always be an impact on the performance of existing queries. Hence, it is wise to perform a full performance regression test with your software against the new version.

Oracle has published a good white paper which explains the new features of the Optimizer in 11g and lists important pre- and post-upgrade tasks. Check it out here: Upgrading from Oracle Database 10g to 11g: What to expect from the Optimizer.

Lock / Hang Analysis

When I have to analyze database lock or even hang issues, I use the following SQL statements, scripts and references:

-- all sessions involved in blocking
-- - blocked sessions
select * 
from   v$session ses
where  ses.blocking_session is not null
union
-- - blockers
select * 
from   v$session ses
where  sid in ( select blocking_session from v$session where blocking_session is not null )
;

-- find root blocker
select 'alter system kill session '''||sid||','||serial#||''' immediate;' kill_db_cmd
,      'kill -9 '||process as kill_os_cmd
,      s.*
from   v$session s
where  sid in ( 
   select blocking_session
   from   v$session
              )
and    blocking_session is null              
--and    username = 'DBUSER'
--and    osuser = 'osuser'
;

-- ddl lock information
select dlk.session_id
,      ses.serial#
,      ses.username
,      ses.schemaname
,      ses.machine
,      ses.logon_time
,      dlk.owner
,      dlk.name
,      dlk.type
,      dlk.mode_held
,      dlk.mode_requested
,      'alter system kill session '''||ses.sid||','||ses.serial#||''' immediate;'
from   dba_ddl_locks dlk
  inner join v$session ses
          on dlk.session_id = ses.sid
where (mode_held <> 'Null' or mode_requested <> 'None')
order by dlk.session_id    
;

-- library cache lock information
select distinct ses.ksusenum as sid
,      ses.ksuseser          as serial#
,      ses.ksuudlna          as username
,      ses.ksusemnm          as module
,      obj.kglnaown          as object_owner
,      obj.kglnaobj          as object_name
,      lck.kgllkcnt          as lock_count
,      lck.kgllkmod          as lock_mode
,      lck.kgllkreq          as lock_req
,      swt.state             as wait_state
,      swt.event             as wait_event
,      swt.seconds_in_wait   as sec_in_wait
from   x$kgllk lck        -- library object locks (both held and requested)
,      x$kglob obj        -- objects
,      x$ksuse ses        -- sessions
,      v$session_wait swt -- session wait
where  lck.kgllkreq > 0   -- a lock is requested
and    obj.kglhdadr = lck.kgllkhdl
and    lck.kgllkuse = ses.addr
and    swt.sid = ses.indx
order by swt.seconds_in_wait desc
;

sta ?/rdbms/admin/utllockt
--WAITING_SESSION   LOCK_TYPE         MODE_REQUESTED MODE_HELD      LOCK_ID1          LOCK_ID2
------------------- ----------------- -------------- -------------- ----------------- -----------------
--524               None
--   511            Transaction       Exclusive      Exclusive      1835044           8310

-- it might also be of interest to look into ash
-- history, query coordinator, etc
select * 
from   v$active_session_history
where  time_waited > 0
;

-- displays a row for each lock or latch that is being held, and one row for each outstanding request for a lock or latch.
--  http://download.oracle.com/docs/cd/B14117_01/server.101/b10755/statviews_2210.htm
select * 
from   dba_lock_internal
;

-- all locked objects
select oracle_username
,      os_user_name
,      session_id
,      locked_mode
,      object_name
,      object_type
from   v$locked_object lob
,      dba_objects obj
where  lob.object_id = obj.object_id
order by oracle_username
,        os_user_name
,        session_id
;

-- enqueues
select sid
,      event
,      chr(bitand(p1,-16777216)/16777215)|| chr(bitand(p1, 16711680)/65535) "type"
,      mod(p1,16) as "mode" 
,      p2
,      p3 
from   v$session_wait 
where  event like 'enq%'
; 

-- cumulated statistics on enqueues
select * 
from   v$enqueue_statistics
;


-- see who accesses an object
select *
from   v$access
where  object like 'BLA'
;

-- oradebug dump systemstate
-- - shows complete dump of all processes including locks held/requested
-- - dump created in user dump dest
-- - connect as sysdba
oradebug setmypid
oradebug unlimit
oradebug dump systemstate 266
oradebug tracefile_name

Links / References:

• MOS: How to Find which Session is Holding a Particular Library Cache Lock [ID 122793.1]
• http://orainternals.wordpress.com/2009/06/02/library-cache-lock-and-library-cache-pin-waits/
• http://www.evdbt.com/enqwaits.sql
• http://www.perfvision.com/papers/09_enqueues.ppt