Analyze Queue Table (locked table)

-----------------------------------------------------------Analyze perticular table------------------------------------------

BEGIN
  DBMS_SCHEDULER.create_job (
    job_name        => 'analyze_queue',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN dbms_stats.gather_table_stats(''LDBO'',''TBLRKQUEUE'' ,force=>TRUE); END;',
    start_date      => '01-APR-12 01.00.00 PM ASIA/CALCUTTA',
    repeat_interval=> 'FREQ=HOURLY;INTERVAL=1',
    end_date        => NULL,
    enabled         => TRUE,
    comments        => 'JOB to gather Queue Table statistics');
END;
/

exec DBMS_SCHEDULER.RUN_JOB('analyze_queue');

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


create or replace procedure sp_analyzequeue as
BEGIN
FOR cur_rec IN (select owner,table_name from dba_tab_statistics where owner='LDBO' and stattype_locked is not null) LOOP
BEGIN
dbms_stats.gather_table_stats('' || cur_rec.owner || '' , ''|| cur_rec.table_name || '',force=>TRUE);
END;
  END LOOP;
END;
/


BEGIN
  DBMS_SCHEDULER.create_job (
    job_name        => 'analyze_queue',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN sp_analyzequeue; END;',
    start_date      => '01-APR-12 02:00.00.00 AM ASIA/CALCUTTA',
    repeat_interval => 'freq=DAILY',
    end_date        => NULL,
    enabled         => TRUE,
    comments        => 'Analyze queue tables');
END;
/

exec DBMS_SCHEDULER.RUN_JOB('analyze_queue');

select table_name,num_rows,to_char(last_analyzed,'DD-Mon-YYYY HH24:MI:SS') last_anaylze from user_tables where table_name in (select table_name from dba_tab_statistics where owner='LDBO' and stattype_locked is not null);