Dear Readers, In this article, we will see MySQL Identifying unused indexes. Identifying unused indexes: MySQL doesn’t provide completely reliable options to get the accurate index usage details. Below listed are the some of the options available and some of the options are version specific… Read More
Dear Readers, In this article, we will see DB scattered Read Wait Event. DB scattered Read wait event Adjacent blocks are read from disk allowing Oracle to read many blocks by performing large I/Os. Thus, from the storage perspective, db file scattered read is a… Read More
Dear Readers, In this article, we will see Direct Path ReadWait Event. Direct Path Read Wait event: The Direct Path Read Temp wait events are an access path in which multiple Oracle blocks are read directly to the Oracle process memory without being read into… Read More
Dear Readers, In this article, we will see AWR Customization . AWR Customization: Oracle init parameter “statistics_level” enables AWR to collect and maintain performance data. Values for parameter are :: BASIC : Doesn’t collect AWR or any metric stats TYPICAL: (Default), Collects typical advisories and… Read More
Dear Readers, In this article, we will see Installation and Configuration of GoldenGate 19C on Oracle RAC . Source: 2-Node RAC Oracle 19c Database Source Server has a NFS Drive: /goldengate Target: Oracle 19c Database Plan: Install GoldenGate on both the machine and a soft… Read More
Dear Readers, In this article, we will see Oracle 18c New Features for DBA’s . Oracle 18c New Features for DBA’s Manual Termination of run-away queries A new command is introduced for DBAs to cancel blocking or too expensive statements instead of kill the originating… Read More
Dear Readers, In this article, we will see Kill blocking sessions in RAC . Kill blocking sessions in RAC:
|
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 |
set serveroutput on set feedback off set linesize 300 declare blockcount number; sqlreport varchar(3000); sqloutput varchar(5000); v_rec varchar(3000); cursor blk_curs is select B.USERNAME BUSER,B.SID BSID,B.SERIAL# BSERIAL,B.INST_ID BID,B.STATUS BSTAT,B.LAST_CALL_ET BLAST,W.USERNAME WUSER,W.SID WSID,W.SERIAL# WSERIAL,W.INST_ID WID 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 and B.username not in ('SYS','SYSTEM','DBSNMP','RMAN'); cursor distval is select distinct B.SID CSID, B.USERNAME CUSER 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 and B.username not in ('SYS','SYSTEM','DBSNMP','RMAN'); cursor killcurs(v in varchar, b in number) is select SID ,serial# SERIAL,inst_id ,username ,to_char(logon_time,'''DD-MON-YYYY HH24-MI-SS''') OTIME,event,status ,machine ,round(LAST_CALL_ET/60,2) OMIN FROM gv$session where username is not null and username=v and sid=b order by inst_id; begin DBMS_OUTPUT.put_line('Checking for blocking sessions on this database'); DBMS_OUTPUT.put_line('-----------------------------------------------'); select count(*) into blockcount from (select * from gv$session where BLOCKING_SESSION >0) W, (select * from gv$session ) B where W.BLOCKING_INSTANCE=B.INST_ID and W.BLOCKING_SESSION=B.SID and B.username not in ('SYS','SYSTEM','DBSNMP','RMAN'); IF blockcount > 0 THEN DBMS_OUTPUT.put_line('Found blocking sessions -> Fetching report for the same'); DBMS_OUTPUT.put_line('-------------------------------------------------------'); for v_rec in blk_curs LOOP dbms_output.put_line(v_rec.BUSER ||' ('||v_rec.BSID||','||v_rec.BSERIAL||',@'||v_rec.BID||') is Currently '||v_rec.BSTAT||' for last '||v_rec.BLAST||' Sec and it''s BLOCKING user '|| v_rec.WUSER||' ('||v_rec.WSID||','||v_rec.WSERIAL||',@'||v_rec.WID||')'); end loop; DBMS_OUTPUT.put_line('-'); DBMS_OUTPUT.put_line('-'); DBMS_OUTPUT.put_line('Further details on blocking sessions -> includes kill script of blocking session'); DBMS_OUTPUT.put_line('--------------------------------------------------------------------------------'); for v_rec2 in distval LOOP for v_rec3 in killcurs(v_rec2.CUSER, v_rec2.CSID) loop dbms_output.put_line('alter system kill session '''||v_rec3.SID|| ',' || v_rec3.SERIAL||',@'||v_rec3.inst_id|| ''' immediate; '||v_rec3.username||' '||v_rec3.OTIME||' '|| v_rec3.event||' '||v_rec3.status||' '||v_rec3.machine||' '||v_rec3.OMIN); end loop; end loop; DBMS_OUTPUT.put_line('-'); DBMS_OUTPUT.put_line('-'); ELSE DBMS_OUTPUT.put_line('-'); DBMS_OUTPUT.put_line('-'); DBMS_OUTPUT.put_line('Hurrey !!! No blocking sessions found'); DBMS_OUTPUT.put_line('-'); DBMS_OUTPUT.put_line('-'); END IF; END; / |
Output:
|
1 2 3 4 5 6 7 8 9 10 |
Checking for blocking sessions on this database ------------------------------------------------ Found blocking sessions -> Fetching report for the same -------------------------------------------------------- VINOD (3033,27215,@1) is Currently INACTIVE for last 23 Sec and it's BLOCKING user VINOD (2126,11412,@2) - - Further details on blocking sessions -> includes kill script of blocking session -------------------------------------------------------------------------------- alter system kill session '3033,27215,@1' immediate, VINOD '02-NOV-2020 19-12-04' SQL*Net message from client INACTIVE server1 .38 |
Thank you for giving your valuable time to read the above information. Follow us on Website … Read More
Dear Readers, In this article, we will see the following Comparison between Python and Shell Script for sending email for the set of SQL commands. Python and Shell Script for sending email for the set of SQL commands: Shell Script:
|
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 |
#!/bin/bash #Script to perform testing ## Environment variables TODAY=`date +%d-%b-%Y-%H:%M:%S` export ORACLE_HOME='/u01/app/oracle/product/12.1.0.2' export ORACLE_SID='prod' export ORACLE_BASE='/u01/app/oracle' export PATH=$PATH:${ORACLE_HOME}/bin export MAILIST='ktexperts@gmail.com' export LOG='/tmp/test_mig.log' #Function step1 function step1 { echo " " >> $LOG echo "Started Step 1" >> $LOG echo " " >> $LOG echo "Test email " >> $LOG sqlplus vinod/vinod <<START >>$LOG prompt updating records in EMP table select * from emp where deptno=20; prompt update emp set sal=sal+500 WHERE deptno=20; update emp set sal=sal+500 WHERE deptno=20; prompt select * from emp where deptno=20; select * from emp; START echo " " >> $LOG echo "----- END OF Step1 ------" >> $LOG } #Function to trigger email function mailog { cat ${LOG} | mailx -s "${TODAY} - TEST Mail" ${MAILIST} } #Script execution is here > $LOG if [ "$#" -eq 0 ] then step1 mailog else $1 mailog fi |
Output:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
updating records in EMP table SQL> EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-DEC-80 4800 20 7566 JONES MANAGER 7839 02-APR-81 6975 20 7788 SCOTT ANALYST 7566 19-APR-87 7000 20 7876 ADAMS CLERK 7788 23-MAY-87 5100 20 7902 FORD ANALYST 7566 03-DEC-81 7000 20 SQL> update emp set sal=sal+500 WHERE deptno=20 SQL> 5 rows updated. SQL> select * from emp where deptno=20 SQL> EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-DEC-80 5300 20 7566 JONES MANAGER 7839 02-APR-81 7475 20 7788 SCOTT ANALYST 7566 19-APR-87 7500 20 7876 ADAMS CLERK 7788 23-MAY-87 5600 20 7902 FORD ANALYST 7566 03-DEC-81 7500 20 |
Python Script:… Read More
Dear Readers, In this article, we will see the following Top CPU & ELAPSED Queries. Top CPU Queries :
|
1 2 3 4 5 6 7 8 9 10 |
select * from (SELECT dhst.sql_id,SQL_TEXT,X.CPU_TIME/1000000 CPU_Time_inSec,X.Num_Execs,round(((X.CPU_TIME/1000000)/X.Num_Execs),3) CPUPEREXEC, X.num_rows FROM DBA_HIST_SQLTEXT DHST, (SELECT DHSS.SQL_ID SQL_ID,SUM(DHSS.CPU_TIME_DELTA) CPU_TIME ,sum(executions_delta) as Num_Execs,sum(ROWS_PROCESSED_delta) as num_rows FROM DBA_HIST_SQLSTAT DHSS WHERE DHSS.SNAP_ID IN(SELECT SNAP_ID FROM DBA_HIST_SNAPSHOT WHERE begin_interval_time > =TO_DATE('25/APR/2019 20:50','DD-MON-YYYY HH24:MI') AND END_INTERVAL_TIME<=TO_DATE('26/APR/2019 06:30','DD-MON-YYYY HH24:MI') ) GROUP BY DHSS.SQL_ID) X WHERE X.SQL_ID=DHST.SQL_ID ORDER BY X.CPU_TIME DESC FETCH FIRST 50 ROWS ONLY)y order by y.cpuperexec desc |
Top ELAPSED Queries :
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
select sub.sql_id, txt.sql_text, parsing_schema_name, sub.seconds_since_date as TOTALELAPSEDTIME, sub.execs_since_date as NUmberofExecs, sub.gets_rows_date as RowsProcessed, round(sub.seconds_since_date / (sub.execs_since_date + 0.01), 3) avg_query_time from ( -- sub to sort before top N filter select sql_id, g.parsing_schema_name, round(sum(ELAPSED_TIME_delta) / 1000000) as seconds_since_date, sum(executions_delta) as execs_since_date, sum(buffer_gets_delta) as gets_since_date, sum(ROWS_PROCESSED_delta) as gets_rows_date, row_number() over (order by round(sum(elapsed_time_delta) / 1000000) desc) r from dba_hist_snapshot natural join dba_hist_sqlstat g where begin_interval_time > =TO_DATE('02/APR/2019 07:50','DD-MON-YYYY HH24:MI') AND END_INTERVAL_TIME<=TO_DATE('02/APR/2019 08:20','DD-MON-YYYY HH24:MI') and parsing_schema_name = 'SCOTT' group by sql_id, g.parsing_schema_name) sub join dba_hist_sqltext txt on sub.sql_id = txt.sql_id where r < 10 order by avg_query_time desc |
TOP Elapsed Queries with Text :
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
select sub.sql_id, txt.sql_text, parsing_schema_name, sub.seconds_since_date as TOTALELAPSEDTIME, sub.execs_since_date as NUmberofExecs, sub.gets_rows_date as RowsProcessed, round(sub.seconds_since_date / (sub.execs_since_date + 0.01), 3) avg_query_time from ( -- sub to sort before top N filter select sql_id, g.parsing_schema_name, round(sum(ELAPSED_TIME_delta) / 1000000) as seconds_since_date, sum(executions_delta) as execs_since_date, sum(buffer_gets_delta) as gets_since_date, sum(ROWS_PROCESSED_delta) as gets_rows_date, row_number() over (order by round(sum(elapsed_time_delta) / 1000000) desc) r from dba_hist_snapshot natural join dba_hist_sqlstat g where begin_interval_time > =TO_DATE('22/OCT/2019 10:40','DD-MON-YYYY HH24:MI') AND END_INTERVAL_TIME<=TO_DATE('22/OCT/2019 14:00','DD-MON-YYYY HH24:MI') and parsing_schema_name = 'SCOTT' group by sql_id, g.parsing_schema_name) sub join dba_hist_sqltext txt on sub.sql_id = txt.sql_id and SQL_TEXT like 'update%' --where r < 50 order by avg_query_time desc |
Thank you for giving your valuable time to read the above information.… Read More
Dear Readers, In this article, we will see the following Create Amazon Linux EC2 Instance in AWS CLI. Steps to Follow : Connect to AWS CLI On Windows Create a Key Pair Create a Security Group Create a Amazon EC2 Instance Create a Volume Attach… Read More
