分类 ORACLE 下的文章

下面脚本用于收集SCOTT账户下统计信息过期了或者是从没收集过统计信息的表的统计信息，采样率也根据表的段大小做出了相应调整。

declare
  cursor stale_table is
    select owner,
           segment_name,
           case
             when segment_size < 1 then
              100
             when segment_size >= 1 and segment_size <= 5 then
              50
             when segment_size > 5 then
              30
           end as percent,
           6 as degree
      from (select owner,
                   segment_name,
                   sum(bytes / 1024 / 1024 / 1024) segment_size
              from DBA_SEGMENTS
             where owner = 'SCOTT'
               and segment_name in
                   (select table_name
                      from DBA_TAB_STATISTICS
                     where (last_analyzed is null or stale_stats = 'YES')
                       and owner = 'SCOTT')
             group by owner, segment_name);
begin
  dbms_stats.flush_database_monitoring_info;
  for stale in stale_table loop
    dbms_stats.gather_table_stats(ownname          => stale.owner,
                                  tabname          => stale.segment_name,
                                  estimate_percent => stale.percent,
                                  method_opt       => 'for all columns size repeat',
                                  degree           => stale.degree,
                                  cascade          => true);
  end loop;
end;
/

我们使用method_opt => 'for all columns size auto'方式对表收集统计信息。

SQL> BEGIN
      DBMS_STATS.GATHER_TABLE_STATS(ownname          => 'SCOTT',
                                    tabname          => 'T_STATS',
                                    estimate_percent => 100,
                                    method_opt       => 'for all columns size auto',
                                    no_invalidate    => FALSE,
                                    degree           => 1,
                                    cascade          => TRUE);
    END

我们查看直方图信息。

SQL> select a.column_name,
  2         b.num_rows,
  3         a.num_nulls,
  4         a.num_distinct Cardinality,
  5         round(a.num_distinct / b.num_rows * 100, 2) selectivity,
  6         a.histogram,
  7         a.num_buckets
  8    from dba_tab_col_statistics a, dba_tables b
  9   where a.owner = b.owner
 10     and a.table_name = b.table_name
 11     and a.owner = 'SCOTT'
 12     and a.table_name = 'T_STATS';

COLUMN_NAME       NUM_ROWS  NUM_NULLS CARDINALITY SELECTIVITY HISTOGRAM   NUM_BUCKETS
--------------- ---------- ---------- ----------- ----------- --------------- -------
EDITION_NAME         72674      72674           0           0 NONE                  0
NAMESPACE            72674          1          21         .03 NONE                  1
SECONDARY            72674          0           2           0 NONE                  1
GENERATED            72674          0           2           0 NONE                  1
TEMPORARY            72674          0           2           0 NONE                  1
STATUS               72674          0           2           0 NONE                  1
TIMESTAMP            72674          1        1592        2.19 NONE                  1
LAST_DDL_TIME        72674          1        1521        2.09 NONE                  1
CREATED              72674          0        1472        2.03 NONE                  1
OBJECT_TYPE          72674          0          45         .06 NONE                  1
DATA_OBJECT_ID       72674      64833        7796       10.73 NONE                  1
OBJECT_ID            72674          1       72673         100 NONE                  1
SUBOBJECT_NAME       72674      72145         140         .19 NONE                  1
OBJECT_NAME          72674          0       44333          61 NONE                  1
OWNER                72674          0          31         .04 FREQUENCY            31

15 rows selected.

现有如下SQL。

select * from emp e,dept d where e.deptno=d.deptno;

刷新监控信息

SQL> begin
  2    dbms_stats.flush_database_monitoring_info;
  3  end;
  4  /

我们可以先用explain plan for命令，在plan_table中生成SQL的执行计划。

SQL> explain plan for select * from emp e,dept d where e.deptno=d.deptno;

Explained.
然后我们使用下面脚本检查SQL语句中所有的表的统计信息是否过期。

SQL> select owner, table_name, object_type, stale_stats, last_analyzed
      from dba_tab_statistics
     where (owner, table_name) in
           (select object_owner, object_name
              from plan_table
             where object_type like '%TABLE%'
            union
            select table_owner, table_name
              from dba_indexes
            where (owner, index_name) in
                  (select object_owner, object_name
                     from plan_table
                    where object_type like '%INDEX%'));

OWNER      TABLE_NAME OBJECT_TYP STALE_STATS     LAST_ANALYZED
---------- ---------- ---------- --------------- ------------------
SCOTT      DEPT       TABLE      NO              05-DEC-16
SCOTT      EMP        TABLE      YES             22-OCT-16

最后我们可以使用下面脚本检查SQL语句中表统计信息的过期原因。

select *
  from all_tab_modifications
 where (table_owner, table_name) in
       (select object_owner, object_name
          from plan_table
         where object_type like '%TABLE%'
        union
        select table_owner, table_name
          from dba_indexes
         where (owner, index_name) in
               (select object_owner, object_name
                  from plan_table
                 where object_type like '%INDEX%'));

一、使用场景

在expdp备份不能满足要求的情况下，需要通过rman恢复单表数据，针对表空间恢复是一种比较合理的方式。

二、操作步骤

查询表空间信息

SQL> select distinct b.name from v$tablespace b;

复制表空间

SQL>
run {
allocate channel ch00 type sbt_tape;
allocate channel ch01 type sbt_tape;
send 'NB_ORA_SERV=hz-ps-bak01,NB_ORA_CLIENT=hwdb01';
set newname for datafile '+DATA/bydmes/datafile/system.260.1109607975' to '/u01/app/oradata/system.260.1109607975';
set newname for datafile '+DATA/bydmes/datafile/sysaux.261.1109607985' to '/u01/app/oradata/sysaux.261.1109607985';
restore tablespace SYSAUX,SYSTEM,UNDOTBS1,UNDOTBS2,USERS,YBWMS_KC;
switch datafile all;
release channel ch00;
release channel ch01;
}

注册归档日志

catalog start with '/opt/archbak20230114';

恢复表空间到指定时间点

run{
sql 'alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"';
set until time '2023-01-14 09:30:00';
allocate channel ch00 type sbt_tape;
allocate channel ch01 type sbt_tape;
allocate channel ch02 type sbt_tape;
send 'NB_ORA_SERV=hz-ps-bak01,NB_ORA_CLIENT=hwdb01';
recover tablespace SYSAUX,SYSTEM,UNDOTBS1,UNDOTBS2,USERS,YBWMS_KC;;
switch datafile all;
release channel ch00;
release channel ch01;
release channel ch02;
}

打开数据库

SQL>alter database open resetlogs

备注：
问题处理过程中遇到了一下问题：

**1、使用restore指定数据文件方式恢复，导致undo表空间的数据文件恢复少了，把缺少的数据文件补充上即可。
2、spfile中sga和pga大小指定太小，导致数据库应用太慢，可以ctrl+c停止后，修改完参数再执行，同时操作系统中的sysctl.conf配置过低，也需要修改。**

  1. 1
  2. 2
  3. 3
  4. 4
  5. 5
  6. 6
  7. ...
  8. 16