Oracle数据库的基础教程.ppt

1、,介绍要点,ORACLE公司及其数据库产品 数据库体系结构技术综述 数据库管理 数据库性能调整 9i产品新特性 常见问题Q,启动数据库,把数据库从nomount状态改变到mount状态,ALTER DATABASE database MOUNT;,把数据库打开到只读状态,ALTER DATABASE database OPEN READ ONLY;,以只读方式打开数据库,任何数据库都可以以只读方式打开 以只读方式打开的数据库可以： 执行查询 使用本地管理表空间进行磁盘排序操作 可以使表空间的数据文件online或offline 可以对offline的数据文件和表空间进行恢复操作,A x x x

2、 x,T x x o o,I x x x o,Shutdown Mode Allow new connections Wait until current sessions end Wait until current transactions end Force a checkpoint and close files,N x o o o,Shutdown mode: A AbortI Immediate T TransactionalN Normal,关闭数据库选项,YES,NO,x o,不同关闭数据库的方式,Transfer of Funds,Remove funds from old

3、account,Commit,Log out,Check account balances,Insert new funds,Shutdown Normal,Shutdown Immediate,Shutdown Transactional,Database down,Database down,Database down,动态性能视图,SGA,V$PARAMETERV$SGAV$OPTIONV$PROCESSV$SESSION V$VERSIONV$INSTANCE,Control file,V$THREADV$CONTROLFILE V$DATABASEV$DATAFILEV$DATAFI

4、LE_HEADER V$LOGFILE,动态改变参数,ALTER SESSION SET SQL_TRACE=true;,ALTER SYSTEM SET TIMED_STATISTICS=true;,ALTER SYSTEM SET SORT_AREA_SIZE=131072 DEFERRED;,一些初始化参数可以在数据库运行阶段动态修改,终止用户会话,SELECT sid, serial# FROM v$session WHERE username=SCOTT;,ALTER SYSTEM KILL SESSION 7,15;,通过V$SESSION动态性能试图可以确定要终止的用户会话：,通

5、过执行ALTER SYSTEM命令终止会话：,使用Database Configuration Assistant创建数据库,选择创建数据库方式,预定义的数据库类型,完成数据库创建,使用Export工具,$exp scott/tiger tables=(dept,emp) file=emp.dmp log=exp.log compress=n direct=y,DEPT and EMP tables,emp.dmp,Export,使用Import工具,Import,emp.dmp,$imp scott/tiger tables=(dept,emp) file=emp.dmp log=imp.l

6、og ignore=y,imp.log,Database,OEM控制台实例管理,OEM控制台会话管理,OEM控制台表空间管理,OEM控制台数据文件管理,OEM控制台用户管理,OEM控制台角色管理,OEM控制台表管理,OEM控制台视图管理,数据库性能调整,性能调整,谁来调整？ 应用程序的设计者 应用程序的开发者 数据库管理员 系统管理员 为什么要调整 怎样调整,调整目标, 数据库的响应时间 数据库的高可用性 数据库的命中率 内存利用率 访问最少的数据块 内存中cache的数据块 共享的程序代码 尽可能快的读写数据 保证用户不需等待资源,调整步骤,1. 对设计进行调整 2. 对应用进行调整 3.

7、对内存进行调整 4. 对磁盘I/O进行调整 5. 调整减少资源冲突 6. 对操作系统进行调整,诊断信息,Trace文件: 警告日志文件 后台进程的trace文件 用户的trace文件,警告日志文件, 警告日志文件由一系列按时间排列的信息和错误组成 检查警告日志文件可以: 发现oracle内部错误 (ORA-600) 和数据库块错误 观察数据库操作 查看非缺省的初始化参数 定期清除警告日志文件,警告日志文件,Userprocess,Serverprocess,BACKGROUND_DUMP_DEST= $ORACLE_HOME/rdbms/log,Alert log file,Instance,

8、SGA,Shared pool,用户跟踪文件,启动或停止会话级或实例级服务器跟踪进程: ALTER SESSION命令 SET_SQL_TRACE_IN_SESSION过程 初始化参数SQL_TRACE 用户跟踪文件包含被跟踪的会话的SQL语句的统计信息 在做数据库调整时用户的跟踪文件是很有用的,用户跟踪文件,Userprocess,Serverprocess,存放在参数USER_DUMP_DEST指定的目录下,Instance,SGA,Shared pool,视图、包和工具, 动态故障诊断、 性能和数据字典视图 V$ xxx动态故障诊断和 性能视图 DBA_ xxx 数据字典视图 UTLBS

9、TAT.SQL 和UTLESTAT.SQL 脚本 Oracle等待事件 企业管理事件服务 Oracle诊断和调整工具,数据字典和专用视图,在运行ANALYZE命令 后数据字典和专用试图存储了有用的统计信息： DBA_TABLES, DBA_TAB_COLUMNS DBA_CLUSTERS DBA_INDEXES, INDEX_STATS INDEX_HISTOGRAM, DBA_HISTOGRAMS 这些统计信息是静态的，在再次运行ANALYZE命令之前是不会改变的,动态故障诊断和性能视图, V$视图 是基于X$表的视图 使用V$FIXED_TABLE查看 X$表 通常不会直接查询 动态和连续

10、地变化 命名含义不明确 在数据库启动时被生成，在数据库关闭时被清除,故障诊断和调整,Instance/Database V$DATABASE T V$INSTANCE T V$OPTION TV$PARAMETER T/P V$BACKUP T V$PX_PROCESS_SYSSTAT T/P V$PROCESS T V$WAITSTAT T/P V$SYSTEM_EVENT T/P,Memory V$BUFFER_POOL_STATISTICS T/P V$DB_OBJECT_CACHE T V$LIBRARYCACHE P V$ROWCACHE P V$SYSSTAT T/P V$SGAS

11、TAT P,Disk V$DATAFILE T/P V$FILESTAT T/P V$LOG T V$LOG_HISTORY T V$DBFILE T/P V$TEMPFILE P V$TEMPSTAT P,User/Session V$LOCK P V$OPEN_CURSOR T V$PROCESS T V$SORT_USAGE T/P V$SESSION T/P V$SESSTAT T/P V$TRANSACTION T V$SESSION_EVENT T/P V$SESSION_WAIT T/P V$PX_SESSTAT P V$PX_SESSION P V$SESSION_OBJECT

12、_CACHE P,Contention V$LOCK T/P V$ROLLNAME T/P V$ROLLSTAT T/P V$WAITSTAT T/P V$LATCH T/P,系统级统计信息,会话级统计信息,T 故障诊断 T/P故障诊断/性能调整,收集系统的统计信息,V$STATNAME statistic# name class,V$SYSSTAT statistic# name class value,V$SGASTAT pool name bytes,V$EVENT_NAME event# name parameter1 parameter2 parameter3,V$SYSTEM_EV

13、ENT event total_waits total_timeouts time_waited average_wait,收集会话的统计信息,V$STATNAME statistic# name class,V$SESSTAT sid statistic# value,V$SESSION_WAIT sid seq# event p1/2/3text wait_time seconds_in_wait state,V$SESSION sid serial# username osuser,V$SESSION_EVENT sid event total_waits total_timeouts

14、time_waited average_wait max_wait,V$EVENT_NAME event# name parameter1 parameter2 parameter3,使用UTLBSTAT和UTLESTAT, 收集数据库性能数据 产生报表 用SYSDBA角色在SQL*Plus运行 设置TIMED_STATISTICS参数为TRUE,收集统计信息,utlbstat.sql,Examines the instance,Stores statistics in BEGIN tables,utlestat.sql,Examines the tables,Produces a repor

15、t,report.txt,Stores statistics in END tables,统计信息报告, Library cache统计信息 系统统计信息 等待事件统计信息 Latch统计信息 回滚段竞争统计信息 Buffer等待信息 数据字典命中统计信息 每个数据文件和表空间I/O统计信息,Library Cache 统计信息,SQL Rem Select Library cache statistics.The pinhitrate should be high. SQL select namespace library, gets, 3 round(decode(gethits,0,1,

16、gethits)/decode(gets,0,1,gets),3) 4 gethitratio, pins, 6 round(decode(pinhits,0,1,pinhits)/decode(pins,0,1,pins),3) 7 pinhitratio, reloads, invalidations 9 from stats$lib; LIBRARY GETS GETHITRATI PINS PINHITRATI RELOADSINVALIDAT - - - - - - - BODY 105 1 105 1 0 0 CLUSTER 10 1 9 1 0 0 INDEX 0 1 0 1 0

17、 0 OBJE 0 1 0 1 0 0 PIPE 0 1 0 1 0 0 SQL AREA 2036 .987 12822 .982 95 0 TABLE/PROCED 553 .98 3714 .969 81 0 TRIGGER 917 1 917 .997 3 0 8 rows selected.,I/O统计信息,SQL Rem Select Library cache statistics.The pinhitrate should be high. SQL select namespace library, gets, 3 round(decode(gethits,0,1,gethit

18、s)/decode(gets,0,1,gets),3) 4 gethitratio, pins, 6 round(decode(pinhits,0,1,pinhits)/decode(pins,0,1,pins),3) 7 pinhitratio, reloads, invalidations 9 from stats$lib; LIBRARY GETS GETHITRATI PINS PINHITRATI RELOADSINVALIDAT - - - - - - - BODY 105 1 105 1 0 0 CLUSTER 10 1 9 1 0 0 INDEX 0 1 0 1 0 0 OBJ

19、E 0 1 0 1 0 0 PIPE 0 1 0 1 0 0 SQL AREA 2036 .987 12822 .982 95 0 TABLE/PROCED 553 .98 3714 .969 81 0 TRIGGER 917 1 917 .997 3 0 8 rows selected.,Oracle等待事件,V$EVENT_NAME视图包含所有事件，包括字段 EVENT#、NAME、PARAMETER1、PARAMETER2 和 PARAMETER3,事件统计视图, V$SYSTEM_EVENT: 所有会话的等待事件 V$SESSION_EVENT:每个会话的等待事件 V$SESSION_

20、WAIT:当前活动会话等待的事件,V$SYSTEM_EVENT视图,V$SESSION_EVENT视图,SID EVENT TOTAL_WAITS AVERAGE_WAIT - - - - 10 buffer busy waits 12 5 10 db file sequential read 129 0 10 file open 1 0 10 SQL*Net message to client 77 0 10 SQL*Net more data to client 2 0 10 SQL*Net message from client 76 0,SQL select sid, event, t

21、otal_waits,average_wait 2 from v$session_event where sid=10;,V$SESSION_WAIT视图,SID SEQ#EVENTWAITSTATE TIME - - - 11284pmon timer0WAITING 21697rdbms ipc message0WAITING 3183rdbms ipc message0WAITING 44688rdbms ipc message0WAITING 5114smon timer0WAITING 614SQL*Net message from client-1WAITED SHORT TIME

22、,SQL SELECT sid, seq#, event, wait_time, state 2FROM v$session_wait;,调整字典缓存区的工具,V$LIBRARYCACHE,Shared pool,Library cache Shared SQL and PL/SQL,V$SQLAREA,V$SQLTEXT,V$DB_OBJECT_CACHE,report.txt,V$SGASTAT,SHARED_POOL_SIZE OPEN_CURSORS SESSION_CACHED_CURSORS CURSOR_SPACE_FOR_TIME,Data dictionarycache,UG

23、A,设置字典缓存区大小,设置存储包、视图以及表等对象必须的全局内存空间 设置存储常用SQL语句的内存空间 为了避免内存空间碎片应预留大内存 保存经常使用的对象 把大的匿名块转换成小的调用包函数的匿名块,调整目标和技巧,调整目标： 服务进程在内存中能找到数据 对于OLTP系统有90%命中率 调整技巧： 增加数据块缓存区大小 使用多个缓存区把经常访问的小表缓存到内存 另外设置排序和并行读缓存区,SQL UPDATE emp 2 SET sal=sal*1.1 3 WHERE empno=7369;,LGWR,ARCH,Archivedlog files,SQL DELETE FROM s_emp

24、2 WHERE empno=7400;,Serverprocess,Serverprocess,重做日志缓存区,减少重做操作,更少的重做操作产生更少的重做日志内容，也就需要更小的重做日志缓存区 减少重做操作的方法： 不使用归档模式的直接装载数据 在归档模式下使用NOLOGGING模式直接装载数据 使用NOLOGGING模式直接装载插入数据 在使用SQL语句是使用NOLOGGING模式,调整排序操作,尽可能的避免排序操作 进可能保证排序操作在内存中完成，减少交换和换页操作 分配适当的临时空间，减少空间申请操作 如何避免排序操作 在创建索引时使用NOSORT参数 使用UNION ALL操作代替UN

25、ION操作 在做表联接的时候使用索引 为在ORDER BY子句中引用的列创建索引 为列做分析 在为大对象做分析时使用ESTIMATE参数而不是 COMPUTE参数,调整回滚段,事务在访问回滚段的时候不需要等待 在正常的运行阶段回滚段不需要扩展 用户和包应该尽量少使用回滚段 应该没有事务出现回滚段不够错误 数据读取者应该总是能够查询到读一致镜像数据,9i产品新特性,高可用性,零数据丢失灾难保护 在线数据演化 精确的数据库修复 自助的错误更正,实现连续的数据可用性,Oracle9i 提供持续的数据可用性-存储数据最安全的地方,计划外宕机,系统故障,数据故障 和灾难,系统维护,数据库维护,人为错误,

26、Data Guard 完全没有数据丢失的解决方案,Flashback Query / Log Miner 访问过去数据的时间机器,Dynamic Reconfiguration 调整系统性能而无须中断,Online Redefinition 在线对表进行重组和定义,Real Application Clusters 30秒内完成切换,计划宕机,Oracle9i 数据卫士Data Guard,日志接收,日志应用,Broker 管理接口,备用系统,可选延迟,Standby Logs,日志发送,主系统,日志,Oracle9i Data Guard 数据卫士-灾难保护,零数据丢失保护 Zero Los

27、s Mode 通过同步/非同步的日志传送 减低人为错误 通过 延迟传送方式 Delayed Mode Graceful Switch Over 主系统和备用系统可交替使用 Data Guard Manager OEM 图形工具设置，监控及管理,备用系统,Delayed andZero Loss Modes(Both),主系统,在线重新定义 - 减少计划宕机时间,在线 schema 重新定义 增加, 修改, 删除表字段 完全在线索引操作 创建, 重建 在线表重组 在线结构分析验证 无中断的更新查询,Oracle9i 闪回查询 - 纠正人为错误,允许查看任意时间点的数据 简单的SQL 接口 方便确

28、定过去的变化 系统管理undo信息 用户或应用级控制访问,解决 人为错误 的挑战,Fast-Start 故障恢复,Oracle9i 自动恢复数据 数据库管理员 可以设置恢复时间限制 Oracle9i 大大减少恢复时间 减少延缓的回滚操作影响,案例:高OLTP 业务和批量操作的恢复时间,Oracle9i Log Miner -取消任意的变化,查询日志内容 SQL 接口 支持主要数据类型 按值查询 GUI和命令行接口,可伸缩性和性能,通过集群技术提高事务处理的吞吐量 可伸缩的会话状态管理 细致的自动的资源管理 对于电子商务起关键作用的优化特性,支持千万级的Internet用户,Oracle9i R

29、eal Application Clusters- 线性扩展,完全的内存熔合 支持 读/读，读/写，写/写方式的资源争夺 在集群环境，任何应用可随意扩展 应用系统可以不必考虑集群是否存在 线性的扩展-增加节点，增加性能,Oracle9i 真正应用集群服务器,随节点增减而伸缩自如 增加系统的容错性 任何应用程序不需修改即可运行 每当增加节点集群环境变得更可靠 只有所有节点都宕了,整个系统才可能停止,所有数据 A-Z,应用程序,所有数据 A-Z,应用程序,1026,1900,3648,0,500,1000,1500,2000,2500,3000,3500,4000,单节点,双节点,四节点,用户数,

30、Oracle Applications Scalability,Near-Linear Scalability,Real Application Clusters使Oracle 应用软件获得近线性的扩展性,提高关键系统的性能,基于CPU和内存的查询优化，而不仅仅基于硬盘I/O 提高分布式查询的优化 PL/SQL 本地编译,缓存,回滚段,共享池,个人 内存,Oracle9i 服务器更易于管理智能，动态，自我优化（一）,Oracle9i 服务器更易于管理智能，动态，自我优化（二）,备份与恢复 可重新开始的备份与恢复 备份保持策略 自描述备份文件 基于快速启动的恢复 可恢复语句 Oracle 文件管

31、理（Managed Files）,常见问题Q&A,不同版本数据库数据倒出/倒入（一）,问题描述 不同版本的 Oracle 数据库之间的数据交换，经常用到不同版本的 exp/imp 工具，采用正确的版本的工具做 exp/imp 才能成功的完成数据交换/转移 解决方法 定义 源数据库 数据倒出的数据库 目标数据库 数据倒入的数据库 原则 使用源/目标数据库中版本较低的 exp 工具进行倒出 使用目标数据库相同版本的 imp 工具进行倒入,倒出时需要在数据源数据库运行的脚本 源数据库 目标数据库 运行脚本名称 8.x.x 7.x.x catexp7.sql 7.x.x 6.x.x catexp6.s

32、q 例子 From 7.3.3 to 8.1.6 exp7.3.3 imp8.1.6 From 8.1.5 to 8.0.6 exp8.0.6 imp8.0.6 From 8.1.7 to 8.1.6 exp8.1.6 imp8.1.6 From 8.1.7 to 7.3.4 exp7.3.4 imp7.3.4运行 catexp7.sql,不同版本数据库数据倒出/倒入（二）,如何将数据 exp 到多个数据文件,例子 exp demo/demo file=(orders_1.dmp,orders_2.dmp,orders_3.dmp) log=orders.log filesize=1g tab

33、les=orders 注意点 imp 例子 imp demo/demo file=orders_1.dmp tables=oraders log=oraders.log exp时提供文件名的数量,如何倒出某个表的部分数据,适用情况 部分数据的转移 备份符合某种条件的数据 语法：Query=“一个 where 条件” 例子 $exp scott/tiger query=”where ename like KING%” tables=emp file=exp.dmp C:exp scott/tiger query=“”where ename like KING%” tables=emp file=

34、exp.dmp 参数文件中 Query=“where ename like KING%”,如何提高Import/Export性能,EXPORT 增加BUFFER（eg.2000） 停止所有应用 Export文件不在ORACLE使用的驱动器上 不要export到NFS文件系统 IMPORT 建立一个indexfile，在数据import完成后再建立索引 将import文件放在不同的驱动器上 增加DB_BLOCK_BUFFERS 增加LOG_BUFFER 用非归档方式运行ORACLE： ALTER DATABASE NOARCHIVELOG； 建立大的表空间和大的回滚段，OFFLINE其他回滚段，

35、回滚段的大小为最大表的1/2 使用COMMIT=N 使用ANANLYZE=N,Import/Export常见问题,ORA-00001：Unique constraint () volated Import了重复行。可用IGNORE=NO越过以存在的表 ORA-01555:Snapshot too old 如用CONSISTENT=NO参数做EXPORT是，其他用户不要使用ORACLE ORA-01562：Failed to extend rollback segment IMPORT时可用COMMIT=Y,如何减小倒入表的初始 extent（一）,做数据倒入时报 ora-1658，无法分配初始

36、段 可能原因 原来初始段参数设置很大 Exp 时，compress=y，整个表的数据全部在一个 extent 中 解决办法 扩大表空间，使该表空间有足够的连续自由空间可以建立初始段 用以下步骤，先建立该表，且减小了表的初始段参数值，然后倒入数据,$imp uname/passwd file=exp.dmp tables= indexfile=indx.sql 使用编辑器修改 index.sql ，将建表的 SQL的注释删除，将表的存储参数 initial 和 next 改成合适的值 运行该脚本，建立一个空表 将数据倒入，此时 ignore=y,如何减小倒入表的初始 extent（二）,如何将数

37、据转移到另一个表空间,使用 8i 新特性 alter table X move tablespace Y alter index X rebuild tablespace Y 使用 exp/imp 工具(以基于表的转移为例) exp 表 imp 使用 indexfile 的选项 修改文件中建表语句中的表空间参数 运行该文件建立表 将该用户使用原表空间的限额修改为 0 Imp 数据，并将 ignore 设为 Y,为什么查询会变慢,是否有表目前正被分析或已经分析过 Init.ora文件中的OPTIMIZER_MODE是否被改变 表是否被分析过？分析表是使用estimate还是compute，estimate的percentage参数是多少 Statistics是否改变过 Init.ora文件中的DB_FILE_MULTIBLOCK_READ_COUNT是否被改变 Init.ora文件中的SORT_AREA_SIZE是否被改变 Init.ora文件的其他参数是否被改变,Client 连接 serv