Home » RDBMS Server » Performance Tuning » Newbie, help with ADDM finding
Newbie, help with ADDM finding [message #293673] Mon, 14 January 2008 09:32 Go to next message
hristo
Messages: 258
Registered: May 2007
Senior Member
Hi!

So, I ran ADDM on one of our DBs and found this:
As Im quite new to this sort of thing, any pointers on how to proceed (how to Investigate application logic involving):


FINDING 4: 25% impact (2924 seconds)
------------------------------------
Individual database segments responsible for significant user I/O wait were
found.

   RECOMMENDATION 1: Segment Tuning, 12% benefit (1431 seconds)
      ACTION: Investigate application logic involving I/O on TABLE
         "L2000.ORDERS" with object id 12906.
         RELEVANT OBJECT: database object with id 12906
      RATIONALE: The I/O usage statistics for the object are: 6 full object
         scans, 511370 physical reads, 188 physical writes and 0 direct reads.
      RATIONALE: The SQL statement with SQL_ID "22bmtfpykv40c" spent
         significant time waiting for User I/O on the hot object.
         RELEVANT OBJECT: SQL statement with SQL_ID 22bmtfpykv40c



Regards
H
Re: Newbie, help with ADDM finding [message #293676 is a reply to message #293673] Mon, 14 January 2008 09:56 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

>RELEVANT OBJECT: SQL statement with SQL_ID 22bmtfpykv40c
find above sql statement and check why sql statement perform high physical reads.

1. find above sql statment.
2. set autotrace traceonly
3. execute sql statement
4. post here output.

with code tags and table desc with indexes information.
Re: Newbie, help with ADDM finding [message #293831 is a reply to message #293676] Tue, 15 January 2008 03:46 Go to previous messageGo to next message
hristo
Messages: 258
Registered: May 2007
Senior Member
Hi!

So, where do I find this SQL statement?

Regards
Peter
Re: Newbie, help with ADDM finding [message #295467 is a reply to message #293673] Tue, 22 January 2008 07:03 Go to previous messageGo to next message
hristo
Messages: 258
Registered: May 2007
Senior Member
Instead of starting a new thread I continue in this one.

*************************************************************
Investigate the cause for high "Streams capture: waiting for archive log" waits. Refer to Oracle's "Database Reference" for the description of this wait event. Use given SQL for further investigation.
*************************************************************

So, I have checked "Database reference" and found nothing when searching for this error. Any pointers where to look further?

Re: Newbie, help with ADDM finding [message #295656 is a reply to message #293831] Wed, 23 January 2008 00:40 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member

Quote:
So, where do I find this SQL statement?


SELECT * FROM V$SQL WHERE SQLID = '22bmtfpykv40c'


HTH.

Michael
Re: Newbie, help with ADDM finding [message #297320 is a reply to message #293673] Thu, 31 January 2008 02:37 Go to previous messageGo to next message
hristo
Messages: 258
Registered: May 2007
Senior Member
It seems that
Quote:
"Streams capture: waiting for archive log"

is an idle wait an can be ignored. But I have some of these waits to:
Quote:
Waits on event "log file sync" while performing COMMIT and ROLLBACK operations were consuming significant database time

Any ideas on how to solve that?

Regards
Hristo

[Updated on: Thu, 31 January 2008 02:42] by Moderator

Report message to a moderator

Re: Newbie, help with ADDM finding [message #297327 is a reply to message #297320] Thu, 31 January 2008 02:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What about reading
Database Performance Tuning Guide
Chapter 10 Instance Tuning Using Performance Views
Section 10.3 Wait Events Statistics

Regards
Michel
Re: Newbie, help with ADDM finding [message #297476 is a reply to message #293673] Thu, 31 January 2008 22:29 Go to previous message
trantuananh24hg
Messages: 668
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
Many thing causes this problem, may be one individual SQL, may be application, may be the transaction is already busy...

I've got some reports like you, of course, do not forget tracing this SQL with ID 22bmtfpykv40c. However, this statement may be not in this session that you're here, and so that, you might have nothing from

SQL>select sql_text from v$sql where sql_id='22bmtfpykv40c'


Quote:

RATIONALE: The I/O usage statistics for the object are: 6 full object scans, 511370 physical reads, 188 physical writes and 0 direct reads.



The rationale appeared because you could use some procedures/statement/function..etc.. to kill this session causes.

For me, Physical Reads are not catastrophe, but Logical Reads is..

An example

SQL> select * from test where object_id < 1000;

853 rows selected.


Statistics
----------------------------------------------------------
        288  recursive calls
          0  db block gets
        724  consistent gets
        571  physical reads
          0  redo size
      38396  bytes sent via SQL*Net to client
       1001  bytes received via SQL*Net from client
         58  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        853  rows processed

SQL>

SQL> select * from test where object_id < 1000;

853 rows selected.


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        631  consistent gets
          0  physical reads
          0  redo size
      38396  bytes sent via SQL*Net to client
       1001  bytes received via SQL*Net from client
         58  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        853  rows processed

SQL>


0 recursive calls againts 288
631 consistent gets againts 724. This second did not decrease consistents get more than I want, just because I did not create any index to table TEST.

Full scan table may be not bad, index may be not good!

Using ASH or AWR to generate reports to give you more information of Top SQL, Top Events, Top Waits...

[Updated on: Thu, 31 January 2008 22:31]

Report message to a moderator

Previous Topic: Materialized View issue
Next Topic: if RULE BASE exists in 11G
Goto Forum:
  


Current Time: Mon Dec 05 14:50:49 CST 2016

Total time taken to generate the page: 0.10031 seconds