Sokrates on Oracle

Archive for February, 2013

An undocumented restriction in Workspace Manager – exporting tables with valid time support

Posted by Matthias Rogel on 7. February 2013

If you are using Workspace Manager, it could be probably useful to know, that there is an undocumented restriction concerning import/export.
Due to Import and Export Considerations,
…Workspace Manager supports the import and export of version-enabled tables in one of the following two ways: a full database import and export, and a workspace-level import and export through Workspace Manager procedures. No other export modes, such as schema, table, or partition level, are currently supported….

However, this does not hold for tables with valid time support:

sokrates@11.2 > CREATE TABLE d (id NUMBER PRIMARY KEY);

Table created.

sokrates@11.2 > EXECUTE DBMS_WM.EnableVersioning (table_name=>'D', validTime=>TRUE, hist => 'NONE');

PL/SQL procedure successfully completed.

sokrates@11.2 >  EXECUTE DBMS_WM.Export(table_name => 'D',staging_table => 'D_STG', workspace => 'LIVE');
BEGIN DBMS_WM.Export(table_name => 'D',staging_table => 'D_STG', workspace => 'LIVE'); END;

ERROR at line 1:
ORA-20171: WM error: Export not supported on a table with valid time
ORA-06512: at "WMSYS.LT", line 13185
ORA-06512: at line 1

Support confirmed, that in this case only full db import/export (!) is supported, documentation would be updated somewhen.

Posted in Allgemein | Tagged: , , | Leave a Comment »


Posted by Matthias Rogel on 4. February 2013

The biggest advantage of being developer and DBA at the same time in my eyes is: tuning lies in one hand.
Peter Scott twittered about bringing a query down from 25 hours to 83 seconds by rewriting a query using MINUS.
Funny, in February 1996 I started a new job and my first task was tuning a query running for several hours – basically

select ...
from t
where not exists
( select ...
  from r@remote r
  where <join t and r>

which could be tuned down to a few seconds using MINUS, so quite similar to Peter’s job ( Version was 7.0.something at that time as far as I remember ).
In my experience, most performance gains are achieved by rewriting SQL or even by restructuring your entire application logic.

Posted in Allgemein | Leave a Comment »

what’s in my buffer cache ?

Posted by Matthias Rogel on 4. February 2013

The following SQL shows me what is currently in my buffer cache and runs in a reasonable amount of time ( never longer than 30 seconds with some dozens of GB buffer cache and around 5 million entries in v$cache ), it also shows me the cached percentage of each segment which is currently part of the cache.
Version is 11.2.

with cache_raw 
     c.owner#, c.kind,, c.partition_name, 
     c.status, c.file#, 
     count(*) co
  from v$cache c
  group by 
     c.owner#, c.kind,, c.partition_name,
     c.partition_name, file#, c.status
), cache_raw2
     du.username, c.kind, 
     case when c.partition_name is null
            else || ' partition ' || c.partition_name
     end as name,
     case c.status
        when 'free' then 'not currently in use'
        when 'xcur' then 'exclusive'
        when 'scur' then 'shared current'
        when 'cr' then 'consistent read'
        when 'read' then 'being read from disk'
        when 'mrec' then 'in media recovery mode'
        when 'irec' then 'in instance recovery mode'
     end as status,
     co * (select value from v$parameter where name='db_block_size') as anzb
  from cache_raw c, dba_users du
  where du.user_id(+)=c.owner#
), cache_segm as
     c.username as owner, c.kind, 
       when c.kind in ('INDEX', 'INDEX PARTITION')
       then || ' index on '|| ind.table_name
     end as name, 
     round(100 * c.anzb / sum(c.anzb) over (), 2) percentage,
     round(c.anzb / (1024 * 1024 * 1024), 2) as gbytes_in_cache, 
     round(100 * c.anzb / seg.bytes, 2) perc_of_segment_in_cache, 
     round(seg.bytes / (1024 * 1024 * 1024), 2) as gbytes_in_segment,   
     round(c.anzb / (1024 * 1024), 2) as mbytes_in_cache,
     round(seg.bytes / (1024 * 1024), 2) as mbytes_in_segment,   
     round(c.anzb / (1024), 2) as kbytes_in_cache,   
     round(seg.bytes / (1024), 2) as kbytes_in_segment,            
     c.anzb as bytes_in_cache, 
     seg.bytes as bytes_in_segment,   
     seg.segment_subtype, seg.tablespace_name
  from cache_raw2 c, dba_segments seg, dba_indexes ind
     case when seg.partition_name(+) is null
            then seg.segment_name(+)
            else seg.segment_name(+) || ' partition ' || seg.partition_name(+)
     end =
     and seg.segment_type(+)=c.kind
     ind.owner(+)=c.username and ind.index_name(+)=nvl(substr(, 1, instr(, ' ', 1) - 1),
     case row_number() over(order by c.bytes_in_cache desc) 
       when 1 then round(sum(c.bytes_in_cache) over () / (1024 * 1024 * 1024), 2) 
     end as gb_total,
     c.owner, c.kind,, 
     c.gbytes_in_cache, c.perc_of_segment_in_cache, 
     c.gbytes_in_segment, c.status, 
     c.mbytes_in_cache, c.mbytes_in_segment,   
     c.kbytes_in_cache, c.kbytes_in_segment,            
     c.bytes_in_cache, c.bytes_in_segment,   
     c.segment_subtype, c.tablespace_name
  from cache_segm c
  order by 
     c.bytes_in_cache desc

Posted in Allgemein | Tagged: | 2 Comments »