Oracle进阶与提高ppt课件

1、ORACLE 进阶与提高王忠海5/23/2022.主要内容数据库优化RMANRAC(假设有时间的话.数据库优化操作系统设置不当数据库参数设置不当库构造设计不合理运用程序语句不当能够影响数据库性能的缘由有哪些？.AIX5.3中操作系统优化Oracle用户资源限制操作系统中心参数补丁.AIX中一些根本的查看资源的命令如何查看CPU数量 如何查看内存数量如何查看交换空间文件系统运用情况.AIX根本命令：版本信息# oslevel 5.3.0.0# oslevel -r 5300-07# oslevel -s 5300-07-01-0748.AIX根本命令：查看CPU信息# lsdev -Cc pro

2、cessor proc0 Available 00-00 Processor proc2 Available 00-02 Processor proc4 Available 00-04 Processor proc6 Available 00-06 Processor# lsattr -EHl proc0 attribute value description user_settable frequency 2096901000 Processor Speed False smt_enabled true Processor SMT enabled False smt_threads 2 Pr

3、ocessor SMT threads False state enable Processor state False type PowerPC_POWER5 Processor type False.AIX根本命令：查看内存信息bootinfo r16318464# lsdev -Cc memoryL2cache0 Available L2 Cachemem0 Available Memorylsattr -EHl mem0attribute value description user_settablegoodsize 15936 Amount of usable physical me

4、mory in Mbytes Falsesize 15936 Total amount of physical memory in Mbytes False.AIX根本命令：管理交换空间查看交换空间# lsps -aPage Space Physical Volume Volume Group Size %Used Active Auto Type hd6 hdisk0 rootvg 3072MB 1 yes yes lv设置交换空间smit chps交换空间设置建议.文件系统的设置看看下面的输出。您以为最该调整哪个文件系统大小？# df -mFilesystem MB blocks Free

5、 %Used Iused %Iused Mounted on/dev/hd4 256.00 252.12 1% 2353 1% /dev/hd2 4096.00 2613.59 37% 38 6% /usr/dev/hd9var 4096.00 4003.59 3% 882 1% /var/dev/hd3 128.00 120.16 5% 1938 1% /tmp/dev/hd1 1024.00 514.95 50% 3742 4% /home正是tmp文件系统！假设不调整，安装数据库时将无法胜利。.AIX5.3中心参数调整适用范围在AIX5.3-01以上需求调整的参数：lru_file_re

6、page minperm%、maxperm% 和 maxclient% 等等。参考文档： 在 AIX 中运转 Oracle的优化技巧.mht.oracle用户的资源限制修正/etc/security/limits看一个实例：oracle: fsize = 209715100 data = 20971510 stack = 20971510 core = 20971510 rss = 25165824.AIX中Oracle参数设置SGA设置建议 SGA不要超越总内存数*maxperm%.回想：重要的SGA内存参数Shared_pool_sizeDb_cache_sizeDb_keep_cache

7、_sizeLarge_pool_sizeLog_buffer.回想：重要的PGA内存参数PGA_AGGREGATE_TARGET.如何在AIX5.3中将SGA定在内存中？修正系统参数：v_pinshm 修正Oracle参数：LOCK_SGA参考文档： 如何在Aix5.3中将Oracle SGA定在内存中.doc.AIX中其他的需求调整的Oracle参数TIMED_STATISTICSDB_CACHE_ADVICEoptimizer_index_cachingoptimizer_index_cost_adj.大量导入数据前需求做些什么？能否需求备份？估计数据量思索设置为非归档方式思索删除一些索引

8、，导完之后再创建.大量导入数据之后应该做些什么？分析表。DBMS_STATS.GATHER_SCHEMA_STATS思索设置归档备份.哪些要素最影响性能？CPU耗费内存磁盘IO排序.提高数据库性能的方法用更好的硬件！压服用户将就着用优化数据库优化客户端程序.案例：处理CPU 100%占用步骤一：检查cpu信息：vmstat步骤二：定位CPU高耗费进程ps aux|head -1;ps aux|sort +2 -rn|head -5.案例：处理CPU 100%占用续步骤三：定位有问题的语句SELECT /*+ ORDERED */ sql_text FROM v$sqltext a WHERE

9、(a.hash_value, a.address) IN ( SELECT DECODE (sql_hash_value, 0, prev_hash_value, sql_hash_value ), DECODE (sql_hash_value, 0, prev_sql_addr, sql_address) FROM v$session b WHERE b.paddr = (SELECT addr FROM v$process c WHERE c.spid = &pid)ORDER BY piece ASC/.案例：处理CPU 100%占用续步骤四：定位有问题的会话SELECT SID,SER

10、IAL#,USERNAME,TERMINAL FROM v$session b WHERE b.paddr = (SELECT addr FROM v$process c WHERE c.spid = &pid)/.案例：处理CPU 100%占用续步骤五：采取相关行动1.杀掉会话 alter system kill session sid,serial#;2.分析缘由.优化SQL语句，最具挑战的任务为什么要优化？.Oracle SQL调整过程1.确定由高影响力的SQL2.抽取和解释SQL语句3.调整SQL语句.用V$SQLAREA确定影响力高的语句executionsdisk_readsbuf

11、fer_getssorts.哪些工具可以来查看SQL执行方案autotracealter session set sql_trace=true;dbms_system.set_sql_trace_in_sessionexplain planEtc.查看语句执行方案的简一方法Set autotrace onSet autotrace traceonly前提：存在plan_table表。假设不存在，可执行?/rdbms/admin/utlxplan.sql 执行语句相关技巧： set timing on set time on.案例：解读sql语句执行方案SQL select count(*) f

12、rom lpmnt; COUNT(*) 1155Execution PlanPlan hash value: 3530445977| Id | Operation | Name | Rows | Cost (%CPU)| Time | 0 | SELECT STATEMENT | | 1 | 3 (0)| 00:00:01 | 1 | SORT AGGREGATE | | 1 | | | 2 | INDEX FAST FULL SCAN| LPMNT_DBID_IDX | 1102 | 3 (0)| 00:00:01 |Note - dynamic sampling used for this

13、 statementStatistics 509 recursive calls 0 db block gets 190 consistent gets 105 physical reads 0 redo size 412 bytes sent via SQL*Net to client 381 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 6 sorts (memory) 0 sorts (disk) 1 rows processed.set autotrace的局限性必需执行一遍语句.e

14、xplain plan运用方法Explain plan set statement_id=into table for .查看explain plan过的语句的执行方案先做些格式化任务： set pagesize 1000 col operation format a20 col options format a20 col object_name format a20 col position format 999执行语句：select lpad( ,2*(level-1)|operation operation,options,object_name,position from plan_

15、tableCONNECT BY PRIOR id = parent_id andstatement_id=statement_id;.案例：分析winsvr执行的语句工具：alter system set sql_trace=true;(假设想看每个执行步骤地时间信息，要设置timed_statistics参数查看生成的trace文件 在user_dump_dest环境变量所对目录下。 ls -ln用tkprof命令来格式化输出解读输出。.set_sql_trace_in_sessiondbms_system.set_sql_trace_in_session.优化数据库的工具：statspac

16、k经过statspack可以很容易做出Oracle系统性能的全面报告，是Oracle DBA管理Oracle 9i必需掌握的性能调优工具。.安装statspack创建一个statspack公用表空间运转?/rdbms/admin/spcreate.sql假设是windows平台，那么运转：?rdbmsadminspcreate.sql.运用Statspack预备任务规划自动STATSPACK数据搜集。运转?rdbmsadminspauto.sql可以设置自动搜集statspack数据。这个脚本创建了一个作业。为了运转这个作业，要保证job_queue_processes参数大于0，而且要运用s

17、tatspack所属用户来执行。例如下面的脚本设置每1小时进展一次statspack：variable jobno number;variable instno number;begin select instance_number into :instno from v$instance; dbms_job.submit(:jobno, statspack.snap;, trunc(sysdate+1/24,HH), trunc(SYSDATE+1/24,HH), TRUE); commit;end;/.调整statspack参数executions_th：SQL语句执行的数量默许100di

18、sk_reads_th：sql语句执行的磁盘读入数量默许1000parse_calls_th：sql语句执行的解析调用数量默许1000buffer_gets_th：sql语句执行缓冲区获取的数量默许10000经过statspack.modify_statspack_parameter函数可以改动阈值的默许值。阈值存放在stats$statspack_parameter中。改动阈值举例：sqlexec statspack.modify_statspack_parameter(i_buffer_gets_th=20000);.用statspack搜集信息exec statspack.snap; 等

19、待一会或者执行一些语句exec statspack.snap;.生成statspack报告?/rdbms/admin/spreport.解读statspack报告.移除自动执行STATSPACK搜集-先查看当前自动搜集的jobsSQLselect job,log_user,priv_user,last_date,next_date,interval from user_jobs; -移除义务1SQL execute dbms_job.remove(1); .删除统计资料SQLselect max(snap_id) from stats$snapshot;SQL C:oracleora92rdb

20、msadminsptrunc.sql;.发现占资源高的语句后怎样办？改动语句写法最好适当添加索引改动操作系统参数分析表运用大纲.优化案例：经过改动语句写法来提高性能举一个日期查询的例子.优化案例：经过添加索引来提高性能.优化案例：经过分析表来提高性能.RMANRMAN备份的优点：RMAN会检测和报告损坏的数据块不需求将表空间置于热备方式，RMAN就可以备份数据库。因此热备期间生成的重做会减少RMAN自动跟踪更新新的数据文件和表空间，这样就不再需求在脚本中添加新的表空间和数据文件RMAN只备份运用过的数据块，这样RMAN备份通常小于联机脚本备份RMAN可以与第三方介质管理产品一同无缝地任务RMA

21、N支持增量备份可以测试备份而不需求实践复原。.如何配置RMAN将数据库配置为ARCHIVELOG方式 .创建恢复目录第一步，在目录数据库中创建恢复目录所用表空间：SQL create tablespace rman_ts datafile d:oracleoradatarmanrman_ts.dbf size 20M;第二步，在目录数据库中创建RMAN 用户并授权：SQL create user rman identified by rman default tablespace rman_ts temporary tablespace temp quota unlimited on rman

22、_ts;SQL grant recovery_catalog_owner, connect, resource to rman;第三步，在目录数据库中创建恢复目录C:rman catalog rman/rman恢复管理器：版本8.1.6.0.0 - ProductionRMAN-06008：衔接到恢复目录数据库RMAN-06428：未安装恢复目录RMANcreate catalog tablespace rman_ts;RMAN-06431：恢复目录已创建.注册数据库CONNECT TARGET SYS/PWDTESTDB; REGISTER DATABASE.配置RMAN的默许设置CONFI

23、GURE：用于RMAN操作的默许设备类型，如磁盘或者磁带(SBT)执行自动备份和恢复操作时自动分配的通道数配置备份时的排斥战略配置自动备份时给定备份片的最大空间和任何备份集的大小配置备份最优化的默许设置。可以是ON或者OFF.快照控制文件的默许称号能否自动备份控制文件辅助数据文件的默许称号默许的保管战略.显示默许设置举例：SHOW DEFAULT DEVICE TYPE;SHOW MAXSET SIZE;SHOW RETENTION POLICY;SHOW ALL;显示全部配置.CONFGIURE命令举例 下面语句设置默许磁盘类型和并发度CONFIGURE DEVICE TYPE DISK P

24、ARALLELISM 2;下面设置默许备份文件大小。CONFIGURE CHANNEL DEVICE TYPE DISK MAXPIECESIZE 500M;下面语句配置默许存储位置等信息：CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT D:oraclebackupGAXZRMAN%d_%s_%p_%c;下面语句设置CHANNEL 1和CHANNEL 2,用于RAC数据库CONFIGURE CHANNEL 1 DEVICE TYPE disk CONNECT sys/oracleora921 MAXPIECESIZE 10g;CONFIGURE CHANNE

25、L 2 DEVICE TYPE disk CONNECT sys/oracleora922 MAXPIECESIZE 10g;.FORMAT格式%c 备份片的拷贝数 %d 数据库称号 %D 位于该月中的第几天 (DD) %M 位于该年中的第几月 (MM) %F 一个基于DBID独一的称号%n 数据库称号，向右填补到最大八个字符 %u 一个八个字符的称号代表备份集与创建时间 %p 该备份集中的备份片号，从1开场到创建的文件数%U 一个独一的文件名，代表%u_%p_%c %s 备份集的号 %t 备份集时间戳 .备份整个数据库backup database format D:oraclebackup

26、GAXZRMANora_d%d_s%s_s%p_f%t filesperset 4 plus archivelog delete input;.增量备份差别(Differential)备份是默许的增量备份类型，差别备份会备份上一次进展的同级或者低级备份以来一切变化的数据块，而累积(cumulative)备份，那么备份上次低级备份以来一切的块。例如，星期一进展了一次2级备份，星期二进展了一次3级备份，假设星期四进展3级差别增量备份，那么只备份上次3级备份以来变化过的数据块；假设进展累积3级备份，那么就会备份上次2级备份以来变化的数据块。.案例：增量备份首先要做一个0级备份backup INCRE

27、MENTAL LEVEL 0 database plus archivelog delete input;做一个1级备份：backup INCREMENTAL LEVEL 1 database plus archivelog delete input;做一个1级累计备份：backup INCREMENTAL LEVEL 1 CUMULATIVE DATABASE database plus archivelog delete input;.备份特定内容备份表空间备份数据文件backup datafile 7 format /backup/rman/ora_d%d_s%s_s%p_f%t;备份归

28、档日志backup archivelog all delete input;.RMAN报告LISTRMAN的list命令是一种在数据库控制文件或者恢复目录中查询备份的历史信息的方法。列表提供了一组信息，可以提供各种备份的信息，如对应物、备份集、归档日志备份、控制文件备份等等。REPORTRMAN的report命令被用于判别数据库的当前可恢复形状和提供数据库备份的特定信息、报告最近没有备份的数据文件等信息。.常用LIST命令列出一切备份：List backupset;列出一切备份简要信息：List backupset summary;列出指定备份集备份信息List backupset bs#;列

29、出过期的备份：list expired backup;列出指定表空间的备份信息：List backup of tablespace users;列出一切已备份的归档：list backup of archivelog all summary列出一切需求备份的归档：List archivelog all;.常用REPORT命令报告最近10天没有备份的数据文件report need backup days=10;报告按照默许战略需求备份的文件Report need backup;按照指定战略报告需求备份的文件 report need backup redundancy=2;报告数据库信息：Repo

30、rt schema;报告过期的备份Report obsolete;.备份集的维护删除过期备份：delete obsolete;用delete noprompt obsolete可无需提示。删除指定的文件集：delete backupset bs#;删除一切备份：delete backup;验证备份集，假设备份集不复存在，将被标志为expired：crosscheck backup;删除expired备份集：Delete expired backup;.恢复数据库.恢复案例1：丧失SYSTEM表空间restore datafile 1;recover database;alter databas

31、e open;.恢复案例2：丧失参数文件1.编辑一个init.ora，内容包括：db_name=GAXZinstance_name=GAXZcontrol_files=D:oracleproduct10.2.0oradataGAXZCONTROL01.CTLdb_block_size=8192shared_pool_size=1048576002.RMAN衔接到目的数据库。3. startup nomount pfile=D:RMANINIT.ORA;4.restore spfile;5.Shutdown immediate;6.startup.恢复案例3：恢复控制文件Startup nomo

32、unt;Restore controlfile;Recover database;Alter database open resetlogs;留意，在Oracle9i中，用resetlogs选项翻开数据库后，备份就不再有效了，需求重新备份。.恢复案例4：恢复误删除的表数据根本模拟过程：1.先备份数据2.创建一个TEST数据表。create table test as select owner,table_name,column_name,data_type from all_tab_columns;3.查看一下时间。 SELECT TO_CHAR(SYSDATE,YYYYMMDDHH24MISS) FROM DUAL;4.删除表中的数据。Truncate table test;5.恢复文件。Restore database;6.recover到指定的时间点recover database until time to_date(20211127