DBMS_UTILITY.analyze_DATABASE

[mike2000]

Hi all,

I have the below syntax and I am not sure it's right b/c it's not working:

var j number
begin
dbms_job.submit
(
:j,
'SYS.DBMS_UTILITY.ANALYZE_database(''COMPUTE'');',
SYSDATE,
'NEXT_DAY(trunc(sysdate),''SATURDAY'')+1/10'
);
COMMIT;
end;

1. Is it the right syntax??
2. If it's right I think I miss the privilege to run anaylyze all of objects in the schemas, what is the command to grant "analyze all the objects in the database"
3. Beside the DBMS_UTILITY, I think we can use DBMS_STATS, could someone thow out some light of how to use that please???

[pando]

dont use it, it has a pretty nasty bug

it analyzes SYS schema

dont use analyze_database from dbms_stats and dbms_utility

[mike2000]

Pando,

Thanks for your advises as always, Could you please tell me what bug I might encounter b/c I have to prove to some other people. So you are saying don't user ANALYZE_DATABASE from dbms_stats and dbms_utility, so if I use analyze schemas I should be ok right ???

If I use analyze schemas which mean that I have to add alot of job_queue_processes on the init.ora which cause more processes running in the database. if I have more processes running in the database, will I waste resource on my server ???

Thanks again

[pando]

man... I said it analyzes SYS schema and that's the bug!

well I have to analyze several users but I dont have to schedule one job per user, I have a procedure and some log tables like

GRANT ANALYZE ANY TO &&USER
/

CREATE SEQUENCE SEQ_COD_ERROR_PROCESO
MINVALUE 0 
MAXVALUE 999999999999999999999999999 
INCREMENT BY 1 
START WITH 0 
NOCACHE 
NOORDER 
NOCYCLE
/

CREATE TABLE CTL_ERROR_PROCESO 
(COD_ERROR_PROCESO NUMBER, 
 FEC_ERROR DATE, 
 DES_ERROR VARCHAR2(256))  
PCTFREE 10 
PCTUSED 40 
INITRANS 1 
MAXTRANS 255 
LOGGING 
STORAGE(INITIAL 131072 
        NEXT 131072
        PCTINCREASE 0)
/

CREATE OR REPLACE PROCEDURE ANALIZAR_BBDD
AS
  type array is table of varchar2(30);
  l_users array := array('ARPA_OWN', 'BSCS_OWN', 'CM','CMGED', 'CMSAPDWH', 'CMSAPGED',
                         'CMSAPMDW',  'LSC', 'METADATOS_OWN', 'METRICAS', 'OPSC','PERFSTAT', 'RECHAZOS');
  l_junk varchar2(30);
  errmsg ctl_error_proceso.des_error%type;
BEGIN
  /* asignamos calendario espanola a la sesion */
  execute immediate 'alter session set nls_territory=''SPAIN''';
  IF to_char(sysdate, 'D') in (2, 4, 6)
  THEN
    /* se asigna un sort_area_size grande para acelerar el proceso */
    execute immediate 'alter session set sort_area_retained_size=65536';
    execute immediate 'alter session set sort_area_size=10485760';
    /* bucle para procesar los usuarios */
    FOR i in 1 .. l_users.count
    LOOP
        BEGIN
          select username into l_junk
          from dba_users
          where username = l_users(i);
	  /* se borra las estadisticas antes de analizar por bug de Oracle 8.1.7 */
          dbms_stats.delete_schema_stats(OWNNAME => l_users(i));	
	  /* se analizan las esquemas, se generar histogramas de 75 buckets */ 
          dbms_stats.gather_schema_stats(OWNNAME => l_users(i),
                                         ESTIMATE_PERCENT => 20,
                                         METHOD_OPT => 'FOR ALL COLUMNS SIZE 75',
                                         DEGREE => 8,
                                         CASCADE => TRUE);
        EXCEPTION
          WHEN NO_DATA_FOUND THEN
            errmsg := 'Error durante analyze: no existe el usuario '||l_users(i);
            insert into ctl_error_proceso values
            (seq_cod_error_proceso.nextval, sysdate, errmsg);
            commit;
          WHEN OTHERS THEN
            raise;
        END;
      END LOOP;
    END IF;
EXCEPTION
  WHEN OTHERS THEN
    errmsg := 'Error durante analyze: '||sqlcode||', '||sqlerrm;
    insert into ctl_error_proceso values
    (seq_cod_error_proceso.nextval, sysdate, errmsg);
    commit;
END;
/

variable jobno number
exec dbms_job.submit(JOB => :jobno, -
                     WHAT => 'analizar_bbdd;',- 
                     NEXT_DATE => trunc(SYSDATE+1,'DD')+1/12,-
                     INTERVAL => 'trunc(SYSDATE+1, ''DD'')+1/12');

ok they are in spanish but you can forget about the comments hehe

[mike2000]

What is the problem we might encounters when we analyze SYS objects????

Thanks

[pando]

slow recursive calls

slow database performance (very)

[kgb]

Nothing bad, except of your SQL query may never complete and 'exp' command will not work.

Originally posted by mike2000
What is the problem we might encounters when we analyze SYS objects????
Thanks