Development >  Utilities >  Verifications >  Database statistics >  Oracle statistics  

Display all Hide all

This function is used to visualize the statistics generated on the data base when the database under utilization is Oracle™.

It can also be used to generate or delete statistics.

Important remark

Warning, this type of function facilitates, via a user interface homogenous with the software, the launch of live procedures dedicated to a database admin. It is made to be launched by such an admin (usually called DBA).

To be used optimally, the user should be familiar with the functioning of databases and their optimization.

Conversely, an inappropriate use of the function can be problematic for the performances of the system and might compromise the utilization of the software.

Screen management

This function is used to visualize, generate or delete statistics on the database.
These elements are displayed in three tabs: the first one gives the list of tables and the statistics report for each of them, the second one gives the list of indexes and the statistics report for each of them, the third one gives the list of database parameters linked to the optimizer and linked to the use of statistics.

Header

Presentation

Used to identify the characteristics of the database used.

Close

 

Fields

The following fields are present on this tab :

  • Database (field BDDNAM)

Name of the database.
  • Version (field VER)

Version number of the database.
  • field VERDES

Description of the database version.

Close

 

Tab Tables

Presentation

This tab displays the list of tables and the statistic report for each of them.

It is possible to generate or delete the statistics:

    • via right click for a table,
    • all or some tables by selecting the requested lines before triggering the action using the button at the bottom of the screen

The delete of statistics on a table will delete all the statistics generated for it and, as a consequence, for its indexes.

The generation of statistics on a table will not generate statistics on its indexes.

Close

 

Fields

The following fields are present on this tab :

Grid

  • To process (field PROFLG)

Used to define if the line must be processed in generation or deletion of the statistics, using the corresponding buttons at the bottom of the screen.

  • Table (field TAB)

Name of the table in the database.

  • Statistics (field STTFLG)

Indicate if the valid statistics are generated in the database.

  • Last analysis date (field STTDAT)

Date of the last analysis of the statistics

  • field STTHOU

Time of the last analysis of the statistics

Close

 

Action icon

Yes to all the rest

When faced with an entry grid with multiple choices and a Yes/No answer on each line, this function makes it possible to answer Yes to all the lines starting from the current line.

No to all the rest

When faced with an entry grid with multiple choices and a Yes/No answer on each line, this function makes it possible to answer No to all the lines starting from the current line.

Refresh the line

Used to refresh the display of the grid's current line

Generate for the line

Used to generate statistics for the grid's current line.

Delete the line

Used to delete statistics for the grid's current line.

 

Close

 

Tab Index

Presentation

This tab displays the list of indexes and the statistic report for each of them.

Close

 

Fields

The following fields are present on this tab :

Grid

  • To process (field PROFLG)

Used to define if the line must be processed in generation or deletion of the statistics, using the corresponding buttons at the bottom of the screen.

  • Table (field TAB)

Name of the table in the database.

  • Index (field INDEX)

Name of the index in the database

  • Statistics (field STTFLG)

Indicate if the valid statistics are generated in the database.

  • Last analysis date (field STTDAT)

Date of the last analysis of the statistics

  • field STTHOU

Time of the last analysis of the statistics

Close

 

It is possible to generate or delete the statistics:

    • via right click for an index,
    • all or some indexes by selecting the requested lines before triggering the action using the buttons at the bottom of the screen

Action icon

Yes to all the rest

When faced with an entry grid with multiple choices and a Yes/No answer on each line, this function makes it possible to answer Yes to all the lines starting from the current line.

No to all the rest

When faced with an entry grid with multiple choices and a Yes/No answer on each line, this function makes it possible to answer No to all the lines starting from the current line.

Refresh the line

Used to refresh the display of the grid's current line

Generate for the line

Used to generate statistics for the grid's current line.

Delete the line

Used to delete statistics for the grid's current line.

 

Close

 

Tab Basis

Presentation

This tab displays the list of database parameters linked to the optimizer and the utilization of statistics.

Close

 

Fields

The following fields are present on this tab :

Grid Optimizer parameters

  • Instance (field INSTID)

 
  • Parameter (field PAR)

Display the parameters, linked to the optimizer and the use of the statistics.

The parameters present depend on the Oracle version. These parameters cannot be modified from this option.
  • Value (field PARVAL)

 
  • Default value (field DEFVAL)

 
  • Description (field DES)

 

Close

 

It is not possible to act on these values.

Action icon

Refresh the line
Generated for the line
Deleted for the line

 

Close

 

Tab Basis

Fields

The following fields are present on this tab :

Grid Optimizer parameters

  • Instance (field INSTID)

 
  • Parameter (field PAR)

Display the parameters, linked to the optimizer and the use of the statistics.

The parameters present depend on the Oracle version. These parameters cannot be modified from this option.
  • Value (field PARVAL)

 
  • Default value (field DEFVAL)

 
  • Description (field DES)

 

Close

 

Specific Buttons

This button, also accessible using the short cut, used to refresh the display of the tabs.

Used to generate statistics for all the selected lines in the Tables and Index tab's grids.

Used to delete statistics for all the selected lines in the Tables and Index tab's grids.

Explanations for the Oracle statistics

When should the statistics be calculated?

It depends on the change rate and volume of the data from the database.

The statistics of an object become obsolete when an important DML activity volume is operated on the object.
The user should pay attention to process to a new analysis after an insert or a delete so as to be sure that the dictionary statistics the distribution and the content of the grid's line match. If the table contains afterwards an important number of lines and if the statistics are about a small proportion of them, the execution plan built by the optimizer might not be optimal.

How does the optimizer work?

The optimization based on cost is declared by the optimizer_mode parameter which can have three values

    • ALL_ROWS (default value in Oracle 10)
    • FIRST_ROWS [n]
    • FIRST_ROWS
    • CHOOSE (default value in Oracle 9, not compatible with Oracle 10)


The statistics optimization is declared by the value RULE. In that case, the optimizer uses the syntax of the request in order to perform the best execution plan. The optimizer of syntax is not compatible with Oracle 10.

  • If the parameter OPTIMIZER_MODE is set to  ALL_ROWS, it means "Best debit": the request is executed so that it can be as fast as possible for all entries to load.
  • If the parameter OPTIMIZER_MODE is set to  FIRST_ROWS, it means "Best time": the request is executed so that the first answer can be found as fast as possible.
  • If the parameter OPTIMIZER_MODE is set to  FIRST_ROWS [n], it means "Best time": the request is executed so that the "n" first answers can be found as fast as possible.
  • If the parameter OPTIMIZER_MODE is set to CHOOSE, the presence of statistics in the dictionary determines if the statistics optimizer is used.

Use of a mode based on costs

It is important that statistics be generated for all the objects within a framework. Indeed, the presence of partial statistics for an instruction "select" can cause the evaluation of statistics for objects that do not have statistics. This type of statistics sample carried out during the execution is not entered permanently in the data dictionary. Therefore, it is repeated at each execution of the same request.

Technical information

All information on tables, indexes and statistics are read directly in the database via Oracle tables and views:

    • V$PARAMETER
    • PRODUCT_COMPONENT_VERSION
    • USER_TABLES
    • USER_INDEXES

The statistics dates correspond to the column LAST_ANALYZED of the Oracle views USER_TABLES et USER_INDEXES.

Statistics are generated thanks to the package Oracle DBMS_STATS.
The processes used are:

    • GATHER_TABLE_STATS
    • GATHER_INDEX_STATS
    • DELETE_TABLE_STATS
    • DELETE_INDEX_STATS

Error messages

The only error messages are the generic ones.

Tables used

SEEREFERTTO Refer to documentation Implementation