Search

OakieTags

Who's online

There are currently 0 users and 38 guests online.

Recent comments

Affiliations

Oakies Blog Aggregator

How To Cancel A Query Running In Another Session

This is not really anything new - in fact Tanel Poder has already blogged about it a while ago. Tanel has specifically covered the handling of "urgent" TCP packets and how this could be used to signal a "cancel" to another process, however this only works on Unix environments and not with Windows SQL*Plus clients. In Tanel's article it is also mentioned that there is an officially documented way of doing this via the Resource Manager if you happen to have an Enterprise Edition license.

In my quick tests however the call to DBMS_RESOURCE_MANAGER.SWITCH_CONSUMER_GROUP_FOR_SESS using "CANCEL_SQL" as consumer group only errors out with ORA-29366 saying that the specified consumer group is invalid.

So ideally there should be an approach that is independent from client or server O/S or license details, and indeed there is one, however it is using an undocumented event and therefore is unsupported and can only be used at your own risk.

If you set event 10237 ("ORA-10237: simulate ^C (for testing purposes)") in a session to any level greater 0 then any currently running and future execution will be "cancelled", so once the cancellation was successful the event needs to be unset otherwise the session will be in an unusable state cancelling any further attempts (applies even if the "lifetime 1" clause is used instead of "forever" when using ORADEBUG to set the event).

So a simple script like the following should be sufficient to cancel a current execution in another session without the need to kill the session.

--------------------------------------------------------
--
-- simulate_control_c.sql
--
-- Purpose:
--
-- Sets event 10237 in a session to simulate
-- pressing CONTROL+C for that session
--
-- Allows to cancel a running SQL statement from
-- a remote session without killing the session
--
-- If the session is stuck on the server side
-- which means that it can't be killed this
-- probably won't help either
--
-- Requirements:
--
-- EXECUTE privilege on SYS.DBMS_SYSTEM
-- SELECT privilege on V$SESSION
--
-- Usage:
--
-- @simulate_control_c
--
-- Note:
--
-- The usage of that event is undocumented
-- Therefore use at your own risk!
-- Provided for free, without any warranties -
-- test this before using it on anything important
--
-- Other implementation ideas:
--
-- The following code is supposed to achieve the same on Enterprise Edition
-- and enabled Resource Manager in a documented way
-- In all versions tested (10.2.0.4, 11.1.0.7, 11.2.0.1, 11.2.0.2) I get however
-- ORA-29366 and it doesn't work as described
-- Note that the official documentation doesn't explicitly mention CANCEL_SQL as
-- valid consumer group for this call

-- begin
-- sys.dbms_resource_manager.switch_consumer_group_for_sess(
-- ,,'CANCEL_SQL'
-- );
-- end;
--
-- When running on Unix KILL -URG sent to the server process
-- should also simulate a Control-C
-- This doesn't work with Windows SQL*Plus clients though
--
-- See Tanel Poder's blog post for more info
-- http://blog.tanelpoder.com/2010/02/17/how-to-cancel-a-query-running-in-a...
--
-- Author:
--
-- Randolf Geist
-- http://oracle-randolf.blogspot.com
--
-- Versions tested:
--
-- 11.2.0.1 Server+Client
-- 10.2.0.4 Server
-- 11.2.0.2 Server
--
--------------------------------------------------------

set echo off verify off feedback off

column sid new_value v_sid noprint
column serial# new_value v_serial noprint

-- Get details from V$SESSION
select
sid
, serial#
from
v$session
where
sid = to_number('&1')
and status = 'ACTIVE'
;

declare
-- Avoid compilation errors in case of SID not found
v_sid number := to_number('&v_sid');
v_serial number := to_number('&v_serial');
v_status varchar2(100);
-- 60 seconds default timeout
n_timeout number := 60;
dt_start date := sysdate;
begin
-- SID not found
if v_sid is null then
raise_application_error(-20001, 'SID: &1 cannot be found or is not in STATUS=ACTIVE');
else
-- Set event 10237 to level 1 in session to simulate CONTROL+C
sys.dbms_system.set_ev(v_sid, v_serial, 10237, 1, '');
-- Check session state
loop
begin
select
status
into
v_status
from
v$session
where
sid = v_sid;
exception
-- SID no longer found
when NO_DATA_FOUND then
raise_application_error(-20001, 'SID: ' || v_sid || ' no longer found after cancelling');
end;

-- Status no longer active
-- then set event level to 0 to avoid further cancels
if v_status != 'ACTIVE' then
sys.dbms_system.set_ev(v_sid, v_serial, 10237, 0, '');
exit;
end if;

-- Session still active after timeout exceeded
-- Give up
if dt_start + (n_timeout / 86400) < sysdate then
sys.dbms_system.set_ev(v_sid, v_serial, 10237, 0, '');
raise_application_error(-20001, 'SID: ' || v_sid || ' still active after ' || n_timeout || ' seconds');
end if;

-- Back off after 5 seconds
-- Check only every second from then on
-- Avoids burning CPU and potential contention by this loop
-- However this means that more than a single statement potentially
-- gets cancelled during this second
if dt_start + (5 / 86400) < sysdate then
dbms_lock.sleep(1);
end if;
end loop;
end if;
end;
/

It is particularly useful in Windows environments where the SQL*Plus executable by default doesn't allow cancelling a current execution by pressing Control+C - it works only while fetching or pressing it a second time, terminating the whole SQL*Plus client.

Note that Tanel's method is probably able to cancel queries that this approach cannot cancel because the URGENT signal handler under Unix effectively causes an interrupt to the running process executing the corresponding handler code whereas the event set here has to be actively checked by the code of the running process.

EXPLAIN PLAN shared memory

Recently I did an investigation of an ORA-04031 which happens almost regularly on a 10.2.0.4 Oracle database server with 9G of memory allocated to shared pool and disabled Automatic Shared Memory Management (sga_target=0). The problem query is a report that is a very big SQL query – more than 200K of plain text. The exact error message is “ORA-04031: unable to allocate 235704 bytes of shared memory (“shared pool”,”select * from (…”,”Typecheck”,”qry_text : qcpisqt”)”. Here I’ll briefly describe what I did to identify root cause of the error and will talk about one interesting detail of the EXPLAIN PLAN as it was used to reproduce the issue.

So there’re quite a number of good docs out there on the subject of ORA-04031, such as

  • ORA-04031 errors and monitoring shared pool subpool memory utilization with sgastatx.sql
  • Oracle Troubleshooting TV Show: Season 1, Episode 01 ;-)
  • Even before I started to investigate the issue I was almost 100% sure that the root cause is memory fragmentation and just too large query that cannot find a continuous memory chunk. I tried to confirm my assumption on a development database with 2GB shared pool. The error was reproduced here as well when I tried to EXPLAIN PLAN for the query I didn’t have access to the X$ stuff on this DB to run some queries, but I had ALTER SESSION grant and was able to get a heap dump of the Shared Pool with

    alter session set events '4031 trace name heapdump level 2';

    Then I used script heapdump_analyzer from TPT to aggregate trace results and see how many usable chunks of size greater than 200000 bytes available. Not many:





    Total_size #Chunks Chunk_siz From_heap, Chunk_type, Alloc_reason
    22666392 27 839496 heap(4,0), R-free,  
    22666392 27 839496 heap(3,0), R-free,  
    22666392 27 839496 heap(2,0), R-free,  
    20020800 86 232800 heap(1,0), R-freeable, sql area
    4190296 1 4190296 heap(1,0), recreate, KSFD SGA I/O b
    1044584 1 1044584 heap(1,0), recreate, PX subheap
    626576 1 626576 heap(2,0), R-free,  
    617112 1 617112 heap(3,0), R-free,  
    538048 2 269024 heap(4,0), freeable, PX msg pool
    351208 1 351208 heap(3,0), R-free,  
    347808 1 347808 heap(4,0), R-free,  
    308568 1 308568 heap(4,0), freeable, character set o
    269024 1 269024 heap(3,0), freeable, PX msg pool
    267360 1 267360 heap(3,0), R-free,  
    242016 1 242016 heap(4,0), R-free,  
    232800 1 232800 heap(1,0), freeable, sql area
    220840 1 220840 heap(1,0), free,  
    216632 1 216632 heap(1,0), free,  
    215392 1 215392 heap(1,0), free,  
    211344 1 211344 heap(1,0), free,  
    202856 1 202856 heap(2,0), R-free,  
    202520 1 202520 heap(2,0), R-free,  
    201744 1 201744 heap(1,0), free,  

    So there are very few chunks that are free; there’s no free chunk of appropriate (235704 bytes) size in sub-pool 1. And this was actually with half-full shared pool, i.e. v$sgastat reported 1GB of free memory out of 2GB shared pool. But still it was too fragmented to find a single big enough memory piece.
    The interesting part begins when the query that is constantly failing with ORA-04031 when doing EXPLAIN PLAN, executes perfectly well many times, even with a hard parse! That was really surprising and hard to explain. A co-worker of mine noticed that it could be due to a simple but also unclear fact that EXPLAIN PLAN shared cursor uses more memory – much more actually. Here is a simple script to demonstrate it with very straightforward query:

    set serveroutput off
    col uq_id new_value uq_id
    select to_char(sysdate, 'YYYYMMDDHH24MISS') uq_id from dual;
    
    select /*+ &uq_id */ count(*) from dual;
    
    select sharable_mem
      from v$sql 
     where sql_id = (select s.prev_sql_id from v$session s where sid = userenv('sid'))
       and child_number = (select s.prev_child_number from v$session s where sid = userenv('sid'));
    
    explain plan for select /*+ &uq_id */ count(*) from dual;
    
    select sharable_mem
      from v$sql 
     where sql_id = (select s.prev_sql_id from v$session s where sid = userenv('sid'))
       and child_number = (select s.prev_child_number from v$session s where sid = userenv('sid'));
    

    Here I’m using a specific string inside a comment to make sure that shared cursor has exactly 1 child cursor. And that is results I’ve got running script on different Oracle versions:

    Version Normal query EXPLAIN PLAN
    10.2.0.5 32-bit 8996 9289
    10.2.0.4 64-bit 10584 15321
    11.2.0.2 64-bit 15600 36881
    11.2.0.3 64-bit 15624 36905 (actual memory allocated appears to be much higher than reported)

    It’s clear that something is making EXPLAIN PLAN sharable cursors bigger for some reason. Thanks to Jonathan Lewis I found a MOS Bug 6242723: EXPLAIN PLAN FOR DOES USE MORE MEMORY (closed as not a bug) which explains what happened in Oracle version 10g: some memory, such as “typecheck” that was put into CGA in previous versions is now placed into SGA. There’s also described a way to see what’s in this memory. For some reason I wasn’t able to get the data the same way as described in the document. I did it little bit differently, but logically it’s the same way, /me thinks. It was done on the 11.2.0.3 @ Win7 64-bit.
    Dump library cache at some very high level (not a good idea trying this on a loaded system):

    SQL> alter session set events 'immediate trace name library_cache level 16';
    
    Session altered.
    

    In the trace file found two library cache objects corresponding to the two statements with all information about heaps that are being used by the cursors. This is it cut to a minimum:

    Bucket: #=49063 Mutex=000007FF59A09748(0, 11, 0, 6)
      LibraryHandle:  Address=000007FF5D435088 Hash=d36abfa7 LockMode=0 PinMode=0 LoadLockMode=0 Status=VALD
        ObjectName:  Name=select /*+ 20111124204027 */ count(*) from dual
          FullHashValue=669e3c9bfa2348f7de703912d36abfa7 Namespace=SQL AREA(00) Type=CURSOR(00) Identifier=3546988455 OwnerIdn=90
    ...
        LibraryObject:  Address=000007FF5732E0E0 HeapMask=0000-0001-0001-0000 Flags=EXS[0000] Flags2=[0000] PublicFlags=[0000]
          DataBlocks:
            Block:  #='0' name=KGLH0^d36abfa7 pins=0 Change=NONE
              Heap=000007FF5D434FD0 Pointer=000007FF5732E180 Extent=000007FF5732E060 Flags=I/-/P/A/-/-
              FreedLocation=0 Alloc=2.437500 Size=3.976563 LoadTime=2667103
          ChildTable:  size='16'
            Child:  id='0' Table=000007FF5732EF90 Reference=000007FF5732E9D0 Handle=000007FF5D434C28
          Children:
            Child:  childNum='0'
    ...
                  DataBlocks:
                    Block:  #='0' name=KGLH0^d36abfa7 pins=0 Change=NONE
                      Heap=000007FF5D434B70 Pointer=000007FF5732D180 Extent=000007FF5732D060 Flags=I/-/-/A/-/-
                      FreedLocation=0 Alloc=2.500000 Size=3.937500 LoadTime=2667103
                    Block:  #='6' name=SQLA^d36abfa7 pins=0 Change=NONE
                      Heap=000007FF5732E7A0 Pointer=000007FF53FDAC88 Extent=000007FF53FDA030 Flags=I/-/-/A/-/E
                      FreedLocation=0 Alloc=4.953125 Size=7.898438 LoadTime=0
                NamespaceDump:
                  Child Cursor:  Heap0=000007FF5732D180 Heap6=000007FF53FDAC88 Heap0 Load Time=11-24-2011 20:40:26 Heap6 Load Time=11-24-2011 20:40:26
        NamespaceDump:
          Parent Cursor:  sql_id=dww1t2b9qpgx7 parent=000007FF5732E180 maxchild=1 plk=n ppn=n
    
    Bucket: #=95957 Mutex=000007FF59BD3678(0, 9, 0, 6)
      LibraryHandle:  Address=000007FF5D3F7918 Hash=60ed76d5 LockMode=0 PinMode=0 LoadLockMode=0 Status=VALD
        ObjectName:  Name=explain plan for select /*+ 20111124204027 */ count(*) from dual
          FullHashValue=c6f3b5c0c9bd54209f7b684560ed76d5 Namespace=SQL AREA(00) Type=CURSOR(00) Identifier=1626175189 OwnerIdn=90
    ...
        LibraryObject:  Address=000007FF5731B0E0 HeapMask=0000-0001-0001-0000 Flags=EXS[0000] Flags2=[0000] PublicFlags=[0000]
          DataBlocks:
            Block:  #='0' name=KGLH0^60ed76d5 pins=0 Change=NONE
              Heap=000007FF5D3F7860 Pointer=000007FF5731B180 Extent=000007FF5731B060 Flags=I/-/P/A/-/-
              FreedLocation=0 Alloc=2.437500 Size=3.976563 LoadTime=2667164
          ChildTable:  size='16'
            Child:  id='0' Table=000007FF5731BF90 Reference=000007FF5731B9D0 Handle=000007FF5D3F76C8
          Children:
            Child:  childNum='0'
    ...
                  DataBlocks:
                    Block:  #='0' name=KGLH0^60ed76d5 pins=0 Change=NONE
                      Heap=000007FF5D3F7610 Pointer=000007FF5731A180 Extent=000007FF5731A060 Flags=I/-/-/A/-/-
                      FreedLocation=0 Alloc=2.500000 Size=3.937500 LoadTime=2667164
                    Block:  #='6' name=SQLA^60ed76d5 pins=0 Change=NONE
                      Heap=000007FF5731B7A0 Pointer=000007FF53F91C88 Extent=000007FF53F91030 Flags=I/-/-/A/-/E
                      FreedLocation=0 Alloc=119.320313 Size=123.000000 LoadTime=0
                NamespaceDump:
                  Child Cursor:  Heap0=000007FF5731A180 Heap6=000007FF53F91C88 Heap0 Load Time=11-24-2011 20:40:26 Heap6 Load Time=11-24-2011 20:40:26
        NamespaceDump:
          Parent Cursor:  sql_id=9yyv88phfuxqp parent=000007FF5731B180 maxchild=1 plk=n ppn=n
    

    For each cursor there’s a list with one child cursor in it. Each child cursor, in turn, has references to two sub-heaps, 0 and 6. Dump of memory for sub-heaps 6:

    SQL> alter session set events 'immediate trace name heapdump_addr address 0x000007FF5732E7A0';
    
    Session altered.
    
    SQL> alter session set events 'immediate trace name heapdump_addr address 0x000007FF5731B7A0';
    
    Session altered.
    

    And this is a comparison of what is in trace for normal query and for the query with EXPLAIN PLAN:

    Normal query EXPLAIN PLAN
    HEAP DUMP heap name="SQLA^d36abfa7"  desc=000007FF5732E7A0
     extent sz=0xfe8 alt=32767 het=368 rec=0 flg=2 opc=2
     parent=00000001492BD1C0 owner=000007FF5732E660 nex=0000000000000000 xsz=0xfe8 heap=0000000000000000
     fl2=0x27, nex=0000000000000000, dsxvers=1, dsxflg=0x0
     dsx first ext=0x53fda030
    EXTENT 0 addr=000007FF53FD9018
      Chunk      7ff53fd9028 sz=     2936    free      "               "
      Chunk      7ff53fd9ba0 sz=       40    freeable  "kggsmInitCompac"
      Chunk      7ff53fd9bc8 sz=       32    freeable  "kggsmInitCompac"
      Chunk      7ff53fd9be8 sz=       32    freeable  "kggsmInitCompac"
      Chunk      7ff53fd9c08 sz=       40    freeable  "kggsmInitCompac"
      Chunk      7ff53fd9c30 sz=       32    freeable  "kggsmInitCompac"
      Chunk      7ff53fd9c50 sz=      144    freeable  "kggsmCommonInit"
      Chunk      7ff53fd9ce0 sz=       80    freeable  "kggsmInit:sm   "
      Chunk      7ff53fd9d30 sz=       40    freeable  "idndef : qcuAll"
      Chunk      7ff53fd9d58 sz=       48    freeable  "qeSel: qkxrXfor"
      Chunk      7ff53fd9d88 sz=       48    freeable  "kggac: kggacCre"
      Chunk      7ff53fd9db8 sz=       40    freeable  "idndef : qcuAll"
      Chunk      7ff53fd9de0 sz=       40    freeable  "idndef : qcuAll"
      Chunk      7ff53fd9e08 sz=      104    freeable  "opiprwd : opitc"
      Chunk      7ff53fd9e70 sz=      232    freeable  "pqctx:kkfdParal"
      Chunk      7ff53fd9f58 sz=       80    freeable  "ctxPlanSig:qksc"
      Chunk      7ff53fd9fa8 sz=       88    freeable  "KGHSC_ALLOC_BUF"
    EXTENT 1 addr=000007FF53FDA030
      Chunk      7ff53fda040 sz=       80    perm      "perm           "  alo=80
      Chunk      7ff53fda090 sz=       40    freeable  "idndef : qcuAll"
      Chunk      7ff53fda0b8 sz=       32    freeable  "kksoff : opitca"
      Chunk      7ff53fda0d8 sz=       40    freeable  "qeeRwo: qeeCrea"
      Chunk      7ff53fda100 sz=       64    freeable  "kksol : kksnsg "
      Chunk      7ff53fda140 sz=      128    freeable  "qeeOpt: qeesCre"
      Chunk      7ff53fda1c0 sz=       40    freeable  "qeeRwo: qeeCrea"
      Chunk      7ff53fda1e8 sz=      216    freeable  "qergss:qergsAll"
      Chunk      7ff53fda2c0 sz=       40    freeable  "qeeRwo: qeeCrea"
      Chunk      7ff53fda2e8 sz=       88    freeable  "qerfis : rfialo"
      Chunk      7ff53fda340 sz=       72    freeable  "qksmm: qksmmCs "
      Chunk      7ff53fda388 sz=       40    freeable  "xplGenXpl:planL"
      Chunk      7ff53fda3b0 sz=       32    freeable  "qkaReorderAggs."
      Chunk      7ff53fda3d0 sz=       96    freeable  "qkaapd : qkaqkn"
      Chunk      7ff53fda430 sz=      400    freeable  "opixpop:kctdef "
      Chunk      7ff53fda5c0 sz=       32    freeable  "opixfalo:froaty"
      Chunk      7ff53fda5e0 sz=       32    freeable  "opixfalo:ctxkct"
      Chunk      7ff53fda600 sz=       40    freeable  "chedef : qcuatc"
      Chunk      7ff53fda628 sz=       32    freeable  "kobjn : kkdcchs"
      Chunk      7ff53fda648 sz=       64    freeable  "cxach : opiSem "
      Chunk      7ff53fda688 sz=       80    freeable  "ctxqrol : kkqsr"
      Chunk      7ff53fda6d8 sz=       48    freeable  "qksrcMarkQB:qks"
      Chunk      7ff53fda708 sz=       48    freeable  "ktamd : ktagmd "
      Chunk      7ff53fda738 sz=       40    freeable  "chedef : qcuatc"
      Chunk      7ff53fda760 sz=      136    freeable  "audRegFro:audta"
      Chunk      7ff53fda7e8 sz=       40    freeable  "idndef : qcuAll"
      Chunk      7ff53fda810 sz=      400    freeable  "kctdef : qcdlgo"
      Chunk      7ff53fda9a0 sz=       40    freeable  "idndef : qcuAll"
      Chunk      7ff53fda9c8 sz=       40    freeable  "idndef : qcuAll"
      Chunk      7ff53fda9f0 sz=       64    freeable  "unmdef in opipr"
      Chunk      7ff53fdaa30 sz=       48    freeable  "qctctx: kkmqccr"
      Chunk      7ff53fdaa60 sz=       72    freeable  "qcsctx: kkmqccr"
      Chunk      7ff53fdaaa8 sz=       40    freeable  "qcptgc: kkmqccr"
      Chunk      7ff53fdaad0 sz=       80    freeable  "qcpctx: kkmqccr"
      Chunk      7ff53fdab20 sz=       56    freeable  "qcmemctx : kkmq"
      Chunk      7ff53fdab58 sz=      152    freeable  "qcctx : kkmqccr"
      Chunk      7ff53fdabf0 sz=       64    freeable  "kksol : kkscuf "
      Chunk      7ff53fdac30 sz=       64    freeable  "kksol : kkscuf "
      Chunk      7ff53fdac70 sz=      912    freeable  "ctxdef:kksLoadC"
    Total heap size    =     8088
    FREE LISTS:
     Bucket 0 size=152
      Chunk      7ff53fda060 sz=        0    kghdsx
     Bucket 1 size=280
     Bucket 2 size=536
     Bucket 3 size=1048
     Bucket 4 size=2072
      Chunk      7ff53fd9028 sz=     2936    free      "               "
     Bucket 5 size=4120
     Bucket 6 size=4144
     Bucket 7 size=4168
     Bucket 8 size=4192
     Bucket 9 size=4216
    Total free space   =     2936
    UNPINNED RECREATABLE CHUNKS (lru first):
    PERMANENT CHUNKS:
      Chunk      7ff53fda040 sz=       80    perm      "perm           "  alo=80
    Permanent space    =       80
    
    HEAP DUMP heap name="SQLA^60ed76d5"  desc=000007FF5731B7A0
     extent sz=0xfe8 alt=32767 het=368 rec=0 flg=2 opc=2
     parent=00000001492BD1C0 owner=000007FF5731B660 nex=0000000000000000 xsz=0xfe8 heap=0000000000000000
     fl2=0x27, nex=0000000000000000, dsxvers=1, dsxflg=0x0
     dsx first ext=0x53f91030
    EXTENT 0 addr=000007FF53F72F28
      Chunk      7ff53f72f38 sz=     3688    free      "               "
      Chunk      7ff53f73da0 sz=       40    freeable  "kggsmInitCompac"
      Chunk      7ff53f73dc8 sz=       32    freeable  "kggsmInitCompac"
      Chunk      7ff53f73de8 sz=       32    freeable  "kggsmInitCompac"
      Chunk      7ff53f73e08 sz=       40    freeable  "kggsmInitCompac"
      Chunk      7ff53f73e30 sz=      144    freeable  "kggsmCommonInit"
      Chunk      7ff53f73ec0 sz=       80    freeable  "kggsmInit:sm   "
    EXTENT 1 addr=000007FF53F73F28
      Chunk      7ff53f73f38 sz=     4056    freeable  "TCHK^60ed76d5  "  ds=000007FF53F91B38
    EXTENT 2 addr=000007FF53F74F28
      Chunk      7ff53f74f38 sz=     4056    freeable  "TCHK^60ed76d5  "  ds=000007FF53F91B38
    EXTENT 3 addr=000007FF53F75F28
      Chunk      7ff53f75f38 sz=     4056    freeable  "TCHK^60ed76d5  "  ds=000007FF53F91B38
    EXTENT 4 addr=000007FF53F76F28
      Chunk      7ff53f76f38 sz=     4056    freeable  "TCHK^60ed76d5  "  ds=000007FF53F91B38
    EXTENT 5 addr=000007FF53F77F28
      Chunk      7ff53f77f38 sz=     4056    freeable  "TCHK^60ed76d5  "  ds=000007FF53F91B38
    EXTENT 6 addr=000007FF53F78F28
      Chunk      7ff53f78f38 sz=     4056    freeable  "TCHK^60ed76d5  "  ds=000007FF53F91B38
    EXTENT 7 addr=000007FF53F79F28
      Chunk      7ff53f79f38 sz=     4056    freeable  "TCHK^60ed76d5  "  ds=000007FF53F91B38
    EXTENT 8 addr=000007FF53F7AF28
      Chunk      7ff53f7af38 sz=     4056    freeable  "TCHK^60ed76d5  "  ds=000007FF53F91B38
    EXTENT 9 addr=000007FF53F7BF28
      Chunk      7ff53f7bf38 sz=     4176    freeable  "TCHK^60ed76d5  "  ds=000007FF53F91B38
    EXTENT 10 addr=000007FF53F7CFA0
      Chunk      7ff53f7cfb0 sz=     4176    freeable  "TCHK^60ed76d5  "  ds=000007FF53F91B38
    EXTENT 11 addr=000007FF53F7E018
      Chunk      7ff53f7e028 sz=     4056    freeable  "TCHK^60ed76d5  "  ds=000007FF53F91B38
    EXTENT 12 addr=000007FF53F7F018
      Chunk      7ff53f7f028 sz=     4056    freeable  "TCHK^60ed76d5  "  ds=000007FF53F91B38
    EXTENT 13 addr=000007FF53F80018
      Chunk      7ff53f80028 sz=     4056    freeable  "TCHK^60ed76d5  "  ds=000007FF53F91B38
    EXTENT 14 addr=000007FF53F81018
      Chunk      7ff53f81028 sz=       64    freeable  "kggsmInitCompac"
      Chunk      7ff53f81068 sz=       48    freeable  "qeSel: qkxrXfor"
      Chunk      7ff53f81098 sz=       48    freeable  "kggac: kggacCre"
      Chunk      7ff53f810c8 sz=       40    freeable  "idndef : qcuAll"
      Chunk      7ff53f810f0 sz=       40    freeable  "idndef : qcuAll"
      Chunk      7ff53f81118 sz=       40    freeable  "idndef : qcuAll"
      Chunk      7ff53f81140 sz=      232    freeable  "pqctx:kkfdParal"
      Chunk      7ff53f81228 sz=       80    freeable  "ctxPlanSig:qksc"
      Chunk      7ff53f81278 sz=       88    freeable  "KGHSC_ALLOC_BUF"
      Chunk      7ff53f812d0 sz=       40    freeable  "qeeRwo: qeeCrea"
      Chunk      7ff53f812f8 sz=       64    freeable  "kksol : kksnsg "
      Chunk      7ff53f81338 sz=      128    freeable  "qeeOpt: qeesCre"
      Chunk      7ff53f813b8 sz=       40    freeable  "qeeRwo: qeeCrea"
      Chunk      7ff53f813e0 sz=      216    freeable  "qergss:qergsAll"
      Chunk      7ff53f814b8 sz=       40    freeable  "qeeRwo: qeeCrea"
      Chunk      7ff53f814e0 sz=       88    freeable  "qerfis : rfialo"
      Chunk      7ff53f81538 sz=       72    freeable  "qksmm: qksmmCs "
      Chunk      7ff53f81580 sz=       40    freeable  "xplGenXpl:planL"
      Chunk      7ff53f815a8 sz=       32    freeable  "qkaReorderAggs."
      Chunk      7ff53f815c8 sz=       96    freeable  "qkaapd : qkaqkn"
      Chunk      7ff53f81628 sz=      400    freeable  "opixpop:kctdef "
      Chunk      7ff53f817b8 sz=       32    freeable  "opixfalo:froaty"
      Chunk      7ff53f817d8 sz=       32    freeable  "opixfalo:ctxkct"
      Chunk      7ff53f817f8 sz=       40    freeable  "chedef : qcuatc"
      Chunk      7ff53f81820 sz=       32    freeable  "kobjn : kkdcchs"
      Chunk      7ff53f81840 sz=       64    freeable  "cxach : opiSem "
      Chunk      7ff53f81880 sz=       80    freeable  "ctxqrol : kkqsr"
      Chunk      7ff53f818d0 sz=       48    freeable  "qksrcMarkQB:qks"
      Chunk      7ff53f81900 sz=       48    freeable  "ktamd : ktagmd "
      Chunk      7ff53f81930 sz=       40    freeable  "chedef : qcuatc"
      Chunk      7ff53f81958 sz=      136    freeable  "audRegFro:audta"
      Chunk      7ff53f819e0 sz=       40    freeable  "idndef : qcuAll"
      Chunk      7ff53f81a08 sz=      400    freeable  "kctdef : qcdlgo"
      Chunk      7ff53f81b98 sz=       40    freeable  "idndef : qcuAll"
      Chunk      7ff53f81bc0 sz=       40    freeable  "idndef : qcuAll"
      Chunk      7ff53f81be8 sz=       88    freeable  "qbpdef: qekbCre"
      Chunk      7ff53f81c40 sz=       40    freeable  "chedef : qcuatc"
      Chunk      7ff53f81c68 sz=       40    freeable  "idndef : qcuAll"
      Chunk      7ff53f81c90 sz=      568    freeable  "frodef:qcpitnm "
      Chunk      7ff53f81ec8 sz=       48    freeable  "idndef : qcpiex"
      Chunk      7ff53f81ef8 sz=       40    freeable  "chedef : qcuatc"
      Chunk      7ff53f81f20 sz=      112    freeable  "optdef: qcopCre"
      Chunk      7ff53f81f90 sz=      112    freeable  "qcpifqtqc : qcs"
    EXTENT 15 addr=000007FF53F82018
      Chunk      7ff53f82028 sz=     4056    freeable  "qbcqtcHTHeap   "  ds=000007FF53F91310
    EXTENT 16 addr=000007FF53F83018
      Chunk      7ff53f83028 sz=       40    freeable  "idndef : qcuAll"
      Chunk      7ff53f83050 sz=       64    freeable  "seldef: qcopCre"
      Chunk      7ff53f83090 sz=     3952    freeable  "qbcqtcHTHeap   "  ds=000007FF53F91310
    EXTENT 17 addr=000007FF53F84018
      Chunk      7ff53f84028 sz=     4056    freeable  "qbcqtcHTHeap   "  ds=000007FF53F91310
    EXTENT 18 addr=000007FF53F85018
      Chunk      7ff53f85028 sz=       64    freeable  "opldef: qcopCre"
      Chunk      7ff53f85068 sz=       40    freeable  "idndef : qcuAll"
      Chunk      7ff53f85090 sz=     3952    freeable  "qbcqtcHTHeap   "  ds=000007FF53F91310
    EXTENT 19 addr=000007FF53F86018
      Chunk      7ff53f86028 sz=     4056    freeable  "TCHK^60ed76d5  "  ds=000007FF53F91B38
    EXTENT 20 addr=000007FF53F87018
      Chunk      7ff53f87028 sz=     4056    freeable  "TCHK^60ed76d5  "  ds=000007FF53F91B38
    EXTENT 21 addr=000007FF53F88018
      Chunk      7ff53f88028 sz=     4056    freeable  "TCHK^60ed76d5  "  ds=000007FF53F91B38
    EXTENT 22 addr=000007FF53F89018
      Chunk      7ff53f89028 sz=     4056    freeable  "TCHK^60ed76d5  "  ds=000007FF53F91B38
    EXTENT 23 addr=000007FF53F8A018
      Chunk      7ff53f8a028 sz=     4056    freeable  "TCHK^60ed76d5  "  ds=000007FF53F91B38
    EXTENT 24 addr=000007FF53F8B018
      Chunk      7ff53f8b028 sz=     4056    freeable  "TCHK^60ed76d5  "  ds=000007FF53F91B38
    EXTENT 25 addr=000007FF53F8C018
      Chunk      7ff53f8c028 sz=     4056    freeable  "TCHK^60ed76d5  "  ds=000007FF53F91B38
    EXTENT 26 addr=000007FF53F8D018
      Chunk      7ff53f8d028 sz=     4056    freeable  "TCHK^60ed76d5  "  ds=000007FF53F91B38
    EXTENT 27 addr=000007FF53F8E018
      Chunk      7ff53f8e028 sz=     4056    freeable  "TCHK^60ed76d5  "  ds=000007FF53F91B38
    EXTENT 28 addr=000007FF53F8F018
      Chunk      7ff53f8f028 sz=     4056    freeable  "TCHK^60ed76d5  "  ds=000007FF53F91B38
    EXTENT 29 addr=000007FF53F90018
      Chunk      7ff53f90028 sz=     4056    recreate  "TCHK^60ed76d5  "  latch=0000000000000000
         ds      7ff53f91b38 sz=    97584 ct=       24
                 7ff53f73f38 sz=     4056
                 7ff53f74f38 sz=     4056
                 7ff53f75f38 sz=     4056
                 7ff53f76f38 sz=     4056
                 7ff53f77f38 sz=     4056
                 7ff53f78f38 sz=     4056
                 7ff53f79f38 sz=     4056
                 7ff53f7af38 sz=     4056
                 7ff53f7bf38 sz=     4176
                 7ff53f7cfb0 sz=     4176
                 7ff53f7e028 sz=     4056
                 7ff53f7f028 sz=     4056
                 7ff53f80028 sz=     4056
                 7ff53f86028 sz=     4056
                 7ff53f87028 sz=     4056
                 7ff53f88028 sz=     4056
                 7ff53f89028 sz=     4056
                 7ff53f8a028 sz=     4056
                 7ff53f8b028 sz=     4056
                 7ff53f8c028 sz=     4056
                 7ff53f8d028 sz=     4056
                 7ff53f8e028 sz=     4056
                 7ff53f8f028 sz=     4056
    EXTENT 30 addr=000007FF53F91030
      Chunk      7ff53f91040 sz=       80    perm      "perm           "  alo=80
      Chunk      7ff53f91090 sz=      616    recreate  "qbcqtcHTHeap   "  latch=0000000000000000
         ds      7ff53f91310 sz=    16632 ct=        5
                 7ff53f82028 sz=     4056
                 7ff53f83090 sz=     3952
                 7ff53f84028 sz=     4056
                 7ff53f85090 sz=     3952
      Chunk      7ff53f912f8 sz=      184    freeable  "qcpifqtqc : kgh"
      Chunk      7ff53f913b0 sz=       40    freeable  "qcpifqtqc : qbc"
      Chunk      7ff53f913d8 sz=       48    freeable  "qbcqut : qcpisq"
      Chunk      7ff53f91408 sz=      720    freeable  "qbcdef:qcpiqbk "
      Chunk      7ff53f916d8 sz=      584    freeable  "xpl : prsxpl   "
      Chunk      7ff53f91920 sz=       64    freeable  "unmdef in opipr"
      Chunk      7ff53f91960 sz=       48    freeable  "qctctx: kkmqccr"
      Chunk      7ff53f91990 sz=       72    freeable  "qcsctx: kkmqccr"
      Chunk      7ff53f919d8 sz=       40    freeable  "qcptgc: kkmqccr"
      Chunk      7ff53f91a00 sz=       80    freeable  "qcpctx: kkmqccr"
      Chunk      7ff53f91a50 sz=       56    freeable  "qcmemctx : kkmq"
      Chunk      7ff53f91a88 sz=      152    freeable  "qcctx : kkmqccr"
      Chunk      7ff53f91b20 sz=      208    freeable  "Typecheck heap "
      Chunk      7ff53f91bf0 sz=       64    freeable  "kksol : kkscuf "
      Chunk      7ff53f91c30 sz=       64    freeable  "kksol : kkscuf "
      Chunk      7ff53f91c70 sz=      912    freeable  "ctxdef:kksLoadC"
    Total heap size    =   125952
    FREE LISTS:
     Bucket 0 size=152
      Chunk      7ff53f91060 sz=        0    kghdsx
     Bucket 1 size=280
     Bucket 2 size=536
     Bucket 3 size=1048
     Bucket 4 size=2072
      Chunk      7ff53f72f38 sz=     3688    free      "               "
     Bucket 5 size=4120
     Bucket 6 size=4144
     Bucket 7 size=4168
     Bucket 8 size=4192
     Bucket 9 size=4216
    Total free space   =     3688
    UNPINNED RECREATABLE CHUNKS (lru first):
    PERMANENT CHUNKS:
      Chunk      7ff53f91040 sz=       80    perm      "perm           "  alo=80
    Permanent space    =       80
    

    So one of the biggest differences in the EXPLAIN PLAN is presence of TCHK allocations, presumably this is “typecheck” allocations. 22 of them are 4056 bytes and 2 are 4176 bytes, totaling 97584 bytes of shared memory. It’s possible to go further and dump what is inside this allocations:

    SQL> alter session set events 'immediate trace name heapdump_addr address 0x000007FF53F91B38';
    
    Session altered.

    HEAP DUMP heap name="TCHK^60ed76d5"  desc=000007FF53F91B38
     extent sz=0xfc0 alt=32767 het=32767 rec=0 flg=2 opc=2
     parent=000007FF5731B7A0 owner=000007FF5731B660 nex=0000000000000000 xsz=0xfc0 heap=0000000000000000
     fl2=0x27, nex=0000000000000000, dsxvers=1, dsxflg=0x0
     dsx first ext=0x53f90058
    EXTENT 0 addr=000007FF53F73F50
      Chunk      7ff53f73f60 sz=     2936    free      "               "
      Chunk      7ff53f74ad8 sz=       40    freeable  "chedef : qcuatc"
      Chunk      7ff53f74b00 sz=       32    freeable  "xplCopyRow:qkbl"
      Chunk      7ff53f74b20 sz=      520    freeable  "xplins:xplrow  "
      Chunk      7ff53f74d28 sz=      488    freeable  "KGHSC_ALLOC_BUF"
    EXTENT 1 addr=000007FF53F74F50
      Chunk      7ff53f74f60 sz=      104    freeable  "qksxaMoveList:x"
      Chunk      7ff53f74fc8 sz=       96    freeable  "qksxaMoveList:x"
      Chunk      7ff53f75028 sz=       96    freeable  "qksxaMoveList:x"
      Chunk      7ff53f75088 sz=       96    freeable  "qksxaMoveList:x"
      Chunk      7ff53f750e8 sz=     3624    freeable  "kggec.c.kggfa  "
    EXTENT 2 addr=000007FF53F75F50
      Chunk      7ff53f75f60 sz=       88    freeable  "qolHintAlloc:pi"
      Chunk      7ff53f75fb8 sz=       56    freeable  "qolHintAlloc:pi"
      Chunk      7ff53f75ff0 sz=       56    freeable  "qolHintAlloc:pi"
      Chunk      7ff53f76028 sz=      224    freeable  "qolHintAlloc:ne"
      Chunk      7ff53f76108 sz=       96    freeable  "qksxaMoveList:x"
      Chunk      7ff53f76168 sz=       96    freeable  "qksxaMoveList:x"
      Chunk      7ff53f761c8 sz=       96    freeable  "qksxaMoveList:x"
      Chunk      7ff53f76228 sz=       96    freeable  "qksxaMoveList:x"
      Chunk      7ff53f76288 sz=       96    freeable  "qksxaMoveList:x"
      Chunk      7ff53f762e8 sz=      520    freeable  "xplins:xplrow  "
      Chunk      7ff53f764f0 sz=       96    freeable  "qolHintAlloc:pi"
      Chunk      7ff53f76550 sz=      224    freeable  "qolHintAlloc:ne"
      Chunk      7ff53f76630 sz=       96    freeable  "qksxaMoveList:x"
      Chunk      7ff53f76690 sz=       88    freeable  "kggec.c.kggfa  "
      Chunk      7ff53f766e8 sz=     2088    freeable  "kggec.c.kggfa  "
    EXTENT 3 addr=000007FF53F76F50
      Chunk      7ff53f76f60 sz=       80    freeable  "qolHintAlloc:pi"
      Chunk      7ff53f76fb0 sz=      224    freeable  "qolHintAlloc:ne"
      Chunk      7ff53f77090 sz=       88    freeable  "kggec.c.kggfa  "
      Chunk      7ff53f770e8 sz=     3624    freeable  "kggec.c.kggfa  "
    EXTENT 4 addr=000007FF53F77F50
      Chunk      7ff53f77f60 sz=       72    freeable  "chedef : qcuatc"
      Chunk      7ff53f77fa8 sz=     3944    freeable  "kgghte.c.kggfa "
    EXTENT 5 addr=000007FF53F78F50
      Chunk      7ff53f78f60 sz=       72    freeable  "rwodef : rwoalc"
      Chunk      7ff53f78fa8 sz=     3944    freeable  "qke.c.kggfa    "
    EXTENT 6 addr=000007FF53F79F50
      Chunk      7ff53f79f60 sz=       72    freeable  "chedef : qcuatc"
      Chunk      7ff53f79fa8 sz=     3944    freeable  "kggec.c.kggfa  "
    EXTENT 7 addr=000007FF53F7AF50
      Chunk      7ff53f7af60 sz=      272    free      "               "
      Chunk      7ff53f7b070 sz=       48    freeable  "kghscCopyData:d"
      Chunk      7ff53f7b0a0 sz=      520    freeable  "xplins:xplrow  "
      Chunk      7ff53f7b2a8 sz=       56    freeable  "qolHintAlloc:pi"
      Chunk      7ff53f7b2e0 sz=      224    freeable  "qolHintAlloc:ne"
      Chunk      7ff53f7b3c0 sz=       40    freeable  "xplCopyRow:qkbl"
      Chunk      7ff53f7b3e8 sz=       48    freeable  "xplCopyRow:proj"
      Chunk      7ff53f7b418 sz=       40    freeable  "xplCopyRow:opti"
      Chunk      7ff53f7b440 sz=      224    freeable  "qolHintAlloc:ne"
      Chunk      7ff53f7b520 sz=      224    freeable  "qolHintAlloc:ne"
      Chunk      7ff53f7b600 sz=       88    freeable  "kggec.c.kggfa  "
      Chunk      7ff53f7b658 sz=       88    freeable  "kgghte.c.kggfa "
      Chunk      7ff53f7b6b0 sz=      192    freeable  "qknAllocate:qkn"
      Chunk      7ff53f7b770 sz=       88    freeable  "qke.c.kggfa    "
      Chunk      7ff53f7b7c8 sz=       88    freeable  "kggec.c.kggfa  "
      Chunk      7ff53f7b820 sz=      200    freeable  "qkeCreateAnalys"
      Chunk      7ff53f7b8e8 sz=       40    freeable  "chedef : qcuatc"
      Chunk      7ff53f7b910 sz=       40    freeable  "chedef : qcuatc"
      Chunk      7ff53f7b938 sz=       40    freeable  "chedef : qcuatc"
      Chunk      7ff53f7b960 sz=       40    freeable  "chedef : qcuatc"
      Chunk      7ff53f7b988 sz=       40    freeable  "chedef : qcuatc"
      Chunk      7ff53f7b9b0 sz=       40    freeable  "chedef : qcuatc"
      Chunk      7ff53f7b9d8 sz=       72    freeable  "kggdlHd:Init   "
      Chunk      7ff53f7ba20 sz=      232    freeable  "kggec.c.kggfa  "
      Chunk      7ff53f7bb08 sz=       88    freeable  "kggec.c.kggfa  "
      Chunk      7ff53f7bb60 sz=       96    freeable  "qksxaMoveList:x"
      Chunk      7ff53f7bbc0 sz=      328    freeable  "qknAllocate:qkn"
      Chunk      7ff53f7bd08 sz=      160    freeable  "qmuCreatePermSu"
      Chunk      7ff53f7bda8 sz=       72    freeable  "allocator state"
      Chunk      7ff53f7bdf0 sz=       96    freeable  "qksxaMoveList:x"
      Chunk      7ff53f7be50 sz=      192    freeable  "qknAllocate:qkn"
    EXTENT 8 addr=000007FF53F7BF50
      Chunk      7ff53f7bf60 sz=     4136    freeable  "qkxr.c.kgght   "
    EXTENT 9 addr=000007FF53F7CFC8
      Chunk      7ff53f7cfd8 sz=     4136    freeable  "qkxr.c.kgght   "
    EXTENT 10 addr=000007FF53F7E040
      Chunk      7ff53f7e050 sz=       72    freeable  "kkoFroCtx: qksf"
      Chunk      7ff53f7e098 sz=     3944    freeable  "kggec.c.kggfa  "
    EXTENT 11 addr=000007FF53F7F040
      Chunk      7ff53f7f050 sz=       72    freeable  "chedef : qcuatc"
      Chunk      7ff53f7f098 sz=     3944    freeable  "qkxr.c.kgght   "
    EXTENT 12 addr=000007FF53F80040
      Chunk      7ff53f80050 sz=       48    freeable  "rwodef : rwoalc"
      Chunk      7ff53f80080 sz=       72    freeable  "qksxaMoveList:x"
      Chunk      7ff53f800c8 sz=       40    freeable  "opldef: qcopCre"
      Chunk      7ff53f800f0 sz=      112    freeable  "opndef: qcopCre"
      Chunk      7ff53f80160 sz=       40    freeable  "rwodef : rwoalc"
      Chunk      7ff53f80188 sz=      104    freeable  "qkaFroCtx: qksf"
      Chunk      7ff53f801f0 sz=       72    freeable  "qkaQbcCtx: qksq"
      Chunk      7ff53f80238 sz=       96    freeable  "qksxaAddList:xa"
      Chunk      7ff53f80298 sz=       96    freeable  "qksxaAddList:xa"
      Chunk      7ff53f802f8 sz=       96    freeable  "qksxaAddList:xa"
      Chunk      7ff53f80358 sz=       96    freeable  "qksxaAddList:xa"
      Chunk      7ff53f803b8 sz=       96    freeable  "qksxaAddList:xa"
      Chunk      7ff53f80418 sz=       96    freeable  "qksxaAddList:xa"
      Chunk      7ff53f80478 sz=       72    freeable  "qksxaAddList:xa"
      Chunk      7ff53f804c0 sz=       72    freeable  "qksxaAddList:xa"
      Chunk      7ff53f80508 sz=       96    freeable  "qksxaAddList:xa"
      Chunk      7ff53f80568 sz=       96    freeable  "qksxaAddList:xa"
      Chunk      7ff53f805c8 sz=       96    freeable  "qksxaAddList:xa"
      Chunk      7ff53f80628 sz=       40    freeable  "idndef : qcuAll"
      Chunk      7ff53f80650 sz=       40    freeable  "qbnm : qksqbAll"
      Chunk      7ff53f80678 sz=       48    freeable  "kkoqdsmpqi : kk"
      Chunk      7ff53f806a8 sz=      176    freeable  "kkotp : kkoiqb "
      Chunk      7ff53f80758 sz=       56    freeable  "kkoqbc: costCac"
      Chunk      7ff53f80790 sz=       88    freeable  "fptdef : apacfc"
      Chunk      7ff53f807e8 sz=      280    freeable  "kkoFroAnn: qksf"
      Chunk      7ff53f80900 sz=      464    freeable  "kkoqb: qksqbIni"
      Chunk      7ff53f80ad0 sz=       88    freeable  "kggec.c.kggfa  "
      Chunk      7ff53f80b28 sz=       88    freeable  "qkxr.c.kgght   "
      Chunk      7ff53f80b80 sz=       40    freeable  "chedef : qcuatc"
      Chunk      7ff53f80ba8 sz=       40    freeable  "chedef : qcuatc"
      Chunk      7ff53f80bd0 sz=       40    freeable  "chedef : qcuatc"
      Chunk      7ff53f80bf8 sz=       56    freeable  "kkoInitOptimCac"
      Chunk      7ff53f80c30 sz=      104    freeable  "kkqtFroCtx: qks"
      Chunk      7ff53f80c98 sz=       40    freeable  "idndef : qcuAll"
      Chunk      7ff53f80cc0 sz=       80    freeable  "qbcreg : qksqbR"
      Chunk      7ff53f80d10 sz=      232    freeable  "kkqtQbcCtx: qks"
      Chunk      7ff53f80df8 sz=      520    recreate  "kggec.c.kggfa  "  latch=0000000000000000
         ds      7ff53f90108 sz=      520 ct=        1
    EXTENT 13 addr=000007FF53F86040
      Chunk      7ff53f86050 sz=       72    freeable  "kggslHd:Init   "
      Chunk      7ff53f86098 sz=     3944    freeable  "kgghte.c.kggfa "
    EXTENT 14 addr=000007FF53F87040
      Chunk      7ff53f87050 sz=       72    freeable  "chedef : qcuatc"
      Chunk      7ff53f87098 sz=     3944    freeable  "kgghte.c.kggfa "
    EXTENT 15 addr=000007FF53F88040
      Chunk      7ff53f88050 sz=       72    freeable  "kggslAllocItem "
      Chunk      7ff53f88098 sz=     3944    freeable  "kggec.c.kggfa  "
    EXTENT 16 addr=000007FF53F89040
      Chunk      7ff53f89050 sz=       72    freeable  "kggsmCommonInit"
      Chunk      7ff53f89098 sz=     3944    freeable  "kgghte.c.kggfa "
    EXTENT 17 addr=000007FF53F8A040
      Chunk      7ff53f8a050 sz=       72    freeable  "qbnm : qksqbAll"
      Chunk      7ff53f8a098 sz=     3944    freeable  "kgghte.c.kggfa "
    EXTENT 18 addr=000007FF53F8B040
      Chunk      7ff53f8b050 sz=       72    freeable  "idndef : qcuAll"
      Chunk      7ff53f8b098 sz=     3944    freeable  "kggec.c.kggfa  "
    EXTENT 19 addr=000007FF53F8C040
      Chunk      7ff53f8c050 sz=       72    freeable  "kggsmCommonInit"
      Chunk      7ff53f8c098 sz=     3944    freeable  "kgghte.c.kggfa "
    EXTENT 20 addr=000007FF53F8D040
      Chunk      7ff53f8d050 sz=       72    freeable  "chedef : qcuatc"
      Chunk      7ff53f8d098 sz=     3944    freeable  "kggec.c.kggfa  "
    EXTENT 21 addr=000007FF53F8E040
      Chunk      7ff53f8e050 sz=       72    freeable  "halias : qkshtT"
      Chunk      7ff53f8e098 sz=     3944    freeable  "kggec.c.kggfa  "
    EXTENT 22 addr=000007FF53F8F040
      Chunk      7ff53f8f050 sz=       72    freeable  "idndef : qcuAll"
      Chunk      7ff53f8f098 sz=     3944    freeable  "kggms.c.kggfa  "
    EXTENT 23 addr=000007FF53F90058
      Chunk      7ff53f90068 sz=       80    perm      "perm           "  alo=80
      Chunk      7ff53f900b8 sz=       56    freeable  "qkshtRegistry :"
      Chunk      7ff53f900f0 sz=      184    freeable  "kggec.c.kggfa  "
      Chunk      7ff53f901a8 sz=      144    freeable  "qksbgCreateCurs"
      Chunk      7ff53f90238 sz=      160    freeable  "kksgaAlloc elem"
      Chunk      7ff53f902d8 sz=      152    freeable  "kkets : kkdlgtd"
      Chunk      7ff53f90370 sz=       80    freeable  "kctph : kkdlgtd"
      Chunk      7ff53f903c0 sz=      248    freeable  "qsmksol : qsmg_"
      Chunk      7ff53f904b8 sz=      720    freeable  "qbcdef:prsdrv  "
      Chunk      7ff53f90788 sz=      304    freeable  "qkemgx: qkemgCr"
      Chunk      7ff53f908b8 sz=       88    freeable  "kgghte.c.kggfa "
      Chunk      7ff53f90910 sz=       88    freeable  "kgghte.c.kggfa "
      Chunk      7ff53f90968 sz=       88    freeable  "kggec.c.kggfa  "
      Chunk      7ff53f909c0 sz=       80    freeable  "kggsmInit:sm   "
      Chunk      7ff53f90a10 sz=       88    freeable  "kgghte.c.kggfa "
      Chunk      7ff53f90a68 sz=       88    freeable  "kgghte.c.kggfa "
      Chunk      7ff53f90ac0 sz=       88    freeable  "kggec.c.kggfa  "
      Chunk      7ff53f90b18 sz=       80    freeable  "kggsmInit:sm   "
      Chunk      7ff53f90b68 sz=       88    freeable  "kgghte.c.kggfa "
      Chunk      7ff53f90bc0 sz=       88    freeable  "kggec.c.kggfa  "
      Chunk      7ff53f90c18 sz=       88    freeable  "kggec.c.kggfa  "
      Chunk      7ff53f90c70 sz=       88    freeable  "kggms.c.kggfa  "
      Chunk      7ff53f90cc8 sz=      168    freeable  "kggec.c.kggfa  "
      Chunk      7ff53f90d70 sz=       88    freeable  "kggec.c.kggfa  "
      Chunk      7ff53f90dc8 sz=       72    freeable  "qksHd : qksAllo"
      Chunk      7ff53f90e10 sz=      400    freeable  "ctxcdef: opiprs"
      Chunk      7ff53f90fa0 sz=       48    freeable  "qcsqlpath: qcsA"
      Chunk      7ff53f90fd0 sz=       48    freeable  "qcsqlpath: qcsA"
    Total heap size    =    96600
    FREE LISTS:
     Bucket 0 size=80
      Chunk      7ff53f90088 sz=        0    kghdsx
     Bucket 1 size=168
      Chunk      7ff53f73f60 sz=     2936    free      "               "
      Chunk      7ff53f7af60 sz=      272    free      "               "
    Total free space   =     3208
    UNPINNED RECREATABLE CHUNKS (lru first):
    PERMANENT CHUNKS:
      Chunk      7ff53f90068 sz=       80    perm      "perm           "  alo=80
    Permanent space    =       80
    

    All this is too cryptic and I have no idea what exactly is located in this memory pieces. Also it’s very surprising to see that “typecheck” memory seems to be not accounted to sharable memory through V$-views at all, although it is a big part of it. More than 100K for a simple query is just too big in my opinion.

    Thank you for reading till the end :) I’ll be happy to hear your comments on the topic.

    Filed under: Oracle Tagged: memory, ORA-04031, tpt

    Index Hints

    A new form of index hint appeared in 10g – and it’s becoming more common to see it in production code; instead of naming indexes in index hints, we describe them. Consider the following hint (expressed in two ways, first as it appeared in the outline section of an execution plan, then cosmetically adjusted to look more like the way you would write it in your SQL):

    INDEX(@"SEL$1" "PRD"@"SEL$1" ("PRODUCTS"."PRODUCT_GROUP" "PRODUCTS"."ID"))
    index(@sel$1 prd@sel$1(product_group  id))
    

    With this syntax, Oracle is directed to use an index on the table aliased as prd in query block with the (default) name sel$1; the index has to start with the columns (product_group, id) in that order – with preference given to an exact match, otherwise using the lowest cost index that starts the right way. In passing, although this example shows the default query block names, it’s good practice to name query blocks explicitly with the /*+ qb_name() */ hint.

    With this in mind, what is the correct way to hint an index with the following definition:

    create index prd_case on products(
            case product_group
                    when 'CLASSICAL CD' then id
            end
    )
    ;
    

    The correct answer is the obvious guess – you have to go back to the old syntax which (again in two versions) would be:

    INDEX(@"SEL$1" "PRD"@"SEL$1" "PRD_CASE")
    index(@sel$1 prd@sel$1 prd_case)
    

    There simply is no alternative.

    So how about hinting a bitmap join index, e.g:

    create bitmap index pe_home_st_idx on people(st.name)
    from
            states  st,
            towns   ho,
            people  pe
    where
            ho.id_state     = st.id
    and     pe.id_town_home = ho.id
    ;
    

    Again you could use the traditional approach of supplying the index name, but in this case you can also take the newer approach of supplying the index description – except you have to remember to qualify any column names with their table name (irrespective of the aliases you used for the table in the query) e.g:

    /*+ index(pe (states.name)) */
    

    Finally, left as an exercise to the readers, how should you hint an index on a virtual column:

    alter table products
            add cd_product generated always as (
                    case product_group
                            when 'CLASSICAL CD' then id
                    end
            ) virtual
    ;
    
    create index prd_virt on products(cd_product);
    
    

    If you check user_tab_columns and user_ind_columns they both contain the column name cd_product, so it seems pretty clear that we should be able to use the 10g description method – but will the optimizer recognise the index if hinted that way, and what will the outline section of the execution plan show ?

    Lack of Index and Constraint Comments

    Something I’ve just reminded myself of is that under Oracle you cannot add a comment on an index or a constraint. You can only add comments on tables, views, materialized views, columns of those object types and a couple of esoteric things like Operators, Editions and Indextypes.

    Here is an example of adding comments to tables and columns:

    set pause off feed off
    drop table mdw purge;
    create table mdw(id number,vc1 varchar2(10));
    comment on table mdw is 'Martin Widlake''s simple test table';
    comment on column mdw.id is 'simple numeric PK sourced from sequence mdw_seq';
    comment on column mdw.vc1 is'allow some random text up to 10 characters';
    --
    desc user_tab_comments
    
     Name                                                  Null?    Type
     ----------------------------------------------------- -------- ------------------------------------
     TABLE_NAME                                            NOT NULL VARCHAR2(30)
     TABLE_TYPE                                                     VARCHAR2(11)
     COMMENTS                                                       VARCHAR2(4000)
    
    --
    select * from dba_tab_comments where table_name='MDW'
    /
    OWNER                          TABLE_NAME                     TABLE_TYPE
    ------------------------------ ------------------------------ -----------
    COMMENTS
    ----------------------------------------------------------------------------------------------------
    MDW                            MDW                            TABLE
    Martin Widlake's simple test table
    
    select * from dba_col_comments where table_name='MDW'
    order by column_name
    /
    OWNER                          TABLE_NAME                     COLUMN_NAME
    ------------------------------ ------------------------------ --------------
    COMMENTS
    ----------------------------------------------------------------------------------------------------
    MDW                            MDW                            ID
    simple numeric PK sourced from sequence mdw_seq
    MDW                            MDW                            VC1
    allow some random text up to 10 characters
    -- now to add a big comment so need to use the '-' line continuation character in sqlplus
    --
    comment on table mdw is 'this is my standard test table.-
     As you can see it is a simple table and has only two columns.-
     It will be populated with 42 rows as that is the solution to everything.'
    /
    select * from dba_tab_comments where table_name='MDW'
    OWNER                          TABLE_NAME                     TABLE_TYPE
    ------------------------------ ------------------------------ -----------
    COMMENTS
    ----------------------------------------------------------------------------------------------------
    MDW                            MDW                            TABLE
    this is my standard test table.  As you can see it is a simple table and has only two columns.  It w
    ill be populated with 42 rows as that is the solution to everything.
    --
    /

    Adding comments on tables, views and columns seems to have dropped out of fashion over the years but I think it is still a very useful feature of oracle and I still do add them (though I am getting a little slack about it myself over the last 3 or 4 years, which I must stop).

    Comments are great, you can put 4000 characters of information into the database about each table, view and column. This can be a brief description of the object, a full explanation of what a column is to hold or even a list of typical entries for a column or table.

    But you can’t add a comment on indexes or constraints. Why would I want to? Well, constraints and indexes should only be there for a reason and the reason is not always obvious from either the names of the columns or the name of the constraint or index, especially where you have a naming standard that forces you to name indexes and constraints after the columns they reference.

    When you design a database, do a schema diagram or an ERD, you label your relationships between entities/tables. It tells you exactly what the relationship is. You might create an index to support a specific method of access or particular business function. You might alter the index in a way not immediately obvious to the casual observer, such as to allow queries that use the index to avoid having to visit the table. All of those things will, of course, be fully documented in the maintained project documentation in the central repository, available and used by all…

    If I was able to add comments to constraints and indexes within the database then they would there. You move the system from one platform to the other, they are there. If for any wildly unlikely reason the central documentation lets you down, the information is always there in the database and easy to check. You may not be able to track down the original design documents but you have the database in front of you, so comments in that will persist and be very easy to find.

    Lacking the ability to add comments on indexes and constraints, I have to put them at the table level, which I always feel is a kludge. I might actually raise an enhancement request for this, but as Oracle 12 is already nailed down, it will have to wait until Oracle 14. (A little bird told me Larry said there would be no Oracle 13…).

    Tuning Oracle to Make a Query Slower

    I had an interesting little project this morning. Of course it takes longer to write it down than to do actually do it, but it was kind of interesting and since I haven’t done a post in quite some time (and it’s the day before Thanksgiving, so it’s pretty quite at the office anyway) I decided to share. One of the Enkitec guys (Tim Fox) was doing a performance comparison between various platforms (Exadata using it’s IB Storage Network, Oracle Database Appliance (ODA) using it’s direct attached storage, and a standard database on a Dell box using EMC fiber channel attached storage). The general test idea was simple – see how the platforms stacked up for a query that required a full scan of a large table. More specifically, what Tim wanted to see was the relative speed at which the various storage platforms could return data. The expectation was that the direct attached storage would be fastest and the fibre channel storage would be slowest (especially since we only had a single 2G HBA). He tested ODA and Exadata and got basically what he expected, but when he went to test the database on the Dell he was surprised that it was actually faster than either of the other two tests. So here’s some output from the initial tests: First the Exadata. It’s an X2 quarter rack with one extra storage server. Note that we had to set cell_offload_processing to false to turn off the Exadata storage optimizations, thus giving us a measurement of the hardware capabilities without the Exadata offloading.

    > !sqlp
    sqlp
     
    SQL*Plus: Release 11.2.0.2.0 Production on Wed Nov 23 11:08:28 2011
     
    Copyright (c) 1982, 2010, Oracle.  All rights reserved.
     
     
    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
    With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
    Data Mining and Real Application Testing options
     
    SYS@DEMO1> @uptime
     
    INSTANCE_NAME    STARTUP_TIME      CURRENT_TIME         DAYS    SECONDS
    ---------------- ----------------- ----------------- ------- ----------
    DEMO1            07-NOV-2011 12:37 23-NOV-2011 11:08   15.94    1377058
     
    SYS@DEMO1> set sqlprompt "_USER'@'EXADATA'>' "
    SYS@EXADATA> 
    SYS@EXADATA> ! cat /etc/redhat-release
    Enterprise Linux Enterprise Linux Server release 5.5 (Carthage)
     
    SYS@EXADATA> ! uname -a
    Linux enkdb03.enkitec.com 2.6.18-194.3.1.0.3.el5 #1 SMP Tue Aug 31 22:41:13 EDT 2010 x86_64 x86_64 x86_64 GNU/Linux
     
    SYS@EXADATA> alter session set "_serial_direct_read"=always;
     
    Session altered.
     
    SYS@EXADATA> alter session set cell_offload_processing=false;
     
    Session altered.
     
    SYS@EXADATA> set autotrace on
    SYS@EXADATA> set timing on
    SYS@EXADATA> select count(*) from instructor.class_sales;
     
      COUNT(*)
    ----------
      90000000
     
    Elapsed: 00:00:43.01
     
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 3145879882
     
    ----------------------------------------------------------------------------------
    | Id  | Operation                  | Name        | Rows  | Cost (%CPU)| Time     |
    ----------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT           |             |     1 |   314K  (1)| 00:00:02 |
    |   1 |  SORT AGGREGATE            |             |     1 |            |          |
    |   2 |   TABLE ACCESS STORAGE FULL| CLASS_SALES |    90M|   314K  (1)| 00:00:02 |
    ----------------------------------------------------------------------------------
     
     
    Statistics
    ----------------------------------------------------------
              1  recursive calls
              0  db block gets
        1168567  consistent gets
        1168557  physical reads
              0  redo size
            526  bytes sent via SQL*Net to client
            524  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
              1  rows processed
     
    SYS@EXADATA> set autotrace off
    SYS@EXADATA> @fss
    Enter value for sql_text: select count(*) from instructor.class_sales
    Enter value for sql_id: 
     
    SQL_ID         CHILD      EXECS   AVG_ROWS     AVG_ETIME       AVG_CPU       AVG_PIO      AVG_LIO SQL_TEXT
    ------------- ------ ---------- ---------- ------------- ------------- ------------- ------------ ----------------------------------------
    b2br1x82p9862      0          1          1         43.00          3.16  1,168,557.00    1,168,567 select count(*) from instructor.class_sa
     
    Elapsed: 00:00:00.08

    So the test on the Exadata took 43 seconds to read and transport roughly 1 million 8K blocks. The same test on the ODA looked like this:

    [oracle@patty scripts]$ !rl rlwrap sqlplus / as sysdba  
     
    SQL*Plus: Release 11.2.0.2.0 Production on Wed Nov 23 10:16:51 2011  
     
    Copyright (c) 1982, 2010, Oracle.  All rights reserved.   
     
     
    Connected to: 
    Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production 
    With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, 
    Data Mining and Real Application Testing options   
     
    INSTANCE_NAME    STARTUP_TIME               CURRENT_TIME                  DAYS    SECONDS 
    ---------------- -------------------------- -------------------------- ------- ---------- 
    ODA1             22-NOV-2011 12:23          23-NOV-2011 10:16              .91      78793  
     
    SYS@ODA1> ! cat /etc/redhat-release 
     
    Red Hat Enterprise Linux Server release 5.5 (Tikanga)  
     
    SYS@ODA1> ! uname -a 
     
    Linux patty 2.6.18-194.32.1.0.1.el5 #1 SMP Tue Jan 4 16:26:54 EST 2011 x86_64 x86_64 x86_64 GNU/Linux  
     
    SYS@ODA1> set timing on
     
    SYS@ODA1> alter session set '_serial_direct_read'=always;  
     
    Session altered.  
     
    Elapsed: 00:00:00.00 
     
    SYS@ODA1> set autotrace on 
    SYS@ODA1> select count(*) from instructor.class_sales;    
     
    COUNT(*) 
    ----------   
    90000000  
    Elapsed: 00:00:30.60  
     
    Execution Plan 
    ---------------------------------------------------------- 
    Plan hash value: 3145879882  
     
    -------------------------------------------------------------------------- 
    | Id  | Operation          | Name        | Rows  | Cost (%CPU)| Time     | 
    -------------------------------------------------------------------------- 
    |   0 | SELECT STATEMENT   |             |     1 |   317K  (1)| 00:00:05 | 
    |   1 |  SORT AGGREGATE    |             |     1 |            |          | 
    |   2 |   TABLE ACCESS FULL| CLASS_SALES |    90M|   317K  (1)| 00:00:05 | 
    --------------------------------------------------------------------------   
     
     
    Statistics 
    ----------------------------------------------------------          
            99  recursive calls           
             0  db block gets     
       1154080  consistent gets     
       1153994  physical reads         
           516  redo size         
           526  bytes sent via SQL*Net to client         
           524  bytes received via SQL*Net from client           
             2  SQL*Net roundtrips to/from client           
             7  sorts (memory)           
             0  sorts (disk)           
             1  rows processed  
     
    SYS@ODA1> set autotrace off 
    SYS@ODA1> @fss 
    Enter value for sql_text: select count(*) from instructor.class_sales 
    Enter value for sql_id:   
     
    SQL_ID         CHILD PLAN_HASH_VALUE      EXECS ROWS_PROCESSED  AVG_ETIME    AVG_CPU    AVG_PIO    AVG_LIO SQL_TEXT 
    ------------- ------ --------------- ---------- -------------- ---------- ---------- ---------- ---------- ---------------------------------------- 
    b2br1x82p9862      0      3145879882          1              1      30.55      13.91  1,153,994  1,154,080 select count(*) from instructor.class_sa

    As expected, the direct attached disk was faster than moving the blocks across the IB network. It took about 30 seconds to read roughly the same number of blocks. The same test on the Dell produced this output:

     [osborne@homer scripts]$ sqlp  
     
    SQL*Plus: Release 11.2.0.3.0 Production on Wed Nov 23 11:20:20 2011  
     
    Copyright (c) 1982, 2011, Oracle.  All rights reserved.   
     
     
    Connected to: 
    Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production 
    With the Partitioning, OLAP, Data Mining and Real Application Testing options  
     
    SYS@EXADATA> set sqlprompt "'_USER'@'DELL'>'" 
    SYS@DELL> !cat /etc/redhat-release 
     
    Red Hat Enterprise Linux Server release 5.5 (Tikanga)  
     
    SYS@DELL> ! uname -a 
     
    Linux homer.enkitec.com 2.6.18-194.el5 #1 SMP Mon Mar 29 22:10:29 EDT 2010 x86_64 x86_64 x86_64 GNU/Linux  
     
    SYS@DELL> alter session set '_serial_direct_read'=always;  
     
    Session altered.  
     
    SYS@DELL> set autotrace on 
     
    SYS@DELL> set timing on 
     
    SYS@DELL> select count(*) from instructor.class_sales;    
     
    COUNT(*) 
    ----------   
    90000000  
     
    Elapsed: 00:00:11.31  
     
    Execution Plan 
    ---------------------------------------------------------- 
    Plan hash value: 3145879882  
     
    -------------------------------------------------------------------------- 
    | Id  | Operation          | Name        | Rows  | Cost (%CPU)| Time     | 
    -------------------------------------------------------------------------- 
    |   0 | SELECT STATEMENT   |             |     1 |   315K  (1)| 01:03:08 | 
    |   1 |  SORT AGGREGATE    |             |     1 |            |          | 
    |   2 |   TABLE ACCESS FULL| CLASS_SALES |    90M|   315K  (1)| 01:03:08 | 
    --------------------------------------------------------------------------   
     
     
    Statistics ----------------------------------------------------------          
            77  recursive calls  
             0  db block gets  
       1168660  consistent gets  
       1168569  physical reads  
             0  redo size  
           526  bytes sent via SQL*Net to client  
           524  bytes received via SQL*Net from client  
             2  SQL*Net roundtrips to/from client  
            10  sorts (memory)  
             0  sorts (disk)  
             1  rows processed  
     
    SYS@DELL> set autotrace off 
    SYS@DELL> @fss 
    Enter value for sql_text: select count(*) from instructor.class_sales 
    Enter value for sql_id:   
     
    SQL_ID         CHILD      EXECS   AVG_ROWS     AVG_ETIME       AVG_CPU       AVG_PIO      AVG_LIO SQL_TEXT 
    ------------- ------ ---------- ---------- ------------- ------------- ------------- ------------ ---------------------------------------- 
    b2br1x82p9862      0          1          1         11.29         11.25  1,168,569.00    1,168,660 select count(*) from instructor.class_sa  
     
    Elapsed: 00:00:00.03

    This is where the results were surprising. Tim expected the single 2G HBA to be considerably slower than the other two set ups, but it only took 11 seconds on the Dell / EMC set up to read the same data. So this is where I got to help Tim try to make it slower! (there’s a first time for everything) So what gives? The first thought was that Oracle was caching the data: But no – you’ll notice that we set the _serial_direct_read parameter to always, which forces direct path reads and bypasses the buffer cache altogether. Also, the stats bear out that Oracle is doing physical reads. But 1 Million real i/o’s can’t happen in 11 seconds so it’s got to be memory access right? (I’m sure you’re way ahead of me by now) The answer of course is "Right – it is memory access!" – but it’s the Linux file system cache not the Oracle buffer cache. A quick check of the Linux memory usage showed us that the file cache was over 20G and the table was less than 10G. So Oracle must be set up without Direct I/O.

    SYS@DELL> !cat /proc/meminfo 
     
    MemTotal:     32948892 kB 
    MemFree:       2769420 kB 
    Buffers:        615664 kB 
    Cached:       24727756 kB 
    SwapCached:     101844 kB 
    Active:       21322184 kB 
    Inactive:      5261948 kB 
    HighTotal:           0 kB 
    HighFree:            0 kB 
    LowTotal:     32948892 kB 
    LowFree:       2769420 kB 
    SwapTotal:    34996216 kB 
    SwapFree:     34147776 kB 
    Dirty:             448 kB 
    Writeback:           0 kB 
    AnonPages:     1138828 kB 
    Mapped:        4758828 kB 
    Slab:          1088356 kB 
    PageTables:     167464 kB 
    NFS_Unstable:        0 kB 
    Bounce:              0 kB 
    CommitLimit:  51470660 kB 
    Committed_AS: 15637340 kB 
    VmallocTotal: 34359738367 kB 
    VmallocUsed:    371108 kB 
    VmallocChunk: 34359367199 kB 
    HugePages_Total:     0 
    HugePages_Free:      0 
    HugePages_Rsvd:      0 
    Hugepagesize:     2048 kB  
     
    SYS@DELL> !realfreemem.sh -a 
     
    Free Memory: 2703M 
    Cached Memory: 24148M 
    Total Free Memory: 26851M 
    Total Memory: 32176M 
    Percent Memory Free (including cache): 83%  
     
    SYS@DELL> @table_size 
    Enter value for owner: INSTRUCTOR 
    Enter value for table_name: CLASS_SALES 
    Enter value for type:   
     
    OWNER                SEGMENT_NAME                   TYPE               TOTALSIZE_MEGS TABLESPACE_NAME 
    -------------------- ------------------------------ ------------------ -------------- ------------------------------ 
    INSTRUCTOR           CLASS_SALES                    TABLE                     9,151.0 ODACOMP_DATA 
                                                                           -------------- 
    sum                                                                           9,151.0  
     
    Elapsed: 00:00:00.04
     
    SYS@DELL> -- so how is Oracle configured for I/O? 
    SYS@DELL> @parms 
    Enter value for parameter: filesys 
    Enter value for isset:  
    Enter value for show_hidden:   
     
    NAME                                               VALUE                                                                  ISDEFAUL ISMODIFIED ISSET 
    -------------------------------------------------- ---------------------------------------------------------------------- -------- ---------- ---------- 
    filesystemio_options                               NONE                                                                   TRUE     FALSE      FALSE  
     
    Elapsed: 00:00:00.01

    So the database was not configured to use direct i/o or async i/o (filesystemio_options=none). A quick check with strace verified that direct i/o was not being used. So we modified the filesystemio_options setting and tried again.

    SYS@DELL> alter system set filesystemio_options=setall scope=spfile;  
     
    System altered.  
     
    SYS@DELL> startup force 
    ORACLE instance started.  
     
    Total System Global Area 4910620672 bytes 
    Fixed Size                  2236648 bytes 
    Variable Size            2298482456 bytes 
    Database Buffers         2600468480 bytes 
    Redo Buffers                9433088 bytes 
    Database mounted. Database opened. 
     
    SYS@ODACOMP>  
    SYS@ODACOMP> @uptime  
     
    INSTANCE_NAME    STARTUP_TIME               CURRENT_TIME                  DAYS    SECONDS 
    ---------------- -------------------------- -------------------------- ------- ---------- 
    ODACOMP          23-NOV-2011 12:45          23-NOV-2011 12:47              .00        121  
     
    SYS@ODACOMP> set sqlprompt "'_USER'@'DELL'>'" 
    SYS@DELL>
    SYS@DELL> ! cat /etc/redhat-release 
     
    Red Hat Enterprise Linux Server release 5.5 (Tikanga)  
     
    SYS@DELL> ! uname -a 
     
    Linux homer.enkitec.com 2.6.18-194.el5 #1 SMP Mon Mar 29 22:10:29 EDT 2010 x86_64 x86_64 x86_64 GNU/Linux  
     
    SYS@DELL> set timing on 
    SYS@DELL> alter session set '_serial_direct_read'=always;  
     
    Session altered.  
     
    Elapsed: 00:00:00.00
     
    SYS@DELL> set autotrace on 
     
    SYS@DELL> select count(*) from instructor.class_sales;    
     
    COUNT(*) 
    ----------   
    90000000  
     
    Elapsed: 00:01:39.73  
     
    Execution Plan 
    ---------------------------------------------------------- 
    Plan hash value: 3145879882  
     
    -------------------------------------------------------------------------- 
    | Id  | Operation          | Name        | Rows  | Cost (%CPU)| Time     | 
    -------------------------------------------------------------------------- 
    |   0 | SELECT STATEMENT   |             |     1 |   315K  (1)| 01:03:08 | 
    |   1 |  SORT AGGREGATE    |             |     1 |            |          | 
    |   2 |   TABLE ACCESS FULL| CLASS_SALES |    90M|   315K  (1)| 01:03:08 | 
    --------------------------------------------------------------------------   
     
     
    Statistics ----------------------------------------------------------  
             1  recursive calls  
             0  db block gets  
       1168567  consistent gets  
       1168557  physical reads  
             0  redo size  
           526  bytes sent via SQL*Net to client  
           524  bytes received via SQL*Net from client  
             2  SQL*Net roundtrips to/from client  
             0  sorts (memory)  
             0  sorts (disk)  
             1  rows processed  
     
    SYS@DELL> set autotrace off 
    SYS@DELL> @fss 
    Enter value for sql_text: select count(*) from instructor.class_sales 
    Enter value for sql_id:   
     
    SQL_ID         CHILD      EXECS   AVG_ROWS     AVG_ETIME       AVG_CPU       AVG_PIO      AVG_LIO SQL_TEXT 
    ------------- ------ ---------- ---------- ------------- ------------- ------------- ------------ ---------------------------------------- 
    b2br1x82p9862      0          1          1         99.71          7.32  1,168,557.00    1,168,567 select count(*) from instructor.class_sa  
     
    Elapsed: 00:00:00.05

    Ah that’s more like it. Roughly 100 seconds now to complete the query. That’s 10X slower. Nice job. It feels so good when you can make something go slower. ;) I should note that in general, it is a very good idea to set filesystemio_options=SETALL. So please don’t jump to the conclusion that setting this parameter to NONE will make your database run faster. These days, the memory is generally better used by Oracle than by the file system cache. Glenn Fawcett has a good (if somewhat dated) post which compares the effects of caching blocks in the file system cache vs. in the Oracle buffer cache in Solaris. As you might image, allowing Oracle to use the memory is generally more effective as shown in his results. So that’s it for today. By the way, this post took 4 times as long to write as the actual testing took. Maybe I’ll get better at it if I start practicing a little more often.

    Why Isn’t My Index Used… When USER2 Executes this Query?

    November 23, 2011 I previously wrote a couple of articles that mention reasons why an index might not be use for a particular query, including an article that was formatted as a True or False quiz with several reference articles.  A few days ago I saw an OTN thread that caught my curiosity, where the [...]

    Mark Hurd Knows CIOs. I Know Trivia. CIOs May Not Care About Either! Hang On, I’m Booting My Cell Phone.

    According to this techweb article, one of Oracle’s presidents “knows CIOs.” The article didn’t spend much time substantiating that notion but the title roped me in. I did read the entire article which I feel entitles me to blog a bit.

    First, I’ll simply quote a quote that the article attributes to Oracle’ Mark Hurd:

      Hurd reiterated Oracle’s claim that the highly tuned Exadata hardware-software combo yields 70x performance improvements–reports that took 70 minutes now take one minute

    Sure, Exadata can easily be 70x faster than some other system.  For instance, the “70-minute system” might have been a 2-socket Harpertown Xeon-based server. That would be about 1/70th Exadata’s database grid–from a CPU perspective. Or, perhaps, the Exadata 70x edge on these “reports” came from improved I/O.  In that case, perhaps the 70-minute system was attached to storage that provided about 1GB/s (e.g.,  a low-end storage array that suffered controller saturation at 1GB/s). That would be about 1/70th the storage bandwidth of a full-rack Exadata configuration. But that all seems unlikely. It is much more likely that someone took the time to tune the query plans used by the “report” in which case the storage and CPU doesn’t really factor as heavily.

    Certainly the I/O power of Exadata was not the 70x ingredient. Allow me to explain. If the “report” actually touched the same amount of data in the Exadata case the total data visited would have been about 5 terabytes and nobody runs “reports” that perform nearly 5 TB of disk I/O. We are talking about Oracle database after all and therefore the 70-minute system would have had indexes, partitioning and a other such I/O-elimination features available to it. Visiting nearly 5TB of data after I/O elimination (e.g., partition elimination, indexes, etc)  is unlikely. Unless the query plan was non-optimal (likely). But I’m not blogging about that.

    The article continues to quote Mark Hurd:

    The customer who says it cost me $7 million to do that job before, you can literally take 70x off that and it costs him $100,000

    That’s weird math and I’m simply not going to blog about that.

    Finally, the article quotes Mark Hurd’s take on Big Data:

    Well, it’s a tough world, man. When I grew up in this industry, there were IBM 360s, DEC VAXs, Data Generals–all that kind of stuff. And this [pointing to his iPhone] is a VAX. The power in this thing is like a VAX.

    Alright, so that is the quote I’m blogging about.  The VAX family of products spanned many generations. However, if one mentions IBM 360 and VAX in the same sentence we can safely presume the VAX in mind is of the printed circuit board (PCB) era. While I’m personally not quoted in press articles as “knowing CIOs”, I do know trivia. DEC VAX products of the PCB era were 1MIPS machines.  I cannot impress upon you how terribly disappointed you’d be just waiting for an iPhone application to start up on a 1MIPS system.

    No, I can’t go about saying I “know CIOs” but I do know that the processor in my smart phone—a Qualcomm Snapdragon—is a 2100 MIPS processor.

    Yes, sadly, 2100x is all I’m blogging about.

    Filed under: oracle

    How deep to dig – Another Opinion and Another Good Blog

    I think I’ve posted before about how deep a good DBA should dig into solving issues, as opposed to fixing them as soon as possible and moving on to the next urgent task.

    Well, a friend of mine, Neil Chandler, has just posted on this topic, giving his reasons why you don’t run a 10046 trace on production. Neil raises some good points about how difficult it can be to get permission to do something as intrusive as a 10046 trace on a production system as well as the fact that most problems can be solved way before you get down to the level of tracing. Especially if it is not your job to go around solving the problems that have stumped the in-house team, which is the lot of many people who are recognised as being very good with Oracle.

    That leads me onto a slightly different topic. For every one of those technicians who’s names you know there are at least a dozen who are just as good but not as visible. Some of us choose to make more “noise” {blogging, presenting, writing articles} so we become visible. Others also support the user community but in a less noticeable way, some even positively choosing to keep a lower profile. Neil is one of those. He’s a very good Oracle and SQL*Server DBA and also very knowledgeable about Unix OS’s and SANs/storage {though he would maybe argue he is not – don’t believe him}. When he comes along to the pub his is an opinion worth listening to {once he’s finished demeaning me that is, and I’m sure he will give me a hard time about this article about him next time we meet} and when I’ve asked his opinion he has never failed to help. Neil also supports the UKOUG, he’s been deputy chair of one of the SIGs since it’s inception – but he refuses to be chair, has kept away from presenting and he keeps in the background.

    So, I was very happy when Neil started blogging. It is a mixture of technical stuff and observations on the IT world, all written in a very comfortable style – Look back at his previous post on timestamps not being impacted by FIXED_DATE. A nice piece of information to tuck into your bag of “things to be aware of”.

    So, a belated welcome to the world of blogging Neil. I think it is a blog worth watching.

    So what triggers are we talking about?

    Here's an overview of the talk.

    This post will deal with the first bullet: the scope, what triggers are we talking about? And what triggers are we not talking about. Then there will probably be a few posts on 'properties' of the triggers, most notably I will spend some time on explaining the infamous mutating table error. Next we move on to a high level classification of use-cases of triggers. And talk a bit about why some of these might be considered harmful. Finally we will explain, in detail, the one use case where triggers are the perfect means to achieve the end.

    The most common types of triggers, the ones everybody probably used at some time in their pl/sql programming career, are the "DML event" triggers. As above slide shows, there are twelve of such triggers: four each for every type of DML statement, Insert, Update and Delete. These triggers will be fired by the DBMS before a DML statement, after a DML statement, and before/after each affected row of the triggering DML statement. Stuff you all know right? The big difference between the statement-level and the row-level triggers, is that the latter ones can inspect (and change) the column-values of the current row that is being inserted/deleted/updated.

    So here's an example. Suppose we have an EMP table that holds employees, and we want to execute an update statement that will increase the salary of all clerks (see update statement above). This will for the given table affect three rows. The before update statement trigger will then fire once. Next for each affected row the before and after row triggers will fire. And finally the after statement trigger will fire.

    So if we create the four update triggers on the EMP table as follows:

    We will get the following output (given we have set serveroutput to on).

    Nothing new so far, I hope. Before we continue I just wanted to mention that as of Oracle11G we have the compound trigger feature.

     A compound trigger enables us to create the four update triggers above all in one go as follows:

    Now, do you know why Oracle introduced compound triggers? I'll talk about that in a later post. What I'll say now is this: compound triggers are the answer of an enhancement request made by you (the pl/sql community) a long time ago. Because you have always hit a certain programming pattern with regards to triggers, when using them for a certain use case. Again I'll explain this in more detail in a future post.

    So these are the triggers that are in-scope of this blog: DML event triggers, be them created individually or four in one go using the compound trigger mechanism.

    Oracle DBMS offers us with many more triggers:

    All of which will not be the matter of subject for this blog.

    Stay tuned.

    The Twilight Saga: Breaking Dawn – Part 1…

    I’ve been looking forward to seeing The Twilight Saga: Breaking Dawn – Part 1 for a while. If you follow my blog you will know I pretty much hated all the previous films.

    So why was I so keen to watch it? Basically so I could wax lyrical about how bad a film it is…

    Well, now I’ve seen it and I’m a little disappointed because it wasn’t as bad as I wanted it to be. Don’t let that get your hopes up. It was terrible, but I wanted it to be worse. Since the last outing the main characters have learned a couple of extra expressions. I wouldn’t call it acting, but it is not so wooden as the previous films. It starts off with a sickly saccharin story line, then descends into something a little darker. It was at this point that for a fleeting moment I thought I might actually… like this film (insert gasp)… But no, it just dragged on and every time there was a scene that could have been developed into something cool, it either petered out, or turned into something from a My Little Pony story. It was just anti-climax after anti-climax.

    So in conclusion, it was moderately less toilet than the other films, but not by much!

    This has kinda ruined everything for me. I was looking forward to the last film in the series so I could hate that also, but now I feel rather apathetic about it all… :(

    Cheers

    Tim…