Development > Utilities > Verifications > Database statistics > Oracle statistics
Oracle statistics
This function is used to view the statistics generated for the database when the database used is Oracle™.
It is also used to generate or delete the statistics.
Important remark
Warning, this type of functionfacilitates, via a user interface that is homogenous with the software, the launch of procedures devolved to a database administrator. It is constructed to be launched by such an administrator (normally called DBA).
It therefore assumes a prior knowledge of database functioning and optimization in order to be used profitably.
However, an inappropriate usage of the function may pose serious performance problems and place the security of the software usage in peril.
Screen management
This function is used to display and to generate or delete the statistics on the database.
These elements are presented in three tabs : the first gives the list of tables and the status of the statistics for each, the second gives the list of the indexes and the status of the statistics for each, the third gives the list of database parameters linked to the optimizer and the usage of the statistics.
Header
Presentation
Used to identify the characteristics of the database used.
Close
Fields
The following fields are present on this tab :
| Nom de la base de données |
| Numéro de version de la base de données |
| Description de la version de la base de données |
Close
Tab Tables
Presentation
This tab displays the list of tables and the status of the statistics for each of these.
It is possible to generate or delete the statistics :
- for a table by right click,
- for all or some of the tables by selecting the line required before triggering the action using the buttons at the bottom of the screen
The deletion of the statistics on a table will delete all the statistics generated for it and therefore also its indexes.
The generation of the statistics on a table will not generate the statistics on its indexes.
Close
Fields
The following fields are present on this tab :
Structure
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Close
Functions accessed by right click on the grid
Yes to all the rest
When there is an grid with multiple choice entries with a choice of response Yes / Noon each line, this function is used to respond Yes to all the lines from this line.
No to all the rest
When there is an grid with multiple choice entries with a choice of response Yes / Noon each line, this function is used to respond No to all the lines from this line.
Refresh the line
Used to refresh the display of the current line in the grid
Generate for the line
Used to generate the statistics for the current line in the grid
Delete the line
Used to delete the statistics for the current line in the grid
Fermer
Tab Index
Presentation
This tab displays the list of indexes and the status of the statistics for each of these.
Close
Fields
The following fields are present on this tab :
Table
| 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. |
| Name of the table in the database. |
| Indicate if the valid statistics are generated in the database. |
| Date of the last analysis of the statistics |
| Time of the last analysis of the statistics |
Close
It is possible to generate or delete the statistics :
- for a index by right click,
- for all or some of the indexes by selecting the line required before triggering the action using the buttons at the bottom of the screen
AFC/APATCHA/40/210
AFC/APATCHA/40/215
Functions accessed by right click on the grid
Refresh the line
Used to refresh the display of the current line in the grid
Generate for the line
Used to generate the statistics for the current line in the grid
Delete the line
Used to delete the statistics for the current line in the grid
Fermer
Tab Basis
Presentation
This tab presents the list of parameters for the database linked to the optimizer and the usage of the statistics.
Close
Fields
The following fields are present on this tab :
Table
| 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. |
| Name of the table in the database. |
| Name of the index in the database |
| Indicate if the valid statistics are generated in the database. |
| Date of the last analysis of the statistics |
| Time of the last analysis of the statistics |
Close
It is not possible to operate on these values here.
Functions accessed by right click on the grid
Tab Basis
Fields
The following fields are present on this tab :
Table Optimizer parameters
| 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. |
|
|
|
|
|
|
Close
Specific Buttons
This button, also accessible using the short cut, used to refresh the display of the tabs. |
Used to generate the statistics for all the selected lines in the grids in the tabs Tables and Index. |
Used to delete the statistics for all the selected lines in the grids in the tabs Tables and Index. |
Explanations for the Oracle statistics
What statistics must be calculated ?
This depends on the rate and volume of changes to the data in the database.
The statistics for an object become obsolete when a large volume of DML activity takes place on the object.
It is therefore necessary to follow a mass insertion or deletion by a new analysis, in order to ensure the correspondence between the statistics in the dictionary, the distribution and the contents of the lines in a table. If after this the table contains a large number of lines and the statistics are based on a small subset of these, it is possible that the execution plan constructed by the optimizer will not be optimal.
How does the optimizer function ?
The optimization based on the cost is declared by the optimizer_mode parameter that can take 3 values
- ALL_ROWS (default value in Oracle 10)
- FIRST_ROWS [n]
- FIRST_ROWS
- CHOOSE (default value in Oracle 9, not supported in Oracle 10)
Static optimization is declared by the value RULE. In this case, the optimizer uses the request system to carryout the best execution plan. The syntax optimizer is no longer supported in Oracle 10.
- If the OPTIMIZER_MODE parameter is set to ALL_ROWS, it means "Best debit" : the request is executed so that it is the most rapid possible for all the records to be loaded.
- If the OPTIMIZER_MODE parameter is set to FIRST_ROWS, it means "Best time" : the request is executed so that the first response is found as rapidly as possible.
- If the OPTIMIZER_MODE parameter is set to FIRST_ROWS[n], it means "Best time" : the request is executed so that the first n responses are found as rapidly as possible.
- If the OPTIMIZER_MODE parameter is set to CHOOSE, it is the presence of the statistics in the dictionary that determines if the statistics optimizer is used.
Use of a mode based on the costs
It is important that the statistics are generated for all the objects in a structure. In fact, the presence of partial statistics for a select instruction can lead to the server process evaluating statistics for objects that it does not have available. This type of statistics sampling carried out during the execution are not saved in a permanent fashion in the data dictionary. It is therefore repeated on each execution for the same request.
Technical information
All the information on the tables, index and statistics are read directly in the database from the Oracle tables and views :
- V$PARAMETER
- PRODUCT_COMPONENT_VERSION
- USER_TABLES
- USER_INDEXES
The dates for the statistics corresponding to the column LAST_ANALYZED for the Oracle USER_TABLES and USER_INDEXES views.
The statistics are generated with the help of the Oracle DBMS_STATS package.
The procedures used are :
- GATHER_TABLE_STATS
- GATHER_INDEX_STATS
- DELETE_TABLE_STATS
- DELETE_INDEX_STATS