Home » RDBMS Server » Performance Tuning » Alter Session (workareas and area_sizes)
Alter Session (workareas and area_sizes) [message #221624] |
Tue, 27 February 2007 16:15 |
smartin
Messages: 1803 Registered: March 2005 Location: Jacksonville, Florida
|
Senior Member |
|
|
Background: All of this applies to a sandbox 10.2.0.1 db on my pc. For a session operation, workarea_size_policy of auto uses a portion (5% I believe) of pga_agg_target, and if it is manual, it uses the area_size parameters, such as hash_area_size and sort_area_size.
In reading through the alter session docs in the sql reference, I was surprised to learn that the area_sizes were included in the list of modifiable options. Especially considering that if you do an alter system on these two to change the init params themselves, you can not do so unless your scope is set to spfile (can't do it dynamically).
So I'm trying a test to see if I can alter the performance of a query by altering the values of these two area_sizes. It isn't working. But it isn't producing an error on the alter session commands (alter system commonds of the same type do produce errors).
Below was my test case. Does anyone have any thoughts on this situation? Is it as simple as the alter session simply has no affect on these two settings, but is silent about it so that one might think it does? Or is something else going on that I'm missing? And, is there a view to display the current session settings (not using trace file)?
MYDBA@orcl >
MYDBA@orcl > create table test as select * from all_objects;
Table created.
MYDBA@orcl >
MYDBA@orcl > insert into test select * from test;
49829 rows created.
MYDBA@orcl > insert into test select * from test;
99658 rows created.
MYDBA@orcl > insert into test select * from test;
199316 rows created.
MYDBA@orcl > insert into test select * from test;
398632 rows created.
MYDBA@orcl > commit;
Commit complete.
MYDBA@orcl > insert into test select * from test;
797264 rows created.
MYDBA@orcl > insert into test select * from test;
1594528 rows created.
MYDBA@orcl > commit;
Commit complete.
MYDBA@orcl > insert into test select * from test;
3189056 rows created.
MYDBA@orcl > commit;
Commit complete.
MYDBA@orcl >
MYDBA@orcl > -- buffer cache...
MYDBA@orcl > select owner, count(*) from test group by owner having count(*) > 10000;
OWNER COUNT(*)
------------------------------ ----------
DMSYS 24192
OE 14336
SYSMAN 165248
MDSYS 109952
WMSYS 29696
SYSTEM 54400
SH 38272
ORDSYS 213632
PUBLIC 2558592
OLAPSYS 91904
EXFSYS 35712
XDB 44416
CTXSYS 43264
SYS 2930560
14 rows selected.
MYDBA@orcl >
MYDBA@orcl > select value from v$mystat where statistic# in (25,26);
VALUE
----------
3661396
5955156
MYDBA@orcl >
MYDBA@orcl > explain plan for
2 select owner, count(*) from test group by owner having count(*) > 10000;
Explained.
MYDBA@orcl > select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
Plan hash value: 1349321023
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6867K| 111M| 20608 (8)| 00:04:08 |
|* 1 | FILTER | | | | | |
| 2 | HASH GROUP BY | | 6867K| 111M| 20608 (8)| 00:04:08 |
| 3 | TABLE ACCESS FULL| TEST | 6867K| 111M| 19400 (2)| 00:03:53 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(COUNT(*)>10000)
Note
-----
- dynamic sampling used for this statement
19 rows selected.
MYDBA@orcl >
MYDBA@orcl > set timing on
MYDBA@orcl > select owner, count(*) from test group by owner having count(*) > 10000;
OWNER COUNT(*)
------------------------------ ----------
DMSYS 24192
OE 14336
SYSMAN 165248
MDSYS 109952
WMSYS 29696
SYSTEM 54400
SH 38272
ORDSYS 213632
PUBLIC 2558592
OLAPSYS 91904
EXFSYS 35712
XDB 44416
CTXSYS 43264
SYS 2930560
14 rows selected.
Elapsed: 00:00:12.98
MYDBA@orcl > set timing off
MYDBA@orcl >
MYDBA@orcl > select value from v$mystat where statistic# in (25,26);
VALUE
----------
5824084
7724628
MYDBA@orcl >
MYDBA@orcl > alter session set workarea_size_policy = manual;
Session altered.
MYDBA@orcl >
MYDBA@orcl > alter session set hash_area_size=16000;
Session altered.
MYDBA@orcl > alter session set sort_area_size=16000;
Session altered.
MYDBA@orcl >
MYDBA@orcl > explain plan for
2 select owner, count(*) from test group by owner having count(*) > 10000;
Explained.
MYDBA@orcl > select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
Plan hash value: 1349321023
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6867K| 111M| 20608 (8)| 00:04:08 |
|* 1 | FILTER | | | | | |
| 2 | HASH GROUP BY | | 6867K| 111M| 20608 (8)| 00:04:08 |
| 3 | TABLE ACCESS FULL| TEST | 6867K| 111M| 19400 (2)| 00:03:53 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(COUNT(*)>10000)
Note
-----
- dynamic sampling used for this statement
19 rows selected.
MYDBA@orcl >
MYDBA@orcl > set timing on
MYDBA@orcl > select owner, count(*) from test group by owner having count(*) > 10000;
OWNER COUNT(*)
------------------------------ ----------
SYSTEM 54400
EXFSYS 35712
DMSYS 24192
XDB 44416
SYS 2930560
PUBLIC 2558592
SH 38272
OE 14336
WMSYS 29696
CTXSYS 43264
ORDSYS 213632
MDSYS 109952
OLAPSYS 91904
SYSMAN 165248
14 rows selected.
Elapsed: 00:00:12.93
MYDBA@orcl > set timing off
MYDBA@orcl >
MYDBA@orcl > select value from v$mystat where statistic# in (25,26);
VALUE
----------
5824084
7724628
MYDBA@orcl >
MYDBA@orcl > alter session set hash_area_size=10000000;
Session altered.
MYDBA@orcl > alter session set sort_area_size=10000000;
Session altered.
MYDBA@orcl >
MYDBA@orcl > explain plan for
2 select owner, count(*) from test group by owner having count(*) > 10000;
Explained.
MYDBA@orcl > select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
Plan hash value: 1349321023
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6867K| 111M| 20608 (8)| 00:04:08 |
|* 1 | FILTER | | | | | |
| 2 | HASH GROUP BY | | 6867K| 111M| 20608 (8)| 00:04:08 |
| 3 | TABLE ACCESS FULL| TEST | 6867K| 111M| 19400 (2)| 00:03:53 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(COUNT(*)>10000)
Note
-----
- dynamic sampling used for this statement
19 rows selected.
MYDBA@orcl >
MYDBA@orcl > set timing on
MYDBA@orcl > select owner, count(*) from test group by owner having count(*) > 10000;
OWNER COUNT(*)
------------------------------ ----------
DMSYS 24192
OE 14336
SYSMAN 165248
MDSYS 109952
WMSYS 29696
SYSTEM 54400
SH 38272
ORDSYS 213632
PUBLIC 2558592
OLAPSYS 91904
EXFSYS 35712
XDB 44416
CTXSYS 43264
SYS 2930560
14 rows selected.
Elapsed: 00:00:12.59
MYDBA@orcl > set timing off
MYDBA@orcl >
MYDBA@orcl > select value from v$mystat where statistic# in (25,26);
VALUE
----------
5824084
7921236
MYDBA@orcl >
MYDBA@orcl > drop table test;
Table dropped.
MYDBA@orcl >
MYDBA@orcl > spool off
[Updated on: Wed, 28 February 2007 08:30] Report message to a moderator
|
|
|
Re: Alter Session (workareas and area_sizes) [message #221629 is a reply to message #221624] |
Tue, 27 February 2007 16:55 |
gintsp
Messages: 118 Registered: February 2007
|
Senior Member |
|
|
Normally it is working, I see following potential problems with your tests:
1) you haven't done stats gathering so Oracle is doing dynamic sampling therefore your queries are affected by this operation
2) when you use hash and sort area size 16K it is too small, because Reference guide says that minimu for sort_area_size = "Minimum: the value equivalent of six database blocks"
So I don't know what is if you set it less than minimum, either Oracle uses its minimum or comes back to workarea_size+policy=auto
3) it is hard to follow your test case because I don't know what are stats number 25, 26
4) when query can completely run in the provided sort/hahs area space following increase won't help you
5) either your box is too good or the test table is too small because your test cases are running too fast, and also this dynamic sampling thing meant in (1) Another thing might be that all the time actually is used for scanning the table and for hash group by Oracle needs very tiny space, so not very big difference what the hash area size is. You can quite easily check this using 10046 trace and looking for what the query is waiting and how much that takes in % from the all query time.
Ok if you'd like to see an example where it is working then you can check my paper Long running Operations in Oracle (entries in v$session_longops) at http://www.gplivna.eu/papers/v$session_longops.htm
there under chapter Hash joins can either fly or crawl you can see how hash area size can make quite big impact on query duration.
Also if you are not sure why the query takes the same time and whether your sort/hash area sizes are taken into account you can enable 10053 trace which gives you all CBO parameter values as well as plan generation path of course and you can check whether something has changed or not.
Gints Plivna
http://www.gplivna.eu
[Updated on: Tue, 27 February 2007 17:04] Report message to a moderator
|
|
|
|
Re: Alter Session (workareas and area_sizes) [message #221795 is a reply to message #221624] |
Wed, 28 February 2007 08:25 |
smartin
Messages: 1803 Registered: March 2005 Location: Jacksonville, Florida
|
Senior Member |
|
|
Thanks Gints for your time and your extended comments and your test case. I'll also take a look at your referenced link.
You make some good points, especially about me needing to come up with a better test query (I know, I was not being very creative with mine).
I have to go work on real work for the moment, but have two follow up questions to ponder until I can get a chance later today or tomorrow to revisit this and run another test.
1. One of the factors affecting this is the new 10gR2 Hash Group By. But what would you say if I already achieved the same results when I set my optimizer features parameter down to 10.1.0, so that it used a sort group by, which isn't as efficient, instead of a hash group by?
2. Is it possible to modify these memory allocations at the session level in the first place, even though they are not dynamic memory settings at the system level? I guess that is my whole point of the test and what I'm trying to find out with all of this. The docs say you can, and the commands run without error. But I'm skeptical that the alter session has any affect, and want to prove it one way or the other.
Note there would be two possible ways for it to have an effect. One would be to affect the plan the optimizer comes up with, because it thinks it has different sizes to work with than it actually does. Another would be actually modifying the memory allocations themselves.
Oh and I haven't started tracing any of this for a reason that I can get into later..I may have no choice but to do so eventually, at least to prove what is going on.
|
|
|
Re: Alter Session (workareas and area_sizes) [message #221832 is a reply to message #221624] |
Wed, 28 February 2007 10:35 |
smartin
Messages: 1803 Registered: March 2005 Location: Jacksonville, Florida
|
Senior Member |
|
|
Well I edited my test case and let it run while doing other work. The main modification I made was to change the query to be more "intensive". I also gathered stats, and used autotrace instead of explain plan.
But check out the results. They seem to imply that the alter sessions do have a drastic effect. In particular, notice the physical reads and the recursive calls and the sorts.
My guess is that these are due to oracle having enough space to perform the sorting and hashing in memory, in one (or a few) passes, instead of spilling to temp space and doing the multi pass approach. So while I'm not sure this test case proves what I wanted to prove, it sure does imply it heavily.
Gints, or anyone, have any comments or thoughts or holes in this logic or in the test case itself?
MYDBA@orcl >
MYDBA@orcl > create table test as select * from all_objects;
Table created.
MYDBA@orcl >
MYDBA@orcl > insert into test select * from test;
49827 rows created.
MYDBA@orcl > insert into test select * from test;
99654 rows created.
MYDBA@orcl > insert into test select * from test;
199308 rows created.
MYDBA@orcl > insert into test select * from test;
398616 rows created.
MYDBA@orcl > commit;
Commit complete.
MYDBA@orcl > insert into test select * from test;
797232 rows created.
MYDBA@orcl > insert into test select * from test;
1594464 rows created.
MYDBA@orcl > commit;
Commit complete.
MYDBA@orcl > insert into test select * from test;
3188928 rows created.
MYDBA@orcl > commit;
Commit complete.
MYDBA@orcl >
MYDBA@orcl > exec dbms_stats.gather_table_stats('mydba','test');
PL/SQL procedure successfully completed.
MYDBA@orcl >
MYDBA@orcl > -- buffer cache...
MYDBA@orcl > select owner, count(*) from test group by owner having count(*) > 10000;
OWNER COUNT(*)
------------------------------ ----------
MDSYS 109952
DMSYS 24192
PUBLIC 2558592
CTXSYS 43264
OLAPSYS 91904
SYSTEM 54400
EXFSYS 35712
SH 38272
OE 14336
ORDSYS 213632
SYSMAN 165248
XDB 44416
SYS 2930560
WMSYS 29696
14 rows selected.
MYDBA@orcl >
MYDBA@orcl > set autotrace on explain statistics;
MYDBA@orcl >
MYDBA@orcl > set timing on
MYDBA@orcl > select owner, count(*)
2 from (select * from test union select * from test)
3 group by owner having count(*) > 10000;
OWNER COUNT(*)
------------------------------ ----------
PUBLIC 19989
SYS 22895
Elapsed: 00:10:10.87
Execution Plan
----------------------------------------------------------
Plan hash value: 1650989861
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12M| 206M| | 412K (2)| 01:22:32 |
|* 1 | FILTER | | | | | | |
| 2 | HASH GROUP BY | | 12M| 206M| | 412K (2)| 01:22:32 |
| 3 | VIEW | | 12M| 206M| | 410K (2)| 01:22:04 |
| 4 | SORT UNIQUE | | 12M| 1131M| 3066M| 410K (51)| 01:22:04 |
| 5 | UNION-ALL | | | | | | |
| 6 | TABLE ACCESS FULL| TEST | 6377K| 565M| | 19650 (3)| 00:03:56 |
| 7 | TABLE ACCESS FULL| TEST | 6377K| 565M| | 19650 (3)| 00:03:56 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(COUNT(*)>10000)
Statistics
----------------------------------------------------------
1402 recursive calls
7368 db block gets
174758 consistent gets
459329 physical reads
0 redo size
520 bytes sent via SQL*Net to client
381 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
1 sorts (disk)
2 rows processed
MYDBA@orcl > set timing off
MYDBA@orcl >
MYDBA@orcl > alter session set workarea_size_policy = manual;
Session altered.
MYDBA@orcl >
MYDBA@orcl > alter session set hash_area_size=16000;
Session altered.
MYDBA@orcl > alter session set sort_area_size=16000;
Session altered.
MYDBA@orcl >
MYDBA@orcl > set timing on
MYDBA@orcl > select owner, count(*)
2 from (select * from test union select * from test)
3 group by owner having count(*) > 10000;
OWNER COUNT(*)
------------------------------ ----------
PUBLIC 19989
SYS 22895
Elapsed: 00:31:31.93
Execution Plan
----------------------------------------------------------
Plan hash value: 1650989861
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12M| 206M| | 1384K (1)| 04:36:54 |
|* 1 | FILTER | | | | | | |
| 2 | HASH GROUP BY | | 12M| 206M| | 1384K (1)| 04:36:54 |
| 3 | VIEW | | 12M| 206M| | 1382K (1)| 04:36:26 |
| 4 | SORT UNIQUE | | 12M| 1131M| 3066M| 1382K (51)| 04:36:26 |
| 5 | UNION-ALL | | | | | | |
| 6 | TABLE ACCESS FULL| TEST | 6377K| 565M| | 19650 (3)| 00:03:56 |
| 7 | TABLE ACCESS FULL| TEST | 6377K| 565M| | 19650 (3)| 00:03:56 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(COUNT(*)>10000)
Statistics
----------------------------------------------------------
2570 recursive calls
191666 db block gets
174994 consistent gets
1767048 physical reads
0 redo size
520 bytes sent via SQL*Net to client
381 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
54 sorts (memory)
1 sorts (disk)
2 rows processed
MYDBA@orcl > set timing off
MYDBA@orcl >
MYDBA@orcl > alter session set hash_area_size=20000000;
Session altered.
MYDBA@orcl > alter session set sort_area_size=20000000;
Session altered.
MYDBA@orcl >
MYDBA@orcl > set timing on
MYDBA@orcl > select owner, count(*)
2 from (select * from test union select * from test)
3 group by owner having count(*) > 10000;
OWNER COUNT(*)
------------------------------ ----------
PUBLIC 19989
SYS 22895
Elapsed: 00:01:18.45
Execution Plan
----------------------------------------------------------
Plan hash value: 1650989861
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12M| 206M| | 298K (3)| 00:59:40 |
|* 1 | FILTER | | | | | | |
| 2 | HASH GROUP BY | | 12M| 206M| | 298K (3)| 00:59:40 |
| 3 | VIEW | | 12M| 206M| | 295K (2)| 00:59:12 |
| 4 | SORT UNIQUE | | 12M| 1131M| 3066M| 295K (51)| 00:59:12 |
| 5 | UNION-ALL | | | | | | |
| 6 | TABLE ACCESS FULL| TEST | 6377K| 565M| | 19650 (3)| 00:03:56 |
| 7 | TABLE ACCESS FULL| TEST | 6377K| 565M| | 19650 (3)| 00:03:56 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(COUNT(*)>10000)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
174758 consistent gets
167628 physical reads
0 redo size
520 bytes sent via SQL*Net to client
381 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
2 rows processed
MYDBA@orcl > set timing off
MYDBA@orcl >
MYDBA@orcl > set autotrace off
MYDBA@orcl >
MYDBA@orcl > drop table test;
Table dropped.
MYDBA@orcl >
MYDBA@orcl > spool off
In doing this sort of thing, I sure can't help but have an increased respect for the work that Jonathan Lewis must have put into writing his CBO book.
|
|
|
|
Re: Alter Session (workareas and area_sizes) [message #221998 is a reply to message #221624] |
Thu, 01 March 2007 07:37 |
smartin
Messages: 1803 Registered: March 2005 Location: Jacksonville, Florida
|
Senior Member |
|
|
I'll keep that in mind about re-running the query after changing the memory params, I clearly didn't do that.
As far as my doubts...I did have them. And still do, but to a much much lesser extent. The reason:
MYDBA@orcl > alter system set sort_area_size = 256000 scope = memory;
alter system set sort_area_size = 256000 scope = memory
*
ERROR at line 1:
ORA-02096: specified initialization parameter is not modifiable with this option
MYDBA@orcl >
These are memory allocation settings that I have always considered as not dynamic. And I thought the ref docs say they aren't dynamic. The alter system command considers them that way as well. So why would the alter session be different?
I will run some traces. The reason I didn't before is that I'm currently at a site as a contractor and have no real admin type privs. I can't even run autotrace, much less trace files. And certainly not alter system scope=spfile. So was trying to come up with something I could show without needing those things.
|
|
|
Goto Forum:
Current Time: Tue Dec 03 21:18:38 CST 2024
|