oracle学习笔记Word文档下载推荐.docx
《oracle学习笔记Word文档下载推荐.docx》由会员分享,可在线阅读,更多相关《oracle学习笔记Word文档下载推荐.docx(11页珍藏版)》请在冰豆网上搜索。
1.3、控件文件jrb.ctl
UNRECOVERABLE--关闭日志,不可恢复
Loaddata--控制文件标识
INFILE*--要输入的数据文件名为这里在命令中指定,所以用*代替
"
fix15"
--指定一行记录的字符数,包括换行符
INTOTABLEsource_jrb--指定表名
APPEND--向表中追加记录
JRposition(1:
8)DATE'
YYYY-MM-DD'
--定长指定1-8位字符为列jr,类型为date
DQDHposition(9:
10),
HBZLposition(11:
12),
SFJRposition(13:
13),
JLZTposition(14:
14),
BUSI_DATA_DATEposition(1:
2)"
to_date(‘20110428’),'
a、insert,为缺省方式,在数据装载开始时要求表为空
b、append,在表中追加新记录
c、replace,删除旧记录,替换成新装载的记录
d、truncate,同上
Data文件:
jrb.dat
20030101000011
20080207000011
20080208000011
20080211000011
20080212000011
20080404000011
20080504000001
20080501000011
20080502000011
20080609000011
20080915000011
20080927000001
1.4、执行命令
将jrb.ctl文件和jrb0.dat文件放在e:
\tem目录下
sqlldrarms/arms@armsdbcontrol=e:
\tem\jrb.ctldata=e:
\tem\JRB0.datlog=e:
\tem\JRB.logbad=e:
\tem\JRB.baddirect=true
arms/arms@armsdb这里数据库用户名:
arms;
密码:
数据库:
armsdb;
2、oracle对应用程序性能分析
有的时候我们发现一个应用系统运行很慢,运行很慢主要是由于跟数据库有关,致使数据很长时间没有返回数据,这个时候我们可能去查oracle到底哪里有问题,是什么原因导致他运行非常慢。
其实很多时间都是由于程序人员写的sql执行效率非常低或者某个时间段访问量忽然猛增或者其他原因,那么我们如何定位到是哪一个SQL或是哪一个时间段有问题呢?
因为如果我们定位到某个时间段比其他时间段cpu,内存都相当耗时,我们就可以查看我们应用系统在这个时间段做什么,从而分析出系统运行慢的原因;
我们也可以分析某个sql的运行时间,有时一段sql跑上几个小时都没跑出来。
如果我们能定位到这些sql,对我们改善系统性能也是有很大帮助。
我们这里通个分析addmrt和awrrpt来粗略分析数据库性能。
在awrrpt这个里面,我们可以得到耗时前五的事件。
调取addmrt和awrrpt文件
登录oracle服务器进行以下目录:
cd$ORACLE_HOME
cdrdbms/admin
找到awrrpt.sql
lsawrrpt.sql
以数据库管理员进入sqlplus
sqlplus/assysdba
执行wrrpt.sql
@/database/product/10gR2/db_1/rdbms/admin/awrrpt.sql
在上图输入格式类型,我们就把这些报告保存为txt格式的,默认保存为html格式。
输完后回车
输入报告天数,我们输入1,输入1表示他将把最近1天的报告展示出来,如果是2那就是近两天的。
这里输入开始的一个snapid,在这里我们可以看到他是每隔一小时记一次快照,我们可以修改这个快照间隔。
回车后,文件名我们这里不输,默认就可以了。
回车后,在当前执行sql的目录中就生成了一个awrrpt_1_14078-14079.txt的文件,我们通过ftp把文件下载到本地(这里oracle服务器在AIX机器上)
选择要下载到本地的目录(我这里e:
\tem)
连接FTP
ftp168.3.63.190
输入用户名密码。
登到文件所在路径:
cd/database/product/10gR2/db_1/rdbms/admin
下载文件
getawrrpt_1_14078_14079.txtawrrpt_1_14078_14079.txt
3、oracle执行计划
通过oracle执行计划我们可以查询oracle数据库在执行一个sql时所花费的代价,它详细地列出了一个sql执行各部分时所耗费的代价,根据这些信息,可以为我们优化sql提供帮助。
3.1、相关的概念
DrivingTable驱动表:
又称为外层表OUTERTABLE。
这个概念用于嵌套与HASH连接中。
如果该rowsource返回较多的行数据,则对所有的后续操作有负面影响。
如果一个大表在WHERE条件有限制条件,则该大表作为驱动表也是合适的,所以并不是只有小表可以作为驱动表,正确说法应该为应用查询的限制条件后,返回较少行源的表作为驱动表。
在执行计划中,应该为靠上的那个rowsource,在后面描述中,一般将该表称为连接操作的rowsource1。
ProbedTable(被探查表):
该表又称为内层表INNERTABLE。
在我们从驱动表中得到具体一行的数据后,在该表中寻找符合连接条件的行。
所以该表应当为大表[实际上应该为返回较大rowsource的表]且相应列上应该有索引。
在后面的描述中,一般将该表称为连接操作的rowsource2.
组合索引(concatenatedindex):
由多个列构成的索引,如createindexidx_emponemp(col1,col2)。
在组合索引中有一个重要的概念:
引导列(leadingcolumn),在上面的例子中,col1列为引导列。
当我们进行查询时可以使用"
wherecol1=?
,也可以使用"
andcol2=?
,都会使用索引,但是"
wherecol2=?
查询就不会使用该索引。
所以限制条件中包含引导列时,该限制条件才会使用该组合索引。
可选择性(selectivity):
比较一下列中唯一键的数量和表中的行数,就可以判断该列的可选择性。
如果该列的“唯一键的数量/表中的行数”的比值越接近1,则该列的可选择性越高,该列就越适合创建索引,同样索引的可选择性也越高。
在可选择性高的列上进行查询时,返回的数据就较少,比较适合使用索引查询。
3.2、oracle访问数据的存取方法
1)全表扫描(FullTableScans,FTS)
为实现全表扫描,Oracle读取表中所有的行,并检查每一行是否满足语句的WHERE限制条件.一个多块读操作可以使一次I/O能读取多块数据块,而不是只读取一个数据块,这极大的减少了I/O总次数,提高了系统的吞吐量,所以利用多块读的方法可以十分高效地实现全表扫描,而且只有在全表扫描的情况下才能使用多块读操作。
在这种访问模式下,每个数据块只被读一次。
使用FTS的前提条件:
在较大的表上不建议使用全表扫描,除非取出数据的比较多,超过总量的5%-10%,或你想使用并行查询功能时。
3.3、索引扫描(IndexScan)
我们先通过index查找到数据对应的rowid值(对于非唯一索引可能返回多个rowid值),然后根据rowid直接从表中得到具体的数据,这种查找方式称为索引扫描或索引查找(indexlookup)。
一个rowid唯一的表示一行数据,该行对应的数据块是通过一次i/o得到的,在此情况下该次i/o只会读取一个数据库块。
索引扫描由2步组成:
[1]扫描索引得到对应的rowid值。
[2]通过找到的rowid从表中读出具体数据。
每步都是单独的一次I/O,但是对于索引,由于经常使用,绝大多数都已经CACHE到内存中,所以第1步的I/O经常是逻辑I/O,即数据可以从内存中得到。
但是对于第2步来说,如果表比较大,则其数据不可能全在内存中,所以其I/O很有可能是物理I/O,这是一个机械操作,相对逻辑I/O来说,是极其费时间的。
所以如果对大表进行索引扫描,取出的数据如果大于总量的5%-10%,使用索引扫描会效率下降很多。
4、表之间的连接
4.1、嵌套循环(NestedLoops)
这个连接方法有驱动表(外部表)的概念。
其实,该连接过程就是一个2层嵌套循环,所以外层循环的次数越少越好,这也就是我们为什么将小表或返回较小rowsource的表作为驱动表(用于外层循环)的理论依据。
如果使用这种方法,决定使用哪个表作为驱动表很重要。
有时如果驱动表选择不正确,将会导致语句的性能很差、很差。
从内部连接过程来看,需要用rowsource1中的每一行,去匹配rowsource2中的所有行,所以此时保持rowsource1尽可能的小与高效的访问rowsource2(一般通过索引实现)是影响这个连接效率的关键问题。
这只是理论指导原则,目的是使整个连接操作产生最少的物理I/O次数。
在上面的连接过程中,我们称Rowsource1为驱动表或外部表。
RowSource2被称为被探查表或内部表。
在NESTEDLOOPS连接中,Oracle读取rowsource1中的每一行,然后在rowsourc2中检查是否有匹配的行,所有被匹配的行都被放到结果集中,然后处理rowsource1中的下一行。
这个过程一直继续,直到rowsource1中的所有行都被处理。
这是从连接操作中可以得到第一个匹配行的最快的方法之一,这种类型的连接可以用在需要快速响应的语句中,以响应速度为主要目标。
如果drivingrowsource(外部表)比较小,并且在innerrowsource(内部表)上有唯一索引,或有高选择性非唯一索引时,使用这种方法可以得到较好的效率。
NESTEDLOOPS有其它连接方法没有的的一个优点是:
可以先返回已经连接的行,而不必等待所有的连接操作处理完才返回数据,这可以实现快速的响应时间。
如果不使用并行操作,最好的驱动表是那些应用了where限制条件后,可以返回较少行数据的的表,所以大表也可能称为驱动表,关键看限制条件。
对于并行查询,我们经常选择大表作为驱动表,因为大表可以充分利用并行功能。
当然,有时对查询使用并行操作并不一定会比查询不使用并行操作效率高,因为最后可能每个表只有很少的行符合限制条件,而且还要看你的硬件配置是否可以支持并行(如是否有多个CPU,多个硬盘控制器),所以要具体问题具体对待。
5、如何查看oracle执行计划
我们一般都用plsql工具来查看oracle的执行计划,在pl/sql的SQL窗口中写上我们要分析的SQL语句,按F5可以查看他的执行计划。
SQL语句:
selecta.dqdh||a.jgdh,a.khrq,a.khrq,a.dqdh,a.jgdh,a.zhye,a.xzxh,a.zhdh
fromSOURCE_DXZDJBa
joinARMS_CFG_GZTSbona.dqdh=b.dqdh
andb.tyrq=to_Date('
3000/12/31'
'
yyyy/mm/dd'
joinARMS_ORGAN_ENTRYIDe
ona.dqdh=e.organ_no
ande.entry_id='
10033'
wherea.jlzt='
1'
anda.zhye<
>
0
anda.xzrq<
to_date('
1990/12/31'
andwork_date(a.khrq,get_date)=b.gzts
andwork_date(sysdate,sysdate)=1
anda.khrq>
=to_date('
2010-01-01'
yyyy-mm-dd'
解释计划:
这里为了好解释,后面的红色字体加了一个行号。
对于这个解释计划如何看,哪一步先执行哪一步后执行,原则是最右边最上面的先执行。
上面这个图最右边是第8行,对象名称是idx_dqdh_dxzdjb,描述是indexrangescan。
意思是索引扫描,再看他的上面7行,对象名称是source_dxzdjb,这两行合起就是查source_dxzdjb这张表,查询方式是索引查询。
然后然后再看第6行是一个全表访问,表名是arms_cfg_gzts,再看第5行,是一个嵌套循环,也就是把source_dxzdjb与arms_cfg_gzts表作连接,source_dxzdjb表作为驱动表。
因为oracle每次都只能是两个表进行连接,所以他把上面的两张表连接后作为一个新的表(暂时这么说吧)再与arms_organ_entryid这个表连接。
我们可以看到目前这个sql的耗费是很少的,也就是说这个SQL执行效率还可以。
如果耗费非常大的话就需要考虑进行优化。
比如一个tableaccessfull的耗费值非常大,我们可以考滤给他建索引。
多表的话我们可以看看他选的驱动表是否合理,驱动表的选择是结果集越少越好(并不是大表不能作驱动表,如果大表有where条件限制查询出来的结果集少也可以作驱动表)。
5、oracle会话
Oracle会话也叫session,我们可以通pl/sql以sysdba身份登录在工具→会话中来查看,如下图所示
有时我们需要对数据库进行还原,把原来的数据库及用户删掉,有时会发现删不掉,因为还有用户连着数据库,我们就可以在这里把这些连接数据库的用户会话kill掉,选中一个会话右键kill;
还有如果有用户把一个表锁定一直不释放,我们可以通过这里选中某个会话看是哪一个会话锁定了表,如果长时间不释放,我们也可以强制结束会话来释放该表的锁。