Queries running on database

-

 

What are the queries that are running? 


select sesion.sid,        sesion.username,        optimizer_mode,        hash_value,        address,        cpu_time,        elapsed_time, 

       sql_text 

  from v$sqlarea sqlarea, v$session sesion  where sesion.sql_hash_value = sqlarea.hash_value    and sesion.sql_address    = sqlarea.address    and sesion.username is not null 

/



Get the rows fetched, if there is difference it means processing is happening 

select b.name, a.value vlu from v$sesstat a, v$statname b where a.statistic# = b.statistic# 

and sid =&sid and a.value != 0 and b.name like '%row%' 

/ 



Get the sql_hash_value 

select sql_hash_value from v$session where sid='&sid'; 



SQL> select sql_hash_value from v$session where sid='&sid'; 

Enter value for sid: 1075 

old   1: select sql_hash_value from v$session where sid='&sid' 

new   1: select sql_hash_value from v$session where sid='1075' 

 

SQL_HASH_VALUE 

-------------- 

     928832585 



Get the sql_Text  

 

SQL> select sql_text v$sql from  v$sql where hash_value =&Enter_Hash_Value; Enter value for enter_hash_value: 928832585 




Get the explain_plan 


set lines 190 

col XMS_PLAN_STEP format a40 

set pages 100 

 

select       case when access_predicates is not null then 'A' else ' ' end ||     case when filter_predicates is not null then 'F' else ' ' end xms_pred,     id        xms_id, 

    lpad(' ',depth*1,' ')||operation || ' ' || options xms_plan_step,      object_name                   xms_object_name, 

    cost                          xms_opt_cost,     cardinality                   xms_opt_card,     bytes                         xms_opt_bytes, 

    optimizer                     xms_optimizer from  

    v$sql_plan      where  

  hash_value in (&SQL_HASH_VALUE)   and to_char(child_number) like '%'; 




ONE SCRIPT TO IDENTIFY ALL PERFORMANCE ISSUE RELATED TO SQL QUERY



ONE SCRIPT TO IDENTIFY ALL PERFORMANCE ISSUE RELATED TO                            SQL QUERY  

================================================================

 

Set echo off set trimspool on set define on

column filename new_val filename

select to_char(sysdate, 'yyyymmdd-hh-mi-ss' ) filename from dual; column dbname new_value dbname noprint select name dbname from v$pdbs; spool &dbname-&filename..txt

 

      ================== DB INFO ==================

 

set lines 750 pages 9999

select name CDB_NAME,(select name from v$pdbs) PDB_NAME,database_role from v$database;

select INSTANCE_NAME,HOST_NAME,logins,VERSION from v$instance;

 

 ==================       TOTALCONNECTIONS     ==================

 

set lines 750 pages 9999 break on report

compute SUM of tot on report compute SUM of active on report compute SUM of inactive on report col username for a50

select DECODE(username,NULL,'INTERNAL',USERNAME) Username, count(*) TOT,

COUNT(DECODE(status,'ACTIVE',STATUS)) ACTIVE, COUNT(DECODE(status,'INACTIVE',STATUS)) INACTIVE from gv$session

where status in ('ACTIVE','INACTIVE') group by username;

 

 ================     SESSIONS DETAILS        ================

 

set linesize 750 pages 9999 column box format a30 col serial# for 999999 column spid format a10 column username format a30 column program format a30 column os_user format a20 col LOGON_TIME for a20

 

select b.inst_id,b.sid,b.serial#,a.spid, substr(b.machine,1,30) box,to_char

(b.logon_time, 'dd-mon-yyyy hh24:mi:ss') logon_time, substr(b.username,1,30) username, substr(b.osuser,1,20) os_user,

substr(b.program,1,30) program,status,b.last_call_et AS last_call_et_secs,b.sql_id from gv$session b,gv$process a

where b.paddr = a.addr and a.inst_id = b.inst_id and type='USER' order by b.inst_id,b.sid;

 

 ================     SQL DETAIL’S       ================

 

column sid format 9999 column username format a15 column PARSING_SCHEMA_NAME format a15 column sql_text format a50 column module format a35 select

a.inst_id,a.sid,a.username,b.PARSING_SCHEMA_NAME,a.module,a.sql_id,a.sql _child_number child,b.hash_value,to_char (a.sql_exec_start, 'dd-Mon-yyyy

hh24:mi:ss') sql_exec_start,(sysdate-sql_exec_start)*24*60*60 SECS,b.rows_processed,a.status,substr(b.sql_text,1,50) sql_text from gv$session a,gv$sqlarea b where a.sql_hash_value = b.hash_value and a.sql_address = b.address and a.module not like '%emagent%' and a.module not like '%oraagent.bin%' and a.username is not null order by a.status;

 

 

 

 ==================  SQL MONITER REPORT  =========================

 

column text_line format a1000 set lines 750 pages 9999 set long 20000 longchunksize 20000

select dbms_sqltune.report_sql_monitor_list() text_line from dual;

 

 ============    SQL MONITER – EXECUTING    =========================

 

set lines 1000 pages 9999 column sid format 9999 column serial for 999999 column status format a15 column username format a10 column sql_text format a80 column module format a30 col program for a30 col SQL_EXEC_START for a20

 

SELECT * FROM

(SELECT status,inst_id,sid,SESSION_SERIAL# as Serial,username,sql_id,SQL_PLAN_HASH_VALUE, program,

TO_CHAR(sql_exec_start,'dd-mon-yyyy hh24:mi:ss') AS sql_exec_start,

ROUND(elapsed_time/1000000) AS "Elapsed (s)", ROUND(cpu_time /1000000) AS "CPU (s)", substr(sql_text,1,30) sql_text

FROM gv$sql_monitor where status='EXECUTING' and module not like '%emagent%'

ORDER BY sql_exec_start desc

);

 

 ================   BLOCKING SESSIONS     ================

 

set lines 750 pages 9999 col blocking_status for a100

select s1.inst_id,s2.inst_id,s1.username || '@' || s1.machine

|| ' ( SID=' || s1.sid || ' ) is blocking '

|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status

from gv$lock l1, gv$session s1, gv$lock l2, gv$session s2 where s1.sid=l1.sid and s2.sid=l2.sid and s1.inst_id=l1.inst_id and s2.inst_id=l2.inst_id and l1.BLOCK=1 and l2.request > 0 and l1.id1 = l2.id1 and l2.id2 = l2.id2 order by s1.inst_id;

 

 ==============    MORE DETAIL      ==============

 

accept sid default '' -  'Please provide the sid: ' accept inst_id default '' -  'Please provide the inst_id: '  ================   SID DETAILS     ================

 

column box format a30 column spid format a10 column username format a20 column program format a30 column os_user format a20 col LOGON_TIME for a20

 

select b.inst_id,b.sid,b.serial#,a.spid, substr(b.machine,1,30) box,to_char

(b.logon_time, 'dd-mon-yyyy hh24:mi:ss') logon_time, substr(b.username,1,20) username, substr(b.osuser,1,20) os_user,

substr(b.program,1,30) program,status,b.last_call_et AS last_call_et_secs,b.sql_id from gv$session b,gv$process a where b.paddr = a.addr and a.inst_id = b.inst_id and type='USER' and b.sid=&sid and b.inst_id=&inst_id order by logon_time;

 

 

 

 

column sid format 9999

column username format a15 column PARSING_SCHEMA_NAME format a15 column sql_text format a50 column module format a35 select

a.inst_id,a.sid,a.username,b.PARSING_SCHEMA_NAME,a.module,a.sql_id,a.sql _child_number child,b.hash_value,to_char (a.sql_exec_start, 'dd-Mon-yyyy

hh24:mi:ss') sql_exec_start,(sysdate-sql_exec_start)*24*60*60 SECS,b.rows_processed,a.status,substr(b.sql_text,1,50) sql_text from gv$session a,gv$sqlarea b where a.sql_hash_value = b.hash_value and a.sql_address = b.address and a.sid=&sid and a.inst_id=&inst_id and a.module not like '%emagent%' and a.module not like '%oraagent.bin%' and a.username is not null order by a.status;

 

accept sql_id default '' -  'Please provide the sql_id: '

 

 ================   SID WATING ON     ================

 

COLUMN username FORMAT A20

COLUMN sid FORMAT 9999

COLUMN serial# FORMAT 999999

COLUMN event FORMAT A40

 

SELECT NVL(s.username, '(oracle)') AS username,

s.sid,

s.serial#, se.event, se.total_waits, se.total_timeouts, se.time_waited, se.average_wait, se.max_wait, se.time_waited_micro FROM gv$session_event se, gv$session s

WHERE s.sid = se.sid AND s.sid = &sid and s.inst_id=se.inst_id and s.inst_id=&inst_id

ORDER BY se.time_waited DESC

/

 

col WAIT_CLASS for a10

SELECT sw.inst_id,NVL(s.username, '(oracle)') AS username,

s.sid,

s.serial#, sw.event, sw.wait_class, sw.wait_time, sw.seconds_in_wait, sw.state

FROM gv$session_wait sw, gv$session s

WHERE s.sid = sw.sid and s.inst_id=sw.inst_id and s.sid=&sid and s.inst_id=&inst_id

ORDER BY sw.seconds_in_wait DESC;

 

 ================  SESSIONS LongOps  ================

 

SET VERIFY OFF

 

SELECT

a.sid,RPAD(a.opname,30),a.sofar,a.totalwork,a.ELAPSED_SECONDS,ROUND(((a. sofar)*100)/a.totalwork,3) "%_COMPLETED",time_remaining,

RPAD(a.username,10) username,a.SQL_HASH_VALUE,B.STATUS

FROM GV$SESSION_LONGOPS a, gv$session b WHERE a.sid=&sid and b.inst_id=&inst_id

AND a.sofar<> a.totalwork

/

 

 

 

 

 =============== REALTIME MONITORING FOR SID  ===========

 

SELECT *

FROM

(SELECT status, --username, sql_id, sql_exec_id,

TO_CHAR(sql_exec_start,'dd-mon-yyyy hh24:mi:ss') AS sql_exec_start,

ROUND(elapsed_time/1000000) AS "Elapsed (s)", ROUND(cpu_time /1000000) AS "CPU (s)", buffer_gets,

ROUND(physical_read_bytes /(1024*1024)) AS "Phys reads (MB)",

ROUND(physical_write_bytes/(1024*1024)) AS "Phys writes (MB)"

FROM gv$sql_monitor where sid=&sid and inst_id=&inst_id

ORDER BY elapsed_time DESC

)

WHERE rownum<=20;

 

SELECT ROUND(elapsed_time /1000000) AS "Elapsed (s)",

ROUND(cpu_time /1000000,3) AS "CPU (s)",

ROUND(queuing_time /1000000,3) AS "Queuing (s)",

ROUND(user_io_wait_time /1000000,3) AS "I/O wait (s)",

ROUND(application_wait_time/1000000,3) AS "Appli wait (s)",

ROUND(concurrency_wait_time/1000000,3) AS "Concurrency wait (s)",

ROUND(cluster_wait_time /1000000,3) AS "Cluster wait (s)", ROUND(physical_read_bytes /(1024*1024)) AS "Phys reads (MB)", ROUND(physical_write_bytes /(1024*1024)) AS "Phys writes (MB)", buffer_gets AS "Buffer gets",

ROUND(plsql_exec_time/1000000,3) AS "Plsql exec (s)",

ROUND(java_exec_time /1000000,3) AS "Java exec (s)"

FROM gv$sql_monitor

WHERE sid=&sid and inst_id=&inst_id;

 

 ================== SQL_MONITOR REPORT   ===============

 

set pagesize 0 echo off timing off linesize 1000 trimspool on trim on long

2000000 longchunksize 2000000 select

DBMS_SQLTUNE.REPORT_SQL_MONITOR(

sql_id=>'&sql_id', report_level=>'ALL', type=>'TEXT') from dual;

 

 ===============  FULL SQL STATEMENT ===============

 

set lines 1000 pages 9999 set long 20000 col sql_text for a500

select sql_text from dba_hist_sqltext where sql_id = '&sql_id';  ================   BIND VARIABLES ================

 

col VALUE_STRING for a50

SELECT NAME,POSITION,DATATYPE_STRING,VALUE_STRING FROM gv$sql_bind_capture WHERE sql_id='&sql_id' and inst_id=&inst_id;

 

 ================  SQL HISTORY     ================

 

set lines 1000 pages 9999

COL instance_number FOR 9999 HEA 'Inst';

COL end_time HEA 'End Time';

COL plan_hash_value HEA 'Plan|Hash Value';

COL executions_total FOR 999,999 HEA 'Execs|Total';

COL rows_per_exec HEA 'Rows Per Exec';

COL et_secs_per_exec HEA 'Elap Secs|Per Exec';

COL cpu_secs_per_exec HEA 'CPU Secs|Per Exec';

COL io_secs_per_exec HEA 'IO Secs|Per Exec';

COL cl_secs_per_exec HEA 'Clus Secs|Per Exec';

COL ap_secs_per_exec HEA 'App Secs|Per Exec';

COL cc_secs_per_exec HEA 'Conc Secs|Per Exec';

COL pl_secs_per_exec HEA 'PLSQL Secs|Per Exec';

COL ja_secs_per_exec HEA 'Java Secs|Per Exec';

SELECT 'gv$dba_hist_sqlstat' source,h.instance_number,

TO_CHAR(CAST(s.begin_interval_time AS DATE), 'DD-MM-YYYY HH24:MI') snap_time,

TO_CHAR(CAST(s.end_interval_time AS DATE), 'DD-MM-YYYY HH24:MI') end_time,

h.sql_id,

h.plan_hash_value,

h.executions_total,

TO_CHAR(ROUND(h.rows_processed_total / h.executions_total), '999,999,999,999') rows_per_exec,

TO_CHAR(ROUND(h.elapsed_time_total / h.executions_total / 1e6, 3), '999,990.000') et_secs_per_exec,

TO_CHAR(ROUND(h.cpu_time_total / h.executions_total / 1e6, 3), '999,990.000') cpu_secs_per_exec,

TO_CHAR(ROUND(h.iowait_total / h.executions_total / 1e6, 3), '999,990.000') io_secs_per_exec,

TO_CHAR(ROUND(h.clwait_total / h.executions_total / 1e6, 3), '999,990.000') cl_secs_per_exec,

TO_CHAR(ROUND(h.apwait_total / h.executions_total / 1e6, 3), '999,990.000') ap_secs_per_exec,

TO_CHAR(ROUND(h.ccwait_total / h.executions_total / 1e6, 3), '999,990.000') cc_secs_per_exec,

TO_CHAR(ROUND(h.plsexec_time_total / h.executions_total / 1e6, 3), '999,990.000') pl_secs_per_exec,

TO_CHAR(ROUND(h.javexec_time_total / h.executions_total / 1e6, 3),

'999,990.000') ja_secs_per_exec FROM dba_hist_sqlstat h, dba_hist_snapshot s

WHERE h.sql_id = '&sql_id'

AND h.executions_total > 0

AND s.snap_id = h.snap_id

AND s.dbid = h.dbid

AND s.instance_number = h.instance_number

UNION ALL

SELECT 'gv$sqlarea_plan_hash' source,h.inst_id,

TO_CHAR(sysdate, 'DD-MM-YYYY HH24:MI') snap_time, TO_CHAR(sysdate, 'DD-MM-YYYY HH24:MI') end_time,

h.sql_id,

h.plan_hash_value,

h.executions,

TO_CHAR(ROUND(h.rows_processed / h.executions), '999,999,999,999') rows_per_exec,

TO_CHAR(ROUND(h.elapsed_time / h.executions / 1e6, 3), '999,990.000') et_secs_per_exec,

TO_CHAR(ROUND(h.cpu_time / h.executions / 1e6, 3), '999,990.000') cpu_secs_per_exec,

TO_CHAR(ROUND(h.USER_IO_WAIT_TIME / h.executions / 1e6, 3), '999,990.000') io_secs_per_exec,

TO_CHAR(ROUND(h.CLUSTER_WAIT_TIME / h.executions / 1e6, 3), '999,990.000') cl_secs_per_exec,

TO_CHAR(ROUND(h.APPLICATION_WAIT_TIME / h.executions / 1e6, 3), '999,990.000') ap_secs_per_exec,

TO_CHAR(ROUND(h.CLUSTER_WAIT_TIME / h.executions / 1e6, 3), '999,990.000') cc_secs_per_exec,

TO_CHAR(ROUND(h.PLSQL_EXEC_TIME / h.executions / 1e6, 3), '999,990.000') pl_secs_per_exec,

TO_CHAR(ROUND(h.JAVA_EXEC_TIME / h.executions / 1e6, 3), '999,990.000') ja_secs_per_exec

FROM gv$sqlarea_plan_hash h WHERE h.sql_id = '&sql_id' and h.inst_id=&inst_id

AND h.executions > 0 order by source ;

 

 ==================== SQL_ID WAITING ON ?  ============

 

select sql_id,event, time_waited "time_waited(s)", case when time_waited = 0 then

0 else

round(time_waited*100 / sum(time_waited) Over(), 2) end "percentage" from

(

select sql_id,event, sum(time_waited) time_waited from gv$active_session_history where sql_id = '&sql_id' and inst_id=&inst_id group by sql_id,event

)

order by time_waited desc; ===============    OBJECT STATISTICS ====================

 

col table_name for a40 col owner for a30

select distinct owner, table_name, STALE_STATS, last_analyzed, stattype_locked from dba_tab_statistics where (owner, table_name) in (select distinct owner, table_name from dba_tables where ( table_name) in ( select object_name from gv$sql_plan

where upper(sql_id) = upper('&sql_id') and inst_id=&inst_id and object_name is not null))

--and STALE_STATS='YES'

/

 

col index_name for a50

SELECT owner, index_name, table_name,last_analyzed, sample_size, num_rows, partitioned, global_stats

FROM dba_indexes

WHERE index_name IN (

select distinct rtrim(substr(plan_table_output, instr(plan_table_output, '|', 1, 3)+2, (instr(plan_table_output, '|', 1, 4)-instr(plan_table_output, '|', 1, 3)-2)), '

')

from (

SELECT plan_table_output

FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id', null, 'BASIC'))

UNION ALL

SELECT * FROM TABLE(dbms_xplan.display_awr('&sql_id', null, null, 'ALL'))

)

where plan_table_output like '%INDEX%'

)

ORDER BY owner, table_name, index_name

/

 

 ===================  EXPLAIN PLAN FROM MEMORY  ================

 

col PLAN FOR a150

SELECT

RPAD('(' || p.plan_line_ID || ' ' || NVL(p.plan_parent_id,'0') || ')',8) || '|' ||

RPAD(LPAD (' ', 2*p.plan_DEPTH) || p.plan_operation || ' ' || p.plan_options,60,'.') ||

NVL2(p.plan_object_owner||p.plan_object_name, '(' ||

p.plan_object_owner|| '.' || p.plan_object_name || ') ', '') ||

NVL2(p.plan_COST,'Cost:' || p.plan_COST,'') || ' ' ||

NVL2(p.plan_bytes||p.plan_CARDINALITY,'(' || p.plan_bytes || ' bytes, ' || p.plan_CARDINALITY || ' rows)','') || ' ' ||

NVL2(p.plan_partition_start || p.plan_partition_stop,' PStart:' || p.plan_partition_start || ' PStop:' || p.plan_partition_stop,'') ||

NVL2(p.plan_time, p.plan_time || '(s)','') AS PLAN

FROM gv$sql_plan_monitor p

WHERE sid=&sid

and p.inst_id=&inst_id

ORDER BY p.plan_line_id, p.plan_parent_id;

 

select * from table(dbms_xplan.display_cursor('&sql_id', NULL, 'ALLSTATS LAST'));

 

 ===================  EXPLAIN PLAN FROM AWR ====================

 

select * from table(dbms_xplan.display_awr('&sql_id', NULL, null, 'ALLSTATS LAST'));

 

 ====================  SQL PROFILES  ===================

 

set lines 1000 pages 9999 col name for a30 col task_exec_name for a16 col category for a10 col created for a30 col sql_text for a150

col signature for 9999999999999999999999999

 

select sql.sql_id,sql.child_number as child , prof.name, prof.category, prof.created,prof.task_exec_name,prof.FORCE_MATCHING,prof.status,prof.SIG NATURE from

dba_sql_profiles prof, gv$sql sql

where sql.sql_id in ('&sql_id')

-- and sql.child_number=child_number

-- and sql.force_matching_signature=prof.SIGNATURE order by created;

 

 ================   SQL BASELINES     ================

 

col SQL_HANDLE for a30 col origin for a16 col last_modified for a30 col last_verified for a30

 

select sql_handle, plan_name, origin, created, last_modified, last_verified,ENABLED,ACCEPTED,FIXED,REPRODUCED from dba_sql_plan_baselines

where signature in (select force_matching_signature from gv$sql where sql_id='&sql_id' and inst_id=&inst_id);

 

undef sid undef sql_id undef inst_id spool off;

 

 

Comments

Post a Comment

Popular posts from this blog

MY NOTEPAD

-
set lines 200 pages 200; col END_TIME for a20; col ELAPSED_TIME for a25; col START_TIME for a20 SELECT T.SESSION_KEY, T.INPUT_TYPE, T.STATUS, T.SUM_BACKUP_PIECES_IN_GB, T.START_TIME, T.END_TIME, T.SECONDS ELAPSED_TIME FROM ( SELECT SESSION_KEY, INPUT_TYPE, STATUS, OUTPUT_BYTES/1024/1024/1024 SUM_BACKUP_PIECES_IN_GB, TO_CHAR (START_TIME, 'dd/mm/yyyy hh24:mi') START_TIME, TO_CHAR (END_TIME, 'dd/mm/yyyy hh24:mi') END_TIME, TIME_TAKEN_DISPLAY SECONDS FROM V$RMAN_BACKUP_JOB_DETAILS WHERE INPUT_TYPE='ARCHIVELOG' ORDER BY SESSION_KEY DESC) T WHERE ROWNUM <= 1; ================ ASM disk group free space ============== set sqlblanklines on set lines 180 set pages 999le col "Group Name" form a25 col "Disk Name" form a30 col "State" form a15 ...
Read more

Oracle OEM Holistic Patch

-
  Oracle OEM Holistic Patch  The Concept Starting with Oracle Enterprise Manager (OEM) 13.5 RU24 (October 2024), Oracle introduced the Holistic Patch for OEM — a unified patching model that consolidates several separate updates into a single coordinated package. Previously, administrators had to patch: * The OMS and Agents with the quarterly Release Update (RU). * OEM Application via the System Patch Bundle (SPB). * WebLogic CPU and JDK manually. There was an element of complication because of the need to keep the solution homogeneous. With the Holistic Patch, the last two steps are merged into a single patch bundle. Now we have a new Holistic patch for OEM that is released with each new OMS Release Update. Prerequisites Apply RU 24 Patch  PATCH 36761595  /later on all OMSes before applying Oct Holistic Patch   PATCH 37305292             (OR) Apply RU 25 Patch  PATCH 37057635  /later on all OMSes before applying ...
Read more

Upgrade Oracle 19c DB to 21c using Auto Upgrade utility inprogress

-
Image
  Upgrade Oracle 11g to 21c using AutoUpgrade utility: How do I upgrade Oracle database to 21c?  install the 21c oracle binaries into the server . Oracle Virtual Bix  Oracle Linux 7 Oracle Database 19c currently running Upgrading 19c database to 21c directly Oracle Database 19c on premises is available – and one of the key features is the new Auto Upgrade utility. In the following days I will publish several blog posts explaining and showcasing the new Auto Upgrade. What is the Auto Upgrade? The Oracle Database Auto Upgrade utility is a new tiny little command line tool which allows you to upgrade your databases in an unattended way. I call it the Hands-Free Upgrade. The new Auto Upgrade utility in Oracle 19che idea of the tool is to run the prechecks against multiple databases, fix 99% of the potential issues, set a restore point in case something goes wrong – and then upgrade your databases. And of course, do the post upgrade, recompilation and time zone adjustment. The...
Read more