Cool SQLs

CheckAccessPatterns.sql

/*
http://www.dba-oracle.com/t_plan9i_sql_full_table_scans.htm

Here is a great script to show SQL access patterns, grouped by full-table scans, index range scans and index unique scans

 */

--**************************************************************
-- Object Access script report
--
-- © 2012 by Donald K. Burleson
--
--   No part of this SQL script may be copied. Sold or distributed
--   without the express consent of Donald K. Burleson
--**************************************************************

-- ********************************************************
-- Report section
-- ********************************************************

spool plan.lst

set echo off
set feedback on

set pages 999;
column nbr_FTS  format 9,999,999
column num_rows format 999,999,999
column blocks   format 999,999
column owner    format a14;
column name     format a24;
column ch       format a1;

column object_owner heading "Owner"            format a12;
column ct           heading "# of SQL selects" format 999,999;

select
   object_owner,
   count(*)   ct
from
   v$sql_plan
where
   object_owner is not null
group by
   object_owner
order by
   ct desc
;


--spool access.lst;

set heading on;
set feedback on;

ttitle 'full table scans and counts|  |The "K" indicates that the table is in the KEEP Pool (Oracle8).'
select
   p.owner,
   p.name,
   t.num_rows,
--   ltrim(t.cache) ch,
   decode(t.buffer_pool,'KEEP','Y','DEFAULT','N') K,
   s.blocks blocks,
   sum(a.executions) nbr_FTS
from
   dba_tables   t,
   dba_segments s,
   v$sqlarea    a,
   (select distinct
     address,
     object_owner owner,
     object_name name
   from
      v$sql_plan
   where
      operation = 'TABLE ACCESS'
      and
      options = 'FULL') p
where
   a.address = p.address
   and
   t.owner = s.owner
   and
   t.table_name = s.segment_name
   and
   t.table_name = p.name
   and
   t.owner = p.owner
   and
   t.owner not in ('SYS','SYSTEM')
having
   sum(a.executions) > 1
group by
   p.owner, p.name, t.num_rows, t.cache, t.buffer_pool, s.blocks
order by
   sum(a.executions) desc;


column nbr_RID  format 999,999,999
column num_rows format 999,999,999
column owner    format a15;
column name     format a25;

ttitle 'Table access by ROWID and counts'
select
   p.owner,
   p.name,
   t.num_rows,
   sum(s.executions) nbr_RID
from
   dba_tables t,
   v$sqlarea s,
  (select distinct
     address,
     object_owner owner,
     object_name name
   from
      v$sql_plan
   where
      operation = 'TABLE ACCESS'
      and
      options = 'BY ROWID') p
where
   s.address = p.address
   and
   t.table_name = p.name
   and
   t.owner = p.owner
having
   sum(s.executions) > 9
group by
   p.owner, p.name, t.num_rows
order by
   sum(s.executions) desc;

--*************************************************
--  Index Report Section
--*************************************************

column nbr_scans  format 999,999,999
column num_rows   format 999,999,999
column tbl_blocks format 999,999,999
column owner      format a9;
column table_name format a20;
column index_name format a20;

ttitle 'Index full scans and counts'
select
   p.owner,
   d.table_name,
   p.name index_name,
   seg.blocks tbl_blocks,
   sum(s.executions) nbr_scans
from
   dba_segments seg,
   v$sqlarea s,
   dba_indexes d,
  (select distinct
     address,
     object_owner owner,
     object_name name
   from
      v$sql_plan
   where
      operation = 'INDEX'
      and
      options = 'FULL SCAN') p
where
   d.index_name = p.name
   and
   s.address = p.address
   and
   d.table_name = seg.segment_name
   and
   seg.owner = p.owner
having
   sum(s.executions) > 9
group by
   p.owner, d.table_name, p.name, seg.blocks
order by
   sum(s.executions) desc;


ttitle 'Index range scans and counts'
select
   p.owner,
   d.table_name,
   p.name index_name,
   seg.blocks tbl_blocks,
   sum(s.executions) nbr_scans
from
   dba_segments seg,
   v$sqlarea s,
   dba_indexes d,
  (select distinct
     address,
     object_owner owner,
     object_name name
   from
      v$sql_plan
   where
      operation = 'INDEX'
      and
      options = 'RANGE SCAN') p
where
   d.index_name = p.name
   and
   s.address = p.address
   and
   d.table_name = seg.segment_name
   and
   seg.owner = p.owner
having
   sum(s.executions) > 9
group by
   p.owner, d.table_name, p.name, seg.blocks
order by
   sum(s.executions) desc;


ttitle 'Index unique scans and counts'
select
   p.owner,
   d.table_name,
   p.name index_name,
   sum(s.executions) nbr_scans
from
   v$sqlarea s,
   dba_indexes d,
  (select distinct
     address,
     object_owner owner,
     object_name name
   from
      v$sql_plan
   where
      operation = 'INDEX'
      and
      options = 'UNIQUE SCAN') p
where
   d.index_name = p.name
   and
   s.address = p.address
having
   sum(s.executions) > 9
group by
   p.owner, d.table_name, p.name
order by
   sum(s.executions) desc;

spool off

TunningSQLs.sql

/*
The V$LIBRARYCACHE table contains statistics on how well you are utilizing the library cache. The important columns to view in this table are PINS and RELOADS.
--PINS: The number of times the item in the library cache was executed.
--RELOADS: The number of times the library cache misses and the library object must be reloaded.
A low number of reloads relative to the number of executions indicates a high cache-hit rate. To get an idea of the total number of cache misses, use this statement
 */

--Cache Misses
SELECT SUM(reloads) "Cache Misses", SUM(pins) "Executions", 100 * ( SUM(reloads) / SUM(pins) ) "Cache Miss Percent"
  FROM v$librarycache;

/*
The total amount of reloads should be near zero. If you see more than 1 percent library cache misses, take action. You can reduce the number of cache misses by writing identical SQL statements
or by increasing the size of the library cache. You should be able to reduce the library cache misses by increasing the amount of memory available for the library cache. This can be done by increasing the Oracle tunable parameter SHARED_POOL_SIZE.
 */
SELECT namespace, reloads "Cache Misses", pins "Executions"
  FROM v$librarycache;

/*
You can check the efficiency of the data dictionary cache. Statistics for the data dictionary cache are stored in the dynamic performance table V$ROWCACHE (the data dictionary cache is sometimes known as the row cache). The important columns to view in this table are GETS and GETMISSES:
- GETS: The total number of requests for the particular item.
- GETMISSES: The total number of requests resulting in cache misses.
A few number of cache misses are expected, especially during startup when the cache has not been populated.
 */
SELECT SUM(getmisses) "Cache Misses", SUM(gets) "Requests", 100 * ( SUM(getmisses) / SUM(gets) ) "Cache Miss Percent"
  FROM v$rowcache;

SELECT parameter, getmisses "Cache Misses", gets "Requests"
  FROM v$rowcache;

-----------------------------------------------------------
select sid, value "Session UGA Memory"
from v$sesstat,v$statname
WHERE name = 'session uga memory' AND v$sesstat.STATISTIC# = v$statname.STATISTIC#;

select SUM(value) "Sum of Session UGA Memory"
from v$sesstat,v$statname
WHERE name = 'session uga memory' AND
v$sesstat.statistic# = v$statname.statistic#;

select sid, value "Session PGA Memory"
from v$sesstat,v$statname
WHERE name = 'session pga memory' AND
v$sesstat.statistic# = v$statname.statistic#;

select SUM(value) "Sum of Session PGA Memory"
from v$sesstat,v$statname
WHERE name = 'session pga memory' AND
v$sesstat.statistic# = v$statname.statistic#;

select sid, value "Session UGA Memory Max"
from v$sesstat,v$statname
WHERE name = 'session uga memory max' AND
v$sesstat.statistic# = v$statname.statistic#;

select SUM(value) "Sum of Session UGA Memory Max"
from v$sesstat,v$statname
WHERE name = 'session uga memory max' AND
v$sesstat.statistic# = v$statname.statistic#;

select sid, value "Session PGA Memory Max"
from v$sesstat,v$statname
WHERE name = 'session pga memory max' AND
v$sesstat.statistic# = v$statname.statistic#;

select SUM(value) "Sum of Session PGA Memory Max"
from v$sesstat,v$statname
WHERE name = 'session pga memory max' AND
v$sesstat.statistic# = v$statname.statistic#;

/*
 Degree of Parallelism

 */
select * from v$pq_sysstat;

WithClause.sql

WITH sdeCal AS (
        SELECT sde.datagroup AS StaticCalendar, stringvalue AS StaticHoliday,
               DBMS_UTILITY.get_hash_value(sde.datagroup || to_date(stringvalue,'DDMONYYYY'), 1, POWER(2,16)-1) AS Matching
          FROM scalendar sde
          LEFT JOIN scalendar2 sdec
          ON sde.id            =sdec.id
          WHERE sde.datasetname='HOLIDAY CALENDAR'
          AND propertyname     ='Holiday Date'
          ORDER BY sde.datagroup, to_date(sdec.stringvalue,'DDMONYYYY')),
      hcCal AS (
        SELECT hc.name AS HolidayCalendar, hcc.datevalue AS Holiday,
               DBMS_UTILITY.get_hash_value(hc.name || hcc.datevalue, 1, POWER(2,16)-1) AS Matching
          FROM sholidaycalendar hc
          LEFT JOIN sholidaycalendar2 hcc
          ON hc.id=hcc.id
          ORDER BY hc.name, hcc.datevalue)
SELECT decode(sdeCal.StaticCalendar,    NULL, ' ', sdeCal.StaticCalendar)   AS StaticCalendar,
       decode(sdeCal.StaticHoliday,     NULL, ' ', sdeCal.StaticHoliday)    AS StaticHoliday,
       decode(hcCal.HolidayCalendar,    NULL, ' ', hcCal.HolidayCalendar)   AS HolidayCalendar,
       decode(hcCal.Holiday,            NULL, ' ', hcCal.Holiday)           AS Holiday,
       decode(sdeCal.Matching - hcCal.Matching, 0, 'VALID   - Matched',
                                                   'INVALID - Different')  AS Static_Vs_Calendar
  FROM sdeCal
  FULL OUTER JOIN hcCal
  ON sdeCal.Matching = hcCal.Matching;