Use the optimizer performance view to obtain the SQL statement execution environment

The running environment of Oracle SQL statements is divided into multiple different levels, including instance level, session level, and statement level. The priority increases sequentially. That is, the statement-level execution environment has the highest priority, followed by the session level, and the instance level is the lowest. In turn, the instance-level environment settings affect the global environment, while the session-level affects the current session, and the statement-level settings only affect the current statement. It can be seen that the parameters in each stage in the runtime environment have a direct impact on the final database performance or the executed SQL statement. Therefore, it is particularly important to obtain the current SQL statement running environment when optimizing or debugging the database. Therefore, Oracle provides three important views to obtain parameter information at different levels.

1. Name of the optimizer performance View

-- The following three performance views correspond to different levels.
Scott @ ORCL> select table_name from dict where table_name like 'v $ % OPTIMIZER_ENV ';

TABLE_NAME
------------------------------
V $ SYS_OPTIMIZER_ENV
V $ SES_OPTIMIZER_ENV
V $ SQL _OPTIMIZER_ENV

-- Note: the preceding three views are actually a subset of parameters in Oracle 10053 events.

Ii. instance-level execution environment (view V $ SYS_OPTIMIZER_ENV)

-- Provides instance-level environment execution information. When the Oracle optimizer generates an execution plan for an SQL statement, it is determined based on the value of instance-level parameters. For example, optimizer_mode is used
-- Determine the optimizer_index_cost_adj mode to determine the cost ratio between full table scan and index scan.
-- You can set instance-level parameters in the following way:

Alter system set parameter = value scope = memory | both | spfile;

-- Let's take a look at the running environment of the current instance.

Scott @ ORCL> select
2 name,
3 value,
4 isdefault
5 from
6 v $ sys_optimizer_env
7 order by 3, 1;

NAME | VALUE | ISD
----------------------------------- | --------------- | ---
Statistics_level | all | NO --> the non-default value is displayed as NO here.
Active_instance_count | 1 | YES
Bitmap_merge_area_size | 1048576 | YES
Cpu_count | 1 | YES
Cursor_sharing | exact | YES
Hash_area_size | 131072 | YES
Optimizer_dynamic_sampling | 2 | YES
Optimizer_features_enable | 10.2.0.1 | YES
Optimizer_index_caching | 0 | YES
Optimizer_index_cost_adj | 100 | YES
Optimizer_mode | all_rows | YES
Optimizer_secure_view_merging | true | YES
Parallel_ddl_mode | enabled | YES
Parallel_dml_mode | disabled | YES
Parallel_execution_enabled | true | YES
Parallel_query_mode | enabled | YES
Parallel_threads_per_cpu | 2 | YES
Pga_aggregate_target | 59392 KB | YES
Query_rewrite_enabled | true | YES
Query_rewrite_integrity | enforced | YES
Skip_unusable_indexes | true | YES
Sort_area_retained_size | 0 | YES
Sort_area_size | 65536 | YES
Star_transformation_enabled | false | YES
Workarea_size_policy | auto | YES

-- From the preceding query results, we can see that only the statistics_level parameter in the current system uses non-default values. The default value of the statistics_level parameter is typical, and here it is all.
-- Changes to any parameter at the instance level have a global impact. Therefore, exercise caution. Of course, if a parameter is set improperly, the impact is global.
-- Reasonable parameter settings can prevent the adjustment of hundreds of boring SQL statements. Conversely, the sharp decline in the performance of the entire database may also be caused by improper instance-level parameter settings.

1 2 3 Next Page