SCRIPTS FOR WEEKLY BASIS

Copy archive log from file system to +ASM diskgroup on Standby database



catalog archivelog '/tmp/thread_1_seq_372969.12335.973281287';
copy archivelog  '/tmp/thread_1_seq_372969.12335.973281287' to '+RECO_RCDB';


=======================================================================================


RMAN-06571: datafile 301 does not have recoverable copy

catalog datafilecopy '+DATA_RCDW/rhfdr/datafile/ggs_data01.dbf';
============================================================================================
to clear cache at os level
sync; echo 3 > /proc/sys/vm/drop_caches

===================================================================================================

SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"
FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG
GROUP BY THREAD#)) ARCH, (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME)
FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;



W0rkar0und%

=========================\


full load

stop extract, dump
alter it with begin now both

start extract
export from source with flashback scn
import it in target
start repli aftercsn











SQL> EXEC DBMS_STATS.GATHER_DATABASE_STATS(
     ESTIMATE_PERCENT=>100,METHOD_OPT=>'FOR ALL COLUMNS SIZE SKEWONLY',
     CASCADE => TRUE,
     degree => 4,
     OPTIONS => 'GATHER STALE',
     GATHER_SYS => TRUE,
     STATTAB => PROD_STATS);

CASCADE => TRUE :Gather statistics on the indexes as well. If not used Oracle will decide whether to collect index statistics or not.
DEGREE => 4 :Degree of parallelism.
options:
       =>'GATHER' :Gathers statistics on all objects in the schema.
       =>'GATHER AUTO' :Oracle determines which objects need new statistics, and determines how to gather those statistics.
       =>'GATHER STALE':Gathers statistics on stale objects. will return a list of stale objects.
       =>'GATHER EMPTY':Gathers statistics on objects have no statistics.will return a list of no stats objects.
        =>'LIST AUTO' : Returns a list of objects to be processed with GATHER AUTO.
        =>'LIST STALE': Returns a list of stale objects as determined by looking at the *_tab_modifications views.
        =>'LIST EMPTY': Returns a list of objects which currently have no statistics.
GATHER_SYS => TRUE :Gathers statistics on the objects owned by the 'SYS' user.
STATTAB => PROD_STATS :Table will save the current statistics. see SAVE & IMPORT STATISTICS section -last third in this post-.



Tuesday, November 27, 2012

All About Statistics In Oracle

In this post I'll try to summarize all sorts of statistics in Oracle, I strongly recommend reading the full article, as it contains information you may find it valuable in understanding Oracle statistics.

#####################################
Database | Schema | Table | Index Statistics
#####################################

Gather Database Statistics:
=======================
SQL> EXEC DBMS_STATS.GATHER_DATABASE_STATS(
     ESTIMATE_PERCENT=>100,METHOD_OPT=>'FOR ALL COLUMNS SIZE SKEWONLY',
     CASCADE => TRUE,
     degree => 4,
     OPTIONS => 'GATHER STALE',
     GATHER_SYS => TRUE,
     STATTAB => PROD_STATS);

CASCADE => TRUE :Gather statistics on the indexes as well. If not used Oracle will decide whether to collect index statistics or not.
DEGREE => 4 :Degree of parallelism.
options:
       =>'GATHER' :Gathers statistics on all objects in the schema.
       =>'GATHER AUTO' :Oracle determines which objects need new statistics, and determines how to gather those statistics.
       =>'GATHER STALE':Gathers statistics on stale objects. will return a list of stale objects.
       =>'GATHER EMPTY':Gathers statistics on objects have no statistics.will return a list of no stats objects.
        =>'LIST AUTO' : Returns a list of objects to be processed with GATHER AUTO.
        =>'LIST STALE': Returns a list of stale objects as determined by looking at the *_tab_modifications views.
        =>'LIST EMPTY': Returns a list of objects which currently have no statistics.
GATHER_SYS => TRUE :Gathers statistics on the objects owned by the 'SYS' user.
STATTAB => PROD_STATS :Table will save the current statistics. see SAVE & IMPORT STATISTICS section -last third in this post-.

Note: All above parameters are valid for all kind of statistics (schema,table,..) except Gather_SYS.
Note: Skew data means the data inside a column is not uniform, there is a particular one or more value are being repeated much than other values in the same column, for example the gender column in employee table with two values (male/female), in a construction or security service company, where most of employees are male workforce,the gender column in employee table is likely to be skewed but in an entity like a hospital where the number of males almost equal the number of female workforce, the gender column is likely to be not skewed.

For faster execution:

SQL> EXEC DBMS_STATS.GATHER_DATABASE_STATS(
ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE,degree => 8);

What's new?
ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE => Let Oracle estimate skewed values always gives excellent results.(DEFAULT).
Removed "METHOD_OPT=>'FOR ALL COLUMNS SIZE SKEWONLY'" => As histograms is not recommended to be gathered on all columns.
Removed  "cascade => TRUE" To let Oracle determine whether index statistics to be collected or not.
Doubled the "degree => 8" but this depends on the number of CPUs on the machine and accepted CPU overhead during gathering DB statistics.

Starting from Oracle 10g, Oracle introduced an automated task gathers statistics on all objects in the database that having [stale or missing] statistics, To check the status of that task:
SQL> select status from dba_autotask_client where client_name = 'auto optimizer stats collection';

To Enable Automatic Optimizer Statistics task:
SQL> BEGIN
     DBMS_AUTO_TASK_ADMIN.ENABLE(
     client_name => 'auto optimizer stats collection',
     operation => NULL,
     window_name => NULL);
     END;
     /

In case you want to Disable Automatic Optimizer Statistics task:
SQL> BEGIN
     DBMS_AUTO_TASK_ADMIN.DISABLE(
     client_name => 'auto optimizer stats collection',
     operation => NULL,
     window_name => NULL);
     END;
     /

To check the tables having stale statistics:

SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
SQL> select OWNER,TABLE_NAME,LAST_ANALYZED,STALE_STATS from DBA_TAB_STATISTICS where STALE_STATS='YES';

[update on 03-Sep-2014]
Note: In order to get an accurate information from DBA_TAB_STATISTICS or (*_TAB_MODIFICATIONS, *_TAB_STATISTICS and *_IND_STATISTICS) views, you should manually run DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO procedure to refresh it's parent table mon_mods_all$ from SGA recent data, or you have wait for an Oracle internal that refresh that table  once a day in 10g onwards [except for 10gR2] or every 15 minutes in 10gR2 or every 3 hours in 9i backwards. or when you run manually run one of GATHER_*_STATS procedures.
[Reference: Oracle Support and MOS ID 1476052.1]

Gather SCHEMA Statistics:
======================
SQL> Exec DBMS_STATS.GATHER_SCHEMA_STATS (
     ownname =>'SCOTT',
     estimate_percent=>10,
     degree=>1,
     cascade=>TRUE,
     options=>'GATHER STALE');


Gather TABLE Statistics:
====================
Check table statistics date:
SQL> select table_name, last_analyzed from user_tables where table_name='T1';

SQL> Begin DBMS_STATS.GATHER_TABLE_STATS (
     ownname => 'SCOTT',
     tabname => 'EMP',
     degree => 2,
     cascade => TRUE,
     METHOD_OPT => 'FOR COLUMNS SIZE AUTO',
     estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);










 //for getting table ap

[oracle@rcdbdbadm01 ~]$cat ap.sql

select * from table(dbms_xplan.display_cursor('&sqlid',&child));


 ------------------------------------------------------------------------------------
 [oracle@rcdbdbadm01 ~]$cat awr.sql

select * from table(dbms_xplan.display_Awr('&sql'))

-------------------------------------------------------------------------------------
 [oracle@rcdbdbadm01 ~]$ cat column_stats.sql

select column_name,data_type,num_distinct, num_nulls, density, histogram
 from dba_tab_columns
 where   owner = upper('&1')
 and     table_name = upper('&2')
 order by column_name
 /

-------------------------------------------------------------------------------------
 [oracle@rcdbdbadm01 ~]$ cat index_columns.sql

select c.index_name,
         c.column_name "COLUMN_NAME", c.column_position,
         a.column_expression
 from    dba_ind_columns c,
         dba_ind_expressions a
 where   c.table_owner='&SCHEMA'
 and     c.table_name='&table_name'
 and     a.index_name(+) = c.index_name
 and     a.table_owner(+) = c.table_owner
 and     a.index_owner(+) = c.index_owner
 and     a.table_name(+) = c.table_name
 and     a.column_position(+) = c.column_position
 order by 1,3
 /

-------------------------------------------------------------------------------------
 [oracle@rcdbdbadm01 ~]$ cat lockd.sql

select sql_id,ROW_WAIT_ROW#,ROW_WAIT_OBJ#,ROW_WAIT_FILE#,ROW_WAIT_BLOCK# from gv$session
 where event='enq: TX - row lock contention';
 /
 -------------------------------------------------------------------------------------
 [oracle@rcdbdbadm01 ~]$ cat lock.sql
 COLUMN sess format A15
 col BLOCK for 999
 col lmode for 999
 col CTIME for 9999
 col REQUEST for 999
 col USERNAME for a9
 col STATUS for a8
 col ID1 for 999999999
 col ID2 for 9999999
 col SERIAL# for 99999
 SELECT a.inst_id,substr(DECODE(request,0,'Holder: ','Waiter: ')||a.sid,1,12) sess, b.serial#, a.type, a.id1, a.id2, a.lmode,
       a.request,a.
 block, a.ctime, b.username, b.status, b.sql_id, b.prev_sql_id, b.ROW_WAIT_OBJ#
 from    gv$session b,
         (select distinct b.*
         from    gv$lock a,
                 gv$lock b
         where   a.id1 = b.id1
         and     a.id2 = b.id2
         and     a.request > 0) a
 where   a.sid = b.sid
 and     a.inst_id = b.inst_id
 order by a.id1, a.id2, a.block desc, ctime
 /
 -------------------------------------------------------------------------------------

[oracle@rcdbdbadm01 ~]$ cat obj.sql
 select owner,object_name,object_type from dba_objects where object_id=&obj

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

[oracle@rcdbdbadm01 ~]$ cat s1.sql
 select a.dest_id,a.thread#,C.CURRENT_SEQ, a.recv received,b.apply applied from
 (select dest_id,thread#,max(sequence#) RECV from v$archived_log
 where archived = 'YES'
 and dest_id in (2,3,4)
 group by dest_id,thread#) a,
 (select dest_id,thread#,max(sequence#) APPLY from v$archived_log
 where applied = 'YES'
 group by dest_id,thread#) b,
 (select dest_id,thread#,max(sequence#) CURRENT_SEQ from v$archived_log
 group by dest_id,thread#) c
 where a.dest_id = b.dest_id
 and a.thread# = b.thread#
 and a.thread#=c.thread#
 and c.dest_id=b.dest_id;

-------------------------------------------------------------------------------------
 [oracle@rcdbdbadm01 ~]$ cat sd.sql
  select inst_id, sql_id,child_number,plan_hash_value,executions,buffer_gets,
  round(buffer_gets/case when nvl(executions,0)=0 then 1 else executions end) "bg/ex",
  trunc(elapsed_time/1000000/case when nvl(executions,0)=0 then 1 else executions end) elapsed_exec,rows_processed,  IO_CELL_OF
  FLOAD_ELIGIBLE_BYTES/1024/1024  "Eligible",IO_INTERCONNECT_BYTES/1024/1024 "actual" ,module from gv$sql where sql_id='&sqlid';

-------------------------------------------------------------------------------------
 [oracle@rcdbdbadm01 ~]$ cat sm.sql    //for getting sql_ID
 set lines 300 pages 50000 verify off

col CURRDT for a22
 col event for a30
 col username for a20

select to_char(sysdate,'DD-MON-YY HH24:MI:SS') currdt, inst_id inst, sid, username, sql_id, sql_child_number child, case when
 event like 'latch free%' then event || ' ' || p2 else event end event, ROW_WAIT_OBJ#, round(last_call_et/60) lce from gv$session
 where wait_class != 'Idle' and event not in ('db file parallel write')  order by inst_id, sql_id;




select to_char(sysdate,'DD-MON-YY HH24:MI:SS') currdt, inst_id inst, sid, username, sql_id, sql_child_number child, case when
 event like 'latch free%' then event || ' ' || p2 else event end event, ROW_WAIT_OBJ#, last_call_et/60 lce from gv$session
 where wait_class != 'Idle' and event not in ('db file parallel write')  order by inst_id, sql_id;

-------------------------------------------------------------------------------------
 [oracle@rcdbdbadm01 ~]$ cat s.sql                       ////Waiting
 set time on timing on
 set lines 200 pages 10000
 col username for a10
 col osuser for a14
 col event for a33
 col inst for 99
 col sid for 99999
 col et for 9999999
 col module for a22
 col blocker for 999999
 col bs for 999999
 col osuser for a12
 col bi for 99
 col program for a45
 select systimestamp from dual;
 select inst_id as inst,sid,username,sql_id,status,event,last_call_et as et, row_wait_obj# as obj,BLOCKING_SESSION as bs,
 BLOCKING_INSTANCE as bi,module,machine from gv$session where wait_class !='Idle' order by inst,event;

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

[oracle@rcdbdbadm01 ~]$ cat table_stats.sql
 select owner, partitioned, num_rows, blocks, last_analyzed,temporary,
 global_stats, round(sample_size/decode (num_rows, 0, 1, null, 1, num_rows)*100,0) estimate_percent,degree
 from dba_tables
 where table_name = upper('&1')

-------------------------------------------------------------------------------------
 [oracle@rcdbdbadm01 ~]$ cat t.sql
 alter session set nls_date_format='DD-MON-YY HH24:MI:SS' ;
 set lines 250 pages 50000
 col STAT_NAME for a30
 col PLATFORM_NAME for a40
 col ipaddress for a15
 select b.*, name,LOG_MODE,OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE,FORCE_LOGGING,PLATFORM_NAME,FLASHBACK_ON from v$database,
 (select UTL_INADDR.get_host_address() "IPADDRESS" from dual) b;

col host_name for a10
 select * from gv$instance;
 select * from v$osstat;

SELECT PHYSICAL_SZGB,ACTUAL_SZGB,FREE_SZ_GB FROM
 (select sum(bytes)/1024/1024/1024 physical_szgb from dba_data_files) A,
 (select sum(bytes)/1024/1024/1024 actual_szgb from dba_segments) B,
 (select sum(bytes)/1024/1024/1024 free_sz_gb from dba_free_space) C;

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

SELECT t.*
 FROM v$sql s, table(DBMS_XPLAN.DISPLAY_CURSOR(s.sql_id, s.child_number)) t WHERE sql_text LIKE 'CREATE TABLE%';