Oracle_数据库_DBA_基本技能(1).ppt

Oracle DBA 基本技能/常用技巧,张华Oracle支持服务部2007.02,主要内容,Oracle 数据库的组成结构管理临时表空间加快索引的创建使用 SPFILE 作为初始化参数文件()使用 Profile 加强数据库安全(口令的复杂程度)监控系统的锁资源争用(共享资源)SQL语句调优,Oracle 数据库的基本结构,Oracle 服务器(C/S),An Oracle server(关系型数据库)Is a database management system that provides an open,comprehensive,integrated approach to information management 实例数据库Consists of an Oracle instance and an Oracle database,Oracle 服务器的主要组件,Instance,SGA,Redo LogBuffer,Shared Pool,Data DictionaryCache,LibraryCache,DBWR,SMON,PMON,CKPT,LGWR,Others,Userprocess,Serverprocess,PGA,Control files(组成文件),Datafiles,Database,DatabaseBuffer Cache,Redo Log files,Java Pool,Large Pool,Parameterfile,Archived Log files归档日志文件,数据库,关键进程,Oracle 实例,An Oracle instance:SGA(内存的结构)Is a means to access an Oracle database(连接数据库)Always opens one and only one databaseConsists of memory and background process structures,Background process structures,Memory structures,Instance,SGA,Redo LogBuffer,Shared Pool,Data DictionaryCache,LibraryCache,DBWR,SMON,PMON进程监控,CKPT,LGWR,Others,DatabaseBuffer Cache,Java Pool,Large Pool,Oracle Database,An Oracle database:Is a collection of data that is treated as a unitConsists of three file types,建立一个数据库连接,Connecting to an Oracle instance:Establishing a user connectionCreating a session,Session created,Database user,Userprocess,Serverprocess,Connection established,用户进程,A program that requests interaction with the Oracle serverMust first establish a connectionDoes not interact directly with the Oracle server,Database user,Serverprocess,Userprocess,Connectionestablished,服务器进程,A program that directly interacts with the Oracle serverFulfills calls generated and returns resultsCan be dedicated(独占)or shared server(共享),Connection established,Session created,Database user,Userprocess,Serverprocess,服务器进程,sdjf_dom2_srv4:/oracle ps-ef|grep oracle oracle 2904150 2379866 0 Jan 27-0:06 oraclejzjf2(实例名)(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)oracle 3866722 1 0 08:50:21-0:00 oraclejzjf1(LOCAL=NO)oracle 3870846 1 0 11:34:20-0:00 oraclejzjf1(LOCAL=NO)oracle 3895388 1 0 02:37:35-0:00 oraclejzjf1(LOCAL=NO)oracle 3903652 1 0 20:14:28-1:28 oraclejzjf1(LOCAL=NO),进程的所有者通常为 oracle 用户进程的名称为：oracleSID(实例的名称)可以被安全的 kill,服务器进程,sdjf_dom2_srv4:/oracle topasName PID CPU%PgSp Owneroracle 561246 36.6 7.1 oracleoracle 2711644 6.7 2.3 rate oracle 3358774 2.0 7.4 oracleoracle 454698 0.1 16.1 oracle-执行查询select s.sid,s.status,s.terminal,s.machine,s.program from v$process p,v$session s where s.paddr=p.addr and p.spid=561246;(返回Session ID,状态),察看 Top CPU 进程找到该进程的 SID,服务器进程,-察看会话的属性select s.sid,s.status,s.program,sa.sql_text,sa.hash_value from v$session s,v$sqlarea sa where s.sql_address=sa.address and s.sql_hash_value=sa.hash_valueand s.sid=1561;SIDSTATUSTERMINALMACHINEPROGRAM-ACTIVE菏泽计费WORKGROUP菏泽计费plsqldev.exe-察看会话正在执行的 SQLselect sa.sql_textfrom v$session s,v$sqlarea sa where s.sql_address=sa.address and s.sql_hash_value=sa.hash_value and s.sid=1488;,根据 SID 察看会话的属性察看会话正在执行的 SQL,服务器进程,-杀死服务器进程ps-ef|grep-v grep|grep LOCAL=NO|awk print$2|xargs kill-9,杀死服务器进程，以加快实例的关闭,进程列出来,后台进程,Maintains and enforces relationships between physical and memory structures:Mandatory background processes:DBWnPMONCKPTLGWRSMONOptional background processes:ARCnLMDn QMNnCJQ0LMON RECODnnnLMS SnnnLCKnPnnn,后台进程,sdjf_dom2_srv4:/oracle ps-ef|grep ora_ oracle 466988 1 0 Jan 06-11:54 ora_smon_jzjf2 oracle 487480 1 0 Jan 06-15:08 ora_lmon_jzjf2 oracle 602182 1 0 Jan 06-14:54 ora_dbw0_jzjf2 oracle 704594 1 0 Jan 06-19:20 ora_pmon_jzjf2 oracle 737408 1 0 Jan 06-23:33 ora_ckpt_jzjf2,进程的所有者通常为 oracle 用户进程的名称为：ora_后台进程类型_SID被 kill 后可能会导致实例终止,管理临时表空间,临时表空间中的空间管理,OPS/RAC 中的每个实例仅创建一个临时段，实例中的所有进程将共享该排序段实例启动后第一个利用磁盘执行排序操作的进程创建临时段；实例关闭后，系统将清空临时表空间临时空间的分配以 extent 为单位进行，并通过 SGA 中的Sort Extent Pool(内存结构)对这些 extents 进行管理。每个实例会维护一个属于自己的 SEP，并通过视图v$sort_segment 向用户开放,临时表空间中的空间管理,临时表空间已经 100%满了遇到了 ORA-01652“unable to extend temp segment by%s in tablespace%s”错误 查询 v$sort_segment(当前临时段用了多少,还多少没用)查询 v$tempseg_usage(当前临时段的用户),临时表空间中的空间管理,select s.tablespace_name,s.total_blocks,s.used_blocks from v$sort_segment s;TABLESPACE_NAMETOTAL_BLOCKSUSED_BLOCKS-TEMP4420864(一共)15104(用了)select s.sid,s.program,s.machine,t.extents,t.blocks from v$tempseg_usage t,v$session s where t.session_addr=s.saddr;SIDPROGRAMMACHINEEXTENTSBLOCKS-1247plsqldev.exeJSJZXZXY3384(用了数据块),临时表空间中的空间管理,在 OPS/RAC 中，当一个实例需要临时空间并且 SEP 中没有可用的 extent 时：或者增长临时段(例一个实例80%,一个20%,20%的进行大操作,将从80%的实例中在分配出来给20%)或者系统自动从另一个实例 re-assign extent增大参数 PGA_AGGREGAGE_TARGET 参数值以尽量避免磁盘排序,select*from v$sysstat where name like%sort%;STATISTIC#NAMECLASSVALUESTAT_ID-312sorts(memory)64797009872091983730313sorts(disk)6469002533123502314sorts(rows)64603954468743757672740,加快索引的创建,加快索引的创建,alter session set db_file_multiblock_read_count=128;alter index bill.ind_charge_id rebuild parallel 5 nologging;create index parallel 5 nologging;,创建超大分区表的本地索引,create index idx_subs_score_detail_attr_sid on subs_score_detail_attr(subsoid,region,)local tablespace inx unusable;,首先创建 UNUSABLE 状态的索引然后利用多个进程，rebuild 失效状态的索引分区,能够利用 nologging 参数的操作,CREATE TABLE AS SELECTINSERT TABLE SELECTCREATE INDEXREBULD INDEX or INDEX PARTITIONMOVE TABLE or TABLE PARTITIONSQL*Loader direct-path load,查找失效的索引,-查找非分区索引select owner,index_name,index_type,table_owner,table_namefrom dba_indexeswhere partitioned=NO and status=UNUSABLE;OWNERINDEX_NAMEINDEX_TYPETABLE_OWNER TABLE_NAME-BILLIND_CHARGE_IDNORMALBILLT_RC_INSTANCEBILLIND_CHARGE_ID0NORMALBILLT_RC_INSTANCE,-查找分区索引select i.index_owner,i.index_name,i.partition_namefrom dba_ind_partitions i where status=UNUSABLE;,使用 SPFILE 作为初始化参数文件,初始化参数文件,From Oracle9i onwards,there are two types of initialization parameter file used:Static parameter file,PFILE Persistent server parameter file,SPFILE,spfiledb01.ora,初始化参数文件,CONNECT/AS SYSDBASTARTUP,Oracle Instance,SGA,Redo LogBuffer,Shared Pool,Data DictionaryCache,LibraryCache,DBW0,SMON,PMON,CKPT,LGWR,Others,DatabaseBuffer Cache,Java Pool,Large Pool,PFILEinitSID.ora,文本文件通过文本编辑器进行修改采用手工的方式进行修改所作的修改在实例下次启动时生效仅在实例启动时打开确省的存取目录是$ORACLE_HOME/dbs,SPFILEspfileSID.ora,二进制文件通过 Oracle 服务器进行修改总是存放在服务器上Ability to make changes persistent across shut down and start up,创建 SPFILE,通过 PFILE 创建 SPFILEwhereSPFILE-NAME:SPFILE to be createdPFILE-NAME:PFILE creating the SPFILE可以在实例启动之前或者之后执行,CREATE SPFILE=$ORACLE_HOME/dbs/spfileDBA01.oraFROM PFILE=$ORACLE_HOME/dbs/initDBA01.ora;,创建 SPFILE,SQL sqlplus/as sysdbaConnected to an idle instance.SQL create pfile from spfile;create pfile from spfile*ERROR at line 1:ORA-27037:unable to obtain file statusIBM AIX RISC System/6000 Error:2:No such file or directoryAdditional information:3SQL create pfile from spfile=/dev/rjf_spfile;File created.,不指定文件名，系统将使用缺省值，或者 spfile 参数值,SPFILE 内容示例,*.background_dump_dest=/home/dba01/ADMIN/BDUMP*.control_files=/home/dba01/ORADATA/u01/ctrl01.ctl*.core_dump_dest=/home/dba01/ADMIN/CDUMP*.db_block_size=8192*.db_name=jzjfjzjf1.thread=1jzjf2.thread=2jzjf1.undo_tablespace=UNDOTBS1jzjf2.undo_tablespace=UNDOTBS2,检查当前是否使用了 SPFILE,察看初始化参数 spfile,sqlplus/as sysdbaSQL show parameter spfileNAME TYPE VALUE-spfile string/dev/rjf_spfile,修改 SPFILE 中的参数,修改参数值指明是临时修改还是永久保存重设参数值,ALTER SYSTEM SET undo_tablespace=UNDO2;,ALTER SYSTEM SET undo_tablespace=UNDO2 SCOPE=BOTH;ALTER SYSTEM SET undo_retention=3600 SCOPE=memory SID=jzjf1;,ALTER SYSTEM RESET undo_suppress_errors SCOPE=BOTH SID=*;,STARTUP 命令,处理顺序:spfileSID.orainitSID.oraSpecified PFILE can override precedence.PFILE 中可以指明使用 SPFILE,STARTUP PFILE=$ORACLE_HOME/dbs/initDBA1.ora,SPFILE=/database/startup/spfileDBA1.ora,使用 Profile 加强数据库安全,使用 Profile 加强数据库安全,A profile is a named set of password and resource limits.通过 CREATE USER 或者 ALTER USER command 将 profile 赋予用户概要文件可以被启用或者禁用Profiles can relate to the DEFAULT profile.,User,Password expiration and aging,Password verification,Setting up profiles,Password 管理,Set up password management by using profiles and assigning them to users.Lock,unlock,and expire accounts using the CREATE USER or ALTER USER command.Password limits are always enforced.To enable password management,run the utlpwdmg.sql script as the user SYS.,启用 Password 管理,账号锁定,Password 过期,Password 历史,Parameter,Number of days before a password can be reusedMaximum number of changes required before a password can be reused,PASSWORD_REUSE_TIMEPASSWORD_REUSE_MAX,Description,Password 校验,用户定义的 Password 函数,This function must be created in the SYS schema and must have the following specification:,function_name(userid_parameter IN VARCHAR2(30),password_parameter IN VARCHAR2(30),old_password_parameter IN VARCHAR2(30)RETURN BOOLEAN,Password 校验函数 VERIFY_FUNCTION,Minimum length is four characters.Password should not be equal to username.Password should have at least one alphabetic,one numeric,and one special character.Password should differ from the previous password by at least three letters.,CREATE PROFILE grace_5 LIMIT FAILED_LOGIN_ATTEMPTS 3 PASSWORD_LOCK_TIME UNLIMITED PASSWORD_LIFE_TIME 30 PASSWORD_REUSE_TIME 30 PASSWORD_VERIFY_FUNCTION verify_function PASSWORD_GRACE_TIME 5;,设置 Password 选项：创建 Profile,ALTER PROFILE default LIMITFAILED_LOGIN_ATTEMPTS 3PASSWORD_LIFE_TIME 60PASSWORD_GRACE_TIME 10;,设置 Password 选项：修改 Profile,Use ALTER PROFILE to change password limits.,DROP PROFILE developer_prof;,DROP PROFILE developer_prof CASCADE;,删除 Profile,Drop the profile using DROP PROFILE command.DEFAULT profile cannot be dropped.CASCADE revokes the profile from the user to whom it was assigned.,监控锁资源的争用,锁机制,锁机制的根本目的是：使对共享资源的访问串行化，比如行记录、数据库对象的定义等。Automatic managementExclusive and Share lock modesLocks held until commit or rollback operationsare performed,两种类型的锁,DML or data locks:Table-level locksRow-level locksDDL or dictionary locks,(TM),(TX),TM 锁和 TX 锁,A DML transaction gets at least two locks:A shared table lockAn exclusive row lock,select*from my_test for update;select*from v$lock where sid=1216;SIDTYPELMODEREQUESTCTIMEBLOCK-1216TX6080821216TM308082,Enqueue 机制,The enqueue mechanism keeps track of:Users waiting for locksThe requested lock modeThe order in which users requested the lock,DDL 锁,Exclusive DDL locks are required for:DROP TABLE statementsALTER TABLE statements(The lock is released when the DDL statement completes.)Shared DDL locks are required for:CREATE PROCEDURE statementsAUDIT statements(The lock is released when the DDL parse completes.),锁争用的常见原因,Uncommitted changesBad application design,监控锁活动的工具,Transaction 1,UPDATE employeesSET salary=salary x 1.1;,Transaction 2,Transaction 3,v$lockv$locked_objectdba_waitersdba_blockers,UPDATE employeesSET salary=salary x 1.1WHERE empno=1000;,UPDATE employeesSET salary=salary x 1.1WHERE empno=2000;,select*from v$session s where s.lockwait is not null;select*from v$session_wait w where w.event like enq%;select*from dba_waiters;WAITING_SESSIONHOLDING_SESSIONLOCK_TYPEMODE_HELDMODE_REQUESTED-13201216TransactionExclusiveExclusive,Waiting Lock 的会话,使用下面的语句察看是否有会话在等待锁资源：,select*from v$lock l where l.block=1;SIDTYPELMODEREQUESTCTIMEBLOCK-1216TX604561-察看 blocking 会话是否在等待select*from v$session_wait where sid=1216;SIDSEQ#EVENT-12161155SQL*Net message from client,Blocking 会话,察看 Blocking 会话的信息：,select/*+rule*/l.session_id,l.os_user_name,l.locked_mode,o.owner,o.object_name from v$locked_object l,dba_objects o where l.object_id=o.object_id;SESSION_IDOS_USER_NAMELOCKED_MODEOBJECT_NAME-1251usr_zb3T_ST_MODULE_MONITOR1373hzzcgx3T_USAGE_TOTAL_2007011373hzzcgx3T_USAGE_TOTAL_200701,当前被锁定的对象,察看当前有哪些对象被锁定：,SQL 语句调优,优化 SQL 的思路,优化 SQL 要从以下三个方面入手：访问路径：Access paths are ways in which data is retrieved from the database.联结方法Joins are statements that retrieve data from more than one table.联结次序优化 SQL 要的手段：使用 hint,SQL 的执行计划,一个 SQL 语句执行计划的示例：,常用的访问路径,Full Table Scan/*+full(a)*/Rowid ScanIndex Unique ScanIndex Range Scan/*+index(a idx_name)*/Index Range Scans DescendingIndex Full ScanIndex Fast Full Scan/*+index_ffs(a idx_name)*/,常用的联结方法,Nested Loop JoinHash JoinSort Merge Join,Nested Loop Join,Nested Loop Join 的执行步骤：优化器确定驱动表，或者叫 outter table另一个表作为 innter table对于 outter table 中的每一行记录，Oracle 用其作为条件访问 innter table。在执行计划中：NESTED LOOPS outer_loop inner_loop效率最高的条件：两个表的数据量较小；或者驱动表的数据量较小并且联接条件可以高效的访问 inner table,Nested Loop Join 示例,SELECT e.employee_id,e.salary,j.job_title FROM employees e,jobs j WHERE e.employee_id 103 AND e.job_id=j.job_id;|2|NESTED LOOPS|3|141|7(15)|*3|TABLE ACCESS FULL|EMPLOYEES|3|60|4(25)|4|TABLE ACCESS BY INDEX ROWID|JOBS|19|513|2(50)|*5|INDEX UNIQUE SCAN|JOB_ID_PK|1|,用来指定 Nested Loop Join 的 Hint,/*+use_nl(a b)*/,Hash Join,Hash joins are used for joining large data sets.不涉及到关联次序/*+use_hash(a b)*/,SELECT o.customer_id,l.unit_price*l.quantity FROM orders o,order_items l WHERE l.order_id=o.order_id;-|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|-|0|SELECT STATEMENT|665|13300|8(25)|*1|HASH JOIN|665|13300|8(25)|2|TABLE ACCESS FULL|ORDERS|105|840|4(25)|3|TABLE ACCESS FULL|ORDER_ITEMS|665|7980|4(25)|-,Sort Merge Join,Hash joins generally perform better than sort merge joins.On the other hand,sort merge joins can perform better than hash joins if both of the following conditions exist:The row sources are sorted already.A sort operation does not have to be done./*+use_merge(a b)*/,MajpjMVcyzj21HLfrvy96dv02lPPfYgxUS7IYmZkyEmZ0kGeYZS3bpLCkYH1lt4EK7CxmUX3ijoYSOer7ZuaVWYgz4EpZrUirVpMzzvNtf1XZw5oswSXOtFaejnOcmfE1lZgnN1RSXg8wLCG8CVQ3XPJMvodPFWcpiYJgZazNSEPNIaklYSu7qSd1UpaxmZDlpN9zW7kljfsLCLi26Yv109ffbnDH8LbUN1G6ACURQ39eG12KHL9tXsZ1jzgoCK8g1kuNOh5eFvcmVT5ZYVQt9zk3rp3qLnf02FovEXxVRxjCcFRNppiJljNiOuk6fONnyX7fyGg7sXZ49BmCN5oy9VesHpKzdjTKwjrkCEQCFDehVmGax3lrOEbw63VscA3YSijtUKoCyiLzAlVRp7l4QgPNHxvJFFDyjUVN3oHlMah0XBd4uTbkfPIhHtw0evPmYOrdhEDoPwvYhzlGplU1AU9mpyiCXH8gpPCBRYjq77VcnbXumNE1yGfyTsbSj89J63kRTKDkKUg3mdS5sJ4X5cQ8dK7oW9IkScssECQdz2O9UTlpRjAFPChjhLdzopQzwxQf8ozdzOhogwAooXpUF83BX4C3jRgjDJiiXEUDMaNz4vQ4n164vspddHvOIVuBBdMA4xp1YhiHk0vOJ8TL1BxogzVlMpmod6ianYGmksQq6NWCEd56hZF4wfaNyZcrGfNxnPiG6ZAxSkfmhJAKtNmCqbRmppeXp8inz4eq3HkWCMSORyMMX522xpHG6basNr6KQfbZsFbHjzyNlJrruLolKFcC84dqfijBO5Dy2NaBcNEBPgQrT12PgpcKx2or2YChN5DPjs80zzdtdAdTKuW4uVv9bbZu3K2SZ2aEhTlIC1UqrIWibkzwHh6p8gLv26zr01mJybfOzFc4T7kQH1IpPwOzMDnAKPLsLrznXGjFNIA9bSWWms6ibKZwQIKrMzalwbFrQJvOP1rPH8rx2KkyYqrtQk5VRwM1HSX,