Skip to content
pvmehta.com

pvmehta.com

  • Home
  • About Me
  • Toggle search form
  • Oracle Material from OTN Oracle
  • Oracle 10g for solaris 10 Oracle
  • Oracle 10g Installation/Applying Patches Tips Oracle
  • Configure ssh authentications for RAC Oracle
  • default permission on ~/.ssh/authorized_keys2 or authorized_keys Linux/Unix
  • Oracle 11g Training on 29JAN1010 Oracle
  • online_ts_bkup.sql Oracle
  • reset Sequence Oracle
  • Optimizer_Index init.ora parameter explaination. Oracle
  • How to specify 2 arch location to avoid any kind of DB hanging. Oracle
  • Absolute file number and relative file number Oracle
  • Standby Database File Management in 10g with STANDBY_FILE_MANAGEMENT Oracle
  • handling filenname with space Linux/Unix
  • Good Oracle Architecture In Short and point to point Oracle
  • Good Doc 28-JUN-2006 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

  • When error comes for temporary tablespace with version <= 9i Oracle
  • RAC with RHEL4 and 11g Oracle
  • Set Role explaination. Oracle
  • Does DBMS_JOB recompute the NEXT_DATE interval after or before Oracle
  • Example of How To Resize the Online Redo Logfiles Note:1035935.6 Oracle
  • get_vmstat_linux Oracle

Leave a Reply Cancel reply

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

Categories

  • AWS (2)
  • Azure (1)
  • Linux/Unix (149)
  • Oracle (392)
  • PHP/MYSQL/Wordpress (10)
  • Power-BI (0)
  • Python/PySpark (7)
  • RAC (17)
  • rman-dataguard (26)
  • shell (149)
  • SQL scripts (341)
  • Uncategorized (0)
  • Videos (0)

Recent Posts

  • load SPM baseline from cursor cache05-Jun-2025
  • Drop all SPM baselines for SQL handle05-Jun-2025
  • Load SPM baseline from AWR05-Jun-2025
  • Drop specific SQL plan baseline – spm05-Jun-2025
  • findinfo.sql (SQL for getting CPU and Active session info)27-May-2025
  • SQL Tracker by SID sqltrackerbysid.sql22-Apr-2025
  • How to connect to Oracle Database with Wallet with Python.21-Mar-2025
  • JSON/XML Types in Oracle18-Mar-2025
  • CPU Core related projections12-Mar-2025
  • Exadata Basics10-Dec-2024

Archives

  • 2025
  • 2024
  • 2023
  • 2010
  • 2009
  • 2008
  • 2007
  • 2006
  • 2005
  • sess1.sql Oracle
  • USER_TABLES.Freelists Oracle
  • Clean up Oracle
  • find_log_switch.sql Find log switches in graphical manner Oracle
  • upload.html PHP/MYSQL/Wordpress
  • good note for shared pool tunnig Oracle
  • Wait Based Tuning Step by step with SQL statement Oracle
  • copying/removing directory with all its subdirectory Linux/Unix

Copyright © 2025 pvmehta.com.

Powered by PressBook News WordPress theme