数据中心数据库管理与sql优化技术交流.docx
《数据中心数据库管理与sql优化技术交流.docx》由会员分享，可在线阅读，更多相关《数据中心数据库管理与sql优化技术交流.docx（52页珍藏版）》请在冰点文库上搜索。
数据中心数据库管理与sql优化技术交流
数据中心数据库日常使用与维护技术交流
省金融计算中心黄万东
目录
1、数据下载系统日常检查与查询3
（1）查目前加载的作业3
（2）查哪些文件已经传输到服务器上但未加载4
（3）查某个文件加载到哪天以及状态4
（4）查文件成功加载最新情况4
（5）查失败的作业与失败原因4
（6）查某个文件加载状态、加载开始时间与结束时间5
（7）查某个文件加载的时长6
2、数据下载系统日常特殊处理6
（1）加载异常的处理6
（2）启停7
（3）手工解密文件8
（4）数据文件的清理8
3、数据库服务器性能查看8
（1）常用性能监控命令8
a）AIX的topas8
b）linux的top13
c）ps15
d）sar16
e）vmstat16
f）iostat19
（2）nmon数据21
（3）AWR（AutomaticWorkloadRepository）数据23
（4）ASH（ActiveSessionHistory）数据23
4、日常数据库常用语句23
（1）查目前连接到哪个实例23
（2）查目前各个表空间大小、已经使用大小、剩余大小23
（3）查diskgroup大小、剩余大小25
（4）查表空间的数据文件25
（5）查表或索引占用的空间大小25
（6）查超过300MB的表或索引26
（7）查某个表是否分区26
（8）查某个表是否有子分区27
（9）查分区表的索引是否是local分区27
（10）查哪个表空间占用大却没有分区28
（11）查某个表的索引列和索引是否唯一性28
（12）查哪个索引状态不正确29
（13）查某个用户正在跑的sql29
（14）查客户营销系统bbrun用户非web应用正在运行的sql29
（15）查客户营销系统bbrun用户web应用正在运行的sql30
（16）如何杀某个session30
（17）查表是否被锁30
（18）查回滚段的使用31
（19）查询表的描述32
（20）查询列的描述32
（21）建主键时如何指定表空间33
（22）误删除一张表，如何恢复33
（23）在函数中怎么执行DDL语句33
（24）导出用户的建表语句34
（25）导出用户的建索引语句34
（26）导出用户的建视图语句34
（27）导出用户的建存储过程语句34
（28）导出用户的建函数语句34
（29）导出用户的建序列语句34
5、sql语句优化34
（1）执行计划34
（2）何时要对表进行分析34
（3）如何对一张表进行分析35
（4）sql执行计划35
（5）hint35
（6）用EXISTS替代IN；用NOT EXISTS替代NOT IN35
（7）并行度35
（8）RAC影响35
（9）索引监控35
（10）表和索引的压缩存储的研究35
（11）增加序列的cache值，建议cache值不少于2035
6、日常编写sql应该注意的地方36
1、数据下载系统日常检查与查询
（1）查目前加载的作业
登陆10.241.1.16的cpdds用户，在/DDS_DATA/DDS/ETL/bin目录下执行etlctlstatus
LPendingQue带-的作业表示数据从数据文件解密并加载到cpdds_sdata用户。
TPendingQue带#的作业表示数据从cpdds_sdata用户下merge到cpdds_pdata用户。
进一步查看作业
（2）查哪些文件已经传输到服务器上但未加载
可以通过查看/DDS_DATA/DDS/ETL/run目录下的文件
一直未加载的文件需要联系数据下载系统项目组进行处理。
（3）查某个文件加载到哪天以及状态
selectjob_name,LAST_TXDATE,JOB_STATUSfromcpdds_etl.etl_jobwherejob_namelikeupper（'sav_indpty_tx_stat%t98_indpty_tx_stat'）;
（4）查文件成功加载最新情况
selecta.etl_job_name,b.JOB_FREQUENCY,max（to_char（a.txdate,'YYYYMMDD'））txdate
fromcpdds_etl.etl_job_loga,cpdds_etl.etl_jobb
wherea.etl_job_name=b.job_name
anda.etl_job_namelike'%#%'
anda.STATUS='D'
groupbya.etl_job_name,b.JOB_FREQUENCY
orderbyb.JOB_FREQUENCY,max（to_char（a.txdate,'YYYYMMDD'））desc,a.etl_job_name;
其中STATUS='D'表示数据加载成功；JOB_FREQUENCY=‘D’的是每天加载；JOB_FREQUENCY=‘M’是每月加载；JOB_FREQUENCY=‘Y’是每年加载。
（5）查失败的作业与失败原因
colRETMSGfora60
coletl_job_namefora56
select/*+ORDER（B,A）*/a.etl_job_name,max（to_char（a.txdate,'YYYYMMDD'））txdate,max（to_char（a.end_time,'YYYYMMDDHH24:
MI:
SS'））end_datetime,substr（RETMSG,1,30）RETMSG
fromcpdds_etl.etl_job_loga,cpdds_etl.etl_jobb
wherea.etl_job_name=b.job_name
anda.etl_job_namelike'%#%'
andb.JOB_FREQUENCY='D'anda.STATUS='F'
groupbya.etl_job_name,substr（RETMSG,1,30）
orderby2asc,3asc;
（6）查某个文件加载状态、加载开始时间与结束时间
select/*+ORDER（B,A）*/a.etl_job_name,to_char（a.txdate,'YYYYMMDD'）txdate,to_char（START_TIME,'YYYYMMDD-HH24:
MI:
SS'）,to_char（END_TIME,'YYYYMMDD-HH24:
MI:
SS'）,a.status
fromcpdds_etl.etl_job_loga,cpdds_etl.etl_jobb
wherea.etl_job_name=b.job_name
anda.etl_job_namelike'%#%'
--anda.STATUS='D'
andto_char（a.txdate,'YYYYMMDD'）in（'20110320','20110321'）
andb.JOB_FREQUENCY='D'
--groupbya.etl_job_name,to_char（a.txdate,'YYYYMMDD'）
orderby2desc,a.etl_job_name;
（7）查某个文件加载的时长
select/*+ORDER（B,A）*/a.etl_job_name,to_char（a.txdate,'YYYYMMDD'）txdate,ROUND（TO_NUMBER（END_TIME-START_TIME）*24*60*60）do_time
fromcpdds_etl.etl_job_loga,cpdds_etl.etl_jobb
wherea.etl_job_name=b.job_name
anda.etl_job_namelike'%#%'
--anda.STATUS='D'
andto_char（a.txdate,'YYYYMMDD'）in（'20110320','20110321'）
andb.JOB_FREQUENCY='D'
orderby2desc,a.etl_job_name;
查询结果单位是秒。
2、数据下载系统日常特殊处理
（1）加载异常的处理
目前，数据下载系统从数据文件解密并加载到cpdds_sdata的过程一般不会出错，而碰到结息数据、小额管理费、每月第一天这样的大数据量时，从cpdds_sdata到cpdds_pdata的过程处理很慢，并时常出错（常见是临时表空间和回滚段不足；或进程僵死），需要人工杀session和进程，并且重跑数据。
1）查出对应的session，杀掉session
2）杀对应的进程；
3）等待数据库回滚结束
4）修改cpdds_etl.etl_job_log
updatecpdds_etl.etl_job_logasetstatus='F',end_time=sysdate
whereetl_job_name=upper（'cre_indpty_prod_stat#t98_indpty_prod_stat'）andto_char（a.txdate,'YYYYMMDD'）='20111002';
将状态改成失败（F）
5）修改cpdds_etl.etl_job
updatecpdds_etl.etl_jobasetJOB_STATUS='F'
wherejob_name=upper（'cre_indpty_prod_stat#t98_indpty_prod_stat'）;
将状态改成失败（F），该作业才会重新启动。
6）修改控制文件
cd/DDS_DATA/DDS/ETL/run
mvcre_indpty_prod_stat#t98_indpty_prod_stat_20111002.ctl.tscre_indpty_prod_stat#t98_indpty_prod_stat_20111002.ctl，即将对应作业最后的.ts去掉。
7）作业重新加载
数据下载系统会根据控制文件和cpdds_etl.etl_job的状态重新启动该作业的加载。
（2）启停
1）启动etl
cd/DDS_DATA/DDS/ETL/bin
etlctlstart（启动过程需要输入用户和密码）
2）关闭etl
cd/DDS_DATA/DDS/ETL/bin
etlctlstop
3）启动GTP
cd/DDS_DATA/GTPServer
./gtp.shstop
4）关闭GTP
cd/DDS_DATA/GTPServer
./gtp.shstop
（3）手工解密文件
有时，客户营销系统需要单独处理数据下载系统的某些文件，因此需要手工将数据下载系统的文件先解密，解密前建议建一个目录用于保存解密前后的文件，数据下载系统处理完的数据文件保存在/DDS_DATA/DDS/ETL/success/YYYYMMDD目录下，数据文件拷贝后，到/DDS_DATA/DDS/ETL/bin目录下，执行decrypt，然后根据屏幕提示操作，操作过程需要输入用户和密码。
（4）数据文件的清理
若文件系统/CPDDS_DATA使用率超过85%，可以删除/DDS_DATA/DDS/BAK/99/YYYYMMDD和/DDS_DATA/DDS/BAK/02/YYYYMMDD下的文件（/DDS_DATA/DDS/BAK/99目录是转发给省邮政的文件，/DDS_DATA/DDS/BAK/02是转发给厦门分行的文件）。
也可以删除/DDS_DATA/DDS/ETL/success/YYYYMMDD下的文件，该目录是福建省分行处理成功的文件。
3、数据库服务器性能查看
目前数据中心采用2台IBMP550小型机做为数据库服务器，其中A机IP地址为10.241.1.12（数据库vip地址是10.241.1.13），B机IP地址是10.241.1.14（数据库vip地址是10.241.1.15）。
目前总行数据下载系统连接到A机数据库，客户营销系统web和应用连接到B机数据库，平常要查看数据库服务器的性能，必须登录到相应的服务器上查看，在10.241.1.16上执行vmstat看的是应用服务器的性能，而不是数据库服务器的性能，目前A机和B机登陆的用户是view/view1。
（1）常用性能监控命令
a）AIX的topas
topas下令用于监控种种体系资源，如CPU的应用情况，CPU变乱和行列，内存和换页空间的应用，磁盘性能，网络性能以及NFS统计等。
它还会陈诉指派给差别WLM类的进程对体系资源的损耗情况。
它还能陈诉体系中最热点的进程和事变量管理器（WLM）的热点类。
有关WLM类信息只有在WLM激活时才会表现。
topas下令将热点进程界说为那些应用大宗CPU工夫的进程。
topas下令没有作日记的选项，全部信息都是及时的。
CPU使用情况显示一个条形图表来表示累积的CPU使用情况。
如果有多个CPU，按c键两次就可显示CPU列表。
仅按c键一次会关闭这个部分。
User%显示以用户方式执行的程序所使用的CPU的百分数；（缺省按用户%排序）
Kern%显示以内核方式执行的程序所使用的CPU的百分数；
Wait%显示用于等待IO的时间的百分数；
Idle%表示CPU空闲时间的百分数。
网络接口显示了网络接口的列表。
所显示接口的最大数目是正被监视的接口数目，正如-n标志指定的那样。
按n键可关闭此部分。
再按一次n键就会显示一行所有网络接口活动的报告摘要。
两个报告都显示下列字段：
Interf网络接口的名称。
KBPS在监视时间间隔内每秒钟以MB为单位的总吞吐量。
这个字段是每秒接收到的千字节和发送的千字节的总和。
I-Pack在监视时间间隔内每秒钟接收到的数据包的数目。
O-Pack在监视时间间隔内每秒钟发送的数据包的数目。
KB-In在监视时间间隔内每秒钟接收到的千字节的数目。
KB-Out在监视时间间隔内每秒钟发送的千字节的数目。
磁盘显示了物理磁盘的列表。
按d键可关闭这个部分。
再按一次d键就会显示一行所有物理磁盘活动的报告摘要。
两个报告都显示下列字段：
Disk物理磁盘的名称。
Busy%表示物理磁盘活动时间的百分比（驱动器带宽使用）。
KBPS在监视时间间隔内每秒钟读和写的千字节的数目。
此字段是KB-Read和KB-Writ的总和。
TPS每秒钟发送到物理磁盘的传输的数目。
传输是对物理磁盘的I/O请求。
多个逻辑请求可组合成对磁盘的单个I/O请求。
传输大小不确定。
KB-Read每秒钟从物理磁盘读取的千字节的数目。
KB-Writ每秒钟写到物理磁盘的千字节的数目。
进程显示了进程的列表。
按p键可关闭此部分。
按监视时间间隔内进程的CPU使用情况来对进程排序。
对于每一进程会显示下列字段：
Name在进程中执行的可执行程序的名称。
名称已被除去任何路径名和参数信息并被截断到9个字符的长度。
PID进程的进程标识。
CPU%CPU使用情况
在监视时间间隔内进程的平均CPU使用情况。
第一次显示进程时，这个值表示整个进程阶段平均CPU使用情况。
PgSp分配给此进程的调页空间的大小。
这可被认为是进程覆盖区的一种表达式，但并不包括用来保持可执行程序和它依赖的任何共享库的内存。
Owner拥有此进程的用户的用户名。
EVENTS/QUEUES
Cswitch在监视时间间隔内每秒上下文切换的数量。
Syscalls在监视时间间隔内每秒执行的系统调用的数量。
Reads在监视时间间隔内每秒执行的read系统调用的数量。
Writes在监视时间间隔内每秒执行的write系统调用的数量。
Forks在监视时间间隔内每秒执行的fork系统调用的数量。
Execs在监视时间间隔内每秒执行的exec系统调用的数量。
Runqueue准备运行但需要等待处理器可用的平均线程数目。
Waitqueue正在等待页面调度完成的平均线程数目。
FILE/TTY显示所选文件与tty统计信息的每秒频率。
Readch在监视时间间隔内read系统调用每秒读的字节数。
Writech在监视时间间隔内write系统调用每秒写的字节数。
Rawin在监视时间间隔内从TTY中每秒读取的原始字节数。
Ttyout在监视时间间隔内每秒写入TTY中的字节数。
Igets在监视时间间隔内每秒调用信息节点查找例程的数量。
Namei在监视时间间隔内每秒调用路径名查找例程的数量。
Dirblk在监视时间间隔内被目录搜索例程每秒扫描的目录块数目。
PAGING显示页面调度统计信息的每秒频率。
Faults在监视时间间隔内每秒页面出错的数量。
这包括不能激活页面调度的页面故障。
Steals在监视时间间隔内每秒钟有物理内存4K帧被虚拟内存管理器占用。
PgspIn在监视时间间隔内每秒钟从调页空间读取4K页面的数量。
PgspOut在监视时间间隔内每秒钟把4K页面写入调页空间的数量。
PageIn在监视时间间隔内每秒钟读取4K页面的数量。
这包括与从文件系统读取有关的页面调度活动。
从这个值中减去PgspIn就可得到在监视时间间隔内每秒钟从文件系统读取的4K页面的数量。
PageOut在监视时间间隔内每秒钟写4K页面的数量。
这包括与写入文件系统有关的页面调度活动。
从这个值中减去PgspOut就可得到在监视时间间隔内每秒钟写入文件系统的4K页面的数量。
Sios在监视时间间隔内虚拟内存管理器每秒钟发出的I/O请求的数目。
MEMORY显示实际内存大小与内存使用的分布情况。
Real,MB以MB为单位的实际内存大小。
%Comp当前分配给计算页面帧的实际内存的百分数。
计算页面帧通常是那些被调页空间支持的帧。
%Noncomp当前分配给非计算页面帧的实际内存的百分数。
非计算页面帧通常是那些被文件空间（可以是数据文件、可执行文件或共享库文件）支持的帧。
%Client当前被分配用来高速缓存远程安装的文件的实际内存的百分数。
PAGINGSPACE显示调页空间的大小及利用情况。
Size,MB系统上所有调页空间的总和，以MB为单位。
%Used当前在使用的调页空间占所有的百分数。
%Free当前未使用的调页空间占所有的百分数。
NFS（calls/sec）显示每秒调用的NFS状态
ServerV2
ClientV2
ServerV3
ClientV3
b）linux的top
top命令是Linux下常用的性能分析工具，能够实时显示系统中各个进程的资源占用状况，类似于Windows的任务管理器。
统计信息区
前五行是系统整体的统计信息。
第一行是任务队列信息，同uptime命令的执行结果。
其内容如下：
up系统运行时间，格式为时:
分
user当前登录用户数
loadaverage系统负载，即任务队列的平均长度。
三个数值分别为1分钟、5分钟、15分钟前到现在的平均值。
第二、三行为进程和CPU的信息。
当有多个CPU时，这些内容可能会超过两行。
内容如下：
Tasks:
进程总数
running正在运行的进程数
sleeping睡眠的进程数
stopped停止的进程数
zombie僵尸进程数
Cpu（s）:
us用户空间占用CPU百分比
y内核空间占用CPU百分比
ni用户进程空间内改变过优先级的进程占用CPU百分比
id空闲CPU百分比
wa等待输入输出的CPU时间百分比
hi
si
最后两行为内存信息。
内容如下：
Mem:
物理内存总量
used使用的物理内存总量
free空闲内存总量
buffers用作内核缓存的内存量
Swap:
交换区总量
used使用的交换区总量
free空闲交换区总量
cached缓冲的交换区总量。
内存中的内容被换出到交换区，而后又被换入到内存，但使用过的交换区尚未被覆盖，该数值即为这些内容已存在于内存中的交换区的大小。
相应的内存再次被换出时可不必再对交换区写入。
进程信息区
统计信息区域的下方显示了各个进程的详细信息
序号列名含义
PID进程id
USER进程所有者的用户名
PR优先级
NInice值。
负值表示高优先级，正值表示低优先级
VIRT进程使用的虚拟内存总量，单位kb。
VIRT=SWAP+RES
RES进程使用的、未被换出的物理内存大小，单位kb。
RES=CODE+DATA
SHR共享内存大小，单位kb
S进程状态。
D=不可中断的睡眠状态
R=运行
S=睡眠
T=跟踪/停止
Z=僵尸进程
%CPU上次更新到现在的CPU时间占用百分比
%MEM进程使用的物理内存百分比
TIME+进程使用的CPU时间总计，单位1/100秒
COMMAND命令名/命令行
c）ps
列出最占资源20个进程：
ps-ef|sort+3-4nr|head-20
linux：
AIX：
d）sar
AIX下只有root允许执行sar命令：
linux下：
e）vmstat
AIX：
参数
含义
备注
kthr
内核进程的状态
r
运行队列中的进程数,在一个稳定的工作量下,应该少于5
运行的进程比较多，系统很繁忙,当这个值超过了CPU数目，就会出现CPU瓶颈了
b
等待队列中的进程数（等待I/O）,通常情况下是接近0的
memory
虚拟和真实内存的使用信息
avm
活动虚拟页面,在进程运行中分配到工作段的页面空间数
free
空闲列表的数量.一般不少于120,当fre少于120时,系统开始自动的kill进程去释freelist
page
页面活动的信息
re
页面i/o的列表
pi
从页面输入的页（一般不大于5）
po
输出到页面的页
fr
空闲的页面数（可替换的页面数）
sr
通过页面置换算法搜索到的页面数
cy
页面置换算法的时钟频率
faults
在取样间隔中的陷阱及中断数
in
设备中断
sy
系统调用中断
cs
内核进程前后交换中断
CPU
cpu的使用率
us
用户进程的时间
一般us+sy在单用户系统中不大于
升级会员 