ORACLE Diff

SELECT
   p.spid,
   s.sid,
   s.process cli_process,
   s.STATUS,t.disk_reads,
   s.last_call_et/3600 last_call_et_Hrs,
   s.action,
   s.program,
  t.sql_text
FROM
   v$session s,
   v$sqlarea t,
   v$process p
WHERE
   s.sql_address = t.address
AND
   s.sql_hash_value = t.hash_value
AND
   p.addr = s.paddr
-- and
--t.disk_reads > 10
ORDER BY
   t.disk_reads DESC;


   -- You could find disk intensive full table scans with something like this:

   SELECT Disk_Reads DiskReads, Executions, SQL_ID, SQL_Text SQLText,
   SQL_FullText SQLFullText
FROM
(
   SELECT Disk_Reads, Executions, SQL_ID, LTRIM(SQL_Text) SQL_Text,
      SQL_FullText, Operation, Options,
      ROW_NUMBER() OVER
         (Partition BY sql_text ORDER BY Disk_Reads * Executions DESC)
         KeepHighSQL
   FROM
   (
       SELECT Avg(Disk_Reads) OVER (Partition BY sql_text) Disk_Reads,
          MAX(Executions) OVER (Partition BY sql_text) Executions,
          t.SQL_ID, sql_text, sql_fulltext, p.operation,p.options
       FROM v$sql t, v$sql_plan p
       WHERE t.hash_value=p.hash_value AND p.operation='TABLE ACCESS'
       AND p.options='FULL' AND p.object_owner NOT IN ('SYS','SYSTEM')
       AND t.Executions > 1
   )
   ORDER BY DISK_READS * EXECUTIONS DESC
)
WHERE KeepHighSQL = 1
AND rownum <=20;

-- Try this, it will give you queries currently running for more than 60 seconds. Note that it prints multiple lines per running query if the SQL has multiple lines. Look at the sid,serial# to see what belongs together.
SELECT s.username,s.sid,s.serial#,s.last_call_et/60 mins_running,q.sql_text FROM v$session s
JOIN v$sqltext_with_newlines q
ON s.sql_address = q.address
 WHERE STATUS='ACTIVE'
AND TYPE <>'BACKGROUND'
AND last_call_et> 5
ORDER BY sid,serial#,q.piece;

-- The following query returns SQL statements that perform large numbers of disk reads (also includes the offending user and the number of times the query has been run):

SELECT t2.username, t1.disk_reads, t1.executions,
    t1.disk_reads / DECODE(t1.executions, 0, 1, t1.executions) AS exec_ratio,
    t1.command_type, t1.sql_text
  FROM v$sqlarea t1, dba_users t2
  WHERE t1.parsing_user_id = t2.user_id
    AND t1.disk_reads > 50000
  ORDER BY t1.disk_reads DESC;

  --   -- This one shows SQL that is currently "ACTIVE":-This one shows SQL that is currently "ACTIVE":-

  SELECT S.USERNAME, s.sid, s.osuser, t.sql_id, sql_text
FROM v$sqltext_with_newlines t,V$SESSION s
WHERE t.address =s.sql_address
AND t.hash_value = s.sql_hash_value
AND s.STATUS = 'ACTIVE'
AND s.username <> 'SYSTEM'
ORDER BY s.sid,t.piece;



SELECT sess.process, sess.STATUS, sess.username, sess.schemaname, SQL.sql_text
  FROM v$session sess,
       v$sql     SQL
 WHERE SQL.sql_id(+) = sess.sql_id
   AND sess.TYPE     = 'USER';


   SELECT  * FROM gv$process WHERE spid=20096;


   SELECT spid
FROM v$session s, v$process p
WHERE s.paddr = p.addr
AND s.sid= 20096;



SELECT  NAME,
    PHYRDS "Physical Reads",
    round((PHYRDS / PD.PHYS_READS)*100,2) "Read %",
    PHYWRTS "Physical Writes",
    round(PHYWRTS * 100 / PD.PHYS_WRTS,2) "Write %",
    fs.PHYBLKRD+FS.PHYBLKWRT "Total Block I/O's"
FROM (
    SELECT  SUM(PHYRDS) PHYS_READS,
        SUM(PHYWRTS) PHYS_WRTS
    FROM    v$filestat
    ) pd,
    v$datafile df,
    v$filestat fs
WHERE   df.FILE# = fs.FILE#
ORDER   BY fs.PHYBLKRD+fs.PHYBLKWRT DESC;


SELECT d.name, t.name, OBJECT_NAME(A.[OBJECT_ID]) AS [OBJECT NAME],
 I.[NAME] AS [INDEX NAME],
  A.LEAF_INSERT_COUNT,
  A.LEAF_UPDATE_COUNT,
  A.LEAF_DELETE_COUNT

FROM   SYS.DM_DB_INDEX_OPERATIONAL_STATS (NULL,NULL,NULL,NULL ) A
INNER JOIN SYS.INDEXES AS I          ON I.[OBJECT_ID] = A.[OBJECT_ID]
JOIN sys.TABLES t ON i.object_id = t.object_id
JOIN sys.DATABASES d ON a.database_id = d.database_id
AND I.INDEX_ID = A.INDEX_ID WHERE  OBJECTPROPERTY(A.[OBJECT_ID],'IsUserTable') = 1

ORDER BY A.LEAF_INSERT_COUNT + A.LEAF_UPDATE_COUNT + A.LEAF_DELETE_COUNT DESC;



SELECT d.name,
       i.asynch_io
FROM   v$datafile d,
       v$iostat_file i
WHERE  d.file# = i.file_no
AND    i.filetype_name  = 'Data File';