Measure Table Size in ORACLE

Sometimes it is required to measure how much space a tables occupy in Oracle. The size sum-up table and its co-related indexes, partitions, lobs, lob partitions. here are two SQL scripts, first one listed all tables own by a specific user and second one  include tablespace too.

SELECT segment_name, sum(size_mb) size_mb
FROM (
SELECT CASE WHEN x.segment_type in ('LOBSEGMENT','LOB PARTITION') THEN 
  (SELECT table_name FROM dba_lobs y WHERE y.segment_name= x.segment_name)
  WHEN x.segment_type='LOBINDEX' THEN 
  (SELECT table_name FROM dba_lobs y WHERE y.index_name = x.segment_name)
  WHEN x.segment_type in('INDEX','INDEX PARTITION','INDEX SUBPARTITION') THEN 
  (SELECT y.table_name FROM dba_indexes y WHERE y.index_name = x.segment_name)
  WHEN x.segment_type in ('TABLE SUBPARTITION', 'TABLE PARTITION','TABLE')
  THEN x.segment_name END segment_name
,round(sum(x.bytes)/(1024*1024),2) size_mb 
FROM dba_segments x 
WHERE x.owner ='SCOTT'
GROUP BY x.segment_name, x.segment_type
)
GROUP BY segment_name
ORDER BY size_mb DESC;


SELECT segment_name, tablespace_name, sum(size_mb) size_mb
FROM (
SELECT CASE WHEN x.segment_type in ('LOBSEGMENT','LOB PARTITION') THEN 
  (SELECT table_name FROM dba_lobs y WHERE y.segment_name= x.segment_name)
  WHEN x.segment_type='LOBINDEX' THEN 
  (SELECT table_name FROM dba_lobs y WHERE y.index_name = x.segment_name)
  WHEN x.segment_type in('INDEX','INDEX PARTITION','INDEX SUBPARTITION') THEN 
  (SELECT y.table_name FROM dba_indexes y WHERE y.index_name = x.segment_name)
  WHEN x.segment_type in ('TABLE SUBPARTITION', 'TABLE PARTITION','TABLE')
  THEN x.segment_name END segment_name
, x.tablespace_name
, round(sum(x.bytes)/(1024*1024),2) size_mb 
FROM dba_segments x 
WHERE x.owner ='SCOTT'
GROUP BY x.segment_name, x.tablespace_name,x.segment_type
)
GROUP BY segment_name, tablespace_name
ORDER BY size_mb DESC;

Finding Duplicate SQL

The presence of duplicate SQL indicate that there are some SQL statements which doesn't have Bind variables. These duplicate SQL can raise performance issue because they will increase the number of hard parse in database.

ORACLE 10g introduced two new columns in v$sql view, which can help to identifing duplicate SQL more accurately. Those two new columns are:

1. force_matching_signature 
2. exact_matching_signature

exact_matching_signature - If two or more SQL has same value in this column, ORACLE assumes they are same after making some cosmetic adjustments (removing white space, uppercasing all keywords etc) to them. The is simmiler, when parameter cursor_sharing is set to EXACT.

force_matching_signature - the same value in this column (excluding 0) marks SQLs that ORACLE will consider they are same when it replaces all literals with binds (that is, if cursor_sharing=FORCE).



SELECT sql_text , count(1)
FROM v$sql
WHERE force_matching_signature > 0
  AND force_matching_signature <> exact_matching_signature
GROUP BY sql_text
HAVING count(1) > 10
ORDER BY 2;

January 2009

January 07: We can use a single character wild card search In LIKE. For an example - where a.text LIKE '%_L_NK%'. '_' is replace by any character.

January 15: In parent child situation , you can delete records from parent if you delete child records first. In this situation you can truncate child table but not parent table. To truncate parent table you must disable all foreign key constraint of child tables. Interesting :)

Parse To Execute Ratio

All Oracle SQL statements must be parsed at the first time that they execute. Parsing involves a syntax check, a semantic check (against the dictionary), the creation of a decision tree, and the generation of the lowest cost execution plan. Once the execution plan is created, it is stored in the library cache (part of the shared pool) to facilitate re-execution. There are two types of parses:

Hard parse

A new SQL statement must be parsed from scratch. If the database is parsing every statement that is executing, the parse to execute ratio will be close to 1% (high hard parses), often indicating non-reentrant SQL that does not use host variables (Bind Variables).

Soft parse

A reentrant SQL statement where the only unique feature are host variables. The best-case scenario is a parse to execute ratio of 100% which would indicate an application with fully reentrant SQL that parses SQL once and executes many times.




In a real database, some SQL statements will be fully reentrant (execute to parse = 100%), while others must be re-parsed for every execution (execute to parse = 1%). You can see this is the instance efficiency of any STATSPACK and AWR report.


High parses suggests that your system has many incoming unique SQL statements, or that your SQL is not reentrant (i.e. literal values in the WHERE clause, not using bind variables). A hard parse is expensive because each incoming SQL statement must be re-loaded into the shared pool; with the associated overhead involved in shared pool RAM allocation and memory management. Once loaded, the SQL must then be completely re-checked for syntax & semantics and an executable generated. Excessive hard parsing can occur when your shared_pool_size is too small (and reentrant SQL is paged out), or when you have non-reusable SQL statements without host variables.



If the execute to parse ratio is too low, it is possible that the application is not using shareable SQL, or the database has sub-optimal parameters that are reducing the effectiveness of cursor sharing. A problem like excessive parsing is likely to manifest itself as additional network traffic between the application server and clients. The additional parse activity may also show up as a marked increase in CPU consumption on the database server.

Here is a simple sql query which describe the parse call and execution of current sql queries :

Select x.sql_text , x.parse_calls , x.executions
,round( 100*(1-( x.parse_calls / x.executions )),2) execute_to_parse_ratio
FROM v$sql x
WHERE x.parse_calls >0
AND x.executions !=0
AND x.parsing_schema_name='EMP'
ORDER BY execute_to_parse_ratio ;

SELECT x.executions ,
  x.parse_calls ,
  ROUND( 100*(1-(x.parse_calls/x.executions)),2) execute_to_parse_ratio
  , x.sql_text
FROM
  (SELECT DBMS_LOB.SUBSTR (sq.sql_text,500,1) sql_text ,
    SUM(st.executions_delta) executions ,
    SUM(st.parse_calls_delta) parse_calls
  FROM DBA_HIST_SQLSTAT st ,
    DBA_HIST_SQLTEXT sq ,
    DBA_HIST_SNAPSHOT s
  WHERE s.snap_id           = st.snap_id
  AND s.begin_interval_time > sysdate-14
  AND s.end_interval_time   < sysdate
  AND st.sql_id             = sq.sql_id
  AND st.parsing_schema_name='EMP'
  GROUP BY DBMS_LOB.SUBSTR (sq.sql_text,500,1)
  ) x
WHERE x.executions != 0
AND ROUND( 100*(1-(x.parse_calls/x.executions)),2) < 10
ORDER BY execute_to_parse_ratio ;

Related Topics :

1. Bind Variable