Home » RDBMS Server » Performance Tuning » sqlarea sql (oracle 10.2.0.3)
sqlarea sql [message #415621] Tue, 28 July 2009 07:08 Go to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member

How to get those information from Oracle sql area? I sthere any sql for it?

SQL_TEXT	USERNAME	DISK_READS_PER_EXEC	BUFFER_GETS	DISK_READS	PARSE_CALLS	SORTS	EXECUTIONS	ROWS_PROCESSED	HIT_RATIO	FIRST_LOAD_TIME	SHARABLE_MEM	PERSISTENT_MEM	RUNTIME_MEM	CPU_TIME	ELAPSED_TIME	ADDRESS	HASH_VALUE

Re: sqlarea sql [message #415623 is a reply to message #415621] Tue, 28 July 2009 07:14 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
select * from v$sql
Re: sqlarea sql [message #415624 is a reply to message #415623] Tue, 28 July 2009 07:23 Go to previous message
prachij593
Messages: 266
Registered: May 2009
Senior Member
SELECT 
 module, 
 sql_text, 
 username, 
 disk_reads_per_exec, 
 buffer_gets, 
 disk_reads, 
 parse_calls, 
 sorts, 
 executions, 
 rows_processed, 
 hit_ratio, 
 first_load_time, 
 sharable_mem, 
 persistent_mem, 
 runtime_mem, 
 cpu_time, 
 elapsed_time, 
 address, 
 hash_value 
FROM 
  (SELECT
   module, 
   sql_text , 
   u.username , 
   round((s.disk_reads/decode(s.executions,0,1, s.executions)),2)  disk_reads_per_exec, 
   s.disk_reads , 
   s.buffer_gets , 
   s.parse_calls , 
   s.sorts , 
   s.executions , 
   s.rows_processed , 
   100 - round(100 *  s.disk_reads/greatest(s.buffer_gets,1),2) hit_ratio, 
   s.first_load_time , 
   sharable_mem , 
   persistent_mem , 
   runtime_mem, 
   cpu_time, 
   elapsed_time, 
   address, 
   hash_value 
  FROM
   sys.v_$sql s, 
   sys.all_users u 
  WHERE
   s.parsing_user_id=u.user_id 
   and UPPER(u.username) not in ('SYS','SYSTEM') 
  ORDER BY
   4 desc) 
WHERE
 rownum <= 20;


Thanks
Previous Topic: Different plan for same sql
Next Topic: No parallel DML with IOTs
Goto Forum:
  


Current Time: Mon Dec 05 05:07:49 CST 2016

Total time taken to generate the page: 0.08719 seconds