RE: Is this too many?

From: David Kurtz <info_at_go-faster.co.uk>
Date: Thu, 3 Apr 2014 20:48:28 +0100
Message-ID: <009d01cf4f75$b08f8f40$11aeadc0$_at_go-faster.co.uk>

No, this is quite typical for PeopleSoft. It is not good at producing sharable SQL. Lots of reasons, including and not limited to:

. Application Engine programs often convert bind variables back to
literals before they hit the database

. Different concurrent instances of programs use different temporary
working storage records

Consequently you might to disable Automatic Memory Management - or at least set a minimum size for the buffer cache - otherwise it will allocate more memory to the library cache, and very few SQL statements will be reused.  

regards

---

David Kurtz
tel: +44 (0)7771 760660
 <mailto:david.kurtz_at_go-faster.co.uk> mailto:david.kurtz_at_go-faster.co.uk    

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of David Fitzjarrell
Sent: 03 April 2014 19:25
To: oralrnr_at_gmail.com; oracle-l_at_freelists.org Subject: Re: Is this too many?
Importance: High  

I think not. You're running a packaged application with 'canned' queries and multiple users accessing the database at any one time. Also your granularity is a bit small (the entire day) so the numbers will be rather large. If you ran the query against the full date string rather than a portion of it the numbers would be much smaller. FIRST_LOAD_TIME is the date/time the cursor was first loaded (parsed) and won't be updated until that cursor ages out of the SQL area; LAST_LOAD_TIME more accurately reflects the date/time of the last reload of that cursor.    

David Fitzjarrell
Primary author, "Oracle Exadata Survival Guide"

On Thursday, April 3, 2014 12:01 PM, Orlando L <oralrnr_at_gmail.com> wrote:

List  

At 11am apr03 in one of our prod dbs:  

SQL> select * from

  2 (select count(*), substr( FIRST_LOAD_TIME, 1 , 10) from v$sqlarea

  3 group by substr( FIRST_LOAD_TIME, 1 , 10) order by 2 desc ) where rownum < 20;  

  COUNT(*) SUBSTR(FIR

• ----------

      5415 2014-04-03

        20 2014-04-02

         6 2014-04-01

...  

and on the other node (2 node RAC) the count for the day is close to 4000.  

5000 cursors loaded in a span of 11 hours. Is this too much?

This is a peoplesoft fin application. db10.2. sga target is 6G  

TIA.  

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Apr 03 2014 - 21:48:28 CEST