分类 数据库 下的文章

declare   
    ---define Cursor 
    cur_policy_sql  VARCHAR2(32767) := 'select * from  (select object_id,object_name 
     from t1111 order by object_id )  
     where rownum < 10' ;
    Cursor cur_policy is  
     select * from  (select object_id,object_name 
     from t1111 order by object_id )  
     where rownum < 10 ;  
    curPolicyInfo cur_policy%rowtype;---定义游标变量  
Begin  
   Dbms_Output.put_line(cur_policy_sql);
   open cur_policy; ---open cursor  
   Loop   
     --deal with extraction data from DB  
     Fetch cur_policy into curPolicyInfo;  
     Exit when cur_policy%notfound;  
        
     Dbms_Output.put_line(curPolicyInfo.object_id); 
     Dbms_Output.put_line(cur_policy%ROWCOUNT); 
   end loop;  
   
   Exception   
     when others then  
         close cur_policy;  
         Dbms_Output.put_line(Sqlerrm);  
           
   if cur_policy%isopen then    
    --close cursor   
      close cur_policy;  
   end if;  
end;

Oracle 六种方法查看执行计划
目录：

(一)六种执行计划

（1）explain plan for
（2）set autotrace on
（3）statistics_level=all
（4）dbms_xplan.display_cursor获取
（5）事件10046 trace跟踪
（6）awrsqrpt.sql

Oracle提供了6种执行计划获取方法，各种方法侧重点不同。

第一种 ：explain for

步骤一：explain for  查询sql语句

步骤二：select * from table(dbms_xplan.display());

/*
优点：无需真正执行，快捷方便；
缺点：1.没有输出相关统计信息，例如产生了多少逻辑读，多少次物理读，多少次递归调用的情况；

      2.无法判断处理了多少行；
      3.无法判断表执行了多少次

*/

第二种：set autotrace on

  步骤一：sql> set autotrace on traceonly;

  步骤二：sql>执行查询Sql语句

  步骤三：sql> set autotrace off;

/*
优点：1.可以输出运行时的相关统计信息（产生多少逻辑读、多少次递归调用、多少次物理读等）；

      2.虽然要等语句执行完才能输出执行计划，但是可以有traceonly开关来控制返回结果不打屏输出；

缺点：1.必须要等SQL语句执行完，才出结果；

      2.无法看到表被访问了多少次；

*/

第三种：（3）statistics_level=all

步骤一：ALTER SESSION SET STATISTICS_LEVEL=ALL;
步骤二：执行待分析的SQL
步骤三：select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

/*
关键字解读：

1.starts：SQL执行的次数；
2.E-Rows：执行计划预计返回的行数；
3.R-Rows：执行计划实际返回的行数；
4.A-Time：每一步执行的时间(HH:MM:SS.FF)，根据这一行可知SQL耗时在哪些地方；
5.Buffers：每一步实际执行的逻辑读或一致性读；
6.Reads：物理读；
7.OMem：OMem为最优执行模式所需的内存评估值， 这个数据是由优化器统计数据以及前一次执行的性能数据估算得出的；
8.1Mem：1Mem为one-pass模式所需的内存评估值，当工作区大小无法满足操作所需的大小时，需要将部分数据写入临时磁盘空间中(如果仅需要写入一次就可以完成操作，就称一次通过，One-Pass;否则为多次通过，Multi-Pass).该列数据为语句最后一次执行中，单次写磁盘所需要的内存大小，这个由优化器统计数据以及前一次执行的性能数据估算得出的
9.Used_Mem：Used-Mem则为当前操作实际执行时消耗的内存，括号里面为(发生磁盘交换的次数,1次即为One-Pass,大于1次则为Multi_Pass,如果没有使用磁盘，则显示0)

*/

/*
优点：1.可以清晰的从starts得出表被访问多少次；

      2.可以从E-Rows和A-Rows得到预测的行数和真实的行数，从而可以准确判断Oracle评估是否准确；
      3.虽然没有准确的输出运行时的相关统计信息，但是执行计划中的Buffers就是真实的逻辑读的数值；

缺点：1.必须要等执行完后才能输出结果；

      2.无法控制结果打屏输出，不像autotrace可以设置traceonly保证不输出结果；
      3.看不出递归调用，看不出物理读的数值

*/

第四种：dbms_xplan.display_cursor获取

步骤1：select * from table( dbms_xplan.display_cursor('&sql_id') );    --该方法是从共享池得到，如果SQL已被age out出share pool，则查找不到

注释：

     1.还有1种方法，select * from table( dbms_xplan.display_awr('&sql_id') );  --该方法是从awr性能视图里面获取
     2.如果有多个执行计划，可用以下方法查出：
     select * from table(dbms_xplan.display_cursor('&sql_id',0));
     select * from table(dbms_xplan.display_cursor('&s ql_id',1));

/*
优点：1.知道sql_id即可得到执行计划，与explain plan for一样无需执行；

      2.可得到真实的执行计划

缺点：1.没有输出运行的统计相关信息；

      2.无法判断处理了多少行；
      3.无法判断表被访问了多少次；

*/

第五种：事件10046 trace跟踪

/*
步骤1：alter session set events '10046 trace name context forever,level 12'; --开启追踪
步骤2：执行sql语句；
步骤3：alter session set events '10046 trace name context off'; --关闭追踪
步骤4：select tracefile from v$process where addr=(select paddr from v$session where sid=(select sid from v$mystat where rownum<=1)); --找到跟踪后产生的文件

步骤5：tkprof trc文件 生成目标文件 sys=no sort=prsela,exeela,fchela --格式化命令
*/

第六种：awrsqrpt.sql
/*
步骤1：@?/rdbms/admin/awrsqrpt.sql
步骤2：选择你要的断点（begin snap和end snap）
步骤3：输入要查看的sql_id
*/

例子：

见：使用awrsqrpt.sql查看执行计划demo

（二）如何选择
选择时一般遵循以下规则：
1.如果sql执行很长时间才出结果或返回不了结果，用方法1：explain plan for
2.跟踪某条sql最简单的方法是方法1：explain plan for，其次是方法2：set autotrace on
3.如果相关查询某个sql多个执行计划的情况，只能用方法4：dbms_xplan.display_cursor或方法6：awrsqrpt.sql
4.如果sql中含有函数，函数中有含有sql，即存在多层调用，想准确分析只能用方法5：10046追踪
5.想法看到真实的执行计划，不能用方法1：explain plan for和方法2：set autotrace on
6.想要获取表被访问的次数，只能用方法3：statistics_level = all

ORA-04030: 在尝试分配 16328 字节 (koh-kghu call ,kollrsz) 时进程内存不足

oerr查看报错信息，是process获取不到足够的内存，server process消耗的是PGA，而非SGA
[oracle@febdb ~]$ oerr ora 04030
04030, 00000, "out of process memory when trying to allocate %s bytes (%s,%s)"
// *Cause: Operating system process private memory was exhausted.
// *Action:

PGA不足，思路主要查看PGA、workarea的参数值和实例启动以来的统计值，可以分4步来处理，第一步查询参数值、第二步查询隐含参数值、第三步查询v$pgastat统计值、第四步分析前面三步的各项值得出结论

第一步
SQL> show parameter pga
NAME TYPE VALUE

pga_aggregate_target big integer 8G

SQL> show parameter area_size
NAME TYPE VALUE

bitmap_merge_area_size integer 1048576
create_bitmap_area_size integer 8388608
hash_area_size integer 131072
sort_area_size integer 65536
workarea_size_policy string AUTO

第二步
SQL> col NAME format a25
SQL> col VALUE format a20
SQL> col DESCRIPTION format a55
SQL> set linesize 110
SQL> select a.ksppinm name, b.ksppstvl value, a.ksppdesc description from x$ksppi a, x$ksppcv b where a.indx = b.indx and a.ksppinm like '%_pga_max%';
NAME VALUE DESCRIPTION

_pga_max_size 1717985280 Maximum size of the PGA memory for one process

SQL> select a.ksppinm name, b.ksppstvl value, a.ksppdesc description from x$ksppi a, x$ksppcv b where a.indx = b.indx and a.ksppinm like '%smm_max%';
NAME VALUE DESCRIPTION

_smm_max_size_static 838860 static maximum work area size in auto mode (serial)
_smm_max_size 838860 maximum work area size in auto mode (serial)

SQL> select a.ksppinm name, b.ksppstvl value, a.ksppdesc description from x$ksppi a, x$ksppcv b where a.indx = b.indx and a.ksppinm like '%smm_px%';
NAME VALUE DESCRIPTION

_smm_px_max_size_static 4194304 static maximum work area size in auto mode (global)
_smm_px_max_size 4194304 maximum work area size in auto mode (global)

一个进程最大的PGA值，本案例中是1717985280B（_pga_max_size的单位是B）
自动模式下，一个进程的PGA的最大work area值，本案例中是838860KB（_smm_max_size的单位是KB，此值一般是_pga_max_size的50%）
自动模式下，所有进程的PGA最大work area总量值，本案例中是4194304KB（_smm_px_max_size的单位是KB，此值一般是PGA_AGGREGATE_TARGET参数的50%）

第三步
SQL> col value format 9999999999999999
SQL> col name format a40
SQL> select * from v$pgastat where name in ('aggregate PGA target parameter','maximum PGA allocated','maximum PGA used for auto workareas','maximum PGA used for manual workareas','total PGA allocated','total PGA inuse','cache hit percentage') order by 1;
NAME VALUE UNIT

aggregate PGA target parameter 8589934592 bytes
cache hit percentage 97 percent
maximum PGA allocated 29975256064 bytes
maximum PGA used for auto workareas 3414564864 bytes
maximum PGA used for manual workareas 2713600 bytes
total PGA allocated 15749543936 bytes
total PGA inuse 12480521216 bytes

aggregate PGA target parameter
Current value of the PGA_AGGREGATE_TARGET initialization parameter
--当前PGA的参数值，本案例中为8589934592bytes，和参数pga_aggregate_target值一样
cache hit percentage
A value of 100% means that all work areas executed by the system since instance startup have used an optimal amount of PGA memory.
--小于100%表示排序等消耗work areas的操作不一定都是在PGA完成，而是work areas走了磁盘使用了临时表空间，本案例中为97%
maximum PGA allocated
Maximum number of bytes of PGA memory allocated at one time since instance startup
--PGA曾经达到的最大值，本案例中为29975256064bytes
maximum PGA used for auto workareas
Maximum amount of PGA memory consumed at one time by work areas running under the automatic memory management mode since instance startup
--自动模式下的work areas曾经达到的最大值，本案例中为3414564864bytes
maximum PGA used for manual workareas
Maximum amount of PGA memory consumed at one time by work areas running under the manual memory management mode since instance startup.
--手工模式下的work areas曾经达到的最大值，本案例中为2713600bytes
total PGA allocated
Current amount of PGA memory allocated by the instance. The Oracle Database attempts to keep this number below the value of the PGA_AGGREGATE_TARGET initialization parameter. However, it is possible for the PGA allocated to exceed that value by a small percentage and for a short period of time when the work area workload is increasing very rapidly or when PGA_AGGREGATE_TARGET is set to a small value.
--当前PGA的真实值，本案例中为15749543936 bytes
Oracle数据库试图将这个数字保持在PGA_AGGREGATE_TARGET初始化参数的值以下。然而，PGA可以在短时间内以较小的百分比分配超过该值，当工作区域的工作负载增长非常快，或者当PGA_AGGREGATE_TARGET被设置为一个小的值时，这是可能的。
total PGA inuse
Indicates how much PGA memory is currently consumed by work areas
--当前work areas的真实值。本案例中为12480521216bytes

第四步
根据以上1、2、3的数据，分析是PGA不足还是workarea_size_policy设置为AUTO的问题。
如果PGA不足，重新设置参数pga_aggregate_target，使用更大值
如果PGA设置很大后，还是保报错，则设置参数值workarea_size_policy为MANUAL，再根据业务设置*area_size这些参数的值。
--本案例明显是因为PGA不足引发，设置PGA参数值为20G解决。

生产环境使用最新版本的xtrabackup版本，鉴于xtrabackup 8.0版本移除命令，推荐使用xtrabackup命令进行备份和还原。

安装

在percona官方下载centos版本的rpm包，使用yum安装（示例）：
yum -y localinstall percona-xtrabackup-80-8.0.1-2.alpha2.el7.x86_64.rpm
sudo yum install https://repo.percona.com/centos/7/RPMS/noarch/percona-release-0.1-8.noarch.rpm

sudo percona-release enable tools testing
yum install percona-xtrabackup

  • 全量备份：

1.全量备份
xtrabackup --defaults-file=/etc/my.cnf --host=172.16.1.52 --user=root --password=xxxyyy --port=3310 --backup --target-dir=/data/backup/
或者：使用参数--datadir替换掉参数--defaults-file.
# xtrabackup --host=172.16.1.52 --user=root --password=xxxyyy --port=3310 --datadir=/data/crm/ --backup --target-dir=/data/backup/

2.数据恢复：
2.1停止掉运行的数据库实例：
# /etc/init.d/mysqld_multi stop 3310
2.2 准备：
xtrabackup --host=172.16.1.52 --user=root --password=xxxyyy --port=3310 --prepare --target-dir=/data/backup/
2.3 拷回数据：
xtrabackup --host=172.16.1.52 --user=root --password=xxxyyy --port=3310 --datadir=/data/crm --copy-back --target-dir=/data/backup/
2.3 修改目录属性启动数据库：
chown -R mysql.mysql /data/crm
chmod -R 755 /data/crm
2.4 启动数据库实例:
/etc/init.d/mysqld_multi start 3310

2.5 若有主从的问题可以查看备份目录下的文件：
# cat xtrabackup_binlog_pos_innodb
crm_bin.001282 1049711778

  • 增量备份：

全量备份的目录为：mkdir -p /data/backup
增量备份的目录为： mkdir -p /data/backupIncr

  1. 备份操作之前需要停止掉数据库：

1.备份操作：
1.1.全量备份：
xtrabackup --defaults-file=/etc/my.cnf --host=172.16.1.52 --user=root --password=xxxyyy --port=3310 --backup --parallel=3 --target-dir=/data/backup/
1.2.增量备份：
xtrabackup --defaults-file=/etc/my.cnf --host=172.16.1.52 --user=root --password=xxxyyy --port=3310 --backup --parallel=3 --target-dir=/data/backupIncr --incremental-basedir=/data/backup

2.恢复操作：
2.1 准备全备份的日志：
xtrabackup --prepare --apply-log-only --target-dir=/data/backup
2.2 准备增量备份的日志：
xtrabackup --prepare --apply-log-only --target-dir=/data/backup --incremental-dir=/data/backupIncr
2.3 全备份准备：
xtrabackup --prepare --target-dir=/data/backup
2.4 拷回数据：
xtrabackup --host=172.16.1.52 --user=root --password=xxxyyy --port=3310 --datadir=/data/crm --copy-back --target-dir=/data/backup/

2.5 修改数据目录的权限和属性：
chown -R mysql:mysql /data/crm
chmod -R 755 /data/crm

2.6 启动实例：
/etc/init.d/mysqld_multi start 3310

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