Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Terry.

Asked: January 16, 2003 - 8:23 pm UTC

Last updated: December 03, 2012 - 9:27 am UTC

Version: 9.2.0

Viewed 10K+ times! This question is

You Asked

Hi Tom,

As some technical papers mentioned, DBA should find out which datafile(s) cause I/O contentions and move them around physical hard disks in order to have better physical disk channel utilization and I/O balance. This is what I normally do now.

However, I read a paper and indicated that we should find out which object(s) cause I/O contention rather than just move datafile(s) among disks. Once we found out, and then do something on those "hot object". e.g . table or index partitioning. This seems reasonable to me in fact.

My question is how to find out these hot objects. And, your comment on this claim (find out hot object which caused I/O contention)

Thank you in advance




and Tom said...

look at v$segstat -- it'll give you tons of stats by object. This is new in 9i

If you are not using SAME (stripe and mirror everything) and you do not have a segment/ datafile -- this will help you find the hot objects and more appropriately distribute them across devices -- sure. If you just know that file 5 with 500 objects in it is "hot" -- what do you do?? if you know that file 5 is hot and apparently object 55 is really hot - now you can work to fix it.

Rating

  (7 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

terry, January 16, 2003 - 9:15 pm UTC

Thank you so much. Please mention more on the 9i segment- level statistics and its applications can help me to do better job on this situation.

By the way, I have found a book (Expert One-On-One Oracle Performance, will publish couple months later) at Amazon web site (Are you writing this book?) If so, could you mention me the content of this book...I cannot wait to have it. Thank you.

Tom Kyte
January 17, 2003 - 8:59 am UTC

did you read about the view in the reference guide?

http://docs.oracle.com/docs/cd/B10501_01/server.920/a96536/ch3166.htm#1098972

then just query it.  for example, to find the segments with the most reads/writes, you might:

ops$tkyte@ORA9I> select owner, object_name, object_type, statistic_name, value
  2    from dba_objects,
  3         (select statistic_name, value, obj#
  4          from (select a.*, max(value) over (partition by statistic_name) mv
  5                    from v$segstat a
  6                   where statistic_name in ( 'logical reads',
  7                                             'physical reads',
  8                                             'physical writes',
  9                                             'physical reads direct',
 10                                             'physical writes direct' )
 11                 )
 12           where value = mv
 13             and mv <> 0) b
 14    where dba_objects.object_id = b.obj#
 15  /

OWNER      OBJECT_NAME                    OBJEC STATISTIC_NAME            VALUE
---------- ------------------------------ ----- -------------------- ----------
BOPPS      SYS_LOB0000039547C00007$$      LOB   physical writes           27026
                                                direct

PEOPLE1    DR$WWC_PEOPLE_PROFILE_IDX$X    INDEX physical writes          150350
PEOPLEA    WWC_PEOPLE_FAST_PEOPLE         TABLE logical reads         386025152
PEOPLE_RAW SYS_LOB0000039609C00009$$      LOB   physical reads          1858082
                                                direct


see
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:6731060526417
for a blurb about the book -- title will most likely change and its due out in the spring/summer.

 

I/O Contention on 8i

Michael T., January 17, 2003 - 7:00 am UTC

Is there any method to get "hot" object information on 8i?

Tom Kyte
January 17, 2003 - 9:28 am UTC

v$segment_statistics

Robert Xuequn Xu, January 17, 2003 - 6:06 pm UTC

I saw there was a v$segment_statistics. Can it be used intead of v$segstat? Then we don't need to make a join with dba_objects.

Tom Kyte
January 17, 2003 - 6:31 pm UTC

of course

io contention of space allocation/used

dan, December 28, 2006 - 5:32 pm UTC

Hi Tom,

Question1:
I just like to know how can you possibly reply to all of the comments made on new and already answered questions, I mean there are thousands of dbas out there who post their queries.
Do you have a team of dbas? if yes, how do we know the answer we got is from Tom himself?

Question2:
I have a database which produces a lot of ios on machine during batch.
I queried the v$segstat for 6 hours which is the batch time and came up with following (sum(value))
ITL waits 5
row lock waits 6181
segment scans 11939
buffer busy waits 24524
physical writes direct 1504919
physical writes 1774632
physical reads direct 2357571
physical reads 3387324
db block changes 6032912
logical reads 205054208
space allocated 5803540480
space used 6390789144

I then concentrate on the last two stats which are space used and space allocated.
So i again queried v$segstat and this time group by obj# where statistic_name in space_used and space_allocated.

I came up with 3 top most objects
OBJ# VAL
---------- ----------
272248 1030684672
272236 1543438336
272240 1828651008
In fact the sum(values) for other objs was way less than these 3 ones.

Then what I did, went to dba_objects and found out the object_names. they were all lobs. I then queries dba_lobs and found their corresponding table names.

I found three tables with those 3 lobs. These tables have a column as defined blob data type.

Next I did select count(*) from these 3 tables, surprise they were all having ZERO rows.

I then wnt to dba_segments and did sum(bytes) where segment_name in (list of lobs of those 3 tables) and i came up with sum(bytes) amounting to 5GB.

If the tables have 0 rows how come their lobs occupy 5 gb.

I then asked the vendor and here is what I got.

During the batch there are three parallel jobs/threads started to complete the batch fastly. These three threads have their values stored in these 3 tables. So a thread 1 takes 50 jobs and insert 50 rows in these tables , then it processes those 50 jobs and when a job is finished, it deletes a record, until all 50 jobs completes and the corresponfing table becomes 0. so these tables are sort of used as staging or lookup for those 3 threads.

Offcourse its not just 1 pass of 50 jobs, when a thread completes 50 jobs , the table becomes 0, it then take on another set of 50 jobs and inserts again 50 records and then the process starts again and so on.

So what I did I truncated these tables and there you go the lobs space all went to 64k.
I have asked them to have truncate table sqls at the end of the batch.

Anyway my question is how can i reduce those higher values in v$segstat for space allocated and apce used.

Is there anything i can do before start of batch to pre allocate space? and I know why space is nbeing used, probably because of lob, the blolb column keeps on having undo segment for each query ad update into that table.



Regards





Tom Kyte
December 29, 2006 - 9:32 am UTC

question 1:

it would not be "asktom" if there was a team.

It is just me - unless it says "I asked so and so, an expert in the field, and they said....."



question 2:

why are you worried about them - have you identified these simply as "big numbers" or as 'the cause of a problem'

Disk i/o

Arvind Mishra, July 23, 2012 - 5:27 pm UTC

Hi Tom,

My database version is 10g. How can I find following using queries:

1. Service time of disk.
2. Throughput time of disk in IOPS
3. Average and maximum service time utilized by Oracle database
4. Average and maximum throughput time utilized by Oracle database.

Thanks,

Arvind

Anand, December 03, 2012 - 6:07 am UTC

Hi Tom,

in table gv$segment_statistics if value is in negative for "space used" then what it means ?
Tom Kyte
December 03, 2012 - 9:27 am UTC

these numbers are counted from instance startup, if you (or something) shrinks a segment - does some reorg on it, it could be negative.

ops$tkyte%ORA11GR2> create table t enable row movement
  2  as
  3  select *
  4    from all_objects;

Table created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> select owner, object_name, value ,
  2         (
  3         select bytes
  4           from user_segments
  5          where segment_name = 'T'
  6             ) alloced
  7    from v$segment_statistics
  8   where statistic_name = 'space used'
  9     and owner = user
 10     and object_name = 'T'
 11  /

OWNER                          OBJECT_NAME                         VALUE    ALLOCED
------------------------------ ------------------------------ ---------- ----------
OPS$TKYTE                      T                                 8511488    9437184

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> delete from t;

72946 rows deleted.

ops$tkyte%ORA11GR2> commit;

Commit complete.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> select owner, object_name, value ,
  2         (
  3         select bytes
  4           from user_segments
  5          where segment_name = 'T'
  6             ) alloced
  7    from v$segment_statistics
  8   where statistic_name = 'space used'
  9     and owner = user
 10     and object_name = 'T'
 11  /

OWNER                          OBJECT_NAME                         VALUE    ALLOCED
------------------------------ ------------------------------ ---------- ----------
OPS$TKYTE                      T                                 1021337    9437184

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> connect / as sysdba
Connected.
sys%ORA11GR2> startup force;
ORACLE instance started.

Total System Global Area  535662592 bytes
Fixed Size                  1346140 bytes
Variable Size             356517284 bytes
Database Buffers          171966464 bytes
Redo Buffers                5832704 bytes
Database mounted.
Database opened.
sys%ORA11GR2> connect /
Connected.
ops$tkyte%ORA11GR2> set linesize 10000
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> select owner, object_name, value ,
  2         (
  3         select bytes
  4           from user_segments
  5          where segment_name = 'T'
  6             ) alloced
  7    from v$segment_statistics
  8   where statistic_name = 'space used'
  9     and owner = user
 10     and object_name = 'T'
 11  /

no rows selected

ops$tkyte%ORA11GR2> alter table t shrink space;

Table altered.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> select owner, object_name, value ,
  2         (
  3         select bytes
  4           from user_segments
  5          where segment_name = 'T'
  6             ) alloced
  7    from v$segment_statistics
  8   where statistic_name = 'space used'
  9     and owner = user
 10     and object_name = 'T'
 11  /

OWNER                          OBJECT_NAME                         VALUE    ALLOCED
------------------------------ ------------------------------ ---------- ----------
OPS$TKYTE                      T                                -1020354      65536


Anand, December 04, 2012 - 3:34 am UTC

Thanks alot tom....

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.