Skip to content
pvmehta.com

pvmehta.com

  • Home
  • About Me
  • Toggle search form
  • Good notes for shared pool Oracle
  • Find_planinfo.sql Oracle
  • Rman Notes -1 Oracle
  • 556976.1 Oracle Clusterware: Components installed Oracle
  • Renaming Global Name GLOBAL_NAME Oracle
  • create a folder in multiple places Linux/Unix
  • To find explain plan for a statement that occurred in past. Oracle
  • Monitor and Trace Unix processes using truss Linux/Unix
  • AWR license Oracle
  • Locktree.sql Oracle
  • Find nth max and min. Oracle
  • move_arch_files.ksh Linux/Unix
  • Jai Shree Ram Oracle
  • How to analyze statspack or AWR report. Oracle
  • Load testing on Oracle 19C RAC with HammerDB 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

  • import-export with multiple files Oracle
  • Privilege to describe the table. Oracle
  • Updated LCK.SQL file. Oracle
  • VIvek Encryption Package and Its Usage Oracle
  • FGA Part-I Oracle
  • 284785.1 How to check RAC Option is currently linked into the Oracle Binary 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 (388)
  • PHP/MYSQL/Wordpress (10)
  • Power-BI (0)
  • Python/PySpark (7)
  • RAC (17)
  • rman-dataguard (26)
  • shell (149)
  • SQL scripts (337)
  • Uncategorized (0)
  • Videos (0)

Recent Posts

  • 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
  • Reading config file from other folder inside class24-Sep-2024
  • Python class import from different folders22-Sep-2024
  • Transfer SQL Profiles from One database to other database.05-Sep-2024
  • Load testing on Oracle 19C RAC with HammerDB18-Jan-2024

Archives

  • 2025
  • 2024
  • 2023
  • 2010
  • 2009
  • 2008
  • 2007
  • 2006
  • 2005
  • DBMS_UTILITY PACKAGE Oracle
  • Oracle Recommended Patches — Oracle Database ID 756671.1 Oracle
  • Gather Stats manually using DBMS_STATS after disabling DBMS_SCHEDULER jobs as previous entry Oracle
  • Sending email with file attachment. Linux/Unix
  • find_longsql.sql Oracle
  • TRUNCATE Privs Oracle
  • default permission on ~/.ssh/authorized_keys2 or authorized_keys Linux/Unix
  • checking redhat linux version Linux/Unix

Copyright © 2025 pvmehta.com.

Powered by PressBook News WordPress theme