Skip to content
pvmehta.com

pvmehta.com

  • Home
  • About Me
  • Toggle search form
  • To see only files and/or folders using LS command Linux/Unix
  • top 10 AI news today Uncategorized
  • Locally Managed Tablespace and Dictionary managed tablespace (LMT-DMT) Oracle
  • normal maintenance for exp-imp and renaming table Oracle
  • proper cpu stats Linux/Unix
  • Goog notes on X$ tables Oracle
  • refre.sql Oracle
  • How to hide author name in WordPress BLOG PHP/MYSQL/Wordpress
  • Oracle 11g Training on 29JAN1010 Oracle
  • find_err.sql for finding errors from dba_errors. Oracle
  • Exadata Basics Oracle
  • TRUNCATE Privs Oracle
  • create database syntax Oracle
  • Updated LCK.SQL file. Oracle
  • pvm_metric.sql for gathering report from vmstat tables Oracle

Find Time Consuming SQL Statements in Oracle 10g

Posted on 22-Dec-2005 By Admin No Comments on Find Time Consuming SQL Statements in Oracle 10g

spool badsql5

set lines 132

set pages 300

col USR format a14

col PAR heading “Parsing ID” format 9999999

col ELA heading “ELP time(Sec)” format 9999999.0

col HASH heading “Hash Value”

col USR heading “Parsing User”

col T heading “SQL Text having Elapsed time > 1 sec”

break on HASH on USR on ELA skip 1

select to_char(a.sql_fullText) T,

(elapsed_time/1000000) “ELA”,

a.hash_value “HASH” ,

b.username “USR”, a.executions, rows_processed, a.last_load_time, a.buffer_gets, a.disk_reads, a.sorts

from v$sql a,dba_users b

where (a.elapsed_time/1000000)>1

and b.username not in (‘SYS’,’SYSTEM’,’DBSNMP’,’VIVEK’, ‘SYSMAN’)

and a.PARSING_SCHEMA_ID = b.user_id

and a.elapsed_time =(select max(elapsed_time) from v$sql g where g.hash_value = a.hash_value)

order by elapsed_time desc;

spool off

Oracle, SQL scripts

Post navigation

Previous Post: Goog notes on X$ tables
Next Post: Find All internal Parameters

Related Posts

  • ENQ: KO – FAST OBJECT CHECKPOINT tips Oracle
  • How to Modify the statistics collection by MMON for AWR repository Oracle
  • Find All internal Parameters Oracle
  • oracle tips… from http://www.bijoos.com/oracle/douknow.htm Oracle
  • initUOCIOTTO.ora Oracle
  • FRA Information. Oracle

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Categories

  • AMC10 (0)
  • Ansible (0)
  • AWS (2)
  • Azure (1)
  • Django (0)
  • GIT (1)
  • Linux/Unix (150)
  • MYSQL (5)
  • Oracle (403)
  • PHP/MYSQL/Wordpress (10)
  • POSTGRESQL (1)
  • Power-BI (0)
  • Python/PySpark (7)
  • RAC (18)
  • rman-dataguard (26)
  • shell (151)
  • SQL scripts (349)
  • SQL Server (6)
  • Uncategorized (9)
  • Videos (0)

Recent Posts

  • Tutornet.US — Source of excellent learning14-Oct-2026
  • OCI Infrastructure Patching14-Oct-2026
  • Free API credits for AI models14-Oct-2026
  • AMC1020-Sep-2026
  • Key Management in Oracle: The Core Issue: Missing Master Key12-May-2026
  • SAT Mathematics 10 questions and answer at the end.30-Apr-2026
  • top 10 AI news today30-Apr-2026
  • runon_allpdbs_show_conname.sh23-Apr-2026
  • runon_allcdbs_find_pdbs.sh23-Apr-2026
  • Running PDB on single node in RAC09-Apr-2026

Archives

  • 2026
  • 2025
  • 2024
  • 2023
  • 2010
  • 2009
  • 2008
  • 2007
  • 2006
  • 2005
  • Library cahe Latches and internal explaination Oracle
  • How to sort list of files on basis of their sizes. Linux/Unix
  • V$CONTROLFILE_RECORD_SECTION reference notes. Oracle
  • Restoring a user’s original password 1051962.101 Oracle
  • set_env_dba Linux/Unix
  • age_alert.ksh aging out alert.log Linux/Unix
  • OCI Infrastructure Patching Uncategorized
  • find_encr_wallet.sql Uncategorized

Copyright © 2026 pvmehta.com.

Powered by PressBook News WordPress theme