oracle數(shù)據(jù)庫(kù)巡檢sql腳本

如何查詢sga內(nèi)各組件信息和pga大小SQL>conn/assysdbaSQL>showparametersga_max-查看sga的大小NAMETYPEVALUENAMETYPEVALUEsga_max_sizebiginteger1578706860sga_max_sizeSQL>showparametersga_target—查看sga_target大?。ㄖ幌?0g以上版本）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查詢數(shù)據(jù)庫(kù)版本的sqlSQL>selectVERSIONfromv$instance;VERSION9.2.0.1.0SQL>selectname,log_modefromv$database;--查看數(shù)據(jù)庫(kù)名，歸檔模式NAMELOG_MODESKYOTAARCHIVELOGNOARCHIVELOG為非歸檔模式查看數(shù)據(jù)庫(kù)字符集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近一個(gè)月內(nèi)每天日志切換次數(shù)selectto_char(first_time,'yyyy-mm-dd')day,count(*)timesfromv$log_historywherefirst_time>sysdate-30groupbyto_char(first_time,'yyyy-mm-dd');查看監(jiān)控當(dāng)天高峰期日志切換情況具體高峰時(shí)段根據(jù)項(xiàng)目特性修改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')以使"用占當(dāng)前表空間大小比例",f.total_bytes空閑空間(M)",f.max_bytes"最大塊(M)",d.max_autobytes表空間擴(kuò)展的最大尺寸(M)",TO_CHAR(ROUND((d.tot_grootte_mb-f.total_bytes)/d.max_autobytes*100,2),'990.99'以)使"用占最大擴(kuò)展比例"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;任務(wù)或job的運(yùn)行情況selectschema_user,last_date,next_date,total_time,interval,failures,whatfromdba_jobswhereschema_user='SKY'以實(shí)際的業(yè)務(wù)用戶為準(zhǔn)查看系統(tǒng)當(dāng)前的等待事件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當(dāng)前等待事件對(duì)應(yīng)的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;當(dāng)前主要全表掃描語(yǔ)句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');單次執(zhí)行物理讀多前20個(gè)語(yǔ)句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;物理多總數(shù)多前10個(gè)sqlSELECT*FROM(SELECTPARSING_USER_ID,EXECUTIONS,SORTS,COMMAND_TYPE,DISK_READS,sql_textFROMv$sqlareaORDERBYdisk_readsDESC)WHEREROWNUM<11;單次邏輯讀多的前10個(gè)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個(gè)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/