move audit tables aud$ and fga$ from system tablespace

Oracle 11g onwards:

New package DBMS_AUDIT_MGMT can be used to move audit base tables AUD$ and FG$ as below. AUDIT_DATA is the new tablespace where these audit tables have to be moved.

BEGIN
    DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
    audit_trail_location_value => 'AUDIT_DATA');
 END;
 
BEGIN
    DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,
    audit_trail_location_value => 'AUDIT_DATA');
 END;

Before oracle 11g :

Shutdown the system and start in restricted mode [ optional]

alter table aud$ move tablespace audit_data;

Create Index on the AUD$ table. [ optionall ]

create index audit_index
on aud$(sessionid, ses$tid)
tablespace audit_index;

Simplification of use of Oracle 10g/11g sql tuning advisory without using sql tuning set

Procedure Advisory in this post gives SQL Tuning advisory report based on parameters user name,starting date and end date. This procedure used DBA_HIST_ views to extract top n sqls from time period between starting date and end date This procedure required user executing this procedure has been granted advisory privilege along with the select privileges on views DBA_HIST_SQLTEXT,DBA_HIST_SNAPSHOT,DBA_HIST_SQLSTAT  Further this procedure uses another procedure Create_Exec_Task defined later in this post

CREATE OR REPLACE PROCEDURE Advisory(p_user VARCHAR2,p_date1 DATE,p_date2 DATE, n NUMBER) AS

sqlt CLOB;

v_report CLOB;

CURSOR c1 IS

SELECT  DBA_HIST_SQLTEXT.sql_text sqlt

FROM

(

SELECT

sub.module, parsing_schema_name,

ROUND(sub.seconds_since_date/60,2) elapsed_time_delta_mins,

sub.execs_since_date,

sub.gets_since_date,

sub.snap_id,

ROUND(sub.seconds_since_date/DECODE(execs_since_date,0,1,

execs_since_date)/60,2) avg_exec_time,

sub.sql_id

FROM

( -- sub to sort before rownum

SELECT module, parsing_schema_name,

sql_id,

ROUND(SUM(elapsed_time_delta)/1000000) AS seconds_since_date,

SUM(executions_delta) AS execs_since_date,

SUM(buffer_gets_delta) AS gets_since_date,

snap_id

FROM

dba_hist_snapshot NATURAL JOIN dba_hist_sqlstat

WHERE

dba_hist_sqlstat.parsing_schema_name IN (p_user)

AND

begin_interval_time BETWEEN p_date1 AND p_date2-- '21-sep-2010' AND '30-sep-2010'

AND module NOT LIKE '%exp%' AND module NOT LIKE '%imp%'

AND module NOT LIKE '%TOAD%'

GROUP BY

module,sql_id,snap_id,parsing_schema_name

ORDER BY snap_id DESC

) sub

WHERE ROWNUM <=n

)T1 , DBA_HIST_SQLTEXT , dba_hist_snapshot

WHERE T1.sql_id=DBA_HIST_SQLTEXT.sql_id

AND T1.snap_id=dba_hist_snapshot.snap_id

--AND ( LOWER(DBA_HIST_SQLTEXT.sql_text) LIKE '%delete%' --OR

-- LOWER(DBA_HIST_SQLTEXT.sql_text) LIKE '%insert%' OR LOWER(DBA_HIST_SQLTEXT.sql_text) LIKE '%delete%')

AND sql_text NOT LIKE '%DBMS_STATS%'

AND sql_text NOT LIKE  '%parallel(t,2)%'

AND sql_text NOT LIKE '%maxbkt%'

AND sql_text NOT LIKE '%substrb%'

ORDER BY elapsed_time_delta_mins ;--T1.snap_id

BEGIN

OPEN c1;

LOOP

FETCH c1 INTO sqlt;

Create_Exec_Task(sqlt,v_report);

EXIT WHEN c1% NOTFOUND;

END LOOP;

DBMS_OUTPUT.PUT_LINE(v_report);

CLOSE c1;

END;

/

CREATE OR REPLACE PROCEDURE Create_Exec_Task(v_sql IN CLOB,v_report OUT CLOB ) AS

v_task_id VARCHAR2(254);

v_task_name VARCHAR2(254) DEFAULT 'task_name' ;

BEGIN

BEGIN

DBMS_SQLTUNE.DROP_TUNING_TASK(v_task_name);

EXCEPTION

WHEN OTHERS THEN NULL;

END;

v_task_id := DBMS_SQLTUNE.CREATE_TUNING_TASK(

sql_text => v_sql,

time_limit => 120,

SCOPE => 'COMPREHENSIVE',

task_name =>v_task_name ,

description => 'no description'

);

DBMS_SQLTUNE.EXECUTE_TUNING_TASK(v_task_name);

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('task_name') INTO v_report FROM dual;

DBMS_SQLTUNE.DROP_TUNING_TASK(v_task_name);

END;

/

SQL> set serveroutput on size 999999

SQL>  set long 999999

SQL> exec advisory(user,sysdate-1,sysdate,10)

spool c:\advisory.log

set long 999999

set serveroutput on size 999999

declare

v_Sql clob;

v_out clob;

begin

v_sql:=' select * from t1,t2 where t1.c1=t2.c1 and t1.c1<=500';

create_exec_task(v_sql,v_out);

dbms_output.put_line(v_out);

end;

/

spool off

Tuning Oracle RAC

CLUSTER WAIT EVENTS QUERIES

 

1. cluster wait events class should not be at top

 

  SELECT wait_class time_cat,

         ROUND ( (time_secs), 2) time_secs,
         ROUND ( (time_secs) * 100 / SUM (time_secs) OVER (), 2) pct
    FROM (  SELECT wait_class wait_class,
                   SUM (time_waited_micro) / 1000000 time_secs
              FROM gv$system_event
             WHERE wait_class <> 'Idle' AND time_waited > 0
          GROUP BY wait_class
          UNION
          SELECT 'CPU', ROUND ( (SUM (VALUE) / 1000000), 2) time_secs
            FROM gv$sys_time_model
          WHERE stat_name IN ('background cpu time', 'DB CPU'))
ORDER BY time_secs DESC;

TIME_CAT,TIME_SECS,PCTCPU,153097,44.27
Other,100067.74,28.94
System I/O,56365.91,16.3
Concurrency,24033.05,6.95
Cluster,4419.66,1.28
Commit,4003.31,1.16
User I/O,2554.04,0.74
Application,774.07,0.22
Configuration,291.68,0.08
Network,154.79,0.04
Administrative,46.5,0.01
Scheduler,0.01,0

2. time spent in cluster wait events or avergae wait ms should not be higher

 WITH system_event

        AS (SELECT CASE
                      WHEN wait_class = 'Cluster' THEN event
                      ELSE wait_class
                   END
                      wait_type,
                   e.*
             FROM gv$system_event e)
SELECT wait_type,
         ROUND (total_waits / 1000, 2) waits_1000,
        ROUND (time_waited_micro / 1000000 / 3600, 2) time_waited_hours,
         ROUND (time_waited_micro / 1000 / total_waits, 2) avg_wait_ms,
         ROUND (time_waited_micro * 100 / SUM (time_waited_micro) OVER (), 2)
            pct_time
    FROM (  SELECT wait_type,
                   SUM (total_waits) total_waits,
                   SUM (time_waited_micro) time_waited_micro
              FROM system_event e
          GROUP BY wait_type
          UNION
          SELECT 'CPU', NULL, SUM (VALUE)
            FROM gv$sys_time_model
           WHERE stat_name IN ('background cpu time', 'DB CPU'))
   WHERE wait_type <> 'Idle'
ORDER BY time_waited_micro DESC;

WAIT_TYPE,WAITS_1000,TIME_WAITED_HOURS,AVG_WAIT_MS,PCT_TIME
CPU,,42.53,,44.27
Other,301559.5,27.8,0.33,28.94
System I/O,62707.59,15.66,0.9,16.3
Concurrency,46016.57,6.68,0.52,6.95
Commit,3903.83,1.11,1.03,1.16
User I/O,3276.44,0.71,0.78,0.74
gc current block 2-way,3027.9,0.42,0.5,0.44
gc cr block 2-way,2144.59,0.33,0.55,0.34
gc current grant busy,1330.46,0.22,0.61,0.23
Application,2154.83,0.22,0.36,0.22
gc cr multi block request,371.18,0.09,0.87,0.09
Configuration,30.9,0.08,9.44,0.08
gc current block busy,77.06,0.08,3.54,0.08
Network,43979.22,0.04,0,0.04
gc current grant 2-way,194.44,0.02,0.4,0.02
gc cr block busy,39.41,0.02,1.6,0.02
Administrative,0.47,0.01,98.73,0.01
gc cr disk read,114.88,0.01,0.37,0.01
gc cr grant 2-way,103.01,0.01,0.39,0.01
gc buffer busy release,6.71,0.01,4.06,0.01
gc current multi block request,31.51,0.01,0.75,0.01
gc current block congested,20.39,0,0.59,0
gc cr block congested,16.11,0,0.65,0
gc current split,0.8,0,8.63,0
gc buffer busy acquire,1.27,0,2.07,0
gc current retry,0.12,0,20.61,0
gc current grant congested,1.22,0,0.5,0
gc cr grant congested,0.59,0,0.51,0
gc cr failure,0.11,0,0.67,0
gc remaster,0,0,45.5,0
gc domain validation,0,0,6.73,0
Scheduler,0,0,2.43,0

3. MEASURING GLOBAL CACHE LATENCY

 

 

To measure Global Cache latency, we use the wait interface as exposed by GV$SYSTEM_EVENT.2 The following query reports on average times for each of the Global Cache request types and single-block read times (for comparison):

  SELECT event,
         SUM (total_waits) total_waits,
         ROUND (SUM (time_waited_micro) / 1000000, 2) time_waited_secs,
         ROUND (SUM (time_waited_micro) / 1000 / SUM (total_waits), 2) avg_ms
    FROM gv$system_event
   WHERE wait_class <> 'Idle'
         AND (   event LIKE 'gc%block%way'
              OR event LIKE 'gc%multi%'
             OR event LIKE 'gc%grant%'
              OR event = 'db file sequential read')
GROUP BY event
  HAVING SUM (total_waits) > 0
ORDER BY event;

EVENT,TOTAL_WAITS,TIME_WAITED_SECS,AVG_MSdb file sequential read,559187,1438.21,2.57
gc cr block 2-way,2144622,1180.78,0.55
gc cr grant 2-way,103014,40.35,0.39
gc cr grant congested,590,0.3,0.51
gc cr multi block request,371178,323.34,0.87
gc current block 2-way,3027919,1526.22,0.5
gc current grant 2-way,194439,77.95,0.4
gc current grant busy,1330461,806.32,0.61
gc current grant congested,1223,0.61,0.5
gc current multi block request,31511,23.68,0.75

EXAMINING THE INTERCONNECT

SELECT instance_number, host_name, instance_name,

 name_ksxpia network_interface, ip_ksxpia private_ip 

 FROM x$ksxpia CROSS JOIN  v$instance

WHERE pub_ksxpia = 'N';

Inst     Host                     Net    Private

#       Name      INSTANCE_NAME  IFace   IP

---- ----------- --------------  ----- ------------

1 elquest.dev.me MELRAC3         eth1 192.168.0.12

On linux box : ping -c 5 -s 8192 192.168.0.12

see the response of private interconnect network

for windows use flag -l for packet size and -n for counts :

SIGNS OF INTERCONNECT PROBLEMS

SELECT name, SUM(VALUE)

 FROM gv$sysstat

 WHERE name LIKE 'gc%lost'

 OR name LIKE 'gc%received'

 OR name LIKE 'gc%served'

GROUP BY name

 ORDER BY name;

NAME,SUM(VALUE)gc blocks lost,8
gc claim blocks lost,0
gc cr blocks received,2888683
gc cr blocks served,2888684
gc current blocks received,4051222
gc current blocks served,4051229