Oracle基本命令.docVIP

1. 活动状态检查通过查询基本视图，确认数据库和实例处于正常运行的状态，可以对外提供数据服务。回复“D1”查看此步骤下全部命令。1.1 实例状态select instance_name,status from v$instance;查询返回实例名称、状态。1.2 会话信息select sessions_current,sessions_highwater from v$license;实例当前会话数据和启动最高连接会话数量select inst_id,username,count(*) from gv$sessiongroup by inst_id,username;查询数据库以用户分组连接数1.3 参数检查select value from v$parameter where name=open_cursors;查询给定参数的设置值，示例参数缺省值为300，通常中等规模数据库推荐设置为1000。1.4 参数修改alter system set undo_retention=3600 comment=default 900 sid=ora11gdg scope=both;修改给定的初始化参数，RAC环境需要注意SID参数。1.5 隐含参数select a.ksppinm name, b.ksppstvl value, a.ksppdesc description from x$ksppi a, x$ksppcv b where a.indx = b.indx and a.ksppinm like _optimizer_use_feedback;alter system set _optimizer_use_feedback=false scope=spfile;应对特殊问题，有时需设置以下划线开头的隐含参数。示例关闭了11.2中引入的cardinality feedback 基数反馈特性。1.6 Cardinality FeedbackCardinality Feedback是11GR2出现的一个新特性，它的出现是为了帮助ORACLE优化器依据更精准的Cardinality产生出更加优秀的执行计划。Cardinality基数的评估准确与否，对于优化器非常重要，直接影响到后续的JOIN COST等重要的成本计算评估，如果评估不当会造成CBO选择不当的执行计划。此技术对于仅执行一次SQL无效，在SQL第一次执行时，记录存储实际的Cardinality和评估的Cardinality之间的差异，如果差异较大，在第二次执行时，优化器会依据实际的Cardinality重新决策生成执行计划，但是需要注意的是，当使用更准确的Cardinality重新生成执行计划时，不一定生成的执行计划与第一次时不一样，完全有可能是相同的。这个技术的出现是由于优化器在一些情况下不能很好的去计算Cardinality的数值，比如：统计信息缺失或陈旧、多谓词、直方图缺失或者缺少直方图等等，在这些情况下，CardinalityFeedback可能会帮上忙。我们来看下Cardinality Feedback是如何发挥作用的。注意使用普通用户来测试Cardinality Feedback，sys用户被默认禁用该特性select product_namefrom order_items o, product_information pwhere o.unit_price = 15 and quantity 1and duct_id = duct_id; 在order_items表上有2个谓词，o.unit_price = 15 and quantity 1，由于优化器对于联合谓词评估不准，导致ORACLE优化器认为经过2个联合谓词的过滤，order_items表只返回一条记录，进而优化器选择了nest loop的执行计划。 如果Cardinality Feedback被开启，在SQL第一次执行结束后，ORACLE会把实际的Cardinality与评估的Cardinality做比较，如果差异较大，这些实际的Cardinality会被存储以期待SQL再次被执行时这些Cardinality被优化器所使用。我们看看第二次执行的情况： 发现ORACLE 优化器依据第一次执行所记录的Cardinality，重新评估执行计划，在第二次执行时，已经选择了HASH JOIN的执行计划，在执行计划的NOTE部分也看到了cardinality feedback used for this statement字样。11GR2针对此特性，也专门在V$SQL_SHARED_CURSOR中增加了USE_FEEDBACK_STATS列来记录SQL是否使用了Cardinality Feedback。如目前我所负责的一个数据库中，有521个SQL都使用到了Cardinality Feedback。select count(*) FROM V$SQL_SHARED_CURSOR where USE_FEEDBACK_STATS =Y; COUNT(*)- 521通过10053 trace两次执行过程，可以很容易发现Cardinality Feedback是如何发挥作用的。在第一次执行时，存储实际的Cardinality信息，在第二次执行时，通过hintOPT_ESTIMATE (TABLE “ORDER_ITEMS” ROWS=13.000000 )的方式来告诉优化器真实的基数信息。优化器根据真实的基数信息重新评估产生执行计划。Cardinality Feedback的开启和关闭通过一个隐含参数_optimizer_use_feedback来控制。此参数可以在session 和 system级别进行设置。SQL alter session set “_optimizer_use_feedback”=false;会话已更改。system级别SQL alter system set “_optimizer_use_feedback”=false;系统已更改。还可以在SQL语句级进行开启和关闭。select /*+ opt_param(_optimizer_use_feedback false) */ count(*) from test;select /*+ opt_param(_optimizer_use_feedback true)*/ count(*) from test;Cardinality feedback的信息将存放在cursor中，当Cursor一旦被aged out则会丢失。如果已经采用了Cardinality Feedback的SQL被刷出共享池，那么在SQL下一次执行的时候，还是会依据表的原始的统计信息来生成执行计划，在第二次执行的时候参考第一次执行时的Cardinality 重新生成执行计划，如此反复。因此，如果发现一个SQL的执行性能经常反复，这个SQL可能是使用了Cardinality Feedback，有必要搞清楚是哪里出现了问题，比如是不是由于统计信息陈旧导致优化器评估了错误的基数进而导致选择了错误的驱动表。如下一个例子，a表上status=ccc的值有15355个，由于统计信息陈旧导致了优化器评估只有1个符合条件。select count(*) from a a1,a a2 where a1.object_id=a2.object_id anda1.object_name=c and a1.object_typeO and a2.status=cccPredicate Information (identified by operation id):- 4 - filter(A2.STATUS=ccc) 5 - access(A1.OBJECT_ID=A2.OBJECT_ID) 6 - filter(A1.OBJECT_NAME=c AND A1.OBJECT_TYPEO)第二次执行时，优化器已经使用了Cardinality Feedback，已经选用了正确的驱动表：Predicate Information (identified by operation id):- 4 - filter(A1.OBJECT_NAME=c AND A1.OBJECT_TYPEO) 5 - access(A1.OBJECT_ID=A2.OBJECT_ID) 6 - filter(A2.STATUS=ccc)Note- - cardinality feedback used for this statement把SQL刷出共享池，看看再一次执行，执行计划会是什么：select address,hash_value,executions,parse_calls from v$sqlarea where sql_id=7s9av17u9k6f5;我们看到执行计划又返回到了第一次执行时的状态，选用了错误的驱动表，再次执行，发现又使用了cardinality feedback：cardinality feedback技术给优化器提供了更加精准的cardinality来帮助优化器更容易产生优秀的执行计划，ORACLE里也有其他的一些技术来提升评估cardinality的精准性，如：dynamic sampling，多列联合的统计信息收集等，如果采用了这些技术的执行计划，cardinality feedback技术将不再被采用。已知的cardinatilty feedback问题Fixed in Note 8608703.8 Bug 8608703 - SubOptimal Execution Plan created by Cardinality FeedbackNote 9465425.8 Bug 9465425 - New cursors generated after cardinality feedback Note 9342979.8 Bug 9342979 Suboptimal plan change with cardinatilty feedbackFixed in 12g Note 8521689.8 Bug 8521689 - SubOptimal execution plan on second execution of GROUP BY query Note 8729064.8 Bug 8729064 Adaptive cursor sharing fails to share / USE_FEEDBACK_STATS not set推荐阅读：【11g新特性】Cardinality Feedback基数反馈/archives/11g-new-feature-cardinality-feedback.html/optimizer/entry/cardinality_feedback/t_cardinality_feedback.htm/2011/07/cardinality-feedback//2012/07/cardinality-feedback-nasty-feature-in.html1.7 连接异常当连接数据库出现异常，需要检测包括网络连通性，监听器状态等信息。1.7.1 连通性tnsping tns_name通过tnsping工具测试配置的服务名称，观察网络是否连通以及响应时间。1.7.2 监听器lsnrctl status listenerlsnrctl status listener_scan1lsnrctl service在数据库服务器上，通过lsnrctl工具检查监听状态和服务信息。1.7.3 监听日志检查adrcishow alert在服务器上，可以通过adrci工具显示各类告警文件，检查监听器日志，可以诊断监听问题。1.8 实例异常当连接数据库实例出现缓慢、挂起等现象，需要进行诊断和分析，甚至可能需要重新启动数据库实例。回复“实例异常”查看此步骤下全部命令。1.8.1 信息采集sqlplus -prelim / as sysdbaSQL oradebug setmypidStatement processed.SQL oradebug unlimitStatement processed.SQL oradebug hanganalyze 3Hang Analysis in /u01/app/oracle/diag/rdbms/ora11g/ora11gdg/trace/ora11gdg_ora_4340.trcSQL oradebug dump systemstate 266Statement processed.间隔一定时间，如20秒，执行下一次数据库采样。SQL oradebug hanganalyze 3Hang Analysis in /u01/app/oracle/diag/rdbms/ora11g/ora11gdg/trace/ora11gdg_ora_4340.trcSQL oradebug dump systemstate 266示范命令，通过采集系统的Hang信息、系统状态信息等，可以分析系统挂起的原因，间隔采样，可以用于对比变化，辅助分析。1.8.2 跟踪alter session set events 10046 trace name context forever,level 12shutdown immediate;startup mount;alter session set events 10046 trace name context forever,level 12alter database open;可以在数据库关闭、启动时遇到阻塞、挂起时，通过示范命令进行跟踪，获取跟踪文件进行分析。1.8.3 安全停库alter system checkpoint;alter system archive log current;shutdown immediate;如果数据库出现异常需要重新启动，可以通过示范命令执行检查点和归档命令，然后尝试以立即方式关闭数据库。1.8.4 强制停库shutdown abort；startup nomount;alter database mount;alter database open;如果立即方式不能顺利关闭数据库，强制的关闭方式为abort。2. 日志信息检查检查数据库各类日志信息，确认数据库实例、集群等是否出现错误和告警，如存在问题，则需要进一步分析和应对。回复“D2”查看此步骤下全部命令。2.1 日志2.1.1 告警日志$ORACLE_BASE/diag/rdbms/$ORACLE_SID/trace/alert_$ORACLE_SID.logshow parameter background_dump_dest根据示例找到告警日志、检查实例是否存在ORA-错误提示灯。2.1.2 集群日志$GRID_HOME/log/alert.log$GRID_HOME/log/(crsd cssd evmd ohasd)/在相应路径找到RAC集群日志，检查是否存在错误提示信息等。2.1.3 ASM日志$GRID_HOME/diag/asm/+asm/trace/alert_.log在相应路径找到ASM日志，检查是否存在错误提示信息等。2.2 trace文件检查select value from v$diag_info where name=Default Trace File;show parameter user_dump_dest;获取会话/全局转储位置，诊断时需要相应文件内容。2.3 监听日志检查回复“监听”监听器lsnrctl status listenerlsnrctl status listener_scan1lsnrctl service2.4 集群状态crsctl status resource -t确认资源状态显示在线2.5 errorstack分析当遇到ORA-错误，而数据库的输出信息不足时，可以采用errorstack进行跟踪，采集更详细的转储信息。回复“errorstack”查看详细命令。alter system set events=600 trace name errorstack forever,level 10;alter system set events=600 trace name errorstack off;示例显示了对ORA-600 错误设置跟踪，并关闭。3. 重做日志维护Oracle REDO日志是数据库的核心组件，检查其状态、维护其成员、监控其归档、审核其性能、是DBA的重要工作。回复“D3”查看详细命令。3.1 Redo 组和成员select group#,sequence#,archived,status from v$log;查询日志组号、序号、是否归档完成和状态信息。如多组日志显示ACTIVE状态，则可能说明数据库存在IO方面的性能问题。select group#,memeber from v$logfile;查看日志组和成员信息。3.2 Redo 维护alter database add logfile group 10(/oracle/dbs/log1c.rdo) size 500m;alter database add logfile member/oracle/dbs/log2c.rdo to group 10;在日志切换频繁时，可能需要增加日志组或者加大日志大小。alter database drop logfile group 10;alter database drop logfile member /oracle/dbs/redo03.log删除指定日志组或日志成员，注意只能对inactive状态的日志执行删除操作。3.3 归档开启archive log list;检查数据库是否处于归档模式。startup mount;alter database archivelog;在mount状态下改变归档模式，启动归档模式之后，务必制定备份归档的日常策略，防止磁盘空间被耗尽。3.4 执行归档alter system switch logfile;切换日志组，开始写入下一个日志组。alter system archive log current;对当前日志组执行归档，切换到下一个日志组。在RAC模式下，会对所有实例执行归档，Thread参数执行归档实例。3.5 调整归档路径alter system set log_archive_dest_2=location=&path sid=&sid;如果数据库因归档耗尽空间，可以指定另外的归档路径，以尽快归档日志，恢复数据库运行。4. 空间信息检查确保数据存储空间可用，定期检查表空间余量，进行表空间和文件维护。回复“D4”查询详细命令。4.1 空间使用select * from sys.sm$ts_used;查看数据库表空间的使用信息。select * from sys.sm$ts_free;查看数据库表空间的剩余空间。4.2 文件信息select tablespace_name,file_name from dba_data_files;查看数据库表空间的数据文件信息。4.3 文件维护alter database datafile &path resize 900m;alter tablespace &tbs_name add datafile &path size 900m;对数据库的表空间容量进行扩容。5. 锁 闩信息查询Lock/Latch 是数据库控制并发的核心手段，检查相关信息可以监控数据库的事务和运行状况。回复“D5”查看详细命令。5.1 锁信息select sid,type,lmode,ctime,block,from v$lock where type not in (MR,AE);查看锁会话ID，类型，持有时间等。注意：如果block1，可能意味着阻塞了其他会话。5.2 锁故障排查在数据库出现锁竞争/阻塞时，需要排查和处理锁定，必要时通过kill阻塞进程消除锁定。5.2.1 查询阻塞会话select sid,sql_id,status,blocking_session from v$session where sid in (select session_id from v$locked_object);查询当前锁事务中阻塞会话和被阻塞会话的sid，sql_id和状态信息。5.2.2 阻塞sql文本select sql_id,sql_text,from v$sqltext where sql_id=&sql_id order by piece;通过sql_id查询得到sql文本，例如通过sql_id查询出阻塞的sql语句。5.2.3 锁阻塞对象信息select owner,object_name,object_type from dba_objectswhere object_id in (select object_id from v$locked_object);通过sid查询阻塞对象的详细信息如对象名称，所属用户等。5.2.4 查询阻塞会话alter system kill session sid,serial#;在oracle实例内杀死阻塞会话进程。5.2.5 杀死系统进程select pro.spid,gram from v$session ses,v$process prowhere ses.sid=&sid and ses.paddr=pro.addr;有时对于活动进程，在系统层面中止更为快速安全，示例找到系统进程号，然后kill中止。5.2.6 闩检查select name,gets,misses,immediate_gets,spin_gets from v$latch order by 2;检查数据库闩的使用情况，misses，spin_gets 统计高的，需要关注。 5.2.7 闩使用检查select addr,gets from v$latch_children where name=cache buffers chains;select hladdr,file#,dbablk from x$bh where hladdr in (select addr from v$latch_children where addr=&addr);通过latch地址，找到该latch守护的x$bh中的相关buffer。6. 等待 统计信息waits和statistics数据分别代表了数据库的等待和运行数据，观察这些数据以了解数据库的等待瓶颈和健康程度。回复”D6“查看详细的命令。6.1 等待事件查询select sid,event,wait_time_micro from v$session_wait order by 3;通过等待事件和等待时间，了解数据库当前连接会话的等待情况。如果会话众多，需要限定查询输出行数。6.2 top10等待事件select * from (select event,total_waits,average_wait,time_waited from v$system_eventwhere wait_classIdle order by time_waited desc)where rownum=10;查看数据库中top10等待事件信息，需要分析和关注非空闲的显著等待。6.3 会话统计数据select s.sid,s.statistic#,,s.value from v$sesstat s,v$statname nwhere s.statistic#=n.statistic#and =redo size and sid=&sid;查看数据库会话的统计信息数据，示例查询了redo的大小，sid需要提供。6.4 系统级统计信息select * from v$sysstat where name=redo size;查询整个系统的统计数据，示例显示数据库实例启动以来的redo日志生成量。7. 对象检查表、索引、分区、约束是数据库的核心存储对象，其核心信息和对象维护是DBA重要的日常工作。回复“D7”查看详细命令。7.1 表对象信息表信息数据select * from (select owner,table_name,num_rows from dba_tables order by num_rows desc nulls last)where rownum11;查看表的基本信息数据：属主、表名、记录行数等。表统计信息select table_name,last_analyzed from dba_tab_statisticswhere table_name=&table_name;查看表最后的统计信息分析收集时间。统计信息影响执行计划、当SQL执行异常时，需要重点分析统计信息。7.2 索引信息数据select * from (select index_name,num_rows,leaf_blocks,clustering_factor from dba_indexes order by 4 desc nulls last)where rownum11;索引的基本信息，输出包括叶块数据和聚簇因子等。如聚簇因子接近行数可能代表索引效率不高。7.3 分区对象检查select table_name,partitioning_type,partition_count,status from dba_part_tables;select table_name,partition_name,high_value from dba_tab_partitions where rownum&owner,tabname=&table_name);exec dbms_stats.gather_index_stats(owner=&owner,indname=&index_name);收集统计信息是一项复杂任务、需谨慎。7.5 失效对象select owner,object_name,object_type,status from dba_objects where statusVALID;检查数据库中的失效对象信息。通常运行健康的数据库中不应有失效的对象。7.6 闪回查询时间闪回：select * from &table_name as of timestamp to_timestamp(2016-06-27 00:02:09,yyyy-mm-dd hh24:mi:ss)；闪回表数据，基于时间点的表数据闪回查询。scn闪回select * from &table_name as of scn &scn;闪回表数据，基于scn的表数据查询，需要提供scn。闪回dropflashback table &old_table to before drop rename to &new_table;闪回删除操作，对已经删除的表进行闪回恢复并重命名。8. AWR报告检查通过AWR报告了解日常高峰时段数据库各项指标和运行状态，通过对比报告观察和基线的变化，通过趋势分析持续关注数据库日常运行状态。回复“D8”查看详细命令。8.1 本地AWRsql ?/rdbms/admin/awrrpt生成本地awr报告信息，需要根据提示输入相应的信息。8.2 指定实例AWRsql ?/rdbms/admin/awrrpti生成指定实例awr报告信息，需要根据提示输入相应的信息。8.3 awr对比报告sql ?/rdbms/admin/awrddrpt生成本地AWR时间段对比报告。8.4 指定实例对比sql ?/rdbms/admin/awrddrpi生成指定实例AWR时间段对比报告。8.5 AWR信息提取sql ?/rdbms/admin/awrextr使用awrextr脚本将AWR性能数据导出，可以用于留错或者异地分析。8.6 AWR信息加载sql ?/rdbms/admin/awrload通过awrload，可以将导出的AWR性能数据导入到其他数据库中，便于集中分析。9. SQL报告检查对TOP_SQL进行持续关注和分析，通过SQL报告分析SQL的效率、性能，并做出报告和优化建议等。回复“D9”查看详细命令。云和恩墨的z3工具，可以自动进行sql优化。9.1 Explain sql执行计划explain plan for select count(*) from user_objects;select * from table(dbms_xplan.display);通过explain plan for 方法获取sql执行计划。9.2 autotrace sql执行计划set autotrace traceonly explain;select count(*) from user_objects;set autotrace off;通过sql*plus的 autotrace 功能获取sql执行计划9.3 dbms_xplan sql执行计划select * from table(dbms_xplan.display_cursor(&sql_id,null,advanced);通过dbms_xplan包获取sql执行计划，sql_id需要提供。回复“SQL执行”9.4 10053事件alter session set tracefile_identifier=10053;alter session set events 10053 trace name context forever ,level 1;执行生产sqlalter session set events 10053 trace name context off;通过10053事件来查看执行计划和详细的sql解析过程，trace文件提供了oracle如何选择执行计划的原因。9.5 绑定变量select dbms_sqltune.extract_bind(bind_data,1).values_string from wrh$_sqlstat where sql_id=&sql_id;查询SQL语句的绑定变量以及历史绑定变量值，需要提供sql_id信息。9.6 sql报告sql ?/rdbms/admin/awrsqrptsql ?/rdbms/admin/awrsqrpi指定sql的监控报告select dbms_sqltune.report_sql_monitor(sql_id=&sql_id,report_level=all,type=ACTIVE) as report from dual;生成指定sql_id的sql monitor report。10. 定时任务检查检查数据库定时任务执行情况，确保后台任务正确执行，尤其应关注统计信息收集等核心任务。回复“D10”查看详细命令。10.1 用户定时任务select job,log_user,last_date,next_date,interval,broken,failures from dba_jobs;查询用户的定时任务(JOB)信息，确保任务在期望的时间成功执行。10.2 系统定时任务select job_name,start_date,repeat_interval from dba_scheduler_jobs;查询系统定时调度信息，查询显示任务名称，初始启动日期以及重复间隔。10.3 系统定时任务 11g+select client_name,mean_job_duration from dba_autotask_client;11g之后增加的字典表，记录每个在7天和30天维护任务的统计信息，查询显示名称和平均执行时间。10.4 启停统计信息任务 10gexec dbms_scheduler.disable(SYS.GATHER_STATS_JOB);exec dbms_scheduler.enable(SYS.GATHER_STATS_JOB);关闭和开启oracle 10g统计信息自动采集任务。10.5 启停统计信息任务 11gexec d