1

Topic: Reanalysis of all sql at change pga_aggregate_target?

The people, whether somebody met similar behavior of basis - at change pga_aggregate_target (11.2.0.4.6) there is an origin of waitings "cursor: pin S wait on X" and "latch: row cache objects".
Looks as reanalysis sql.

2

Re: Reanalysis of all sql at change pga_aggregate_target?

sys@DBNAME> show parameter memory_target
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
memory_target big integer 0

3

Re: Reanalysis of all sql at change pga_aggregate_target?

Takurava;
It is logical:

select name
from (select sql_id from v$sql where rownum=1) s
, v$sql_optimizer_env e
where s.sql_id=e.sql_id
and (name like ' %target % ' or name like ' %mem % ');
NAME
----------------------------------------
pga_aggregate_target
optimizer_inmemory_aware
inmemory_force
inmemory_query
inmemory_size

4

Re: Reanalysis of all sql at change pga_aggregate_target?

Better even so:

SQL>;
1 select name
2 from (select sql_id from v$sql where rownum=1) s
3, v$sql_optimizer_env e
4 where s.sql_id=e.sql_id
5* and (name like ' %target % ' or name like ' %mem % ' or name like ' %size % ')
SQL> /
NAME
----------------------------------------
hash_area_size
bitmap_merge_area_size
sort_area_size
sort_area_retained_size
pga_aggregate_target
_pga_max_size
workarea_size_policy
parallel_execution_message_size
optimizer_inmemory_aware
inmemory_force
inmemory_query
inmemory_size

5

Re: Reanalysis of all sql at change pga_aggregate_target?

Clearly.