Foreign Keys with Missing Indexes

--FK Constraint columns not indexed

set lines 160set pages 40col FKCons       format a30col SourceTable  format a30col SourceColumn format a30col TargetTable  format a30col TargetColumn format a30select scc.constraint_name  as FKCons,       scc.table_name       as SourceTable,       scc.column_name      as SourceColumn,       tcc.table_name       as TargetTable,       tcc.column_name      as TargetColumn        from all_cons_columns scc,        all_constraints sc,       all_cons_columns tcc where sc.constraint_name = scc.constraint_name    --Join Cons to Cons Cols   and sc.r_constraint_name = tcc.constraint_name  --Join source to target   and sc.constraint_type = 'R'                    --RI Constraints only   and scc.owner = '&SCHEMA'                           --Only OMS schema   and tcc.table_name||tcc.column_name 
       not in (select i.table_name||i.column_name                  from all_ind_columns i                where i.index_owner = '&SCHEMA')       --FKs not indexed order by scc.table_name/

I can only guess this was a problem in prior releases of the database.  According to this test in 12c, it doesn't seem possible.


SQL> create table source (sourceid number, constraint sourcepk primary key (sourceid));
Table created.

SQL> create table target (targetid number, constraint targetpk primary key (targetid));
Table created.

SQL> alter table target add constraint sourceidfk foreign key (targetid) references source (sourceid);
Table altered.

SQL> col table_name format a20
SQL> col index_name format a20
SQL> col column_name format a20

SQL> select table_name,index_name,column_name from all_ind_columns where index_owner = 'KEN';

TABLE_NAME           INDEX_NAME           COLUMN_NAME
-------------------- -------------------- --------------------
TARGET               TARGETPK             TARGETID
SOURCE               SOURCEPK             SOURCEID

SQL> drop index sourcepk;
drop index sourcepk
           *
ERROR at line 1:
ORA-02429: cannot drop index used for enforcement of unique/primary key

SQL> alter table source disable constraint sourcepk;
alter table source disable constraint sourcepk
*
ERROR at line 1:
ORA-02297: cannot disable constraint (KEN.SOURCEPK) - dependencies exist

AWR Interval and Retention

SQL
select
       extract( day from snap_interval) *24*60+
       extract( hour from snap_interval) *60+
       extract( minute from snap_interval ) "Snapshot Interval",
       extract( day from retention) *24*60+
       extract( hour from retention) *60+
       extract( minute from retention ) "Retention Interval"
from dba_hist_wr_control;

SAMPLE OUTPUT
Snapshot Interval Retention Interval
----------------- ------------------
               60              10080

Got a Large SGA?, USE HugePages

3 Key Reasons:

  1. Oracle "Strongly recommends always deploying HugePages with Oracle Databases"

  2. Reduces memory footprint by a factor of 500

  3. 4K vs. 2MB memory pages.  More pages can be mapped in memory with less overhead

Great video from OpenWorld DemoGrounds


HOW TO

Do steps 1-4 on EACH NODE


1. Set Oracle User memlock Limits
---------------------------------
NOTE: Value should be 90% of total memory
      <value> is in kb (ex. 200 gb = 209715200 kb)

As Root 
vi /etc/security/limits.conf

oracle soft memlock <value>
oracle hard memlock <value>


2. Set kernel.shmmax
--------------------
NOTE: Value should be comfortably larger than the largest SGA

As Root
get existing value:
 cat /proc/sys/kernel/shmmax
set new value:
 vi /etc/sysctl.conf
 AND
 echo <value in bytes> > /proc/sys/kernel/shmmax
 AND
 sysctl –p

verify new value
 cat /proc/sys/kernel/shmmax


3. Set kernel.shmall
--------------------
NOTE: Value should be sum of SGA's / pagesize 

Get pagesize
 getconf PAGESIZE
Determine sum of SGAs
 ipcs -m |grep oracle, sum of 5th column values + future SGAs

example: 
pagesize= 4,096
SGA's   = 92gb + 58gb growth = 161061273600 bytes
SGA's / pagesize = 39,321,600
kernel.shmall should be 39321600

get existing value:
 cat /proc/sys/kernel/shmall
set new value:
 vi /etc/sysctl.conf
 AND
 echo <value> > /proc/sys/kernel/shmall 
 AND
 sysctl -p
verify new value
 cat /proc/sys/kernel/shmall


4. Set vm.nr_hugepages
----------------------
NOTE: Use recommended value returned from hugepages_settings.sh
get the script here

As Root, add the following to /etc/sysctl.conf

 # Hugepages
 # Allow oracle user access to hugepages
 vm.hugetlb_shm_group = 310
 vm.nr_hugepages = <value>

AND run

sysctl -p

verify setting:
 cat /proc/sys/vm/nr_hugepages


5. Set use_large_pages=ONLY in spfile (11g only)
------------------------------------------------
 For Each DATABASE
  alter system set use_large_pages=ONLY scope=spfile;


6. Restart Database(s)
----------------------
 Verify huge pages in use (alert log)
 Run ipcs -m (should only have a couple segments per database)

Session PGA Memory Usage

A helpful script to find sessions using large amounts of system memory:

SET LINESIZE 140
SET PAGESIZE 100
COL session      HEADING 'SID - User - Client' FORMAT a35
COL current_size HEADING 'Current MB' FORMAT '999,999.99'
COL maximum_size HEADING 'Max MB'     FORMAT '999,999.99'
BREAK ON REPORT
COMPUTE SUM LABEL 'Total' OF current_size ON REPORT
SELECT TO_CHAR(ssn.sid, '9999') || ' - ' || 
       NVL(ssn.username, NVL(bgp.name, 'background')) || ' - ' ||
       NVL(lower(ssn.machine), ins.host_name) "SESSION",
       TO_CHAR(prc.spid, '999999999') "PID/THREAD",
       se1.value/1024/1024 current_size,
       se2.value/1024/1024 maximum_size
 FROM  v$sesstat se1, 
       v$sesstat se2, 
       v$session ssn, 
       v$bgprocess bgp, 
       v$process prc,
       v$instance ins,  
       v$statname stat1, 
       v$statname stat2
 WHERE se1.statistic# = stat1.statistic# 
   AND stat1.name = 'session pga memory'
   AND se2.statistic# = stat2.statistic# 
   AND stat2.name = 'session pga memory max'
   AND se1.sid = ssn.sid
   AND se2.sid = ssn.sid
   AND ssn.paddr = bgp.paddr (+)
   AND ssn.paddr = prc.addr  (+)
-- AND NVL(ssn.username, NVL(bgp.name, 'background')) = '<SCHEMA>'
ORDER BY 4
/
!free
exit
/

Sample output:
SID - User - Client                 PID/THREAD  Current MB      Max MB
----------------------------------- ---------- ----------- -----------
cut...
  867 - XXXX - server01                   9615        3.47      223.60
  729 - XXXX - server01                   9611        3.47      223.78
  469 - XXXX - server01                   3479        3.16      223.85
  605 - XXXX - server03                   4007       10.97    1,373.78
 1039 - XXXX - server01                  21216       11.16    1,373.97
  448 - XXXX - server02                   3852       10.22    1,378.91
                                               -----------
Total                                               980.91

             total       used       free     shared    buffers     cached
Mem:     132085672  125739556    6346116          0     836680   24065240
-/+ buffers/cache:  100837636   31248036
Swap:      8193140       9968    8183172

Top Waits by Object

SQL to Identify Objects Creating Cluster-wide bottlenecks (in the past 24 hours)
set linesize 140
set pagesize 50
col sample_time format a26
col event format a30
col object format a45
--col num_sql heading '# SQL' format 9,999
select
       ash.sql_id,
--       count(distinct ash.sql_id) Num_SQL,
       ash.event,
       ash.current_obj#,
       o.object_type,
       o.owner||'.'||o.object_name||'.'||o.subobject_name object,
       count(*)
  from gv$active_session_history ash,
       all_objects o
 where ash.current_obj# = o.object_id
   and ash.current_obj# != -1
   and ash.event is not null
   and ash.sample_time between  sysdate - 1 and sysdate
--   and ash.sample_time between  sysdate - 4 and sysdate - 3
--   and to_date ('24-SEP-2010 14:28:00','DD-MON-YYYY HH24:MI:SS') and to_date ('24-SEP-2010 14:29:59','DD-MON-YYYY HH24:MI:SS')
 group by
       ash.sql_id,
       ash.event,
       ash.current_obj#,
       o.object_type,
       o.owner||'.'||o.object_name||'.'||o.subobject_name
having count(*) > 20
 order by count(*) desc
/
exit
/

How to Enable and Disable Block Change Tracking

ENABLE

1. Verify BCT is disabled

   SELECT status FROM v$block_change_tracking;

2. Enable BCT with the database OPEN
   alter database enable block change tracking using file '+MY_FRA';
    Database altered.

3. Query BCT
    set lines 100
    col filename format a50
    col size_mb format 99,999
    select status,filename,bytes/1024/1024 size_mb from v$block_change_tracking;

    STATUS     FILENAME                                           SIZE_MB
    ---------- -------------------------------------------------- -------
    ENABLED    +MY_FRA/mydb/changetracking/ctf.6151.791995761        11

DISABLE

1. Verify BCT is enabled

   SELECT status FROM v$block_change_tracking;

2. Enable BCT with the database OPEN
   alter database disable block change tracking;
    Database altered.

Top 5 Timed Wait Events

SQL: (11.1 db required, min)

set feedback off
set verify off
col dbid new_value v_dbid
col min_snap new_value v_min_snap
col max_snap new_value v_max_snap
set termout off
select (select dbid from v$database) dbid,1,min(dhs.snap_id) min_snap, max(dhs.snap_id) max_snap
  from dba_hist_snapshot dhs
 where dhs.end_interval_time >= to_date(sysdate - 1)
   and dhs.instance_number = 1
 group by dbid
/
set termout on
set heading off
--select output from table(DBMS_WORKLOAD_REPOSITORY.awr_report_text (&v_dbid,1,&v_min_snap,&v_max_snap))
WITH aa AS
(SELECT output, ROWNUM r
FROM table(DBMS_WORKLOAD_REPOSITORY.awr_report_text (&v_dbid, 1, &v_min_snap, &v_max_snap)))
SELECT output top_five
FROM aa, (SELECT r FROM aa
WHERE output LIKE 'Top 5 Timed Foreground Events%') bb
WHERE aa.r BETWEEN bb.r AND bb.r + 10
order by bb.r
/
set heading on
prompt
prompt
exit
/

Sample Output:

Top 5 Timed Foreground Events
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                                                           Avg
                                                          wait   % DB
Event                                 Waits     Time(s)   (ms)   time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
log file sync                    15,848,089     144,008      9   30.0 Commit
DB CPU                                          122,035          25.5
direct path read                    600,016     111,336    186   23.2 User I/O
db file sequential read           4,367,904      32,126      7    6.7 User I/O
enq: TM - contention                     49      18,344 4.E+05    3.8 Applicatio