Solved

how evaluate performance database in oracle 10gr2

Posted on 2007-12-05
6
371 Views
Last Modified: 2013-12-18
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

Regards

Gerid Garcia
0
Comment
Question by:yerai
  • 3
6 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 500 total points
ID: 20415029
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

SELECT   s.SID, sn.NAME,
         TO_CHAR(ss.VALUE * (SELECT TO_NUMBER(VALUE)
                               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
ORDER BY VALUE DESC

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



0
 

Author Comment

by:yerai
ID: 20419078
how it's best practice where i need to read records and after delete every 30 seconds
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 20419118
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?

0
 
LVL 74

Expert Comment

by:sdstuber
ID: 20419512
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?
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
capture vmstat info and insert it into an oracle table 31 39
update statement in oracle 9 29
oracle numeric condition check 4 27
return value in based on value passed 6 28
Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question