Re: [External] : Re: Open cursor error
Date: Mon, 21 Aug 2023 21:41:32 -0400
Message-ID: <63c64b2b-3ebb-c7f7-3905-2dab73610135_at_gmail.com>
On 8/21/23 13:40, Gerald Venzl (gerald.venzl) wrote:
> It is mostly just memory in the session process’s PGA and a slight
> overhead for cursor invalidation /if/ the underlying objects change or
> other circumstances arise and trigger one.
> But even the latter is probably not measurable given that we are
> talking about one SID here and not a system with thousands of sessions
> and a cursor wild west.
Cursors are handled through a table in UGA (User Global Area), not the PGA. Putting the table into the PGA would create problems with shared servers or pooled connections. Since each entry in the table is only few bytes long, having 4096 cursors would probably consume around 64K of your memory. Since memory sizes these days are measured in GB, 64K of memory isn't all that much. Believe it or not, Oracle 5.1.22 was able to run on a PC with 640KB of regular RAM and 1MB (Megabyte, not Gigabyte) of extended memory. There was something called "sqlpme.exe" which was loading the SGA into the "extended RAM". The "pme" part used to stand for "protected memory executive". Alas, that is no longer the case. Setting open_cursors to 4096 is my default setup for any Oracle database. 64KB is not all that much any more. I have 64 GB RAM on my desktop machine and with DDR5 and the latest memory chips (256Gb), memories will rapidly grow even larger. Sacrificing 64KB RAM is a negligible price to pay. Unfortunately, Oracle has no parameters to handle angry cursors who are so thrilled by their application performance that they use an undocumented Oracle keyword starting with the letter 'F'. However, that is the problem of the application developers. I'd like to state just one thing, for the record: Hibernate bad! Stay away. It will also help with cursors, both those in memory and those who reside between keyboard and the chair. Yes, that was not so veiled reference to PEBKAC.
-- Mladen Gogala Database Consultant Tel: (347) 321-1217 https://dbwhisperer.wordpress.com -- http://www.freelists.org/webpage/oracle-lReceived on Tue Aug 22 2023 - 03:41:32 CEST