Re: Simple SQL waiting on 'log file sync'
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: "The Human Fly" <sjaffarhussain@xxxxxxxxx>
- Date: Thu, 24 Nov 2005 09:47:00 -0000
There's an article on my website that
mentions the idea, with a schematic of
the state an index can get into.
FIFO - first in first out.
We insert trades (say) into a table,
and want to ensure that trades are
processed in the order they appear,
so we have a flag showing the state
of the trade and a time_stamp, and
create an index
(flag, time_stamp)
Our processor has a query:
select from trades where
flag = 'A' order by time_stamp
The process does it's job to a trade,
then sets the flag to a different value
(and possibly clears the timestamp).
The row moves out of its place in the index.
That's the basic index for a FIFO
Some trades cannot be processed, and
get left in the index - so you end up with
an index that looks like the one in the
article - nearly empty everywhere, but
packed in the recent past.
A regular coalesce minimises the side-effects
You can use function-based indexes in these
circumstances as a good way of reducing the
problem and minimising the work and size
of index.
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/appearances.html
Public Appearances - schedule updated 4th Nov 2005
----- Original Message -----
From: "The Human Fly" <sjaffarhussain@xxxxxxxxx>
To: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
Cc: "oracle-l" <oracle-l@xxxxxxxxxxxxx>
Sent: Thursday, November 24, 2005 9:28 AM
Subject: Re: Simple SQL waiting on 'log file sync'
Sorry to bother you with my questions. A last question, could you
please elobrate about your last point:
(In this respect, it's a bit like
the index that is being used as a FIFO queue,
which is the index most likely to degenerate
into 99% empty with most of it's data in 1%
of the leaf blocks).
Just elobration of FIFO queue.
On 11/24/05, Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx> wrote:
Given that we are currently guessing that
the problem is an unlucky pattern of empty
blocks appearing on the truncate, you might
try to do a coalesce on the index that causes
the problem immediately after the truncate.
The 'maintain global indexes' simply does
a bulk delete (like an sql*load in reverse)
from the index, it doesn't rebuild it. And this
is why you can get the empty blocks.
It's important to note that the problem ISN'T
just the truncate/maintain - it is also an unlucky
side-effect of the nature of the index that is
being maintained. (In this respect, it's a bit like
the index that is being used as a FIFO queue,
which is the index most likely to degenerate
into 99% empty with most of it's data in 1%
of the leaf blocks).
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/appearances.html
Public Appearances - schedule updated 4th Nov 2005
----- Original Message -----
From: "The Human Fly" <sjaffarhussain@xxxxxxxxx>
To: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
Cc: "oracle-l" <oracle-l@xxxxxxxxxxxxx>
Sent: Thursday, November 24, 2005 9:13 AM
Subject: Re: Simple SQL waiting on 'log file sync'
You are 100% correct in your advice. I felt the same. Can you please
suggest me any other workaround to achieve the same, I mean to bypass
this oddity sequences what I am following.
It would be a great help and great relieaf for me, if you could give
any workaround.
Otherwise, should I include index coalesce as a practice after all the
prior sequences done?
My question is, when I am truncating partitions and updating global
indexes, wont that I am rebuilding them? If so, index still left with
empty leafs?
Thanks for your time Jonathan.
--
Best Regards,
Syed Jaffar Hussain
OCP 8i & 9i DBA,
Banque Saudi Fransi,
Saudi Arabia
----------------------------------------------------------------------------------
"Winners don't do different things. They do things differently."
--
//www.freelists.org/webpage/oracle-l
Other related posts:
