Getting information about table size, lob segment and lob index segment size which belongs to table

set linesize 256
col owner for a30
col table_name for a30
col segment_name for a90
col LOB_SECURED for a11
col LOB_COMPRESSION for a15
 
with t_tab as (
select -- Information about table size
       t.OWNER,
       t.TABLE_NAME,
       t.COMPRESSION as TABLE_COMPRESSION,
       t.COMPRESS_FOR as TABLE_COMPRESS_FOR
  from DBA_TABLES t
 where 1 = 1
   and t.TABLE_NAME = NVL(UPPER('&s_table_name'), t.TABLE_NAME)
   and t.OWNER = NVL(UPPER('&s_owner'), t.OWNER)
   and t.OWNER not in ('SYS', 'SYSTEM', 'SYSMAN', 'CTXSYS', 'XDB')
)
select -- Information about table size
       t.OWNER,
       t.TABLE_NAME,
       t.TABLE_COMPRESSION,
       t.TABLE_COMPRESS_FOR,
       ts.SEGMENT_NAME as SEGMENT_NAME,
       cast(null as varchar2(30)) as LOB_SECURED,
       cast(null as varchar2(30)) as LOB_COMPRESSION,
       ROUND(ts.bytes/(1024*1024),2) SPACE_ALOCATED_MB
  from t_tab t
  join DBA_SEGMENTS ts
    on ts.SEGMENT_NAME = t.TABLE_NAME
 where 1 = 1
 union all
select -- Information about lob columns size for table
       t.OWNER,
       t.TABLE_NAME,
       t.TABLE_COMPRESSION,
       t.TABLE_COMPRESS_FOR,
       ls.segment_type || ' "' || dl.segment_name || '" for table "' || dl.table_name || '"' as SEGMENT_NAME,      
       dl.securefile as LOB_SECURED,
       dl.compression as LOB_COMPRESSION,
       ROUND(ls.bytes/(1024*1024),2) SPACE_ALOCATED_MB
  from t_tab t
  join DBA_LOBS dl
    on dl.table_name = t.TABLE_NAME
  join DBA_SEGMENTS ls
    on ls.SEGMENT_NAME = dl.segment_name       
 where 1 = 1
 union all
select -- Information about lob index for lob column
       t.OWNER,
       t.TABLE_NAME,
       t.TABLE_COMPRESSION,
       t.TABLE_COMPRESS_FOR,
       lis.segment_type || ' "' || dl.index_name || '" for LOBSEGMENT "' || dl.segment_name || '"' as SEGMENT_NAME,
       dl.securefile as LOB_SECURED,
       dl.compression as LOB_COMPRESSION,
       ROUND(lis.bytes/(1024*1024),2) SPACE_ALOCATED_MB
  from t_tab t
  join DBA_LOBS dl
    on dl.table_name = t.TABLE_NAME
  join DBA_SEGMENTS lis
    on lis.SEGMENT_NAME = dl.index_name       
 where 1 = 1  
 ORDER BY SPACE_ALOCATED_MB desc, OWNER, TABLE_NAME, SEGMENT_NAME
;

Processes and Sessions Utilization

Sessions utilization

SQL> select (100*current_utilization / limit_value) as session_percent 
  from v$resource_limit
 where resource_name = 'sessions'
;

The limit_value can be checked by:

SQL> show parameter sessions

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sessions                             integer     772

Processes utilization

SQL> select (100*current_utilization / limit_value) as process_percent
  from v$resource_limit
 where resource_name = 'processes'
;

The limit_value can be checked by:

SQL> show parameter processes

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
processes                              integer     500

Determine sessions count from machine, username using v$session

set linesize 256
    col machine format a50
    select machine, username, count(*) cnt
      FROM v$session
     WHERE 1 = 1
     group by machine, username
     order by username, cnt desc
    ;

Output:

MACHINE                                            USERNAME                              CNT
-------------------------------------------------- ------------------------------ ----------
...
...

How to find the SQL statements with hard parses

Find similar SQL statements

1. Find similar SQL statements using gv$open_cursor

select saddr, sid, user_name, address,hash_value,sql_id, sql_text
from gv$open_cursor
where sid in
(select sid from v$open_cursor
group by sid having count(*) > &threshold);

Added sql_id and sort by sql_text to find similar sql's

--
-- Added sql_id and sort by sql_text to find similar sql's
--
select  sql_id, sql_text, count(*) as "OPEN CURSORS", user_name from v$open_cursor
group by sql_id, sql_text, user_name
order by sql_text desc, count(*) desc;

Find SQL_FULL_TEXT by SQL_ID

--
-- Find SQL_FULL_TEXT by SQL_ID
--
select s.sql_id, s.sql_fulltext, s.sql_text
       /*,to_char(substr(s.sql_fulltext, 1, 32767)) as sql_fulltext*/
  from v$sql s
 where s.sql_id = '&s_sql_id'

2. Find similar SQL statements by first 80 characters in SQL_TEXT

SELECT substr(sql_text, 1, 80), COUNT(1)
     , round(SUM(elapsed_time) / power(10, 6), 3) as total_elapsed_time
FROM v$sql
 WHERE 1 = 1
   --and upper(sql_fulltext) not like '%' || 'OPT_DYN_SAMP' || '%'
   --and upper(sql_fulltext) not like '%' || '/* DS_SVC */' || '%'
GROUP BY substr(sql_text, 1, 80)
HAVING COUNT(1) > 10
ORDER BY 2 desc

2.1. Find similar SQL statements by first 80 characters in SQL_TEXT which included specified tables:

SELECT substr(s.sql_text, 1, 80), COUNT(1)
       , round(SUM(elapsed_time) / power(10, 6), 3) as total_elapsed_time
FROM v$sql s
     join (
       select owner, object_name from dba_objects
        where owner = 'SCOTT'
          and object_type = 'TABLE'
          and (
                object_name like upper('emp%')
                or 
                object_name like upper('sal%')
                or
                object_name in ('TAB1', 'TAB2', 'TAB3')
              )
      ) o
   on o.owner = s.parsing_schema_name
 WHERE 1 = 1
   and upper(sql_fulltext) like '%' || upper(o.object_name) || '%'
   and parsing_schema_name = 'SCOTT'
   --and upper(sql_fulltext) not like '%' || 'OPT_DYN_SAMP' || '%'
   --and upper(sql_fulltext) not like '%' || '/* DS_SVC */' || '%'
GROUP BY substr(s.sql_text, 1, 80)
HAVING COUNT(1) > 10
ORDER BY 2 desc

2.3. Find similar SQL statements that have only one execution to see whether they are similar using V$SQLSTATS:

SELECT SUBSTR(SQL_TEXT, 1, 80), COUNT(*)
  FROM V$SQLSTATS
 WHERE EXECUTIONS < 4 
 GROUP BY SUBSTR(SQL_TEXT, 1, 80)
 HAVING COUNT(*) > 1
 ORDER BY 2 DESC;

3. Find SQL_FULL_TEXT by first 80 characters of SQL_TEXT

--
-- Find SQL_FULL_TEXT by first 80 characters of SQL_TEXT
--
select s.sql_id,
       round(s.sharable_mem / 1024 / 1024, 2) as sharable_mem,
       round(s.persistent_mem / 1024 / 1024, 2) as persistent_mem,
       round(s.runtime_mem / 1024 / 1024, 2) as runtime_mem,
       substr(sql_text, 1, 80), s.sql_fulltext 
       /*,to_char(substr(s.sql_fulltext, 1, 32767)) as sql_fulltext*/
  from v$sql s
 where s.sql_text like '%' || '&s_sql_text' || '%'
 order by dbms_lob.getlength(s.sql_fulltext) desc

Find the SQL statements with hard parses

Script copied from forum topic www.orafaq.com - how to find the SQL statement which have many hard parse and modified for personal needs.

If you want to find top sql's with literals, then you can do that with the following script:

-- E. Nossova, Product TuTool : www.tutool.de

set pagesize 0
set feedback off
set verify off
set linesize 180

col nline print newline
col force_match_sig format 9999999999999999999999999
col pct format 99990.99

/* reports top SQL's with literals from the sqlarea,
input parameters:
min_first_load_time in 'dd.mm.yyyy hh24:mi:ss'
                        format,
                    default: trunc(sysdate) 00:00:00,
max_first_load_time in 'dd.mm.yyyy hh24:mi:ss'
                        format,
                    default: sysdate,
top_n - the number of the top sql's 
        (default: 10) */

define min_first_load_time='&min_first_load_time'
define max_first_load_time='&max_first_load_time'
define top_n='&top_n'

select 'Force Matching Signature='||t.force_match_sig||', Count='||max(t.cnt)||', PCT='||max(t.pct)||'%, Min. Username='||min(s.username)||', Max. Username='||max(s.username)||', Min. First Load Time='||max(min_first_load_time)||', Max. First Load Time='||max(max_first_load_time), max(sql_text) nline from
(select u.username, a.force_matching_signature force_match_sig, a.sql_text 
 from v$sql a, dba_users u 
 where 
 a.parsing_user_id = u.user_id and
 to_date(a.first_load_time,'yyyy-mm-dd/hh24:mi:ss') between 
 to_date(nvl('&min_first_load_time',to_char(trunc(sysdate) /*- 1/24*/,'dd.mm.yyyy hh24:mi:ss')), 'dd.mm.yyyy hh24:mi:ss') and
 to_date(nvl('&max_first_load_time',to_char(sysdate,'dd.mm.yyyy hh24:mi:ss')), 'dd.mm.yyyy hh24:mi:ss') and
 a.force_matching_signature != 0 and
 a.exact_matching_signature != 0 and
 a.force_matching_signature != a.exact_matching_signature ) s,
(select * from 
      (select 
              force_matching_signature force_match_sig, 
              count(*) cnt,
              min(first_load_time) min_first_load_time,
              max(first_load_time) max_first_load_time,
              round((ratio_to_report(count(*)) over ())*100, 2)  pct 
         from v$sql 
        where 
          to_date(first_load_time,'yyyy-mm-dd/hh24:mi:ss') between 
            to_date(nvl('&min_first_load_time',to_char(trunc(sysdate) /*- 1/24*/,'dd.mm.yyyy hh24:mi:ss')), 'dd.mm.yyyy hh24:mi:ss') and 
            to_date(nvl('&max_first_load_time',to_char(sysdate,'dd.mm.yyyy hh24:mi:ss')), 'dd.mm.yyyy hh24:mi:ss') and
          force_matching_signature != 0 and
          exact_matching_signature != 0 and
          force_matching_signature != exact_matching_signature
          -- Excluded from report SQL Tuning statements
          and upper(sql_fulltext) not like '%' || 'OPT_DYN_SAMP' || '%'
          and upper(sql_fulltext) not like '%' || '/* DS_SVC */' || '%' 
        group by force_matching_signature
        order by 2 desc
      ) 
where
rownum <= nvl(abs('&top_n'),10)) t
where
s.force_match_sig = t.force_match_sig
group by t.force_match_sig
order by max(t.cnt) desc
/
 



undefine min_first_load_time
undefine max_first_load_time
undefine top_n

set feedback on
set verify on
set linesize 80

Input substitution variables:

/* reports top SQL's with literals from the sqlarea,
input parameters:
min_first_load_time in 'dd.mm.yyyy hh24:mi:ss'
                        format,
                    default: trunc(sysdate) 00:00:00,
max_first_load_time in 'dd.mm.yyyy hh24:mi:ss'
                        format,
                    default: sysdate,
top_n - the number of the top sql's 
        (default: 10) */

Enter value for min_first_load_time: 11.01.2018 00:00:00

Enter value for max_first_load_time: 11.01.2018 23:59:59

Enter value for top_n: 10

Output:

Force Matching Signature=15954354564733902021, Count=317, PCT=14.43%, Min. Username=SCOTT, Max. Username=SCOTT, Min. First Load Time=2018-01-11/01:00:04, Max. First Load Ti
me=2018-01-11/09:00:14
select count(*) from user_sequences where upper(sequence_name) = upper('SQz6zSqA41Lx0AAAE7MhJ30bj4')
...

Get all sql_id versions of matching SQLs by "Force Matching Signature":

set pagesize 0
set feedback on
set verify on

select s.sql_id, s.sql_fulltext, s.sql_text, s.sharable_mem, s.persistent_mem, s.runtime_mem
  from v$sql s
 where s.force_matching_signature = '&force_matching_signature'
 order by dbms_lob.getlength(s.sql_fulltext) desc;

Input substitution variables:

Enter value for force_matching_signature: 15954354564733902021