understanding 9i Segment-Level Statistics

to understand new Segment-Level Statistics feature.
SCOPE & APPLICATION
——————-

Application DBA and Performance DBA’s
Segment-Level Statistics
—————————

This is a new feature introduce in 9.2.
We can now gather segment-level statistics to find out the performance problems
associated with individual segments.

Before Oracle 9.2, there was no easy way to get information, or statistics,
about the usage of various segments.
If one wanted to know how many physical reads or writes occurred on a specific
table, the only way to know for sure was to place that table in its own
tablespace and then query the PHYRDS or PHYWRTS columns of the V$FILESTAT view.
If other tables were in this tablespace, their physical read and write counts
would show up for that tablespace as well.
The problem with this method was that a segment must be the only object in that
tablespace for the method to work accurately.

Many times it is required to know whether a particular index was ever used.
If an index is not being used, then we want to remove it so that it does not
unnecessarily hamper performance of DML statements.
The answer to this question often was to query V$BH, which shows the blocks
currently in the database buffer cache.
If the index had any block in the buffer cache then it must have been used at
one time. But what happens when the index block gets aged out of the cache?
One had to make sure to query V$BH with a short interval between samples to
see if any index block was in the cache.
This method could never guarantee that the index block was not aged out of the
cache between queries on V$BH.

Oracle 9i addressed this issue with the ALTER INDEX MONITORING USAGE command
and the V$OBJECT_USAGE view.
This is a much better solution, but you do have to turn on monitoring for that
index some time before you can query V$OBJECT_USAGE to see if the index has
ever been used.

Oracle 9.2 introduces the new V$SEGMENT_STATISTICS dynamic performance view.
This view lets you see many different statistics on the usage of segments since
instance startup.
You do not have to turn on monitoring or take any special steps to begin using
this view.

The query below shows the statistics that you can obtain with Oracle 9.2.0.2

SQL> select distinct statistic_name from v$segment_statistics;
STATISTIC_NAME
----------------------------------------
ITL waits
buffer busy waits
db block changes
global cache cr blocks served
global cache current blocks served
logical reads
physical reads
physical reads direct
physical writes
physical writes direct
row lock waits
11 rows selected.

Using above statistics we can now know exactly if an index has been used,
without turning on monitoring.
We can also know which tables have the highest physical I/O activity.

If you want to know if an index has ever been used since instance startup,
the solution is quite easy. Simply query V$SEGMENT_STATISTICS to see if there
has even been a physical read on the index in question.

Queries similar to the following can help:

SQL> select statistic_name,value
2 from v$segment_statistics
3 where owner='SCOTT' and object_name='EMPLOYEE_PK'
4 and statistic_name='physical reads';
STATISTIC_NAME VALUE
---------------------------------------- ----------
physical reads 3024
SQL> select statistic_name,value
2 from v$segment_statistics
3 where owner='SDE' and object_name='EMPLOYEE_I1'
4 and statistic_name='physical reads';
no rows selected
The first query shows that 3,024 physical reads have been performed on the 
LAYERS_PK index. This index has obviously been used before. 
The second query shows that no physical reads have ever occurred on the 
EMPLOYEE_I1 index. 
If there have never been any physical reads on this index then it has never
been used.

The same way you can use this view to find out the different statistics listed
above.

For e.g

We want to to determine which segments are contributing to “BUFFER BUSY WAITS”
wait event. Querying V$SEGMENT_STATISTICS can help us determine the answer.

SQL> select event,total_waits 
 from v$system_event where event='buffer busy waits';
EVENT TOTAL_WAITS
---------------------------------------- -----------
buffer busy waits 12282183
SQL> select segment_name,object_type,total_buff_busy_waits
2 from ( select owner||'.'||object_name as segment_name,object_type,
3 value as total_buff_busy_waits
4 from v$segment_statistics
5 where statistic_name in ('buffer busy waits')
6 order by total_buff_busy_waits desc)
7* where rownum <=10
GASP 
SQL> /
SEGMENT_NAME OBJECT_TYPE TOTAL_BUFF_BUSY_WAITS
----------------------------------- ------------- ---------------------
SCOTT.EMPLOYEE TABLE 11256525
SCOTT.DEPT TABLE 1025658

2 rows selected.

From above query we can see that employee table is contributing more on this
perticular wait event.
RELATED DOCUMENTS
—————–

Oracle9i Database Performance Tuning Guide and Reference (Release 2)

 

Sobre Alexandre Pires

ORACLE OCS Goldengate Specialist, OCE RAC 10g R2, OCP 12C, 11g, 10g , 9i e 8i - Mais de 25 anos de experiência na área de TI. Participei de projetos na G&P alocado na TOK STOK, EDINFOR alocado na TV CIDADE "NET", 3CON Alocado no PÃO DE AÇUCAR, DISCOVER alocado na VIVO, BANCO IBI e TIVIT, SPC BRASIL, UOLDIVEO alocado no CARREFOUR e atualmente na ORACLE ACS atendendo os seguintes projetos: VIVO, CLARO, TIM, CIELO, CAIXA SEGUROS, MAPFRE, PORTO SEGURO, SULAMERICA, BRADESCO SEGUROS, BANCO BRADESCO, BASA, SANTANDER, CNJ, TSE, ELETROPAULO, EDP, SKY, NATURA, ODEBRESHT, NISSEI, SICREDI, CELEPAR, TAM, TIVIT, IBM, SMILES, CELEPAR, SERPRO,OKI,BANCO PAN, etc
Esse post foi publicado em INDEX TUNING, PERFORMANCE, PLSQL TUNING. Bookmark o link permanente.

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s