Streams Apply / Error Slowness

In a streams – multi-master situation using version 11.2.0.3 i encountered a serious time delay in apply messages and errors after a Re-instantiation of one of the sites.   When i ran AWR and ASH reports i saw the bottleneck was the queueing table of one of the apply sites i.e. APPLY_FROM_SITE1 (Huge scattered reads counts).  It was doing full table scans and each transaction was taking from .02 seconds to > 45 seconds .  This is a huge problem, especially if you have > 10K transactions to play.

This was the query that was running (Names have been changed to protect the innocent)

select qt.msgid, e.local_transaction_id, e.source_commit_scn, e.external_source_pos, e.aq_transaction_id, e.source_transaction_id, NVL(e.xidusn,0), NVL(e.xidslt, 0), NVL(e.xidsqn, 0), NVL(e.flags, 0)
from “STREAMSUSERNAME”.”APPLY_FROM_SITE1_QT” qt, sys.apply$_error e
where e.apply#=:1 and qt.enq_tid(+)=e.aq_transaction_id and qt.q_name(+)=:2 and e.local_transaction_id=:3 order by qt.step_no

So after some digging and analysis i decided to add an index on the “APPLY_FROM_SITE1_QT” – columns q_name, enq_tid and msgid.   – create index index_site1 on apply_from_site1_qt (q_name, enq_tid,msgid) online parallel 4 (Online to reduce deadlocks).

Once the index was added, the transactions immediately when from > 45 seconds down to .02 seconds. 

Not sure if this is approved by Oracle, but it sure made live much easier after said change.

Advertisements

The History Of Rugby (Re-posted from David Hawkins – google+)

  The True History of Rugby

 
It is largely unknown to players and followers of the modern game that rugby started off purely as a contest for forwards in opposition in line-outs, scrums, rucks and mauls.
This pitted eight men of statuesque physique, supreme fitness and superior intelligence in packs against one another.
In those days, the winner was the pack that won the most set pieces. The debasement of the game began when backs were introduced. This occurred because a major problem was where to locate the next scrum or line-out.
Selecting positions on the ground for these had become a constant source of friction and even violence.
The problem was resolved by employing forward rejects, men of small stature and limited intelligence, to run aimlessly around within the field of play.
Following a set piece, the ball would be thrown to one of them, who would establish the next location either by dropping it or by throwing it to another reject for dropping.
Very occasionally, a third reject would receive the ball before it would be dropped, and crowds would wildly cheer on these rare occasions.
Initially these additional players were entirely disorganised but with the passing of time they adopted set positions.
For instance, take the half-back.
He was usually one of the smallest and least intelligent of the backs whose role was simply to accept the ball from a forward and to pass it on to one of the other rejects who would drop it, providing the new location for the forwards to compete.
He could easily (given his general size) have been called a quarter forward or a ball monkey but then tolerance and compassion are the keys to forward play and the present euphemism was decided on.
The five-eighth plays next to the half-back and his role is essentially the same except that when pressured, he usually panics and kicks the ball.
Normally, he is somewhat taller and slightly better built than the half-back and hence his name.
One-eighth less and he would have been a half-back, three-eighths more and he might well have qualified to become a forward.
The centres were opportunists who had no expertise but wanted to share in the glamour associated with forward packs.
After repeated supplication to the forwards for a role in the game they would be told to get out in the middle of the field and wait for instructions.
Thus, when asked where they played, they would reply “in the centre”. And they remain to this day, parasites and scroungers who mostly work as lawyers or used car dealers.
You may ask, why wingers? The answer is simple. Because these were players who had very little ability and were the lowest in the backline pecking order, they were placed as far away from the ball as possible.
Consequently, and because the inside backs were so diligent in their assigned role of dropping the ball whenever they received it, the main contribution to the game made by the winger was not to get involved.
Their instructions were to run away as quickly as possible whenever trouble appeared, and to avoid tackles at all costs.
The fact that the game was organised so that the wingers didn’t get to touch the ball led to an incessant flow of complaints from them and eventually the apt description “whingers” was applied.
Even though the “h” dropped off over the years, the whinging itself unfortunately has not.
Lastly, the full-back.
This was the position given to the worst handler, the person least able to accept or pass the ball, someone who was always in the way. The name arose because the forwards would understandably become infuriated by the poor play invariably demonstrated by that person, and call out “send that fool back”.
He would then be relegated well out of everyone’s way to the rear of the field.
So there you have it. Let’s return to the glory days of a contest between two packs of eight men of statuesque physique, supreme fitness and superior intelligence.
The rest can go off to where they will be happier, playing soccer.
And now you know…the rest of the story.
Dave