mardi 30 avril 2013

JOB report

Le but est de centraliser dans un table tous les jobs de la journee et faire un diagramme de GANTT   dans EXCEL

drop table MG.report_job ;
create table   MG.report_job
(   id number
,job_name varchar2(120)
,jour varchar2(20)
   , start_time varchar2(30)
   ,elapsed_second number --seconde,
   ,time_taken varchar2(8)--16:18:00
   ,end_time varchar2(8)
   ,s varchar2(30)
);
alter tableMG.report_job add constraint report_job_pk primary key (job,jour,start_time,elapsed_second);

CREATE or replace SEQUENCE MG.REPORT_JOB_SEQ
START WITH 2
MAXVALUE 999999999999999999999999999
MINVALUE 1
NOCYCLE
NOCACHE
NOORDER;

CREATE DATABASE link GTI CONNECT TO sysman IDENTIFIED BY "XXCCVBGFHJKIYUTTRRRR" USING 'HHHH';
Quand il est prive seul    le user   qui l a créé a l droit de l utiliser

declare
newid report_job.id%type;
CURSOR dataCursor IS
    SELECT INPUT_TYPE job_name
        ,to_char(trunc(start_time),'DD-MM-YYYY') jour
       ,TO_CHAR(START_TIME,'hh24:mi')      start_time
       ,ELAPSED_SECONDS
       ,TIME_TAKEN_DISPLAY time_taken --varchar2
       ,upper(STATUS) STATUS
FROM V$RMAN_BACKUP_JOB_DETAILS where trunc(START_TIME)=trunc(sysdate -1 )
union
select
    job_name
    ,to_char(trunc(req_start_date),'DD-MM-YYYY') jour
   ,to_char(req_start_date,'hh24:mi') start_time
   ,3600*extract(hour from run_duration)+ 60* extract(minute from run_duration)+ extract(second from run_duration) --elapsed_seconds
     --,extract(hour from run_duration)||':'|| extract(minute from run_duration)||':'|| extract(second from run_duration) --hour_minutes_second
     ,substr(to_char(run_duration,'hh24:mi:SS'),6,8)
     ,upper(STATUS) S
         from   sys.dba_scheduler_job_run_details
  where
  trunc(req_start_date)=trunc(sysdate -1)
  and to_number(to_char((24*extract(day from run_duration))+(3600)*extract(hour from run_duration)+
        (60)* extract(minute from run_duration)+
        extract(second from run_duration))) >=60 --plus de 60 secondes
union
SELECT operation ||','||TARGET job_name
        ,to_char(trunc(start_time),'DD-MM-YYYY')
       ,to_char(start_time,'hh24:mi')
       --,to_char(end_time,'hh24:mi')
    ,round((24*extract(day from (end_time-start_time) day(9) to second))+
      (3600)*extract(hour from (end_time-start_time) day(9) to second)+
        (60)* extract(minute from (end_time-start_time) day(9) to second)+
        extract(second from (end_time-start_time) day(9) to second)) elapsed_seconds
      --,extract(hour from ('end_time-start_time','HH24'))||':'|| extract(minute from ('end_time-start_time','MM')))||':'|| extract(second from ('end_time-start_time','SS')) Hour_min_sec
      ,substr((to_char((end_time-start_time),'hh24:mi:SS')),12,8)
      ,upper(substr(operation,1,6)||'_SUCCEED')
FROM dba_optstat_operations
where
trunc(start_time)=trunc(sysdate -1)
and to_number(to_char((24*extract(day from (end_time-start_time) day(9) to second))+(3600)*extract(hour from (end_time-start_time) day(9) to second)+
        (60)* extract(minute from (end_time-start_time) day(9) to second)+
        extract(second from (end_time-start_time) day(9) to second))) >=60 --plus de 60 secondes
       union
select job_name,
     to_char(trunc(start_time),'DD-MM-YYYY')
     ,to_char(start_time,'hh24:mi'),
round((24*extract(day from (end_time-start_time) day(9) to second))+
      (3600)*extract(hour from (end_time-start_time) day(9) to second)+
        (60)* extract(minute from (end_time-start_time) day(9) to second)+
        extract(second from (end_time-start_time) day(9) to second)) elapsed_seconds
   ,lpad(extract(hour from (end_time - start_time) day(9) to second) ,2,0)
           ||':'||lpad(( extract(minute from (end_time - start_time) day(9) to second)),2,0)
           ||':'|| lpad(extract(second from (end_time - start_time) day(9) to second),2,0) hour
,upper(status)
from sysman.MGMT$JOB_EXECUTION_HISTORY@GTI
where
trunc(start_time) =trunc (sysdate -1)
  and to_number(to_char((24*extract(day from (end_time-start_time) day(9) to second))+(3600)*extract(hour from (end_time-start_time) day(9) to second)+
        (60)* extract(minute from (end_time-start_time) day(9) to second)+
        extract(second from (end_time-start_time) day(9) to second))) >=60
        and    target_name like 'RADPRD%' order by 3;
BEGIN
        FOR dataRow IN dataCursor
        LOOP
                SELECT report_job_seq.nextval INTO newid FROM DUAL;
                INSERT INTO report_job(
                 id
                ,job_name
                ,jour
                 , start_time
                 ,elapsed_second
                 ,time_taken
                 --,end_time
                ,s
                 ) VALUES(newid,dataRow.job_name,dataRow.jour,dataRow.start_time,dataRow.ELAPSED_SECONDS,dataRow.time_taken,dataRow.STATUS);
        END LOOP;
END;
/
commit;

mercredi 24 avril 2013

calcule de stats 11g

#!/usr/bin/ksh

# analyze schema

PATH=/opt/bin:$PATH

ORACLE_HOME=/appl/oracle/11.2.0.2

export ORACLE_HOME

export TNS_ADMIN=$ORACLE_HOME/network/admin

PATH=$ORACLE_HOME/bin:$PATH

LOG=/appl/oracle/LOG

export ADMIN=/home/oracle

sqlplus -s sys/FGFGFGG@XXXXcrs:1521/FRPERF as sysdba<<EOF1 set echo off set feedback off column timecol new_value timestamp column spool_extension new_value suffix select to_char(sysdate,'yyMondd_hh24mi') timecol, '.log' spool_extension from sys.dual; column output new_value instance_name select value || '_' output from v\$parameter where name = 'instance_name'; spool /appl/oracle/LOG/compute_stats_&&instance_name&&timestamp&&suffix

--select TO_char(SYSDATE,'Mon_Dy_DD@HH@HH24:MI:SS') from dual;

--exec dbms_stats.gather_schema_stats(ownname=>'$1' ,cascade=>TRUE,estimate_percent=> $2,method_opt=>'For All CoLUMNS SIZE 1',OPTIONS=>'GATHER',degree=>8);

--select TO_char(SYSDATE,'Mon_Dy_DD@HH@HH24:MI:SS') from dual;

set timing on

set serveroutput on

set lines 200

alter session set "_optimizer_join_factorization"=false;

alter session set NLS_DATE_FORMAT='DD-MM-YYYY:HH24:MI:SS';

set pages 200

set feed on

select m.table_name, m.timestamp, sum(m.inserts), sum(m.updates), sum(m.deletes), t.num_rows, t.last_analyzed

from sys.dba_tab_modifications m, dba_tables t

where m.table_owner = t.owner

and m.table_name = t.table_name

and m.table_owner in ('APEX_040200','CTRL','R','R_SEC')

group by m.table_name, m.timestamp, t.num_rows, t.last_analyzed

order by 1,2;

set feed off

---------

---index

---------

select a.owner,a.TABLE_NAME,a.index_name,a.NUM_ROWS ,last_analyzed from sys.dba_indexes a,sys.dba_tab_modifications b where a.index_type not in ('IOT - TOP','LOB','DOMAIN') and a.table_name=b.table_name

and a.owner in ('APEX_040200','CTRL','R','R_SEC') order by last_analyzed; begin

for z in (select a.owner,a.index_name from sys.dba_indexes a,sys.dba_tab_modifications b where a.index_type not in ('IOT - TOP','LOB','DOMAIN') and a.table_name=b.table_name and a.table_owner in ('APEX_040200','CTRL','R','R_SEC'))

loop

dbms_output.put_line('STAT IND 100 percent: '||z.owner||'.'||z.index_name);

dbms_stats.gather_index_stats(z.owner, z.index_name,estimate_percent=>100, degree=>4); end loop; end; /

and a.owner in ('APEX_040200','CTRL','R','R_SEC') order by last_analyzed;

--TABLE

begin

for z in ( select r.owner,b.table_name from sys.dba_segments r,sys.dba_tab_modifications b where r.owner =b.table_owner and r.segment_name=b.table_name and r.extents >64 and r.bytes/1024/1024 >300 and r.owner in ('APEX_040200','CTRL','R','R_SEC'))

loop

sys.dbms_output.put_line('STAT TAB 15 percent : '||z.owner||'.'||z.table_name);

sys.dbms_stats.gather_table_stats(z.owner, z.table_name,estimate_percent=>15, degree=>4,cascade=>FALSE,method_opt=>'FOR ALL COLUMNS SIZE REPEAT');

end loop;

end;

begin

for z in (select r.owner,b.table_name from sys.dba_segments r,sys.dba_tab_modifications b where r.owner =b.table_owner and r.segment_name=b.table_name and r.owner in ('APEX_040200','CTRL','R','R_SEC'))

loop

dbms_output.put_line('STAT TAB 100 percent : '||z.owner||'.'||z.table_name);

dbms_stats.gather_table_stats(z.owner, z.table_name,estimate_percent=>100, degree=>4,cascade=>FALSE,method_opt=>'FOR ALL COLUMNS SIZE REPEAT');

end loop;

end;

set feed on

select * from dba_tab_modifications b where b.table_owner in ('APEX_040200','CTRL','R','R_SEC');

set feed off

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

-- fin

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

exit

EOF1

find $LOG -name 'compute_stats*' -mtime +5 -print

jeudi 11 avril 2013

underscore parameters

Requete poure avoir les underscore parameters :

SELECT
   a.ksppinm "Parameter",
   decode(p.isses_modifiable,'FALSE',NULL,NULL,NULL,b.ksppstvl) "Session",
   c.ksppstvl "Instance",
   decode(p.isses_modifiable,'FALSE','F','TRUE','T') "S",
   decode(p.issys_modifiable,'FALSE','F','TRUE','T','IMMEDIATE','I','DEFERRED','D') "I",
   decode(p.isdefault,'FALSE','F','TRUE','T') "D",
   a.ksppdesc "Description"
FROM x$ksppi a, x$ksppcv b, x$ksppsv c, v$parameter p
WHERE a.indx = b.indx AND a.indx = c.indx
   AND p.name(+) = a.ksppinm
   AND UPPER(a.ksppinm) LIKE UPPER('_optimizer_join_factorization')
ORDER BY a.ksppinm;

To view existing hidden parameters execute the following command
while connected AS SYSDBA:
col NAME for a35
col DESCRIPTION for a100

    SELECT name,description from SYS.V$PARAMETER WHERE name
        LIKE '\_%' ESCAPE '\';

To view existing non-default events execute the following commands
while connected AS SYSDBA':
Events:
    SELECT (translate(value,chr(13)||chr(10),' ')) FROM sys.v$parameter2
      WHERE UPPER(name) ='EVENT' AND isdefault='FALSE'

Trace Events:
    SELECT (translate(value,chr(13)||chr(10),' ')) from sys.v$parameter2
      WHERE UPPER(name) = '_TRACE_EVENTS' AND isdefault='FALSE'

======================================
parametre deprecies:
======================================
SELECT name from v$parameter WHERE isdeprecated = 'TRUE' ORDER BY name;

Bug 13984324 wrong result with UNION ALL of similar queries / in DBA_TAB_MODIFICATIONS

Problemes : SQL> select * from dba_tab_modifications; no rows selected SQL> select count(1) from dba_tab_modifications; COUNT(1) ---------- 618 Solution : alter session set "_optimizer_join_factorization"=false; *select from dba_tab_modifications; works fine. Bug 13984324 - wrong result with UNION ALL of similar queries / in DBA_TAB_MODIFICATIONS [ID 13984324.8]**

		Modified Tue Jul 17 12:40:56 CDT 2012 Type PATCH Status PUBLISHED

Bug 13984324 wrong result with UNION ALL of similar queries / in DBA_TAB_MODIFICATIONS

This note gives a brief overview of bug 13984324.
The content was last updated on: 17-JUL-2012
Click here for details of each of the sections below.

Affects:

Product (Component) Oracle Server (Rdbms)

Range of versions believed to be affected Versions >= 11.2 but BELOW 12.1

Versions confirmed as being affected

11.2.0.3

Platforms affected Generic (all / most platforms affected)

Fixed:

This issue is fixed in

11.2.0.4 (Future Patch Set)

Symptoms:	Related To:
Wrong Results	Optimizer dba_tab_modifications _optimizer_join_factorization Optimizer Feature JF

Description

Wrong result are possible with :
 
1) at least 3 branches in UNION ALL
2) at least 2 join factorization predicates
3) Predicate order in branches is different
4) Correct result with "_optimizer_join_factorization"=false;
 
select /*+ FACTORIZE_JOIN(@SET$1(M@SEL$1 M@SEL$2 M@SEL$3)
            (U@SEL$1 U@SEL$2 U@SEL$3)) */ 1 name
from t1_13984324 m, t3_13984324 o,t2_13984324 u
where o.col1 = m.col1 and o.col2 = u.col2 and o.type#=21
union all
select 2 name
from t1_13984324 m, t3_13984324 o,t2_13984324 u
where o.col2 = u.col2 and o.col1 = m.col1 and o.type#=19    <== different order of join predicates than the other 2.
union all
select 3 name
from t1_13984324 m, t3_13984324 o,t2_13984324 u
where o.col1 = m.col1 and o.col2 = u.col2 and o.type#=20;
 
This bug specially affects queries over DBA_TAB_MODIFICATIONS
 
Workaround
"_optimizer_join_factorization"=false

Please note: The above is a summary description only. Actual symptoms can vary. Matching to any symptoms here does not confirm that you are encountering this problem. For questions about this bug please consult Oracle Support.

References

Bug:13984324 (This link will only work for PUBLISHED bugs)
Note:245840.1 Information on the sections in this article

lundi 8 avril 2013

RAC scheduler Oracle

1 Test case :

1.1 Creation de 2 tables

1.1.1 Rac_sticky

create table rac_sticky (

sid number,

service_name varchar2(30),

instance_name varchar2(30),

time timestamp with time zone);

1.1.2 Rac_no_sticky

create table rac_no_sticky (

sid number,

service_name varchar2(30),

instance_name varchar2(30),

time timestamp with time zone);

1.2 service :

srvctl add service -d drpocrad –s departures -r DRPOCRAD1 -a DRPOCRAD2

srvctl start service -d drpocrad -s departures

1.3 job class

BEGIN

DBMS_SCHEDULER.create_job_class (

job_class_name => 'departures_class',

service => 'departures',

logging_level => DBMS_SCHEDULER.LOGGING_RUNS,

comments => 'run on the departures service');

END;

/

grant EXECUTE ANY CLASS to XXXXX;

2 Job sticky

begin

dbms_scheduler.create_job

(

job_name => 'RACsticky',

job_class => 'departures_class',

job_type => 'plsql_block',

job_action => 'begin insert into rac_sticky

(sid,instance_name,service_name,time)

select sid, instance_name, service_name,

systimestamp from v$session, v$instance

where sid = (select sid from v$mystat

where rownum = 1);

end;',

repeat_interval => 'freq=minutely;interval=2'

);

dbms_scheduler.set_attribute

(

name => ' RACsticky ',

attribute => 'instance_id',

value => 1

);

dbms_scheduler.enable(' RACsticky ');

end;

3 Job pas sticky :

BEGIN

DBMS_SCHEDULER.create_job (

job_name => 'RAC_NO_STCIKY',

job_class => 'departures_class',

job_type => 'plsql_block',

job_action => 'begin insert into rac_no_sticky

(sid,instance_name,service_name,time)

select sid, instance_name, service_name,

systimestamp from v$session, v$instance

where sid = (select sid from v$mystat

where rownum = 1);

end;',

repeat_interval => 'freq=minutely;interval=2');

BEGIN

DBMS_SCHEDULER.set_attribute (name => 'RAC_NO_STCIKY',

attribute => 'instance_stickiness',

VALUE => FALSE);

END;

DBMS_SCHEDULER.enable ('RAC_NO_STCIKY');

END;

4 Conclusion

Table RAC_NO_STICKY:

31 departures DRPOCRAD2 4/5/2013 11:20:38.986239 AM -04:00

31 departures DRPOCRAD2 4/5/2013 11:22:36.444641 AM -04:00

31 departures DRPOCRAD2 4/5/2013 11:24:36.045879 AM -04:00

31 departures DRPOCRAD2 4/5/2013 11:26:36.023985 AM -04:00

31 departures DRPOCRAD2 4/5/2013 11:28:36.029161 AM -04:00

31 departures DRPOCRAD2 4/5/2013 11:30:36.026770 AM -04:00

31 departures DRPOCRAD2 4/5/2013 11:32:36.030054 AM -04:00

31 departures DRPOCRAD2 4/5/2013 11:34:36.021619 AM -04:00

31 departures DRPOCRAD2 4/5/2013 11:36:36.023324 AM -04:00

31 departures DRPOCRAD2 4/5/2013 11:38:36.053010 AM -04:00

31 departures DRPOCRAD2 4/5/2013 11:40:36.039355 AM -04:00

31 departures DRPOCRAD2 4/5/2013 11:42:36.032869 AM -04:00

31 departures DRPOCRAD2 4/5/2013 11:44:36.044179 AM -04:00

164 departures DRPOCRAD1 4/5/2013 11:46:42.243874 AM -04:00

Table RAC_STICKY :

En rouge l arret de l instance 1

33 departures DRPOCRAD1 4/5/2013 11:13:55.758974 AM -04:00

139 departures DRPOCRAD1 4/5/2013 11:15:53.068770 AM -04:00

139 departures DRPOCRAD1 4/5/2013 11:17:53.051646 AM -04:00

139 departures DRPOCRAD1 4/5/2013 11:19:53.048000 AM -04:00

139 departures DRPOCRAD1 4/5/2013 11:21:53.063570 AM -04:00

139 departures DRPOCRAD1 4/5/2013 11:23:53.056897 AM -04:00

162 departures DRPOCRAD1 4/5/2013 11:38:03.028472 AM -04:00

162 departures DRPOCRAD1 4/5/2013 11:39:53.053529 AM -04:00

162 departures DRPOCRAD1 4/5/2013 11:41:53.044608 AM -04:00

156 departures DRPOCRAD1 4/5/2013 11:43:53.050679 AM -04:00

164 departures DRPOCRAD1 4/5/2013 11:46:38.831013 AM -04:00

37 departures DRPOCRAD1 4/5/2013 11:47:53.043582 AM -04:00

37 departures DRPOCRAD1 4/5/2013 11:49:53.033371 AM -04:00

Bascule sur service et/ou arrêt de l instance 1 ,

le job 'RAC_NO_STCIKY' continue alors que le RACsticky ne roule que sur l instance 1.

5 Cleanup

BEGIN

SYS.DBMS_SCHEDULER.DROP_JOB

(job_name => 'MG. RACsticky ');

END;

BEGIN

SYS.DBMS_SCHEDULER.DROP_JOB

(job_name => 'MG. RAC_no_sticky ');

END;

BEGIN

SYS.DBMS_SCHEDULER.DROP_JOB_CLASS

(

job_class_name => 'SYS.DEPARTURES_CLASS'

,force => TRUE

);

END;

Srvctl stop service -d drpocrad -s departures

Srvctl remove service -d drpocrad -s departures

Drop table rac_sticky;

Drop table rac_no_sticky;

TANOS dba

Rechercher dans ce blog

mardi 30 avril 2013

JOB report

mercredi 24 avril 2013

calcule de stats 11g

jeudi 11 avril 2013

underscore parameters

Bug 13984324 wrong result with UNION ALL of similar queries / in DBA_TAB_MODIFICATIONS

Bug 13984324 wrong result with UNION ALL of similar queries / in DBA_TAB_MODIFICATIONS

Affects:

Fixed:

Symptoms:

Related To:

Description

References

lundi 8 avril 2013

RAC scheduler Oracle

1 Test case :

1.1 Creation de 2 tables

1.1.1 Rac_sticky

1.1.2 Rac_no_sticky

1.2 service :

1.3 job class

2 Job sticky

3 Job pas sticky :

4 Conclusion

5 Cleanup

*Product (Component)*	Oracle Server (Rdbms)
*Range of versions believed* to be affected**	Versions >= 11.2 but BELOW 12.1
*Versions confirmed* as being affected**	11.2.0.3
Platforms affected	Generic (all / most platforms affected)