KTEXPERTS – Page 27 – KNOWLEDGE SHARING PLATFORM

Oracle: Comparison between Python and Shell Script for sending email for the set of SQL commands

5 years ago by Rakesh SahuOracle DB Admin

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:

#!/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

#!/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

mailog

Output:

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

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

Top Oracle CPU & ELAPSED Queries

5 years ago by Vinod SiramOracle DB Admin

Dear Readers, In this article, we will see the following Top CPU & ELAPSED Queries. Top CPU Queries :

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

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 :

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

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 :

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

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

How to Create Amazon Linux EC2 Instance Using AWS CLI

5 years ago by Ramesh ReddyAWS

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

How to Install Jenkins On Ubuntu 20.04

5 years ago by Ramesh ReddyDevOps

Dear Readers, In this article, we will see the following Install Jenkins On Ubuntu. Steps to Follow : Logging to AWS Account Launch Ubuntu EC2 Instance Connect to Linux EC2 Instance by Using Putty Install the Java Package Configure the Java Home Location Download and… Read More

How to Configure SSH Password Authentication For Amazon EC2 Instance

5 years ago by Ramesh ReddyAWS

Dear Readers, In this article, we will see the following Configure SSH Password Authentication For Amazon EC2 Instance . Steps to Follow : Logging to AWS Account Launch Linux Server Connect to Linux EC2 Instance by Using Putty Enable SSH Password Authentication Connect to Linux… Read More

How to Connect to Amazon EC2 Instance Using Putty

5 years ago by Ramesh ReddyAWS

Dear Readers, In this article, we will see the following Connect to Amazon EC2 Instance Using Putty. Steps to Follow : Logging to AWS Account Launch Linux Server Download putty and puttygen tools Convert pem file to ppk file by using puttygen Connect to Linux… Read More

How to Install and Configure AWS CLI On Windows

5 years ago by Ramesh ReddyAWS

Dear Readers, In this article, we will see the following Connect to Amazon EC2 Instance Using Putty. What is AWS CLI? The AWS CLI is a unified tool to manage your AWS services from a terminal session on your own client. With just one tool… Read More

How to Create a VPC with Public and Private Subnets

5 years ago by Ramesh ReddyAWS

Dear Readers, In this article, we will see the following Realtime Usecase. Realtime Usecase : My Client wants to launch their Websites publicly along with its Database Servers at the back end. Establish network connectivity between Web Servers & Database Servers. Note: No need of… Read More

How to Configure VPC Peering Between Two Different Regions

5 years ago by Ramesh ReddyAWS

How to Register Free New Domain from Freenom Website

5 years ago by Ramesh ReddyAWS

Hi Dears, In this article, we will see Register Free New Domain from Freenom Website. Steps to Follow: Logging to Freenom Account Register a new domain Verify your Domain As first, we need to go inside the freenom website by using below link. https://www.freenom.com/ 1.… Read More