Vinod Siram – Page 6

Top Oracle CPU & ELAPSED Queries

4 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

ORACLE: PL/SQL script to rebuild Table(s) and it’s corresponding indexes

4 years ago by Vinod SiramOracle DB Admin

ORACLE:PL/SQL script to rebuild Table(s) and it’s corresponding indexes. Note : Please implement in lower environments before proceeding to Production. Pre requisites : Please download and check below file for pre-requisites. Table creation scripts The below script will be used for 1.Rebuild table(s) 2.Rebuild… Read More

Protected: ORACLE : Shell script to rebuild indexes for index fragmentation

4 years ago by Vinod SiramOracle DB Admin

There is no excerpt because this is a protected post.

Tools for Administering the Oracle Database

4 years ago by Vinod SiramOracle DB Admin

Dear Readers, In this article, we will discuss Tools for Administering the Oracle Database Please Fill below Google Form (If we missed any Products, Tools, and Utilities that you use in achieving your goals as a Cassandra Administrator.) 👇🏻👇🏻👇🏻👇🏻👇🏻 https://forms.gle/oracledba Monitoring Tools: OEM Nagios EM… Read More

Protected: Shell Script to get EMAIL alerts for Lagging between GoldenGate Processes

5 years ago by Vinod SiramAutomation scripts in Goldengate

There is no excerpt because this is a protected post.

SecureCRT Buttons for Default

5 years ago by Vinod SiramSecure CRT

Hello Reader In this article , we will cover SecureCRT Buttons for Default. ############Setting up Format (format): To convert invalid timestamp value and to check current date.

alter session set nls_date_format='MM/DD/YYYY HH24:MI:SS';
Session altered.

1 2	alter session set nls_date_format='MM/DD/YYYY HH24:MI:SS'; Session altered.

######### Instance Information (inst_info): This SQL outputs the information of instance that is up and running like… Read More

Protected: Shell Script to get EMAIL alerts for STOPPED Processes in Oracle GoldenGate.

SYS>>CREATE TABLE dbmon.TOP_ELAPSEDTIME_SQLIDS
(
  SQL_ID            VARCHAR2(20 CHAR),
  LASTMODIFIEDDATE  DATE                        DEFAULT SYSDATE               NOT NULL
);

SYS>>CREATE TABLE dbmon.TOP_ELAPSEDTIME_SQLIDS

(

SQL_ID VARCHAR2(20 CHAR),

LASTMODIFIEDDATE DATE DEFAULT SYSDATE NOT NULL

);

Another table to store ALL SQL’s.

SYS>>CREATE TABLE dbmon.ALL_ELAPSEDTIME_SQLIDS
(
  SQL_ID            VARCHAR2(20 CHAR),
  LASTMODIFIEDDATE  DATE                        DEFAULT SYSDATE               NOT NULL
);