Sorry Jeremiah, I don't have a clue...
got the same error (after the same 2 hours) after purging the
shared pool; there was no activity at all on the database, so
I thought about increasing the size of the shared pool (~10.5MB)
but had a need, and the option, of simply replacing the
Kurt,
If you're on 8.1.6.3, 8.1.7.0.0 or 8.1.7.1.0 this
sounds suspiciouslly like either bug 1640583 or bug
1397603, both of which are fixed in 8.1.7.2+
The workaround for bug 1397603 is to set
_db_handles_cached = 0 in the init.ora.
HTH,
-- Anita
--- Wiegand, Kurt [EMAIL PROTECTED] wrote:
On the topic, I once had a tablespace with 300,000+ free extents and 0 used
extents. We executed a drop tablespace command, and looking at fet$ and the
rate at which it was dropping extents from the table, we estimated it would
take us 64 hours. This was on a 7.3.4 db, and we thought it better
I hit 1397603 (think that was the one) and with it you completely lose
service to the database. By the way, my understanding is that purging the
shared pool when you hit 4031 errors is not always going to solve the
problem because if there was SQL available to age out Oracle would do it. I
run
[EMAIL PROTECTED]
cc: (bcc: CHITALE Hemant Krishnarao/Prin DBA/CSM/ST Group)
Subject: Re: multiple extents are OK, dagnabbit
Hi Jeremiah,
The problem arose in the catalog upgrade script. It would never
return. My diary says we let one attempt run for 36 hours. The process
showed CPU usage and I/O but nothing happened. Some of the Oracle guys
figured the problem was with the $fet (or whatever tables hold the
sort of on the subject.I once had a table with ~88000 extents (most 1
block!)
it took 8 hours to delete and a subsequent coalesce ran for 2 hours before
failing as it ran out of shared memory(8.1.5).
-Original Message-
Sent: Tuesday, January 22, 2002 11:55 AM
To: Multiple
Hi All,
Actually, in extreme cases ( 87000 in my case, and I had 12
tables
like that) it can cause problems with upgrading. Not sure what, but we
had to do CTAS into new tables with much larger extents to do the
upgrade from 8.1.5 to 8.1.6 here. Had Oracle support and consultants
baffled
And I was worried about 20 to 60 extents. :)
But I do have one question, if a table has multiple extents, 20 extents at
1Mb each, and they are
contiguous, is that equal to 1 extent of 20Mb big ?? Does oracle have to
work harder to get
those 20 extents ? (okay two questions)
Darren
Can you elaborate on exactly what happened? 8.1.5 to 8.1.6 is just a
catalog script and a binary change. What error did you encounter, and
at which step in the upgrade? Extents should not matter in an
upgrade.
--
Jeremiah Wilton
http://www.speakeasy.net/~jwilton
On Mon, 21 Jan 2002, Dave
Hey Jeremiah, add in something to the last paragraph about how using
LMTs will obviate the problem in truncating tables with lots of extents
:)
There really is NO reason to worry about large numbers of extents these
days. I mean, I wouldn't want to really test the unlimited ability
but other
Jerry,
Tell the client that you will be HAPPY to reorg the tables and indexes
over 10 extents. It will cost X dollars and take Y hours of
downtime/slowdown. Insert inappropriately huge numbers into X and Y.
It's amazing how quickly people will change their minds when you talk
hours and dollars.
Jerry - You could approach the issue a little more subtly. Here is an Oracle
paper where Oracle recommends locally managed tablespaces and uniform
extents. If you can point out to them that you are a modern DBA that is
keeping up with new Oracle features, I think that would be persuasive.
Hello,
My 2 cents:
It does make a difference to reorg, esp. when done thoughtfully, with a
specific goal in mind. For example, if you have a order_log table that
started with first extent 1MB and next extent 1MB, and this table has grown
in size to say, 10 million rows (business is good), you
Paul,
With LMT's. uniform extents sizes and properly place objects I think you
avoid most of the situations you described. Cary's paper at hotsos.com
shows that in a system with a lot of activity your disk head is never going
to fulfill the request for a full tablescan in a single operation
Title: Message
Search Tom Kytes
asktom.oracle.com and there is also paper athotsos.com. Also check
out http://www.speakeasy.org/~jwilton/oracle/lots-of-extents.html.
-
Ethan
-Original Message-From:
Cunningham, Gerald [mailto:[EMAIL PROTECTED]]Sent: Thursday,
January 17, 2002 3:46
Here's my swing at it:
http://www.speakeasy.org/~jwilton/oracle/lots-of-extents.html
--
Jeremiah Wilton
http://www.speakeasy.net/~jwilton
On Thu, 17 Jan 2002, Cunningham, Gerald wrote:
I'm trying to convince a client that multiple extents for a table will not
hurt their performance. It's a
Title: Message
Jerry,
If
they want to pay you to reduce their extents, then let 'em!
;-) "A fool and his money are soon
parted."
If
they employ youand want you to work weekends on this, then it's worth the
effort to educate them. I'm surprised an official Oracle white paper
didn't
Jerry - Maybe I'm missing something here. Since you refer to them as a
client, you must have a consulting relationship with them - right? So if
you rebuild the tables, you get more money - right? So you rebuild the
tables, the client is happy, and you are a little wealthier - right? Or
maybe you
19 matches
Mail list logo
