{"id":907,"date":"2005-11-10T12:00:00","date_gmt":"2005-11-10T12:00:00","guid":{"rendered":"http:\/\/orcldoug.com\/blog\/?p=907"},"modified":"2005-11-10T12:00:00","modified_gmt":"2005-11-10T12:00:00","slug":"10g-optimiser-environment-views","status":"publish","type":"post","link":"http:\/\/orcldoug.com\/blog\/2005\/11\/10\/10g-optimiser-environment-views\/","title":{"rendered":"10g Optimiser Environment Views"},"content":{"rendered":"

In a previous blog<\/a> I mentioned that Julian Dyke had talked about these views during his presentation and I noticed Jonathan Lewis also includes it in an Appendix of his new book<\/a> that I started reading last night. There are three versions of the view<\/p>\n

<\/p>\n

Connected to:Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - 64bit ProductionWith the Partitioning, OLAP and Data Mining options
<\/p>SQL> select table_name from dict where table_name like 'V$%OPTIMIZER_ENV';
<\/p>TABLE_NAME------------------------------V$SES_OPTIMIZER_ENVV$SQL_OPTIMIZER_ENVV$SYS_OPTIMIZER_ENV<\/pre>\n
v$sql_optimizer_env – Parameter settings used for every cursor in the SGA
v$ses_optimizer_env – Parameter settings being used by each session
v$sys_optimizer_env – System-wide parameter settings<\/p>\n
The view contains a subset of the parameter settings that the optimiser has used or will use. The three different views are fairly similar, containing the parameter ID, NAME, whether it’s set to the default value and what the current value is. Here I’ve highlighted the slight differences between the three versions of the view.<\/p>\n
SQL> desc v$sql_optimizer_envName                                      Null?    Type----------------------------------------- -------- ----------------------------ADDRESS                                            RAW(8)HASH_VALUE                                         NUMBERSQL_ID                                             VARCHAR2(13)CHILD_ADDRESS                                      RAW(8)CHILD_NUMBER                                       NUMBER<\/em>ID                                                 NUMBERNAME                                               VARCHAR2(40)ISDEFAULT                                          VARCHAR2(3)VALUE                                              VARCHAR2(25)
<\/p>SQL> desc v$ses_optimizer_envName                                      Null?    Type----------------------------------------- -------- ----------------------------SID                                                NUMBER<\/em>ID                                                 NUMBERNAME                                               VARCHAR2(40)ISDEFAULT                                          VARCHAR2(3)VALUE                                              VARCHAR2(25)
<\/p>SQL> desc v$sys_optimizer_envName                                      Null?    Type----------------------------------------- -------- ----------------------------ID                                                 NUMBERNAME                                               VARCHAR2(40)ISDEFAULT                                          VARCHAR2(3)VALUE                                              VARCHAR2(25)DEFAULT_VALUE                                      VARCHAR2(25)<\/em><\/pre>\n
As Jonathan points out, it is a small subset of all the parameters that appear in a 10053 trace file but I think it’s still useful. Anyway, here’s the list<\/p>\n
SQL> set lines 160SQL> set pages 9999SQL> select name, value, isdefault 2  from v$sys_optimizer_env 3  order by 3,1;
<\/p>NAME                                     VALUE                     ISD---------------------------------------- ------------------------- ---active_instance_count                    1                         YESbitmap_merge_area_size                   1048576                   YEScpu_count                                4                         YEScursor_sharing                           exact                     YESdb_file_multiblock_read_count            8                         YEShash_area_size                           131072                    YESoptimizer_dynamic_sampling               2                         YESoptimizer_features_enable                10.1.0.3                  YESoptimizer_index_caching                  0                         YESoptimizer_index_cost_adj                 100                       YESoptimizer_mode                           all_rows                  YESparallel_ddl_mode                        enabled                   YESparallel_dml_mode                        disabled                  YESparallel_execution_enabled               true                      YESparallel_query_mode                      enabled                   YESparallel_threads_per_cpu                 2                         YESpga_aggregate_target                     102400 KB                 YESquery_rewrite_enabled                    true                      YESquery_rewrite_integrity                  enforced                  YESskip_unusable_indexes                    true                      YESsort_area_retained_size                  0                         YESsort_area_size                           65536                     YESstar_transformation_enabled              false                     YESstatistics_level                         typical                   YESworkarea_size_policy                     auto                      YES
<\/p>25 rows selected.<\/pre>\n
So if I look for a session to play with :-<\/p>\n
SQL> select sid, serial#, sql_address, sql_hash_value 2  from v$session 3* where username is not nullSQL> \/
<\/p>      SID    SERIAL# SQL_ADDRESS      SQL_HASH_VALUE---------- ---------- ---------------- --------------      109      55082 00                            0      110      26846 0000000386CBE208     1766478194      112       4359 00                            0      115      17068 000000038615F338     1797293310      116       5603 00000003894F0030     1719539911      119       3535 00                            0      121      28267 00000003894F0030     1719539911      127       8656 00                            0      131      37978 00                            0      133      44095 00                            0      137      10509 00000003894F0030     1719539911      142       4721 00000003894F0030     1719539911     <\/strong> 147      25160 0000000386E5DBB8     3964960483<\/em>            148      26658 00                            0      149      31957 00                            0      150      31744 00                            0      151      22609 00                            0      152      42614 00                            0
<\/p>18 rows selected.<\/pre>\n
I know session 147 is the session I\ufffdm interested in (from the username that I’ve excluded from the output)<\/p>\n
SQL> alter session set sort_area_size = 1048576;
<\/p>Session altered.
<\/p>SQL> select name, value, isdefault 2  from v$ses_optimizer_env 3  where sid = 147 4  order by isdefault, name;
<\/p>NAME                                     VALUE                     ISD---------------------------------------- ------------------------- ---hash_area_size                           2097152                   NOsort_area_size                           1048576                   NOactive_instance_count                    1                         YESbitmap_merge_area_size                   1048576                   YEScpu_count                                4                         YEScursor_sharing                           exact                     YESdb_file_multiblock_read_count            8                         YESoptimizer_dynamic_sampling               2                         YESoptimizer_features_enable                10.1.0.3                  YESoptimizer_index_caching                  0                         YESoptimizer_index_cost_adj                 100                       YESoptimizer_mode                           all_rows                  YESparallel_ddl_mode                        enabled                   YESparallel_dml_mode                        disabled                  YESparallel_execution_enabled               true                      YESparallel_query_mode                      enabled                   YESparallel_threads_per_cpu                 2                         YESpga_aggregate_target                     102400 KB                 YESquery_rewrite_enabled                    true                      YESquery_rewrite_integrity                  enforced                  YESskip_unusable_indexes                    true                      YESsort_area_retained_size                  0                         YESstar_transformation_enabled              false                     YESstatistics_level                         typical                   YESworkarea_size_policy                     auto                      YES
<\/p>25 rows selected.<\/pre>\n
So I can see that I’ve changed sort_area_size for my session and that hash_area_size has also been changed to twice the size of the sort area. Next I’ll execute a statement in the session I’m monitoring<\/p>\n
SQL> select tablespace_name from dba_tablespaces;
<\/p>TABLESPACE_NAME------------------------------SYSTEMUNDOTBSSYSAUXTEMPTOOLSCMDB_DATA_0128_01CMDB_INDEX_0128_01
<\/p>7 rows selected.<\/pre>\n
And pick up the address and hash value of the session\ufffds last statement.<\/p>\n
SQL> select sid, serial#, sql_address, sql_hash_value 2  from v$session 3  where username is not null;
<\/p>      SID    SERIAL# SQL_ADDRESS      SQL_HASH_VALUE---------- ---------- ---------------- --------------      110      26846 00000003892A0D48     3929166307      112       4359 00                            0      115      17068 000000038615F338     1797293310      116       5603 00000003894F0030     1719539911      119       3535 00                            0      121      28267 0000000386CBE208     1766478194      127       8656 00                            0      131      37978 00                            0      133      44095 00                            0      137      10509 00000003894F0030     1719539911      142       4721 00000003894F0030     1719539911      147      25170 0000000386E5DBB8     3964960483      148      26658 00                            0      149      31957 00                            0      150      31744 00                            0      151      22609 00                            0      152      42614 00                            0
<\/p>17 rows selected.
<\/p>SQL> select address, name, value, isdefault 2  from v$sql_optimizer_env 3  where address = '0000000386E5DBB8' and hash_value = 3964960483 4* order by child_number, isdefault, name;
<\/p>0000000386E5DBB8 hash_area_size                   2097152                   NO0000000386E5DBB8 sort_area_size                   1048576                   NO0000000386E5DBB8 active_instance_count            1                         YES0000000386E5DBB8 bitmap_merge_area_size           1048576                   YES0000000386E5DBB8 cpu_count                        4                         YES0000000386E5DBB8 cursor_sharing                   exact                     YES0000000386E5DBB8 db_file_multiblock_read_count    8                         YES0000000386E5DBB8 optimizer_dynamic_sampling       2                         YES0000000386E5DBB8 optimizer_features_enable        10.1.0.3                  YES0000000386E5DBB8 optimizer_index_caching          0                         YES0000000386E5DBB8 optimizer_index_cost_adj         100                       YES0000000386E5DBB8 optimizer_mode                   all_rows                  YES0000000386E5DBB8 parallel_ddl_mode                enabled                   YES0000000386E5DBB8 parallel_dml_mode                disabled                  YES0000000386E5DBB8 parallel_execution_enabled       true                      YES0000000386E5DBB8 parallel_query_mode              enabled                   YES0000000386E5DBB8 parallel_threads_per_cpu         2                         YES0000000386E5DBB8 pga_aggregate_target             102400 KB                 YES0000000386E5DBB8 query_rewrite_enabled            true                      YES0000000386E5DBB8 query_rewrite_integrity          enforced                  YES0000000386E5DBB8 skip_unusable_indexes            true                      YES0000000386E5DBB8 sort_area_retained_size          0                         YES0000000386E5DBB8 star_transformation_enabled      false                     YES0000000386E5DBB8 statistics_level                 typical                   YES0000000386E5DBB8 workarea_size_policy             auto                      YES<\/pre>\n
So for any SQL cursor that’s still in the SGA, we can see what the parameters where set to when it was parsed.<\/p>\n
Finally, I’ll change the sort_area_size back again and you’ll see the optimiser environment view reflect this.<\/p>\n
SQL> alter session set sort_area_size = 65536;
<\/p>Session altered.
<\/p>SQL> select name, value, isdefault 2   from v$ses_optimizer_env 3  where sid = 147 4  order by 3,1;
<\/p>NAME                                     VALUE                     ISD---------------------------------------- ------------------------- ---active_instance_count                    1                         YESbitmap_merge_area_size                   1048576                   YEScpu_count                                4                         YEScursor_sharing                           exact                     YESdb_file_multiblock_read_count            8                         YEShash_area_size                           131072                    YESoptimizer_dynamic_sampling               2                         YESoptimizer_features_enable                10.1.0.3                  YESoptimizer_index_caching                  0                         YESoptimizer_index_cost_adj                 100                       YESoptimizer_mode                           all_rows                  YESparallel_ddl_mode                        enabled                   YESparallel_dml_mode                        disabled                  YESparallel_execution_enabled               true                      YESparallel_query_mode                      enabled                   YESparallel_threads_per_cpu                 2                         YESpga_aggregate_target                     102400 KB                 YESquery_rewrite_enabled                    true                      YESquery_rewrite_integrity                  enforced                  YESskip_unusable_indexes                    true                      YESsort_area_retained_size                  0                         YESsort_area_size                           65536                     YESstar_transformation_enabled              false                     YESstatistics_level                         typical                   YESworkarea_size_policy                     auto                      YES
<\/p>25 rows selected.<\/pre>\n
As for Jonathan’s book, I’ve barely started Chapter 2 but it looks as excellent as I expected. I’ll try to shut up about it until I’ve read it and can review it properly but I think you can assume that I’ll be recommending it. Note that amazon.co.uk are showing 1-2 month lead times but I got mine in a couple of days. Either it’s very popular and they’ve sold out their initial allocation or you shouldn’t necessarily believe the website. I’m not sure which but if anyone knows any more, post a comment.<\/p>\n
Cheers,<\/p>\n
Doug<\/p>\n","protected":false},"excerpt":{"rendered":"
In a previous blog I mentioned that Julian Dyke had talked about these views during his presentation and I noticed Jonathan Lewis also includes it in an Appendix of his new book that I started reading last night. There are three versions of the view Connected to:Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 – 64bit… Continue reading 10g Optimiser Environment Views<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-907","post","type-post","status-publish","format-standard","hentry","category-uncategorized","entry"],"jetpack_featured_media_url":"","jetpack-related-posts":[{"id":1000,"url":"http:\/\/orcldoug.com\/blog\/2006\/06\/18\/saving-optimiser-stats-10g\/","url_meta":{"origin":907,"position":0},"title":"Saving Optimiser Stats – 10g","date":"June 18, 2006","format":false,"excerpt":"In my previous blog I showed how you can save your current optimiser stats into a seperate table whenever you refresh them, so that they can be restored should the new statistics lead to poor SQL execution plans. Oracle have added an improved version of this facility to 10g (which\u2026","rel":"","context":"Similar post","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1019,"url":"http:\/\/orcldoug.com\/blog\/2006\/07\/14\/alter-table-move-and-table-stats-part-ii\/","url_meta":{"origin":907,"position":1},"title":"alter table … move and table stats (part II)","date":"July 14, 2006","format":false,"excerpt":"Following up on another comment from Howard, I took the initrans change off the alter table ... move so that it was the most basic variation and then ran it on 8i, 9i and 10g. I've trimmed lots of the output this time, but I have the log files if\u2026","rel":"","context":"Similar post","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1570,"url":"http:\/\/orcldoug.com\/blog\/2010\/02\/28\/statistics-on-partitioned-tables-part-5\/","url_meta":{"origin":907,"position":2},"title":"Statistics on Partitioned Tables – Part 5","date":"February 28, 2010","format":false,"excerpt":"Actually, before looking at any recent features, let me introduce one more aspect of the existing aggregation approach used by Oracle. The examples used to date have been based on INSERTing new rows into subpartitions and, although that's the approach used for some of our tables and will suit some\u2026","rel":"","context":"With 18 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":999,"url":"http:\/\/orcldoug.com\/blog\/2012\/06\/18\/saving-optimiser-stats-9i\/","url_meta":{"origin":907,"position":3},"title":"Saving Optimiser Stats – 9i","date":"June 18, 2012","format":false,"excerpt":"In a recent blog I described how a mis-timed optimiser statistics collection job led to a bad execution plan for one of the SQL statements in a regular batch jobIt's no coincidence that we were already in the process of implementing a change to our stats collection period to retain\u2026","rel":"","context":"With 4 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1596,"url":"http:\/\/orcldoug.com\/blog\/2010\/04\/22\/statistics-on-partitioned-tables-part-6a-copy_table_stats-intro\/","url_meta":{"origin":907,"position":4},"title":"Statistics on Partitioned Tables – Part 6a – COPY_TABLE_STATS – Intro","date":"April 22, 2010","format":false,"excerpt":"[Phew. At last. The first draft of this was dated more than two weeks ago .... One of the problems with blogging about copying stats was the balance between explaining it and pointing out some of the problems I've encountered. So I've broken up this post, with a little explanation\u2026","rel":"","context":"With 4 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1562,"url":"http:\/\/orcldoug.com\/blog\/2010\/02\/17\/statistics-on-partitioned-tables-part-1\/","url_meta":{"origin":907,"position":5},"title":"Statistics on Partitioned Tables – Part 1","date":"February 17, 2010","format":false,"excerpt":"If you've ever worked on large databases that use partitioned and subpartitioned tables, you'll be aware that there are significant challenges in maintaining up-to-date\/appropriate statistics. We've encountered a few problems at work recently and I decided it would be an idea to put together a series of posts covering the\u2026","rel":"","context":"With 11 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"_links":{"self":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts\/907","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/comments?post=907"}],"version-history":[{"count":0,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts\/907\/revisions"}],"wp:attachment":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/media?parent=907"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/categories?post=907"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/tags?post=907"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}