You could disable memory intensive optimizer features, such as use of hash joins and bitmap indexes. For example, if you revert to the rule based optimizer you may find that the memory requirement for running your SQLs is hugely reduced. Probably the performance will be reduced as well.
If you look at v$sql_workarea you will see the memory required for optimal execution of statement, and as you tune it towards a different plan you will see the memory requirement change accordingly.
Identify the problem sql statement.
Look at the actual execution plan.
Look at actual vs estimated cardinalities.
Problem memory usage is most often associated with poorly sized workareas resulting from inaccurate (too low) row estimates for hash and sort operations.
Rule-based optimizer is not longer supported and should not be used (ignoring internal use by Oracle).
Rule based optimizer is deprecated.
It appears some contributors consistently recommend to use deprecated facilities and/or non-certified combinations of Oracle and OS, because 'it works for them'.
I recommend ignoring this kind of advice as these contributors won't assume responsibility when Oracle Support refuses to help you out.
Senior Oracle DBA
> >user's dba complains that sql statement consumes 2 gb of 10 gb on server.
> The first question to ask is "how does the dba compute these figures ?" many DBAs get this wrong.
Please find query for computing memory consumption received from user's dba:
select a.sid,a.serial#,a.username,b.name STATISTIC_NAME, round(sum(c.value/1024)) PGA_USAGE_KB
from v$session a, v$statname b, v$sesstat c
where b.statistic# in (25) and a.sid=c.sid and b.statistic#=c.statistic#
and a.username is not null
group by a.sid,a.serial#,a.username, b.name
order by pga_usage_kb DESC;
First of all check the memory utilization by memory advisory views and check the memory uses by session though its used SQLs by joining v$session , v$statname , v$sesstat ,
v$process ,v$sqlarea then decide how much memory you want to keep your database running .Also check the kernal parameters of your server .Please refer to ADDM and AWR report also .
just reduce the memory is not wise advice . It should be based on what is running on your system , how much data you are processing and how much memory used in peak hours .
Shivendra Narain Nirala
How is the user measuring this? Some people get it wrong, particularly by including SGA shared memory size.
You may get more information by looking at pga and sga advisors, and the pga statistics for the sql in question. It could be as simple as changing pga configuration parameters, or just changing particular sessions to manual pga. But of course, all of this is dependent on version, init.ora settings, the actual sql, plan, and so forth.
I can't tell, but I think the rule based optimizer suggestion may be a joke.