how evaluate performance database in oracle 10gr2

i need yto konw, what process and what sql is more stronger in any moment in a database 10gr2, because in some moments it's very low, but i dont know the problem is memory, cpu, or I-O


Gerid Garcia
Who is Participating?
sdstuberConnect With a Mentor Commented:
use statspack or, if you've paid for the option,  workload repository.

and look at top sql in between two snapshots.

if you want something realtime, you can query v$sesstat yourself

                               FROM v$parameter
                              WHERE NAME = 'db_block_size'), '999,999,999,999') bytes_written,
         s.username, s.osuser, s.machine, s.terminal, s.program, to_char(s.logon_time,'yyyy-mm-dd hh24:mi:ss') logon_time
    FROM v$sesstat ss, v$statname sn, v$session s
   WHERE sn.NAME IN('physical writes')
     AND ss.statistic# = sn.statistic#
     AND ss.SID = s.SID
     AND s.USER# > 0

change the "IN clause"  to be whatever statistics you're interested in.
note, some stats are "right now" and some stats are cumulative since log in.  So a lightweight process that has been connected for several months might have very high physical write numbers.   So you have to examine each and determine which ones really are the top resource hogs

yeraiAuthor Commented:
how it's best practice where i need to read records and after delete every 30 seconds
Do you need to do anything with the records after you read them and before you delete them?  Or are you reading them just to do the delete?

If possible, do the processing in one SQL and the delete in another SQL.

If it's a read just to do the delete, then delete in one sql and done.

For the 30 second timing, I would create a dbms_job with an interval of 'sysdate + 30/86400'

Can you show an outline of what you're currently doing?  Or some code/pseudo-code of your process?

You've asked this question twice now.

Something else to consider...  How and why is the table getting loaded?  Would it be feasible to declare the table a global temporary and load and processes the data for whatever purposes it has and then simply let the data disappear when the session/transaction ends?
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.