Sunday, December 1, 2013

Help on some information from other systems please for Hotsos 2014 presentation

Looking for some output back from some other systems to categorize "how many different sql statements" and "how many different execution plans".

Along with the output would be helpful to me to note please what release and what "kind" of system OLTP / ERP / DW etc.  If done on a 12c system using CDB/PDB please run from CDB connection.

Output back here or emailed to would be much appreciated.

select round(bytes/(1024*1024),0) count, 'Shared Pool Size' info from v$sgainfo where name = 'Shared Pool Size'
union all
select count(*), 'First copy of sql' from v$sql_shared_cursor where child_number = 0
union all
select count(*), 'Not first copy' from v$sql_shared_cursor where child_number >= 1
union all
select count(*), 'Total Execution plans' from v$sql_shared_cursor
union all
select count(*), 'Count from sqlarea' from v$sqlarea;


  1. DW:
    -------------- ---------------------------------------------------------------
    10752 Shared Pool Size
    170967 First copy of sql
    8645 Not first copy
    179612 Total Execution plans
    171094 Count from sqlarea
    General Purpose DS:
    -------------- ---------------------
    3264 Shared Pool Size
    22397 First copy of sql
    25509 Not first copy
    47906 Total Execution plans
    22721 Count from sqlarea
    Psoft HR:
    -------------- ---------------------------------------------------------------
    2976 Shared Pool Size
    42720 First copy of sql
    4005 Not first copy
    46726 Total Execution plans
    44009 Count from sqlarea
    Interesting metrics, thanks for that!

  2. Oh, apologies: all on, Aix 7.1, mix of P7+ and P6.