使用存储过程对table，index进行统计, 优化查询计划

create or replace procedure sp_analyze is
 v_tablename varchar2(50);
 v_lastday   varchar2(10);
 v_today     varchar2(10);
 cursor c_tablename is
  select distinct table_name from user_tab_partitions where partition_name like v_lastday or partition_name like v_today;

 cursor c_partitionname is
     select partition_name from user_tab_partitions where table_name = v_tablename;

 cursor c_indexname is
  select index_name from user_indexes where table_name = v_tablename;

 v_partition_name varchar2(50);

 v_sqlstr varchar2(1000);
begin

 v_lastday := 'P' || to_char(sysdate - 1, 'yyyymmdd') || '%';  --查询前一天
 v_today   := 'P' || to_char(sysdate, 'yyyymmdd') || '%';      --查询今天

 for CC in c_tablename loop
  begin
   v_tablename := CC.table_name;
   for BB in c_partitionname loop
    begin
     v_partition_name := BB.partition_name;
          --对每个分区中的表进行统计,统计后就知道相关对象数据的各方面的统计信息，优化器就能据此选择更好的执行计划去执行select语句
     v_sqlstr         := 'analyze table ' || v_tablename || ' partition (' || v_partition_name || ') estimate statistics sample 5 percent';
     execute immediate v_sqlstr;
              for AA in c_indexname loop
               begin
                  --对每个表中的索引进行统计, 使系统选用最好的执行计划去执行select语句
                v_sqlstr := 'analyze index ' || AA.index_name || ' partition (' || v_partition_name || ') estimate statistics sample 5 percent';
                execute immediate v_sqlstr;
               exception
                when others then
                 rollback;
               end;
              end loop;--end of index
       exception
     when others then
      rollback;
    end;
   end loop;--end of partition
  end;
 end loop;--end of table
end sp_analyze;