KTEXPERTS – Page 55 – KNOWLEDGE SHARING PLATFORM

Page 55

Protected: Oracle Shell Script : To Monitor FRA Space

7 years ago by KTEXPERTS TEAMShell_Scripting

There is no excerpt because this is a protected post.

Performance Troubleshooting Steps

7 years ago by KTEXPERTS TEAMOracle DB Admin

Performance Troubleshooting Steps reference of this article from : dbaparadise.com

/*
sessions.sql
Example: @sessions.sql
Copyright @2016 dbaparadise.com
*/
set linesize 200
set pagesize 100
clear columns
col inst for 99999999
col sid for 9990
col serial# for 999990
col username for a12
col osuser for a16
col program for a10 trunc
col Locked for a6
col status for a1 trunc print
col "hh:mm:ss" for a8
col SQL_ID for a15
col seq# for 99990
col event heading 'Current/LastEvent' for a25 trunc
col state head 'State (sec)' for a14
select inst_id inst, sid , serial# , username
, ltrim(substr(osuser, greatest(instr(osuser, '\', -1, 1)+1,length(osuser)-14))) osuser
, substr(program,instr(program,'/',-
1)+1,decode(instr(program,'@'),0,decode(instr(program,'.'),0,length(program),instr(program,'.')-
1),instr(program,'@')-1)) program, decode(lockwait,NULL,' ','L') locked, status,
to_char(to_date(mod(last_call_et,86400), 'sssss'), 'hh24:mi:ss') "hh:mm:ss"
, SQL_ID, seq# , event,
decode(state,'WAITING','WAITING '||lpad(to_char(mod(SECONDS_IN_WAIT,86400),'99990'),6)
,'WAITED SHORT TIME','ON CPU','WAITED KNOWN TIME','ON CPU',state) state
, substr(module,1,25) module, substr(action,1,20) action
from GV$SESSION
where type = 'USER'
and audsid != 0 -- to exclude internal processess
order by inst_id, status, last_call_et desc, sid
/

sessions.sql

Example: @sessions.sql

set linesize 200

set pagesize 100

clear columns

col inst for 99999999

col sid for 9990

col serial# for 999990

col username for a12

col osuser for a16

col program for a10 trunc

col Locked for a6

col status for a1 trunc print

col "hh:mm:ss" for a8

col SQL_ID for a15

col seq# for 99990

col event heading 'Current/LastEvent' for a25 trunc

col state head 'State (sec)' for a14

select inst_id inst, sid , serial# , username

, ltrim(substr(osuser, greatest(instr(osuser, '\', -1, 1)+1,length(osuser)-14))) osuser

, substr(program,instr(program,'/',-

1)+1,decode(instr(program,'@'),0,decode(instr(program,'.'),0,length(program),instr(program,'.')-

1),instr(program,'@')-1)) program, decode(lockwait,NULL,' ','L') locked, status,

to_char(to_date(mod(last_call_et,86400), 'sssss'), 'hh24:mi:ss') "hh:mm:ss"

, SQL_ID, seq# , event,

decode(state,'WAITING','WAITING '||lpad(to_char(mod(SECONDS_IN_WAIT,86400),'99990'),6)

,'WAITED SHORT TIME','ON CPU','WAITED KNOWN TIME','ON CPU',state) state

, substr(module,1,25) module, substr(action,1,20) action

from GV$SESSION

where type = 'USER'

and audsid != 0 -- to exclude internal processess

order by inst_id, status, last_call_et desc, sid

/*
sqltext.sql
Example @sqltext.sql 6rv5za4tfnjs8
Copyright dbaparadise.com
*/
set define '&'
set verify off
define sqlid=&1
col sql_text for a80 word_wrapped
col inst_id for 9
break on inst_id
set linesize 150
select inst_id, sql_text
from gv$sqltext
where sql_id = '&sqlid'
order by inst_id,piece
/

sqltext.sql

Example @sqltext.sql 6rv5za4tfnjs8

set define '&'

set verify off

define sqlid=&1

col sql_text for a80 word_wrapped

col inst_id for 9

break on inst_id

set linesize 150

select inst_id, sql_text

from gv$sqltext

where sql_id = '&sqlid'

order by inst_id,piece

/*
findsql.sql
Example: @findsql.sql employee_contact
Copyright dbaparadise.com
*/
set define '&'
define sql_str=&1
col sql_id for A15
col sql_text for A150 word_wrapped
set linesize 170
set pagesize 300
SELECT /* findsql */ sql_id, executions, sql_text
FROM gv$sql
WHERE command_type IN (2,3,6,7,189)
AND UPPER(sql_text) LIKE UPPER('%&sql_str%')
AND UPPER(sql_text) NOT LIKE UPPER('%findsql%')
/
undef sql_str

findsql.sql

Example: @findsql.sql employee_contact

set define '&'

define sql_str=&1

col sql_id for A15

col sql_text for A150 word_wrapped

set linesize 170

set pagesize 300

SELECT /* findsql */ sql_id, executions, sql_text

FROM gv$sql

WHERE command_type IN (2,3,6,7,189)

AND UPPER(sql_text) LIKE UPPER('%&sql_str%')

AND UPPER(sql_text) NOT LIKE UPPER('%findsql%')

undef sql_str

/*
sqlplan.sql
Copyright dbaparadise.com
Example: @sqlplan.sql 1t8v91nxtxgjr
*/
set define '&'
define sqlid=&1
col ELAPSED for 99,990.999
col CPU for 99,990.999
col ROWS_PROC for 999,999,990
col LIO for 9,999,999,990
col PIO for 99,999,990
col EXECS for 999,990
col sql_text for a40 trunc
set lines 200
set pages 300
select inst_id,sql_id,child_number child_num ,plan_hash_value,
round(ELAPSED_TIME/1000000/greatest(EXECUTIONS,1),3) ELAPSED,
round(CPU_TIME/1000000/greatest(EXECUTIONS,1),3) CPU,EXECUTIONS EXECS,
BUFFER_GETS/greatest(EXECUTIONS,1) lio,
DISK_READS/greatest(EXECUTIONS,1) pio,
ROWS_PROCESSED/greatest(EXECUTIONS,1) ROWS_PROC, sql_text
from gv$sql
where sql_id = '&sqlid'
order by inst_id, sql_id, child_number
/
select plan_table_output from table(dbms_xplan.display_cursor('&sqlid',NULL,'ADVANCED -PROJECTION -BYTES
RUNSTATS_LAST'));

sqlplan.sql

Example: @sqlplan.sql 1t8v91nxtxgjr

set define '&'

define sqlid=&1

col ELAPSED for 99,990.999

col CPU for 99,990.999

col ROWS_PROC for 999,999,990

col LIO for 9,999,999,990

col PIO for 99,999,990

col EXECS for 999,990

col sql_text for a40 trunc

set lines 200

set pages 300

select inst_id,sql_id,child_number child_num ,plan_hash_value,

round(ELAPSED_TIME/1000000/greatest(EXECUTIONS,1),3) ELAPSED,

round(CPU_TIME/1000000/greatest(EXECUTIONS,1),3) CPU,EXECUTIONS EXECS,

BUFFER_GETS/greatest(EXECUTIONS,1) lio,

DISK_READS/greatest(EXECUTIONS,1) pio,

ROWS_PROCESSED/greatest(EXECUTIONS,1) ROWS_PROC, sql_text

from gv$sql

where sql_id = '&sqlid'

order by inst_id, sql_id, child_number

select plan_table_output from table(dbms_xplan.display_cursor('&sqlid',NULL,'ADVANCED -PROJECTION -BYTES

RUNSTATS_LAST'));

/*
sqlplan_hist.sql
Copyright dbaparadise.com
Example: @sqlplan_hist.sql 1t8v91nxtxgjr
WARNING! Diagnistic and Tuning Pack licensing is required to run this script!!!
*/
set linesize 200
set pagesize 200
set verify off
set define '&'
define sqlid=&1
select plan_table_output from table(dbms_xplan.display_awr('&sqlid'))

sqlplan_hist.sql

Example: @sqlplan_hist.sql 1t8v91nxtxgjr

WARNING! Diagnistic and Tuning Pack licensing is required to run this script!!!

set linesize 200

set pagesize 200

set verify off

set define '&'

define sqlid=&1

select plan_table_output from table(dbms_xplan.display_awr('&sqlid'))

/*
historical_runs.sql
Copyright dbaparadise.com
Example @historical_runs.sql sql_id
*/
set linesize 200
set pagesize 200
set verify off
set define '&'
define sqlid=&1
col execs for 999,999,999
col avg_etime for 999,999.999
col avg_lio for 999,999,999.9
col begin_interval_time for a30
col node for 9999
break on plan_hash_value skip 1
select sh.snap_id, sh.instance_number inst, sh.begin_interval_time, s.sql_id, s.plan_hash_value,
nvl(s.executions_delta,0) execs,
(s.elapsed_time_delta/decode(nvl(s.executions_delta,0),0,1,s.executions_delta))/1000000 avg_etime,
(s.buffer_gets_delta/decode(nvl(s.buffer_gets_delta,0),0,1,s.executions_delta)) avg_lio
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SH
where s.sql_id = '&sqlid'
and sh.snap_id = s.snap_id
and sh.instance_number = s.instance_number
order by 1, 2, 3
/

historical_runs.sql

Example @historical_runs.sql sql_id

set linesize 200

set pagesize 200

set verify off

set define '&'

define sqlid=&1

col execs for 999,999,999

col avg_etime for 999,999.999

col avg_lio for 999,999,999.9

col begin_interval_time for a30

col node for 9999

break on plan_hash_value skip 1

select sh.snap_id, sh.instance_number inst, sh.begin_interval_time, s.sql_id, s.plan_hash_value,

nvl(s.executions_delta,0) execs,

(s.elapsed_time_delta/decode(nvl(s.executions_delta,0),0,1,s.executions_delta))/1000000 avg_etime,

(s.buffer_gets_delta/decode(nvl(s.buffer_gets_delta,0),0,1,s.executions_delta)) avg_lio

from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SH

where s.sql_id = '&sqlid'

and sh.snap_id = s.snap_id

and sh.instance_number = s.instance_number

order by 1, 2, 3

DevOps

7 years ago by KTEXPERTS TEAMPublic_Articles

DevOps SNO ARTICLE LINK DESCRIPTION 1 https://www.ktexperts.com/devops-maven-introduction/ DevOps : Maven introduction 2 https://www.ktexperts.com/puppet-enterprise-2016-x-puppet-agent-installation/ Puppet Enterprise 2016.x : Puppet Agent installation 3 https://www.ktexperts.com/puppet-enterprise-2016-x-start-stop-scripts/ Puppet Enterprise 2016.x : Start & Stop scripts Thank You … We will update more ….

ORACLE APPS DBA

7 years ago by KTEXPERTS TEAMPublic_Articles

ORACLE APPS DBA SNO ARTICLE LINK DESCRIPTION 1 https://www.ktexperts.com/startup-shutdown-process-in-oracle-apps-in-r12/ Startup & Shutdown process in Oracle Apps in R12. 2 https://www.ktexperts.com/oracle_apps-how-to-kill-runaway-processes-after-terminating-concurrent-request/ Oracle_Apps: How To Kill runaway processes After Terminating Concurrent Request 3 https://www.ktexperts.com/oracle_apps-performance-issue-with-particular-form/ Oracle_Apps: Performance issue with particular form 4 https://www.ktexperts.com/oracle_apps-performance-issue-with-particular-form/ Oracle_Apps: Performance issue with particular… Read More

Oracle RAC Articles

7 years ago by Binnary Ajay KumarORACLE RAC

Oracle RAC ARTICLES Dear Readers, Please check below articles to get good knowledge on Oracle RAC. SNO ARTICLE LINK DESCRIPTION 1 https://www.ktexperts.com/network-issue-during-oracle-12c-rac-installation/ Network issue during Oracle 12c RAC installation 2 https://www.ktexperts.com/ssh-connectivity-methods-in-oracle-rac/ SSH Connectivity methods in Oracle RAC 3 https://www.ktexperts.com/step-by-step-oracle-12c-rac-installation/ Step by Step Oracle 12c RAC… Read More

SQL SERVER DBA

7 years ago by KTEXPERTS TEAMPublic_Articles

SQL SERVER DBA ARTCILES. SNO ARTICLE LINK DESCRIPTION 1 https://www.ktexperts.com/sql-server-issue-to-view-alwayson-health-events/ SQL Server – Issue to view Always on Health Events 2 https://www.ktexperts.com/script-to-copy-one-particular-database-role-from-one-sql-server-to-another/ Script to copy one particular database role from one SQL server to another 3 https://www.ktexperts.com/sql-server-enable-instant-file-initialization/ SQL Server : Enable Instant File Initialization 4… Read More

CASSANDRA ARTICLES.

7 years ago by KTEXPERTS TEAMPublic_Articles

CASSANDRA ARTICLES Dear Readers, Please check below articles to get good knowledge on Cassandra. SNO ARTICLE LINK DESCRIPTION 1 https://www.ktexperts.com/cassandra-cassandra-overviewdemo/ Cassandra : Cassandra overview/Demo 2 https://www.ktexperts.com/cassandra-introduction/ Cassandra Introduction 3 https://www.ktexperts.com/cassandra-similarities-and-differences-between-the-sql-and-cql-languages/ Cassandra-Similarities and Differences between the SQL and CQL languages 4 https://www.ktexperts.com/cassandra-data-distribution-and-replication-part-2/ Cassandra Data distribution and… Read More

Oracle 12c New features

7 years ago by KTEXPERTS TEAMOracle 12C New Features

Dear Readers, Please check the below articles to get good knowledge on Oracle 12c New features. SNO Article Name Article Link 1 Database Archiving in Oracle Database 12c https://www.ktexperts.com/database-archiving-in-oracle-database-12c/ 2 RMAN Commands in Oracle 12c https://www.ktexperts.com/rman-commands-in-oracle-12c/ 3 Moving Online Data files in Oracle 12c https://www.ktexperts.com/moving-online-data-files-in-oracle-12c/… Read More

Oracle DBA Articles

7 years ago by KTEXPERTS TEAMPublic_Articles

Oracle DBA Articles Dear Readers, Please check below articles to get good knowledge on Oracle DBA. Thanks for browsing we will update more………… SNO ARTICLE LINK DESCRIPTION ORACLE ARCHITECTURE ARTICLES 1 https://www.ktexperts.com/oracle-architecture-1/ Oracle log & phy level 2 https://www.ktexperts.com/oracle-architecture-2/ Oracle server arch 3 https://www.ktexperts.com/oracle-architecture-3/… Read More

DIFFERENCE BETWEEN ORACLE AND MySQL

7 years ago by KTEXPERTS TEAMPublic_Articles

DIFFERENCE BETWEEN ORACLE AND MySQL. SNO ARTICLE LINK DESCRIPTION 1 https://www.ktexperts.com/difference-between-oracle-and-mysql-01/ DIFFERENCE BETWEEN ORACLE AND MySQL -01 2 https://www.ktexperts.com/difference-between-oracle-and-mysql-02/ DIFFERENCE BETWEEN ORACLE AND MySQL -02 3 https://www.ktexperts.com/difference-between-oracle-and-mysql-03/ DIFFERENCE BETWEEN ORACLE AND MySQL -03 4 https://www.ktexperts.com/difference-between-oracle-and-mysql-04/ DIFFERENCE BETWEEN ORACLE AND MySQL -04 5 https://www.ktexperts.com/difference-between-oracle-and-mysql-05/ DIFFERENCE BETWEEN… Read More