HI,
I have a table with 1 million records, If I search for one value of a column
it comes very fast and if I do the same query for a different value on the
same column it takes more time. I did not understand why it behaves like
that. Any ideas..
Thanks
kiran
--
Please see the official ORACLE-
Hi Lisetrs,
I have enough free memory from shared_pool_size and
I run stats every week but the BD still shows up the
low hit ratio and some times the later full down to
15%!
Here is some informations:
Obj mem: 79503437 bytes
Shared sql: 23852410 bytes
Cursors: 244875 bytes
Free memory: 20451
Hi
I am having some problem regarding performance.The performance problem get
solved after reboot.Whenever the db unix server keep running more than 72
hrs the performance looks like slow.one of cause I found some memory
leakage.But the box are having 2 instances and for another instance
perf
Title: RE: Performance issue
> -Original Message-
> From: Pampati, Kiran [mailto:[EMAIL PROTECTED]]
>
> I have a table with 1 million records, If I search for one
> value of a column
> it comes very fast and if I do the same query for a
> different value on
Sent: Monday, April 02, 2001 04:36 PM
To: Multiple recipients of list ORACLE-L
Subject: Performance issue
HI,
I have a table with 1 million records, If I search for one value of a column
it comes very fast and if I do the same query for a different value on the
same column it takes
> Hi Lisetrs,
>
> I have enough free memory from shared_pool_size and
> I run stats every week but the BD still shows up the
> low hit ratio and some times the later full down to
> 15%!
> Here is some informations:
Are any of the users complaining that their business functions are
performing poo
Seema,
So what does that tell you about how useful database
ratios are in diagnosing your problem ? I would
suggest you check out some sites about detecting
waits in your system. The information given here
gives no one a starting point to help you diagnose
the problem.
Consider buying Gaja
Well, you don't give us much to go on and I fully agree with the other comment about
buying and using a good tuning book.
However, there is one experiment you might try the next time your instance gets slow.
Try flushing the shared pool (alter system flush shared_pool). If that works, you can
Title: PERFORMANCE ISSUE
Seema,
Questions:
1. On what OS you are running your db?
2. How did u find memory leakage?
Regards,
Rao
-Original Message- From:
[EMAIL PROTECTED]@SUNGARD on behalf of "Seema Singh"
<[EMAIL PROTECTED]> Sent: Thu 6/20/200
It could be memory paging problem. Are you using UFS, VFS, etc?
If yes, see if there is any options where you can mount them in the direct
mode (bypass the fs cache layer)
Waleed
-Original Message-
Sent: Thursday, June 20, 2002 12:39 PM
To: Multiple recipients of list ORACLE-L
Hi
I am
Seema,
See the problem that is arising here on
this post ?
It could be this and it could be that ...
what is the # of cpus ? what o/s ? try this and
try that and on and on we go asking question
after question until you say "Enough, I
am not getting anywhere with this."
This is why you gotta dri
I have a view based
on the query below which is executed in a reasonable way on the master site but
this same view takes hours on the snapshot site. Any idea
?
CREATE OR
REPLACE VIEW V_ENTITE_EMETTEUR2 AS select /*+ ORDERED USE_NL(E A SI SY O LF MO AM MR ST) INDEX(E
I_REF_EMETTEUR)*/
Hi guys
Compliments of the new season.
We have a database running on Oracle 817 and there are around 4 to 5
applications running there. Initially the platform was Windows NT,
service pack 6. Everything was working fine, now recently, the O/S was
upgraded to Windows 2000. Then our problems started
It may be that the name of your index has changed.
It is probably something like I_snap$_E I_REF_EMETTEUR.
You will need to change the hint to reflect the change.
01 01:40
AM PST
Please respond to [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
To: "Multiple recipients of list ORA
:25
À : Multiple recipients of list ORACLE-L
Objet : Re: Snapshot performance issue
It may be that the name of your index has changed.
It is probably something like I_snap$_E I_REF_EMETTEUR.
You will need to change the hint to reflect the change.
01 01:40
AM PST
Please respond to [EMAI
>Hi guys
>
>Compliments of the new season.
>
>We have a database running on Oracle 817 and there are around 4 to 5
>applications running there. Initially the platform was Windows NT,
>service pack 6. Everything was working fine, now recently, the O/S was
>upgraded to Windows 2000. Then our problems
Hi Jackson Dumas,
Have check the virtual memory (paging space) of the OS ?
Because sometimes it's related. And, how big memory that you used in your
server ?
Thank's
Bernardus Deddy Hoeydiono.
-Original Message-
Dumas
Sent: Wednesday, January 08, 2003 2:59 PM
To: Multiple recipients of
Jackson Dumas,
Maybe we need to know whether your os upgrade caused oracle upgrade? I
mean if you did oracle exp/imp, patch, migration etc? I mean if there is anything
else is changed ?
Make a statspack and check what is the oracle doing. That will be
helpful:)
ECTED]>
Sent by: [EMAIL PROTECTED]
01/07/2003 11:58 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Subject:Performance Issue on 817 !!!
Hi guys
Compliments of the new season.
We have a database run
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Subject:Performance Issue on 817 !!!
Hi guys
Compliments of the new season.
We have a database running on Oracle 817 and there are around 4 to 5
applications running there. Initially th
>
>
> To: Multiple recipients of list ORACLE-L
<[EMAIL PROTECTED]
>m>
>cc:
>Subject:Performance Issue on 817 !!!
>
>
>Hi guys
>
>Compliments of the new season.
>
>We have a database running on Oracle 817 and there a
Hi,
I have an online application that does a 'select count(*)' on a few tables.
The 'select counts' always runs slow (about 10secs) for the first time and
then fast again (< 1sec) after subsequent accesses. The query runs slow
again when the data is flushed out of the buffer cache.
10046 trace
Good Morning,
I have a database (8.1.7.3 on Sun Solaris 8) that has
a mixture of tables and indexes in the same
tablespace. Poor initial setup, but that is starting
to be addressed. One of the tables has a BLOB data
type and the LOBSEGMENT is stored in the same
tablespace as the tables and indexes
Hi Listers
I have a unique performance problem. As a
general rule by oracle while writing SQL scripts EXISTS should be used in
place of IN.
I'm having 2 sql for comparison using IN and
EXISTS operators.
With IN operator
SELECT /*+ PUSH_SUBQ USE_NL (a) INDEX (a) */
COUNT(1)
FROM mam_assets a
> Oracle 8.1.7 on AIX.
> Our database is rapidly expanding. Now it's approximately 80GB and for few
> months it's going to be at least double of that size. We are planning some
> reorganizations, so I'm wondering could you give me some advice.
> Is it better to have several big file systems or l
We have 6 databases on one HPUX box (not my idea) that has 3GB RAM. In
this type of situation, can the total of the 6 databases SGAs add up to
3GB? Are there any special types of calculations that should be done
in this type of situation to determine optimal performance sizing for
the databases
#x27;s seems to be that upgrading from
>NT to Win2k is not a good thing. Our SA's refuse to do it.
>
>Jared
>
>
>
>
>
>
>"Jackson Dumas" <[EMAIL PROTECTED]>
>Sent by: [EMAIL PROTECTED]
> 01/07/2003 11:58 PM
> Please respond to ORACLE-L
>
E-L
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Subject:Re_ Performance Issue on 817 !!!
Jared
The O/S was upgraded to Win2000. It was not reinstalled, I mean disk
was not formatted, this was done a fly from WinNT TO Win2k. Please
tell me
Linda,
I guess that the key word is 'partition'. This type of query should not require to
access the table if (hopefully) tid is indexed. If the index on tid is also
partitioned, all index partitions have to be searched. My feeling is that in such a
case what should run faster is some paralle
t; <[EMAIL PROTECTED]>
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Subject: RE: performance issue on select count(*)
Date: Mon, 27 Oct 2003 05:49:24 -0800
Linda,
I guess that the key word is 'partition'. This type of query should
Linda,
I am guessing that since your table is partitioned on an unspecified date
column, that the index on TID is either LOCAL or non-partitioned (i.e.
GLOBAL).
If it is LOCAL (you would have had to specify the keyword, as it is not the
default), then you will be performing indexed RANGE scans on
gt;
Subject: Re: performance issue on select count(*)
Date: Mon, 27 Oct 2003 10:34:59 -0800
Linda,
I am guessing that since your table is partitioned on an unspecified date
column, that the index on TID is either LOCAL or non-partitioned (i.e.
GLOBAL).
If it is LOCAL (you would have had to specify the
The symptom suggests caching is a big factor here - most likely
block-buffers.
Contrary to ?current? popular beliefs, BCHR is still a very relevant
performance indicator - either being very high, or being too low - both of
which gives a good indication of something that needs to be looked at.
So, what exactly is indicated by a high or low hit rate? What, exactly, is "high"
and what do you consider "low"?
What "HR" are you talking about?
This would be the infamous BCHR:
select 'bc_hit_ratio' ratio,( sum(decode(name, 'consistent gets',value,0))
+ sum(decode(name,'db block gets', value
to ORACLE-L
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Subject: Re: performance issue on select count(*)
So, what exactly is indicated by a high or low hit rate? What, exactly, is "high"
and what do you con
After deleting lot of old data, an analyze of the table is in order though ..
Raj
Rajendra dot Jamadagni at nospamespn dot com
All Views expressed in this email are strictly personal.
QOTD: Any clod can have facts, ha
I had the performance issue with CLOB in one of my databases. After I did a
re-org, and separated the tables,indexes and CLOB into different
tablespaces, the performance got tremendous improvement.
Robin
- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EM
Raj,
I agree. I could see where that could affect the
overall performance. The analyze wouldnt have an
effect on a SELECT COUNT(*) though would it??? That is
the piece that really has me stumped at the moment.
--- "Jamadagni, Rajendra"
<[EMAIL PROTECTED]> wrote:
> After deleting lot of old data, a
Robin,
Thanks for the reply. I figured it would. Thats what
we are working at now. It's unfortunate it was set up
this way to begin with, but I am starting to see the
light at the end of the tunnel.
Larry
--- Robin Li <[EMAIL PROTECTED]> wrote:
> I had the performance issue with
Hi!
Maybe it's a delayed commit cleanout issue, due massive deletes, so during
your first select most of your buffers involved in delete have to be cleaned
out (thus becoming dirty and generating extra redo).
Tanel.
- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL
Munish Bajaj <[EMAIL PROTECTED]>
> Date: 2003/06/03 Tue AM 06:59:52 EDT
> To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
> Subject: IN or Exists --- performance issue
>
> Hi Listers
>
> I have a unique performance problem. As a general rule by oracle while
Munish,
You are right, as a *general rule of thumb* EXISTS is *usually* faster than
IN..
There can however be problems when an EXISTS is used to manipulate or select
data from a very large table, where the row exists in a far smaller table,
as it will read every row in the large table, and then s
correlated
subquery.
Regards
David
Lord
-Original Message-From: Munish Bajaj
[mailto:[EMAIL PROTECTED]Sent: 03 June 2003 12:00To:
Multiple recipients of list ORACLE-LSubject: IN or Exists ---
performance issue
Hi Listers
I have a unique performance problem. As a
general ru
>
>Hi Listers
>
>I have a unique performance problem. As a general
>rule by oracle while
>writing SQL scripts EXISTS should be used in place
>of IN.
>
As a general rule there is no general rule. Why do you want to use EXISTS if it goes
faster with IN ?
EXISTS is mostly used with a correlated
AMTo: Multiple recipients of list ORACLE-LSubject: IN or
Exists --- performance issue
Hi Listers
I have a unique performance problem. As a
general rule by oracle while writing SQL scripts EXISTS should be used in
place of IN.
I'm having 2 sql for comparison using IN and
Dear gurus !
We are facing a severe performance problems here that i have no idea of how
to address.
There is a C++ program , which uses OCI , that does the following.
It initiates a long running SQL select statement, and then fetches the
result set from the DB in buffers of 5000 records.
Now , th
I need to get the adue_cd and group by the sum of the adue_amt- amtpd based
on the decoded code ??
SELECT DECODE(adue_cd,'DS','STM','IS','INS','MS','OUI',
'PA','OD1','PN','OD2','PP','OD1')
,SUM(adue_amt - amt_pd)
FROM BSADUE
WHERE la_no = v_la_no
AND adue_cd IN ('DS','IS','M
> 3 GB RAM, how big to set SGA?
How about starting at ~ 2 GB for oracle server processes, and allow ~1
GB for user processes?
(I have no idea as to how much memory HP-UX uses just to breathe ...
leave it room)
How much memory does the OS use before your instances are started?
(baseline)
Connie,
HPUX 11.0 is a bit memory hungry.
You would need to leave some memory for the OS before allocating to the
SGAs else your system will start swapping / paging.
Before allocating memory to the SGA's leave some to the HPUX buffer,
Unix OS, users logging onto the box.
Check what size the
t ORACLE-L <[EMAIL PROTECTED]>
> Subject: Re: performance issue on select count(*)
>
> So, what exactly is indicated by a high or low hit rate? What, exactly, is "high"
> and what do you consider "low"?
> What "HR" are you talking about?
> T
t. there is no 'exact' very high and very low. you have to
interpret it.
>
> that is about it. Anyone who uses it for anymore than that is wrong.
> >
> > From: Mladen Gogala <[EMAIL PROTECTED]>
> > Date: 2003/10/28 Tue PM 12:09:34 EST
> > To: Multipl
recipients of list ORACLE-L <[EMAIL PROTECTED]>
> Subject: RE: IN or Exists --- performance issue
>
>
> >
> >Hi Listers
> >
> >I have a unique performance problem. As a general
> >rule by oracle while
> >writing SQL scripts EXISTS should be used in p
>Dear gurus !
>We are facing a severe performance problems here
>that i have no idea of how
>to address.
>There is a C++ program , which uses OCI , that does
>the following.
>It initiates a long running SQL select statement,
>and then fetches the
>result set from the DB in buffers of 5000 records.
Can you switch on SQL trace whilst this is running,
or peek into v$sql.
It sounds to me as if your code is "fetching
an array" by re-opening the cursor, fetching
and discarding all the rows up to the required
point, and then returning the required rows.
To answer your direct question - when doi
Do you keep the each batch of 5000 rows ? If yes, are you running out of
memory ?
Anjo.
Andrey Bronfin wrote:
> Dear gurus !
> We are facing a severe performance problems here that i have no idea of how
> to address.
> There is a C++ program , which uses OCI , that does the following.
> It in
Thanks a lot Jonathan !
I TKPROFed these statements - nothing outstanding, it looks like the cursor
is not getting closed eafter each fetch.
Anyway , i would not expect the 100th fetch to take 100s times more time
than the first one
Also please note that the select contains 'group by' and 'ord
Stephane , thanks a lot !
actualy , i do not think that this is the case , because it would take
relatively much more time to construct the result set (my SQL contains
massive 'group by' and 'order by' and therefore the whole result set needs
to be constructed before returning even the 1st row), t
Apparently, it's a widely held myth that a large # of extents (let's
say "BETWEEN 5 AND 1000") per table segment is bad for performance. Yet
the same sources who label the belief mistaken persist in pushing for
fitting all of a table in the INITIAL extent.
And that confuses the heck out of me.
This really depends on the type of access. If it's random using indexes, the extents
don't matter much. If you do a lot of full table scans, and the extents are scattered
all over, there may be a performance degradation. YMMV as always.
>>> [EMAIL PROTECTED] 02/27/01 01:06PM >>>
Apparently,
Besides DDL like tuncate and drop will take longer with large # of
extents
Regards
Rafiq
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Date: Tue, 27 Feb 2001 10:31:24 -0800
This really depends on the type of access. If it's random using indexes,
60 matches
Mail list logo
