DBMS_UTILITY.analyze_DATABASE
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: DBMS_UTILITY.analyze_DATABASE

  1. #1
    Join Date
    Sep 2002
    Posts
    411

    DBMS_UTILITY.analyze_DATABASE

    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???

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    dont use it, it has a pretty nasty bug

    it analyzes SYS schema

    dont use analyze_database from dbms_stats and dbms_utility

  3. #3
    Join Date
    Sep 2002
    Posts
    411
    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

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    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

    Code:
    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
    Last edited by pando; 11-18-2002 at 05:25 PM.

  5. #5
    Join Date
    Sep 2002
    Posts
    411
    What is the problem we might encounters when we analyze SYS objects????

    Thanks

  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    slow recursive calls

    slow database performance (very)

  7. #7
    Join Date
    Mar 2001
    Location
    Ireland/Dublin
    Posts
    684
    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
    Best wishes!
    Dmitri

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width