Home » RDBMS Server » Performance Tuning » Physical Reads & Logical Reads (10g)
Physical Reads & Logical Reads [message #575045] Fri, 18 January 2013 07:55 Go to next message
gatetec
Messages: 38
Registered: December 2012
Member
The applications are running very slow due to high I/O, it seems.
From AWR Report, I see certain TS and Objects are busy and have high I/O.
How do you find out the right settings to get optimal I/O?, and how do you find the causes and their possible solutions?
These questions seem too broad, but I want to get the starting point.

Segments by Logical Reads
Total Logical Reads: 1,780,465,045
Captured Segments account for 88.0% of Total
Owner Tablespace Name Object Name Subobject Name Obj. Type Logical Reads %Total
V500 I_A_MEDIUM XPKFILL_PRINT_ORD_HX INDEX 407,518,016 22.89
V500 I_R_SMALL XPKGL_ALIAS INDEX 299,203,312 16.80
V500 D_R_SMALL GL_ALIAS TABLE 296,386,688 16.65
V500 D_A_SMALL PFT_EVENT_OCCUR_LOG TABLE 129,661,968 7.28
V500 D_R_MEDIUM FREQUENCY_SCHEDULE TABLE 65,614,048 3.69

Segments by Physical Reads
Total Physical Reads: 3,691,333
Captured Segments account for 71.6% of Total
Owner Tablespace Name Object Name Subobject Name Obj. Type Physical Reads %Total
V500 D_ENCOUNTER0077 ENCOUNTER TABLE 1,478,710 40.06
V500 D_A_MEDIUM ENCNTR_INFO TABLE 362,832 9.83
V500 D_CLINICAL_EVE1333 CLINICAL_EVENT TABLE 270,950 7.34
V500 I_CLINICAL_EVE1333 XIE24CLINICAL_EVENT INDEX 49,230 1.33
V500 D_PERSON4859 PERSON TABLE 47,121 1.28
Re: Physical Reads & Logical Reads [message #575046 is a reply to message #575045] Fri, 18 January 2013 07:59 Go to previous messageGo to next message
LNossov
Messages: 284
Registered: July 2011
Location: Germany
Senior Member
Could you please upload a complete awr report.
Re: Physical Reads & Logical Reads [message #575047 is a reply to message #575045] Fri, 18 January 2013 08:00 Go to previous messageGo to next message
cookiemonster
Messages: 10859
Registered: September 2008
Location: Rainy Manchester
Senior Member
I'd start by identifying sql statements / pl/sql procedures that are running particularly slowly and see if they can be optimised.
If they can that'd probably do more to reduce the I/O than anything else.
Re: Physical Reads & Logical Reads [message #575049 is a reply to message #575046] Fri, 18 January 2013 08:09 Go to previous messageGo to next message
gatetec
Messages: 38
Registered: December 2012
Member
Please see the attached.
Re: Physical Reads & Logical Reads [message #575054 is a reply to message #575049] Fri, 18 January 2013 10:16 Go to previous messageGo to next message
LNossov
Messages: 284
Registered: July 2011
Location: Germany
Senior Member
I don't see your attachment.
Re: Physical Reads & Logical Reads [message #575055 is a reply to message #575054] Fri, 18 January 2013 10:21 Go to previous messageGo to next message
gatetec
Messages: 38
Registered: December 2012
Member
Sorry, here you go!
  • Attachment: awrrpt-1.pdf
    (Size: 1.85MB, Downloaded 496 times)
Re: Physical Reads & Logical Reads [message #575056 is a reply to message #575054] Fri, 18 January 2013 10:22 Go to previous messageGo to next message
gatetec
Messages: 38
Registered: December 2012
Member
the other half of the report - attached
  • Attachment: awrrpt-2.pdf
    (Size: 1.90MB, Downloaded 780 times)
Re: Physical Reads & Logical Reads [message #575057 is a reply to message #575055] Fri, 18 January 2013 10:22 Go to previous messageGo to next message
BlackSwan
Messages: 22546
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

>The applications are running very slow due to high I/O, it seems.

at what value does I/O go from being acceptable to being "high"?
Is this value the same for every Oracle DB?

[Updated on: Fri, 18 January 2013 10:24]

Report message to a moderator

Re: Physical Reads & Logical Reads [message #575125 is a reply to message #575056] Sat, 19 January 2013 14:50 Go to previous messageGo to next message
LNossov
Messages: 284
Registered: July 2011
Location: Germany
Senior Member
I suppose, you are hitting the bug 6979801 - AWR REPORTS INCORRECT IO STATS FOR TABLESPACES AND DATAFILES DURING RMAN BACKUP. You can compare this report with another one without rman running. Regardless of the bug 6979801 rman can impact the performance.

Performance of your LGWR is unsufficient (the duration of one log write is 0,03 sec., it is too slow). It is the reason of the waits for "log file sync".

You have to check your sqls.

Is it a good idea to use optimize_mode=rule?

Do you notice any performance improvement by event 10298?
Re: Physical Reads & Logical Reads [message #575390 is a reply to message #575045] Tue, 22 January 2013 17:57 Go to previous message
alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California
Senior Member
What I do to identify the longest running queries is look at the average seconds
of execution time with the following script. I amusually interested in the query
with the longest average execution time but in this case I might be interested
in the query with 13083 executions because its total seconds of elapsed time is
over 16 thousand.

This query is very good to run before and after a new code release test
because queries with long execution times will run slow on our web site.
ECSCDAP4P > @v$sqlarea_elapsed.sql

TOT_SEC_ELAPSED HASH_VALUE EXECUTIONS INSTANC AVG_SEC_PER_EXEC SQL_TEXT
--------------- ---------- ---------- ------- ---------------- ---------
      57.039289  130260116         56 CSCDAP4       1.01855873 select *
      57.642821  535777823         56 CSCDAP4       1.02933609 select *
     16332.6634  304521275      13083 CSCDAP4       1.24838825 select *
     104.498913 3115159362         83 CSCDAP2       1.25902305 select *
      93.248114 1077395695         66 CSCDAP4       1.41285021 select *
      129.68471  188283226         83 CSCDAP2       1.56246639 select *
     910.364119 2431632681         56 CSCDAP1       16.2565021 select *
                           ----------
sum                             13483

ECSCDAP4P > select sql_text
  2  from gv$sqltext b
  3  where b.hash_value=2431632681
  4  and inst_id=1
  5* order by inst_id,hash_value,b.piece;

SQL_TEXT
----------------------------------------------------------------
select * from ( select audiovideo0_.id as id0_, audiovideo0_1_.E
XTERNALID as EXTERNALID0_, audiovideo0_1_.author as author0_, au
diovideo0_1_.body as body0_, audiovideo0_1_.byline as byline0_,
audiovideo0_1_.club_id as club18_0_, audiovideo0_1_.comments as
comments0_, audiovideo0_1_.copyright as copyright0_, audiovideo0
_1_.CONTENT_DATE as CONTENT8_0_, audiovideo0_1_.imageURL as imag
eURL0_, audiovideo0_1_.shortName as shortName0_, audiovideo0_1_.
SHOW_IN_DYNA_LISTS as SHOW11_0_, audiovideo0_1_.source as source
0_, audiovideo0_1_.status as status0_, audiovideo0_1_.summary as
 summary0_, audiovideo0_1_.thumbnailImagePath as thumbna15_0_, a
udiovideo0_1_.title as title0_, audiovideo0_1_.url as url0_, aud
iovideo0_.largeImagePath as largeIma2_79_, audiovideo0_.mediumIm
agePath as mediumIm3_79_, audiovideo0_.smallImagePath as smallIm
a4_79_, audiovideo0_.encodings as encodings79_, audiovideo0_.hig
hResolutionStream as highReso6_79_, audiovideo0_.length as lengt
h79_, audiovideo0_.lowResolutionStream as lowResol8_79_, audiovi
deo0_.mediumResolutionStream as mediumRe9_79_, audiovideo0_.MOBI
LE_STREAM_URL_1 as MOBILE10_79_, audiovideo0_.MOBILE_STREAM_URL_
2 as MOBILE11_79_, audiovideo0_.MOBILE_STREAM_URL_3 as MOBILE12_
79_, audiovideo0_.playType as playType79_, audiovideo0_.primaryC
hannel as primary14_79_, audiovideo0_.SINGLE_BITRATE_PATH as SIN
GLE15_79_, audiovideo0_.streamingServer as streami16_79_, audiov
ideo0_.type as type79_ from AUDIO_VIDEO audiovideo0_ inner join
CONTENT audiovideo0_1_ on audiovideo0_.id=audiovideo0_1_.id wher
e audiovideo0_1_.status = 'ACTIVE' and audiovideo0_1_.CONTENT_DA
TE <= CURRENT_TIMESTAMP and audiovideo0_.type='VIDEO' and audiov
ideo0_1_.SHOW_IN_DYNA_LISTS='Y' and audiovideo0_1_.club_id=:1 an
d (audiovideo0_.id in (select content1_.id from CONTENT content1
_ inner join CONTENT_PERSONNEL personnel2_ on content1_.id=perso
nnel2_.CONTENT_FK inner join PERSONNEL personnel3_ on personnel2
_.PERSONNEL_FK=personnel3_.id where personnel3_.id in (select ch
eerleade4_.id from CHEERLEADER cheerleade4_ inner join PERSONNEL
 cheerleade4_1_ on cheerleade4_.id=cheerleade4_1_.id))) order by
 audiovideo0_1_.CONTENT_DATE desc ) where rownum <= :2

The complete v$sqlarea_elapsed.sql follows:
 set pages 50
 set lines 120
 set wrap off
 break on report
 compute sum of executions break on report
 column instance_name format a7
 select elapsed_time/1000000 tot_sec_elapsed,hash_value,executions,i.instance_name,
 elapsed_time/executions/1000000 Avg_sec_per_exec,
 sql_text
 from gv$sqlarea s,gv$instance i
 where executions >50 and elapsed_time/executions/1000000>1
 and upper(sql_text) not like '%DBMS_STATS%'
 and upper(sql_text) not like '%WRH$%'
 and upper(sql_text) not like '%WRI$%'
 and upper(sql_text) not like '%OEM%'
 and upper(sql_text) not like '%DR$%'
 and upper(sql_text) not like '%DBMS%'
 and upper(sql_text) not like '%DBID%'
 and upper(sql_text) not like '%OWNER%'
 and upper(sql_text) not like '%JOB$%'
 and upper(sql_text) not like '%V$%'
 and upper(sql_text) not like '%SEQUENCE#%'
 and upper(sql_text) not like '%DBSNMP%'
 and upper(sql_text) not like '%CTXSYS%'
 and upper(sql_text) not like '%BACKUP_TYPE%'
 and upper(sql_text) not like '%MGMT%'
 and upper(sql_text) not like '%OBJ#%'
 and elapsed_time/1000000>1 and s.inst_id=i.inst_id
 order by elapsed_time/executions/1000000;
Previous Topic: does 11g not do a soft parse when we set cursor_cached_curosr?
Next Topic: what's mean of plh in SQL_TRACE?
Goto Forum:
  


Current Time: Thu Jul 31 14:46:48 CDT 2014

Total time taken to generate the page: 0.12267 seconds