V$SQL_PLAN_STATISTICS V$SQL_PLAN_STATISTICS_ALL

*************************************************************
This article is being delivered in Draft form and may contain
errors. Please use the MetaLink “Feedback” button to advise
Oracle of any issues related to this article.
*************************************************************

PURPOSE
——-

This document explains the usage of two new fixed tables introduced in
9i Release 2:

V$SQL_PLAN_STATISTICS
V$SQL_PLAN_STATISTICS_ALL

SCOPE & APPLICATION
——————-

This article is intended for DBAs and support analysts for tuning purposes.

INTRODUCTION:

In the pre release of 9i, the execution plan for a query can be stored in
the PLAN_TABLE (created by utlxplan.sql). However it is not guaranteed that the
access path given by the explain plan is THE plan used by the optimizer during
the actual execution of the query. This is because the runtime access path may
differ from the explained access path based on the user session environments
(like SORT_AREA_SIZE, HASH_AREA_SIZE etc).

9i Release 1 introduced a new fixed table V$SQL_PLAN to
store the access path of the query (cursor and not the SQL) used by the optimizer
when executing it. This is the actual access path used by the optimizer. In 9i
Release 2, there are 2 more fixed tables introduced that provide more
information, for each cached cursors, the execution statistics of each operation
in the execution plan

V$SQL_PLAN_STATISTICS
V$SQL_PLAN_STATISTICS_ALL

CONFIGURATION PARAMETERS:
The following parameters are needed to collect the row source level information
in the fixed tables:

1) STATISTICS_LEVEL = ALL
2) SQL_TRACE = TRUE (or) TIMED_STATISTICS = TRUE

V$SQL_PLAN_STATISTICS:

This table provides information about the execution statistics on each operation
present in the v$sql_plan. A simple join on v$sql_plan and v$sql_plan_statistics
will give us the execution statistics in the row source level. For example, if a
query has 5 operations in v$sql_plan (operation_id 1 to 5), then the
corresponding execution statistics for all 5 operations will be populated in
v$sql_plan_statistics table. The following columns will give us useful
information on the execution statistics:

1)EXECUTIONS:
This shows the number of times the specific child cursor was executed. For
instance, this column value can be 2 in the following circumstances:
·Two sessions having the same environments, execute the same sql.
·Same session executing a SQL twice.

If 2 sessions differ in their environments (for e.g. hash_area_size), then the
executions column is not incremented; instead a separate child cursor is created
for the same SQL. Check CHILD_NUMBER column to know the number of child cursors
created for a given SQL (address and hash_value).

2)LAST_OUTPUT_ROWS and OUTPUT_ROWS:
The LAST_OUTPUT_ROWS column shows the actual number of rows processed by that
operation. The cumulative sum of all LAST_OUTPUT_ROWS for all executions is
populated in OUTPUT_ROWS column.

3)LAST_DISK_READS and DISK_READS:
Number of times the disk is read for each operation is in
LAST_DISK_READS. The cumulative total for the total number of disk reads for all
executions is given in DISK_READS column.

4)LAST_ELAPSED_TIME and ELAPSED_TIME:
Time in microseconds to execute the operation is in LAST_ELAPSED_TIME. The
cumulative total of all times (in microsecond) for all executions is given in
ELAPSED_TIME.

V$SQL_PLAN_STATISTICS_ALL:

This table contains the holistic view of all the necessary information that is
concatenated from the following tables:

V$SQL_PLAN (all operations, objects, cost, cardinality etc)
V$SQL_PLAN_STATISTICS (execution statistics as above)
V$SQL_WORKAREA

V$SQL_WORKAREA was introduced in 9i Release 1. This contains the information
about the memory usage of all the child cursors in the SQL area. Some of the
useful columns from this view that are included in V$SQL_PLAN_STATISTICS_ALL are:

1) estimated_optimal_size – Estimated size in KB to execute this operation
completely in memory.
2) last_memory_used – The memory (in KB) used by this work area during the last
execution of the cursor
3) active_time – Average time this work area is active (in centi-seconds)

NOTE:

The information on executions and memory usage present in these tables are
considered vital for tuning. It is not necessary to query these 2 tables for all
SQLs. Make use of the information provided in these 2 tables for the SQLs that
consumes lot of resources.

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 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