Re: Transaction table- Keep Pool

From: <jaysingh1_at_optonline.net>
Date: Thu, 05 Aug 2004 10:31:58 -0400
Message-id: <3daafe93dae058.3dae0583daafe9@optonline.net>

It is taking 30 sec for such a small volume of records.

call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse        1      0.03       0.05          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        3     10.71      33.30       6345      19185          0          20
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        5     10.74      33.35       6345      19185          0          20

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 180

Rows Row Source Operation

-------  ---------------------------------------------------
     20  COUNT STOPKEY 
     20   VIEW 
     20    SORT ORDER BY STOPKEY 
    347     MERGE JOIN CARTESIAN 
    348      NESTED LOOPS 
   4884       TABLE ACCESS BY INDEX ROWID PROFILEDUSER 
   4885        INDEX RANGE SCAN (object id 1010125)
   5230       TABLE ACCESS BY INDEX ROWID EXTENDEDATTRIBUTES 
   9766        INDEX UNIQUE SCAN (object id 951554)
    347      SORT JOIN 
      1       TABLE ACCESS BY INDEX ROWID COUNTRIES 
      2        INDEX RANGE SCAN (object id 1131957)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       3        0.00          0.00
  rdbms ipc reply                              6051        0.18          2.31
  global cache freelist wait                   6206        0.18          3.88
  file open                                       4        0.00          0.00
  db file sequential read                      6345        0.04         14.05
  global cache cr request                      3188        0.21          4.87
  latch free                                      1        0.02          0.02
  SQL*Net message from client                     3        0.27          0.32
  SQL*Net more data to client                     1        0.00          0.00
********************************************************************************

Original Message ----- From: Niall Litchfield <niall.litchfield_at_gmail.com> Date: Thursday, August 5, 2004 10:06 am Subject: Re: Transaction table- Keep Pool

> On Thu, 05 Aug 2004 09:08:05 -0400, jaysingh1_at_optonline.net
> <jaysingh1_at_optonline.net> wrote:
> > Hi All,
> >
> > Few queries are taking longer time (40 sec =10*normal time) to
> execute in intermittent manner.For example, 99 percent of the time
> it got executed within 3 to 4 secs and 1 percent of the time it
> is taking more than 35 secs.
> > (35 secs is the application timeout limit)
> >
> > What we are think is that if the requested data block is not in
> DB buffer cache(flushed out by someother query/data) it is going
> for physical read and that may be the reason for longer execution
> time.
> Why do you think this? 30seconds is a *lot* of elapsed time, I can do
> many thousands of disk reads in 30 seconds - especially if those disk
> reads come from some cache somewhere in the system. I'd be strongly
> tempted to trace the session that is experiencing the problem, or at
> least query v$session_wait for that sid whilst you are experiencing
> the hang.
>
> One reason for this sort of behaviour *may* be that you are using bind
> variables and the execution plan chosen is excellent for all but a
> very small number of values for one of the bind vars.
>
> One reason may be that something else is happening on the box at
> the same time.
>
> Another reason might be indeed that you are reading from disk, and
> that disk is swamped.
>
> Unless you have diagnosis rather than symptoms applying a cure might
> be rather unhelpful
> > Is it okay to assign tansactions tables to KEEP POOL?
>
> Yes, but it would be somewhat unusual.
>
> --
> Niall Litchfield
> Oracle DBA
> http://www.niall.litchfield.dial.pipex.com
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request_at_freelists.org
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
>

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Thu Aug 05 2004 - 09:28:48 CDT