oracle数据库巡检sql脚本

如何查询sga内各组件信息和pga大小SQL>conn/assysdbaSQL>showparametersga_max-查看sga的大小NAMETYPEVALUENAMETYPEVALUEsga_max_sizebiginteger1578706860sga_max_sizeSQL>showparametersga_target—查看sga_target大小（只限10g以上版本）NAMETYPEVALUENAMETYPEVALUEsga_targetbiginteger584Msga_targetSQL>showparameterdb_cache_size—查看db_cache大小NAMETYPEVALUENAMETYPEVALUEdb_cache_sizebiginteger1048576000db_cache_sizeSQL>showparametershared_pool_size一查看share_pool大小NAMETYPEVALUENAMETYPEVALUEshared_pool_sizebiginteger318767104shared_pool_sizeSQL>showparameterpg—查看pga_aggregate_targetNAMETYPEVALUEpga_aggregate_targetbiginteger314572800查询数据库版本的sqlSQL>selectVERSIONfromv$instance;VERSION9.2.0.1.0SQL>selectname,log_modefromv$database;--查看数据库名，归档模式NAMELOG_MODESKYOTAARCHIVELOGNOARCHIVELOG为非归档模式查看数据库字符集SQL>selectvalueFROMnls_database_parametersWHEREparameter'NLS_CHARACTERSET';VALUEZHS16GBK查看redolog信息查看日至组及成员SELECTgroup#,memberFROMV$LOGFILE查看日志如成员大小SQL>selectgroup#,bytes,membersfromv$log;GROUP#BYTESMEMBERSTOC\o"1-5"\h\z838860800 1838860800 1838860800 1近一个月内每天日志切换次数selectto_char(first_time,'yyyy-mm-dd')day,count(*)timesfromv$log_historywherefirst_time>sysdate-30groupbyto_char(first_time,'yyyy-mm-dd');查看监控当天高峰期日志切换情况具体高峰时段根据项目特性修改sqlselectcount(*)timesfromv$log_historywherefirst_timebetweento_date('2009-04-1110:00:00','yyyy-mm-ddhh24:mi:ss')andto_date('2009-04-1116:00:00','yyyy-mm-ddhh24:mi:ss');在oracle告警日志中(alertsid.log)，也可以查看最近日志切换的情况

表空间使用率：SELECTUPPERFROM(SELECTWHERE(f.tablespace_name)"表空间名",SELECTUPPERFROM(SELECTWHEREd.tot_grootte_mb表空间大小(M)",d.tot_grootte_mb一f.total_bytesE"使用空间(M)",TO_CHAR(ROUND((d.tot_grootte_mb-f.total_bytes)/d.tot_grootte_mb*100,2),'990.99')以使"用占当前表空间大小比例",f.total_bytes空闲空间(M)",f.max_bytes"最大块(M)",d.max_autobytes表空间扩展的最大尺寸(M)",TO_CHAR(ROUND((d.tot_grootte_mb-f.total_bytes)/d.max_autobytes*100,2),'990.99'以)使"用占最大扩展比例"tablespace_name,ROUND(SUM(BYTES)/(1024*1024),2)total_bytes,ROUND(MAX(BYTES)/(1024*1024),2)max_bytesFROMSYS.dba_free_spaceGROUPBYtablespace_name)f,(SELECTdd.tablespace_name,ROUND(SUM(dd.BYTES)/(1024*1024),2)tot_grootte_mb,TRUNC(SUM(DECODE(dd.maxbytes,0,dd.BYTES,dd.maxbytes))/(1024*1024))max_autobytesFROMSYS.dba_data_filesddGROUPBYdd.tablespace_name)dd.tablespace_name=f.tablespace_nam;任务或job的运行情况selectschema_user,last_date,next_date,total_time,interval,failures,whatfromdba_jobswhereschema_user='SKY'以实际的业务用户为准查看系统当前的等待事件w.p2,w.p3,s.logon_time,SELECTs.SID,w.event,sw.p2,w.p3,s.logon_time,FROMv$sessions,v$session_waitWHEREs.SID=w.SIDANDw.eventNOTLIKE'%SQL*Net%'ANDw.eventNOTLIKE'%rdbms%'ANDw.eventNOTLIKE'%timer%'ANDw.eventNOTLIKE'%jobq%'ANDw.eventNOTLIKE'%wakeup%';SYSDATEw当前等待事件对应的sqlSELECT/*+RULE*/s.SID,w.event,s.username,t.hash_value,t.piece,t.sql_text,SYSDATEFROMv$sessions,v$session_waitw,v$sqltexttWHEREs.SID=w.SIDANDs.sql_hash_value=t.hash_valueANDw.eventIN('dbfilescatteredread','dbfilesequentialread','freebufferwaits','enqueue','latchfree','logfileparallelwrite','logfilesync','bufferbusywaits','dbfileparallelwrite','dbfilesinglewrite','directpathread','directpathwrite','freebufferinspected','freebufferwaits','librarycacheloadlock',

'librarycachelock','librarycachepin','logbufferspace','logfilesinglewrite','logfileswitch(checkpointincomplete)','logfileswitch(archivingneeded)','logfileswitch(clearinglogfile)','logfileswitchcompletion','undosegmentextension')ORDERBYs.SID,t.piece;当前主要全表扫描语句name,SELECTb.sql_text,b.executions,b.sorts,b.disk_reads,b.buffer_gets,a.hash_value,a.object_owner,a.objectname,FROMv$sql_plana,v$sqlbWHEREa.address=b.addressANDa.hash_value=b.hash_valueANDa.child_number=b.child_numberANDa.operation='TABLEACCESS'ANDa.options='FULL'ANDa.object_ownerNOTIN('ODM','QS','QS_CBADM','QS_CS','QS_ES','QS_OS','QS_WS','SYS','SYSTEM');单次执行物理读多前20个语句SELECT*FROM(SELECTround(disk_reads/decode(EXECUTIONS,0,1,executions),2)bit,PARSING_USER_ID,EXECUTIONS,SORTS,

COMMAND_TYPE,DISK_READS,sql_textFROMv$sqlareaORDERBYbitDESC)WHEREROWNUM<21;物理多总数多前10个sqlSELECT*FROM(SELECTPARSING_USER_ID,EXECUTIONS,SORTS,COMMAND_TYPE,DISK_READS,sql_textFROMv$sqlareaORDERBYdisk_readsDESC)WHEREROWNUM<11;单次逻辑读多的前10个sqlSELECT*FROM(SELECTs.sid,b.spid,s.sql_hash_value,buffer_per_exec,cpu_time_per_exec,q.sql_text,q.executions,qbuffer_per_exec,cpu_time_per_exec,ROUND(q.buffer_gets/q.executions)ASROUND(q.elapsed_time/q.executions)ASq.cpu_time,q.elapsed_time,q.disk_reads,q.rows_processedFROMv$sessions,v$processb,v$sqlqWHEREs.sql_hash_value=q.hash_valueANDs.paddr=b.addr--ANDs.status='ACTIVE'ANDq.buffer_gets>0ANDq.executions>0ORDERBYbuffer_per_execDESC)WHEREROWNUM<=10;逻辑读多的前10个sqlSELECT*FROM(SELECTs.sid,b.spid,s.sql_hash_value,q.sql_text,q.executions,q.buffer_gets,q.cpu_time,q.elapsed_time,q.disk_reads,q.rows_processedFROMv$sessions,v$processb,v$sqlqWHERE/r/