Nov 10: 10g Optimiser Environment Views
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
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
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.
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
So if I look for a session to play with :-
I know session 147 is the session I�m interested in (from the username that I've excluded from the output)
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
And pick up the address and hash value of the session�s last statement.
So for any SQL cursor that's still in the SGA, we can see what the parameters where set to when it was parsed.
Finally, I'll change the sort_area_size back again and you'll see the optimiser environment view reflect this.
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.
Cheers,
Doug
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> select table_name from dict where table_name like 'V$%OPTIMIZER_ENV';
TABLE_NAME
------------------------------
V$SES_OPTIMIZER_ENV
V$SQL_OPTIMIZER_ENV
V$SYS_OPTIMIZER_ENV
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
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.
SQL> desc v$sql_optimizer_env
Name Null? Type
----------------------------------------- -------- ----------------------------
ADDRESS RAW(8)
HASH_VALUE NUMBER
SQL_ID VARCHAR2(13)
CHILD_ADDRESS RAW(8)
CHILD_NUMBER NUMBER
ID NUMBER
NAME VARCHAR2(40)
ISDEFAULT VARCHAR2(3)
VALUE VARCHAR2(25)
SQL> desc v$ses_optimizer_env
Name Null? Type
----------------------------------------- -------- ----------------------------
SID NUMBER
ID NUMBER
NAME VARCHAR2(40)
ISDEFAULT VARCHAR2(3)
VALUE VARCHAR2(25)
SQL> desc v$sys_optimizer_env
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER
NAME VARCHAR2(40)
ISDEFAULT VARCHAR2(3)
VALUE VARCHAR2(25)
DEFAULT_VALUE VARCHAR2(25)
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
SQL> set lines 160
SQL> set pages 9999
SQL> select name, value, isdefault
2 from v$sys_optimizer_env
3 order by 3,1;
NAME VALUE ISD
---------------------------------------- ------------------------- ---
active_instance_count 1 YES
bitmap_merge_area_size 1048576 YES
cpu_count 4 YES
cursor_sharing exact YES
db_file_multiblock_read_count 8 YES
hash_area_size 131072 YES
optimizer_dynamic_sampling 2 YES
optimizer_features_enable 10.1.0.3 YES
optimizer_index_caching 0 YES
optimizer_index_cost_adj 100 YES
optimizer_mode all_rows 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 102400 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
statistics_level typical YES
workarea_size_policy auto YES
25 rows selected.
So if I look for a session to play with :-
SQL> select sid, serial#, sql_address, sql_hash_value
2 from v$session
3* where username is not null
SQL> /
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
147 25160 0000000386E5DBB8 3964960483
148 26658 00 0
149 31957 00 0
150 31744 00 0
151 22609 00 0
152 42614 00 0
18 rows selected.
I know session 147 is the session I�m interested in (from the username that I've excluded from the output)
SQL> alter session set sort_area_size = 1048576;
Session altered.
SQL> select name, value, isdefault
2 from v$ses_optimizer_env
3 where sid = 147
4 order by isdefault, name;
NAME VALUE ISD
---------------------------------------- ------------------------- ---
hash_area_size 2097152 NO
sort_area_size 1048576 NO
active_instance_count 1 YES
bitmap_merge_area_size 1048576 YES
cpu_count 4 YES
cursor_sharing exact YES
db_file_multiblock_read_count 8 YES
optimizer_dynamic_sampling 2 YES
optimizer_features_enable 10.1.0.3 YES
optimizer_index_caching 0 YES
optimizer_index_cost_adj 100 YES
optimizer_mode all_rows 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 102400 KB YES
query_rewrite_enabled true YES
query_rewrite_integrity enforced YES
skip_unusable_indexes true YES
sort_area_retained_size 0 YES
star_transformation_enabled false YES
statistics_level typical YES
workarea_size_policy auto YES
25 rows selected.
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
SQL> select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
------------------------------
SYSTEM
UNDOTBS
SYSAUX
TEMP
TOOLS
CMDB_DATA_0128_01
CMDB_INDEX_0128_01
7 rows selected.
And pick up the address and hash value of the session�s last statement.
SQL> select sid, serial#, sql_address, sql_hash_value
2 from v$session
3 where username is not null;
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
17 rows selected.
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;
0000000386E5DBB8 hash_area_size 2097152 NO
0000000386E5DBB8 sort_area_size 1048576 NO
0000000386E5DBB8 active_instance_count 1 YES
0000000386E5DBB8 bitmap_merge_area_size 1048576 YES
0000000386E5DBB8 cpu_count 4 YES
0000000386E5DBB8 cursor_sharing exact YES
0000000386E5DBB8 db_file_multiblock_read_count 8 YES
0000000386E5DBB8 optimizer_dynamic_sampling 2 YES
0000000386E5DBB8 optimizer_features_enable 10.1.0.3 YES
0000000386E5DBB8 optimizer_index_caching 0 YES
0000000386E5DBB8 optimizer_index_cost_adj 100 YES
0000000386E5DBB8 optimizer_mode all_rows YES
0000000386E5DBB8 parallel_ddl_mode enabled YES
0000000386E5DBB8 parallel_dml_mode disabled YES
0000000386E5DBB8 parallel_execution_enabled true YES
0000000386E5DBB8 parallel_query_mode enabled YES
0000000386E5DBB8 parallel_threads_per_cpu 2 YES
0000000386E5DBB8 pga_aggregate_target 102400 KB YES
0000000386E5DBB8 query_rewrite_enabled true YES
0000000386E5DBB8 query_rewrite_integrity enforced YES
0000000386E5DBB8 skip_unusable_indexes true YES
0000000386E5DBB8 sort_area_retained_size 0 YES
0000000386E5DBB8 star_transformation_enabled false YES
0000000386E5DBB8 statistics_level typical YES
0000000386E5DBB8 workarea_size_policy auto YES
So for any SQL cursor that's still in the SGA, we can see what the parameters where set to when it was parsed.
Finally, I'll change the sort_area_size back again and you'll see the optimiser environment view reflect this.
SQL> alter session set sort_area_size = 65536;
Session altered.
SQL> select name, value, isdefault
2 from v$ses_optimizer_env
3 where sid = 147
4 order by 3,1;
NAME VALUE ISD
---------------------------------------- ------------------------- ---
active_instance_count 1 YES
bitmap_merge_area_size 1048576 YES
cpu_count 4 YES
cursor_sharing exact YES
db_file_multiblock_read_count 8 YES
hash_area_size 131072 YES
optimizer_dynamic_sampling 2 YES
optimizer_features_enable 10.1.0.3 YES
optimizer_index_caching 0 YES
optimizer_index_cost_adj 100 YES
optimizer_mode all_rows 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 102400 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
statistics_level typical YES
workarea_size_policy auto YES
25 rows selected.
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.
Cheers,
Doug
Trackbacks
Trackback specific URI for this entry
No Trackbacks
Comments
Display comments as
(Linear | Threaded)
#1 - Barry Cooper said:
2005-11-11 07:22 - (Reply)
Re: Jonathan's book. I pre-ordered it from Amazon.co.uk when I first saw it mentioned (i think on AskTom).
It arrived the day of it's release, which was a nice surprise.
One of my colleagues ordered a copy once he'd seen mine and got it within a couple of days as well. So i guess you may have to take the Amazon expected lead times with a pinch of salt.
#2 - Doug Burns said:
2005-11-11 12:32 - (Reply)
Thanks Barry. I passed your comment on to Jonathan. Maybe it's just a sell-out?
