Hi friends. I have an Oracle 9i installation in RAC. Several
applications run this query many times:

SELECT VALUE FROM NLS_INSTANCE_PARAMETERS WHERE PARAMETER ='NLS_DATE_FORMAT'

The problem is that this make the mayor quantity of hard parses in the
DB!...


select sql_text, parse_calls, executions,cpu_time, elapsed_time
from v$sqlarea
where parse_calls > 10000 and executions < 2*parse_calls
order by parse_calls desc;


SQL_TEXT| PARSE_CALLS| EXECUTIONS|
CPU_TIME| ELAPSED_TIME
SELECT VALUE FROM NLS_INSTANCE_PARAMETERS WHERE PARAMETER
='NLS_DATE_FORMAT'|468961|468961|3923590000|4643338705


Every time that this query is executed, make a hard parse, and executes
many times by second... I will ask the programmers to change it, but
this may take a time...
There is a way to cache this query execution plan or something like
this?? Any idea??

Thanks a lot!

Ernesto Daniel Portela


Oracle LazyDBA home page