OraFAQ Forum: Performance Tuning » Alter Session (workareas and area

Home » RDBMS Server » Performance Tuning » Alter Session (workareas and area_sizes)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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 #221697 is a reply to message #221629]

Wed, 28 February 2007 01:56

gintsp
Messages: 118
Registered: February 2007

Senior Member

Yea now I'm quite sure that "for hash group by Oracle needs very tiny space because after analyze you'll see that expected bytes for hash group by are very tiny

SQL> exec dbms_stats.gather_table_stats(user, 'test');

PL/SQL procedure successfully completed.

Elapsed: 00:01:00.02
SQL> explain plan for
  2  select owner, count(*) from test group by owner having count(*) > 10000;

Explained.

Elapsed: 00:00:00.00
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1349321023

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     2 |    14 | 22401   (7)| 00:04:29 |
|*  1 |  FILTER             |      |       |       |            |          |
|   2 |   HASH GROUP BY     |      |     2 |    14 | 22401   (7)| 00:04:29 |
|   3 |    TABLE ACCESS FULL| TEST |  6989K|    46M| 21170   (2)| 00:04:15 |
----------------------------------------------------------------------------


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(COUNT(*)>10000)

The I run the select and it took 55.06 secs for me but in v$session_longops there was entry that 53 seconds was used for test table scan. So most of the time you are using to scan the table and very short time and memory are needed to to the actual group by.

Actually if you think how you'd do the count on paper you can easily guess that very small space is needed for that - just put distinct values on the paper as they appear and increment counter Smile

So you have to design a query where there is need for much bigger sort and/or hash area space.

Gints Plivna
http://www.gplivna.eu

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.

Report message to a moderator

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.

Report message to a moderator

Re: Alter Session (workareas and area_sizes) [message #221889 is a reply to message #221624]

Wed, 28 February 2007 16:36

gintsp
Messages: 118
Registered: February 2007

Senior Member

>But check out the results. They seem to imply that the alter
>sessions do have a drastic effect.

Of course they have effect, I've never had any doubts about that Smile

>In particular, notice the physical reads and the recursive >calls and the sorts.

Yea most probably the physical read increase is because reading and writing into temp tablespace, but you can easily assure that running 10046 trace and just visually checking in what tablespace/datafile the Oracle is writing in/reading from.

>Gints, or anyone, have any comments or thoughts or holes in
>this logic or in the test case itself?

The only comment probably is that to get very clear results from autotrace you better run the same query at least two consecutive times to avoid first time parsing issues. Parsing won't affect the timing much but I'm quite sure that recursive calls and memory sorts are mostly coming from internal queries parsing your query. And you have to run two consecutive times each time you change your sort/hash are sizes because each time the query is reparsed again because your environment has been changed.

Gints Plivna
http://www.gplivna.eu

Report message to a moderator

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.

Report message to a moderator

Previous Topic:	Explain plan
Next Topic:	Dynamic Table List Selection for STATS

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Nov 27 01:47:10 CST 2024