2017-07-28

收获不止SQL优化读书笔记 - 第三章循规蹈矩--如何读懂SQL执行计划

执行计划分析概述

SQL执行计划

同一条SQL词句，可以有不同的执行计划，但一次只能有一种访问路径。
哪种执行开销更低，性能更好，速度更快，就选哪一种，这个过程叫作Oracle的解析过程。
解析后的SQL执行计划保存到SGA的Shared Pool里，后续再执行同样的SQL只需要在Shared Pool里获取，不需要再次分析了。
SQL执行计划选定选定依据是统计信息

查看表和索引的统计信息

# 表的相关统计信息
select table_name,num_rows,blocks,last_analyzed from user_tables
where table_name in ('&table_name');

# 索引的相关统计信息
select table_name,index_name,blevel,num_rows,leaf_blocks,last_analyzed from user_indexes
where table_name in ('&table_name');

数据库统计信息的收集

# 收集表统计信息
exec dbms_stats.gather_table_stats(ownname=>'&owner',tabname=>'&table_name',estimate_percent=>10,method_opt=>'for all indexed columns');

# 收集索引统计信息
exec dbms_stats.gather_index_stats(ownname=>'&owner',indname=>'&index_name',estimate_percent=>'10',degree=>'4');

# 收集表和索引统计信息
exec dbms_stats.gather_table_stats(ownname=>'&owner',tabname=>'&table_name',estimate_percent=>10,method_opt=>'for all indexed columns',cascade=>TRUE);

# 收集分区表指定分区统计信息
exec dbms_stats.gather_table_stats(ownname=>'&owner',tabname=>'&table_name',partname='&partition_name',estimate_percent=>10,method_opt=>'for all indexed columns',cascade=>TRUE);

读懂执行计划的关键

从案例中辨别低效SQL

2017-07-25

收获不止SQL优化读书笔记 - 第一章全局在胸--用工具对SQL整体优化

SQL优化不同场景对应工具

数据库的整体分析调优工具

AWR：关注数据库的整体性能报告，主要关注点
- DB Time：这个指标主要用来判断当前系统有没有遇到相关瓶颈，是否较为繁忙导致等待时长很长
- load profile：这个指标主要用来展现当前系统的一些指示性能的总体参数
- efficiency percentages：是一些命中率指标，其中Buffer Hit、Library Hit等都表示SGA(System global area)的命中率
- Top 10 Foreground Events by Total Wait Time：等待事件是衡量数据库整体优化情况的重要指标
- SQL Statistics：分别从几个维度来罗列出TOP的SQL
- Segment Statistics
ASH：数据库中的等待事件与哪些SQL具体对应的报告，时间更精准
ADDM：Oracle给出的一些建议
AWRDD：不同时段的性能对比报告
- 不同时期load profile的比较
- 不同时期等待事件的比较
- 不同时期TOP SQL的比较
AWRSQRPT：sql在某两个快照间隔内，SQL的统计信息（总消耗的cpu时间，执行次数，逻辑读，物理读等）与执行计划的报告

阅读全文 >>

2017-07-25

收获不止SQL优化读书笔记 - 第二章风驰电掣--SQL优化过程

SQL调优时间都去哪儿了

不善于批处理频频忙交互：尽量一次性获取SQL调优前所需的信息，如SQL执行计划、SQL执行频率、对应的表和索引尺寸、表和索引的统计信息、表和索引的类型等。
无法抓住主要矛盾瞎折腾：首先要判断出是整体问题，还是局部问题。
未能明确需求目标自费劲：一切以客户的需求为前提。局部问题时，虽慢用户满意即是快，虽快用户不满意即是慢。但是，整体问题必须解决，无论用户感觉是慢还是慢。
没有分析操作难度乱调优：SQL调优前首先要知道这个SOL返回记录有多少，如果很少，就说明调优空间很大，反之就要考虑特殊手段了。 SQL的执行频次、执行时长以及服务器的配置高低也要了解清楚。

阅读全文 >>

2017-07-14

Oracle 12c特性解读-容器数据库和灾备-11 灾备维护经验总结

不影响主库，模拟备库Failover，恢复主备数据同步的过程

删除备库dg broker配置

$ tnsping dgtp

TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 13-JUL-2017 18:31:27

Copyright (c) 1997, 2016, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/12.2.0/db_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.240.4.174)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = dgtp)))
OK (0 msec)

$ vi $ORACLE_HOME/network/admin/tnsnames.ora
# 修改端口号为任意没开通的
DGTP =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.240.4.174)(PORT = 15211))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = dgtp)
    )
  )

$ tnsping dgtp

TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 13-JUL-2017 18:34:37

Copyright (c) 1997, 2016, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/12.2.0/db_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.240.4.174)(PORT = 15211)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = dgtp)))
TNS-12541: TNS:no listener

# 关闭dg broker
SQL> show parameter dg

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cell_offloadgroup_name               string
dg_broker_config_file1               string      /u01/app/oracle/product/12.2.0
                                                 /db_1/dbs/dr1DGTS.dat
dg_broker_config_file2               string      /u01/app/oracle/product/12.2.0
                                                 /db_1/dbs/dr2DGTS.dat
dg_broker_start                      boolean     TRUE
inmemory_adg_enabled                 boolean     TRUE
SQL> alter system set dg_broker_start=false;

# 删除DG broker配置文件
! rm /u01/app/oracle/product/12.2.0/db_1/dbs/dr1DGTS.dat
! rm /u01/app/oracle/product/12.2.0/db_1/dbs/dr2DGTS.dat

阅读全文 >>

2017-07-04

Oracle 12c特性解读-容器数据库和灾备-10 容灾切换和故障演练实践总结

练习DG Broker switchover和failover的过程

练习switchover

# switchover可以在主或备库上运行
# 使用TNS的方式登陆DG Broker
$ dgmgrl sys/Center08@dgtp
DGMGRL for Linux: Release 12.2.0.1.0 - Production on Tue Jul 4 15:01:13 2017

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected to "DGTP"
Connected as SYSDBA.
DGMGRL> show configuration;

Configuration - dgt_dg

  Protection Mode: MaxPerformance
  Members:
  dgtp - Primary database
    dgts - Physical standby database 

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 13 seconds ago)

阅读全文 >>

2017-07-02

Oracle 12c特性解读-容器数据库和灾备-09 12c 灾备环境搭建

Snapshot Standby

查看数据库状态

# 主库状态
set line 150
col DB_UNIQUE_NAME for a15
col FORCE_LOGGING for a15
select name,db_unique_name,open_mode,database_role,flashback_on,force_logging,log_mode from v$database;

NAME      DB_UNIQUE_NAME  OPEN_MODE            DATABASE_ROLE    FLASHBACK_ON       FORCE_LOGGING   LOG_MODE
--------- --------------- -------------------- ---------------- ------------------ --------------- ------------
DGT       DGTP            READ WRITE           PRIMARY          YES                YES             ARCHIVELOG

# 查看备库状态
set line 150
col DB_UNIQUE_NAME for a15
col FORCE_LOGGING for a15
select name,db_unique_name,open_mode,database_role,flashback_on,force_logging,log_mode from v$database;

NAME      DB_UNIQUE_NAME  OPEN_MODE            DATABASE_ROLE    FLASHBACK_ON       FORCE_LOGGING   LOG_MODE
--------- --------------- -------------------- ---------------- ------------------ --------------- ------------
DGT       DGTS            READ ONLY WITH APPLY PHYSICAL STANDBY YES                YES             ARCHIVELOG

阅读全文 >>

2017-06-28

SQL优化--dbms_sqltune应用实例

需要优化的SQL

SELECT a.sjhm
FROM    bfcrm8.hyk_hyxx  b,
        bfcrm8.hyk_grxx  a,
        bfcrm8.hyxfjl    c,
        bfcrm8.hyxfjl_sp d
WHERE   a.hyid         =b.hyid
        AND a.hyid     =c.hyid
        AND c.xfjlid   =d.xfjlid
        AND b.hyktype IN ('101')
        AND b.status  >=0
        AND a.sex      ='1'
        AND d.bmdm LIKE '010104%'
        AND TO_CHAR(sysdate,'YYYY') - TO_CHAR(a.csrq,'YYYY')>='20'
        AND TO_CHAR(sysdate,'YYYY') - TO_CHAR(a.csrq,'YYYY') <'41'
GROUP BY a.sjhm

阅读全文 >>

2017-06-23

Oracle 12c特性解读-容器数据库和灾备-08 容灾简介和环境搭建

搭建12c的Data Guard环境

主备库环境配置

# 主库已安数据，备库只安装了数据库软件，环境配置如下
cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
10.240.4.174  dgtp12  dgtp12.ydgwng.cn
10.240.4.175  dgts12  dgts12.ydgwng.cn

echo "export ORACLE_SID=dgt" >> /home/oracle/.bash_profile
echo "export ORACLE_BASE=/u01/app/oracle" >> /home/oracle/.bash_profile
echo "export ORACLE_HOME=\$ORACLE_BASE/product/12.2.0/db_1" >> /home/oracle/.bash_profile
echo "export LD_LIBRARY_PATH=\$ORACLE_HOME/lib" >> /home/oracle/.bash_profile
echo "export PATH=\$PATH:\$HOME/bin:\$ORACLE_HOME/bin:\$ORACLE_HOME/OPatch" >> /home/oracle/.bash_profile
echo "export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK" >> /home/oracle/.bash_profile
echo "export NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss'" >> /home/oracle/.bash_profile
echo "alias sqlplus='rlwrap sqlplus'" >> /home/oracle/.bash_profile
echo "alias asmcmd='rlwrap asmcmd'" >> /home/oracle/.bash_profile
echo "alias rman='rlwrap rman'" >> /home/oracle/.bash_profile
echo "alias dgmgrl='rlwrap dgmgrl'" >> /home/oracle/.bash_profile

阅读全文 >>

2017-06-16

Oracle 12c特性解读-容器数据库和灾备-07 升级数据库至12c

11g升级到12.2

查看当前系统环境

df -hT
Filesystem           Type   Size  Used Avail Use% Mounted on
/dev/mapper/VolGroup-lv_root
                     ext4    43G   20G   22G  48% /
tmpfs                tmpfs  3.9G  109M  3.8G   3% /dev/shm
/dev/sda1            ext4   477M   85M  364M  19% /boot

free
             total       used       free     shared    buffers     cached
Mem:       8174784    5810232    2364552     111112     196636     731784
-/+ buffers/cache:    4881812    3292972
Swap:     16777212          0   16777212

su - grid
opatch lspatches
22502505;ACFS Patch Set Update : 11.2.0.4.160419 (22502505)
23054319;OCW Patch Set Update : 11.2.0.4.160719 (23054319)
24732075;Database Patch Set Update : 11.2.0.4.170418 (24732075)

exit
su - oracle
sqlplus / as sysdba
select PROPERTY_VALUE from database_properties where PROPERTY_NAME='NLS_RDBMS_VERSION';

PROPERTY_VALUE
--------------------------------------------------
11.2.0.4.0

select name,db_unique_name,open_mode,database_role from v$database;

NAME      DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE
--------- ------------------------------ -------------------- ----------------
DGT       dgts                           READ WRITE           PRIMARY

阅读全文 >>

2017-06-10

Oracle 12c特性解读-容器数据库和灾备-06 PDB备份与恢复

模拟4种完全恢复场景

数据库open状态，普通表空间损坏

# 备份整个数据库
rman target /
RMAN> backup database 
format '/u01/app/rmanbackup/bak_%d_%T_%s_%U';

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO

SQL> conn lyj/lyj@pdb1
SQL> select count(*) from test;

  COUNT(*)
----------
    272404

SQL> select FILE_NAME,TABLESPACE_NAME from dba_data_files;

FILE_NAME                                          TABLESPACE_NAME
-------------------------------------------------- ------------------------------
/u01/app/oracle/oradata/orcl1/pdb1/system01.dbf    SYSTEM
/u01/app/oracle/oradata/orcl1/pdb1/sysaux01.dbf    SYSAUX
/u01/app/oracle/oradata/orcl1/pdb1/undotbs01.dbf   UNDOTBS1
/u01/app/oracle/oradata/orcl1/pdb1/users01.dbf     USERS

# 模拟普通表空间损坏
SQL> !rm /u01/app/oracle/oradata/orcl1/pdb1/users01.dbf

# 可以创建表并插入数据都可以正常完成
create table test1 (id int, name varchar2(10));
insert into test1 values (1,'aaaa');
insert into test1 values (2,'bbbb');
commit;

SQL> col SEGMENT_NAME for a50
SQL> select SEGMENT_NAME,TABLESPACE_NAME from user_segments;

SEGMENT_NAME                                       TABLESPACE_NAME
-------------------------------------------------- ------------------------------
TEST                                               USERS
TEST1                                              USERS

# 再创建一个大表时报错
SQL> create table test2 as select * from all_objects;
create table test2 as select * from all_objects
                                    *
ERROR at line 1:
ORA-01110: data file 30: '/u01/app/oracle/oradata/orcl1/pdb1/users01.dbf'
ORA-01116: error in opening database file 30
ORA-01110: data file 30: '/u01/app/oracle/oradata/orcl1/pdb1/users01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

# 打开另一个session窗口
conn / as sysdba
SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO

alter pluggable database pdb1 close;

# 这时直接打开肯定也会报错
SQL> alter pluggable database pdb1 open;
alter pluggable database pdb1 open
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 30 - see DBWR trace file
ORA-01110: data file 30: '/u01/app/oracle/oradata/orcl1/pdb1/users01.dbf'

# 使用RMAN恢复PDB
rman target /
restore pluggable database pdb1;
recover pluggable database pdb1;

# 这时pdb1就可以OPEN了
alter pluggable database pdb1 open;

# 删除表空间文件后，创建的表也恢复成功
conn lyj/lyj@pdb1
select count(*) from test1;
  COUNT(*)
----------
         2

阅读全文 >>