Instance Information : set linesize 300 select name||’ – ‘||instance_name||’@’||host_name||’ – ‘||DB_UNIQUE_NAME||’ – ‘||version||’ – ‘||open_mode||’ – ‘||to_char(startup_time,’DD-MON-YYYY HH24:MI:SS’) || ‘ – ‘|| database_role from v$database, v$instance; FRA Usage : set linesize 300 col file_type for a40 select * from V$FLASH_RECOVERY_AREA_USAGE; Check Restore Point :… Read More
1. EXPLAIN PLAN FOR SELECT <STATEMENT> set linesize 300SELECT * FROM TABLE(dbms_xplan.display); 2. FROM MEMORY – Get the execution plan which is generated by CBO Use DBMS_XPLAN package:select * from table (dbms_xplan.display_cursor (‘&sqlid’) ) ; For the above, you need to have SQL ID. To… Read More
cat g_stats_1_test.sh sqlplus / as sysdba <<EOF set head off conn / as sysdba set serveroutput on set timing on set echo on spool dict_stats_test.log exec DBMS_STATS.gather_table_stats (ownname => ‘SCOTT’,tabname => ‘EMP’,method_opt=>’FOR COLUMNS SIZE AUTO STATUS’); spool off set timing off set serveroutput off exit;… Read More
set linesize 200 set pagesize 500 spool /tmp/Oct29.html set markup html on set numwidth 40 select * from emp; set long 100000 select sql_id,sql_text from DBA_HIST_SQLTEXT where sql_id in (select sql_id from v$session); spool off
Ask DB details and query details before start working on it. When did this query last took 2 hours?What is the change in volume of data?How many times this query runs each day?Start and end time of this query execution for past 1 month.Any DDL changes… Read More
for i in srvctl config; do echo $i; srvctl status database -d $i; done; for i in srvctl config; do echo $i; srvctl status service -d $i; done;
for ORA_SID in $(cat /var/opt/oracle/oratab |grep -vi asm |grep -v ‘#’|grep -vi agent |grep -vi orcl7 |awk -F”:” ‘{ print $1}’) do ORACLE_SID=$ORA_SID export ORACLE_SID sqlplus -s “/ as sysdba” <<! startup; exit; ! done
$ cat /u01/app/oracle/admin/SEPQA01/scripts/archiveclean.sh #CURRDATETIME=date ‘+%Y%m%d-%H%M%S’ ORACLE_SID=ORCL1;export ORACLE_SID ORACLE_HOME=/u01/app/oracle/product/12102;export ORACLE_HOME LOGDIR=/var/opt/monitoring/oracle/logs LOGFILE=/var/opt/monitoring/oracle/logs/archiveclean.log $ORACLE_HOME/bin/rman <<EOF | tee -a /var/opt/monitoring/oracle/logs/archiveclean.log connect target delete archivelog until time ‘sysdate-1’ ; exit EOF ORACLE_SID=ORCL2;export ORACLE_SID $ORACLE_HOME/bin/rman <<EOF | tee -a /var/opt/monitoring/oracle/logs/archiveclean.log connect target delete archivelog until time ‘sysdate-1’ ; exit EOF… Read More
/u01/app/oracle/DBA/rmanbackup.sh #!/bin/ksh CURRDATETIME=date ‘+%Y%m%d-%H%M%S’a ORACLE_SID=ORCL1;export ORACLE_SID ORACLE_HOME=/u01/app/oracle/product/12.1.0.2;export ORACLE_HOME LOGDIR=/var/opt/oracle/logs LOGFILE=${LOGDIR}/${ORACLE_SID}_rman_${CURRDATETIME}.log RMANBACKUPDIR=/backup/ORCL1/jul16 $ORACLE_HOME/bin/sqlplus dbsnmp/password <<EOF | tee -a $LOGFILE SET LINESIZE 200 COL FILE_NAME FOR A70 SET FEEDBACK OFF SET PAGESIZE 100 COL MEMBER FOR A60 SELECT FILE_NAME,BYTES/1024/1024 MBYTES FROM DBA_DATA_FILES; SELECT FILE_NAME,BYTES/1024/1024 MBYTES FROM DBA_TEMP_FILES;… Read More
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 |
$ cat run_any_sql_10sec.ksh #!/bin/ksh SQL_FILE=$1 TMP_FILE=/tmp/$$.out TMP_FILE_ALL=/tmp/$$.all.out for i in {1..10000} do ${ORACLE_HOME}/bin/sqlplus -s -l /nolog << EOF > ${TMP_FILE} Connect / as sysdba SET SERVEROUT ON ECHO ON COL SEGMENT_NAME FOR A30 COL TABLE_NAME FOR A30 COL COLUMN_NAME FOR A30 COL OWNER FOR A10 COL SQL_TEXT FOR A45 COL INDEX_NAME FOR A30 select to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') from dual; start ${SQL_FILE}; EXIT; EOF cat ${TMP_FILE} >> ${TMP_FILE_ALL} cat ${TMP_FILE} sleep 10 done chmod 754 run_any_sql ./run_any_sql waits.sql ########## waits.sql set linesize 300 select inst_id, event, count(*), avg(Wait_time), max(wait_time) from gv$session_wait where event not in ( 'HS message to agent', 'SQL*Net break/reset to client', 'SQL*Net message from client', 'SQL*Net message to client', 'jobq slave wait', 'pipe get', 'pmon timer', 'queue messages', 'rdbms ipc message', 'smon timer', 'wait for unread message on broadcast channel', 'wakeup time manager', 'ASM background timer', 'DIAG idle wait', 'PX Deq: Execute Reply', 'PX Deq: Execution Msg', 'PX Deq: reap credit', 'Streams AQ: qmn coordinator idle wait', 'Streams AQ: qmn slave idle wait', 'Streams AQ: waiting for messages in the queue', 'Streams AQ: waiting for time management or cleanup tasks', 'class slave wait' ) and wait_class<>'Idle' group by inst_id, event order by 1,3; ######## rowlocks.sql col obj for a85 col ses_cnt for 9999 col info for a35 set lines 160 set trimspool on select obj, count(*) as ses_cnt, min(sql_hash_value) hash, cmd ||' : '|| module "INFO" from (select decode(sign(row_wait_obj#), 1, (select object_name from dba_objects where object_id=row_wait_obj# and data_object_id is not null) ,-1, 'UNKNOWN', 'UNKNOWN') || ':' || event || ':' || p2 || ':' || p3 || ':' || chr(bitand(p1, -16777216)/16777215) || chr(bitand(p1, 16711680)/65535) || ':''' || decode(sign(row_wait_obj#), 1, dbms_rowid.rowid_create(1, (select data_object_id from dba_objects where object_id = row_wait_obj# and data_object_id is not null), row_wait_file#, row_wait_block#, row_wait_row#), 'No rowid') || '''' as obj, decode(command, 2, 'INSERT', 6, 'UPDATE', 7, 'DELETE', 182, 'UPDATE INDEXES', 3, 'SELECT FOR UPDATE', command) as cmd, sql_hash_value, module from v$session where status = 'ACTIVE' and lockwait is not null ) group by obj, cmd||' : '||module order by 2; ######### locktree.sql set lines 199 col sql_id for a13 col sid for a18 col status for a10 col obj_info for a90 col wait_sec for 99999 select sid, sql_id, seconds_in_wait wait_sec, decode(sign(row_wait_obj#), 1, (select object_name from dba_objects where object_id=row_wait_obj# and data_object_id is not null) ,-1, 'Null', 'Null') ||':'|| cmd ||':'|| event#||':'||nvl(substr(event, 1, 15), 'No Event') ||':'|| nvl(p1,0)||':'||nvl(p2, 0) ||':'|| nvl(p3, 0) ||':'|| info "OBJ_INFO" from ( select sid, sql_id, status, seconds_in_wait, sid2, sid3, max(seconds_in_wait) over (partition by sid2) ctime, count(*) over (partition by sid2) cnt, row_wait_obj#, cmd, event, event#, p1, p2, p3, "INFO" from ( select lpad(' ', level-1)||sid sid, decode(sql_id, null, prev_sql_id, sql_id) sql_id, status, seconds_in_wait, blocking_session, blocking_session_status, connect_by_root sid sid2, sys_connect_by_path(sid,':') sid3, row_wait_obj#, decode(command, 2, 'INSERT', 6, 'UPDATE', 7, 'DELETE', 182, 'UPDATE INDEXES', 3, 'SELECT FOR UPDATE', command) as cmd, event, event#, p1, p2, p3, status||':'||machine||':'||substr(module, 1, 10) "INFO" from v$session where taddr is not null or blocking_session <> 0 connect by prior sid=blocking_session start with blocking_session is null ) ) where cnt > 1 order by ctime desc, sid2, length(sid3), seconds_in_wait desc; ########## blocking_sessions.sql set linesize 300 select B.USERNAME ||' ('||B.SID||','||B.SERIAL#||',@'||B.INST_ID||') is Currently '||B.STATUS||' for last '||B.LAST_CALL_ET||' Sec and it''s BLOCKING user '|| W.USERNAME|| ' ('| |W.SID||','||W.SERIAL#||',@'||W.INST_ID||')' from (select INST_ID,SID,SERIAL#,USERNAME,STATUS,BLOCKING_INSTANCE,BLOCKING_SESSION, LAST_CALL_ET from gv$session where BLOCKING_SESSION >0) W, (select INST_ID,SID,SERIAL#,USERNAME,STATUS,LAST_CALL_ET from gv$session ) B where W.BLOCKING_INSTANCE=B.INST_ID and W.BLOCKING_SESSION=B.SID; ############ active_sessions.sql select (select 'Active Sessions in MY DB Inst 1 : '||count(*) from gv$session where status='ACTIVE' and INST_ID=1 and sql_id is not null ) as Active_Sessions from dual union select (select 'Active Sessions in MY DB Inst 2 : '||count(*) from gv$session where status='ACTIVE' and INST_ID=2 and sql_id is not null ) as c2 from dual union select (select 'Active Sessions in MY DB Inst 3 : '||count(*) from gv$session where status='ACTIVE' and INST_ID=3 and sql_id is not null ) as c2 from dual union select (select 'Active Sessions in MY DB Inst 4 : '||count(*) from gv$session where status='ACTIVE' and INST_ID=4 and sql_id is not null ) as c2 from dual; ############ TOPSQL.sql select sql_id,username,count(*) from v$session where type='USER' and status='ACTIVE' group by sql_id,username order by 3; ########## Userconnections.sql select USERNAME,status,count(*) from v$session where username not in ('SYSTEM','SYS') and username is not null group by USERNAME,status order by 2; ########## CPU :: for ((;;)) do sar 2 30; sleep 10; done ########## Memory free -m -s 5 ######### |
