How to convince somebody it's not the oracle

Geert G
Geert G used Ask the Experts™
on
Hi,

I have this problem of trying to explain to some people they are wrong.

Here is the situation:
Biztalk app starts transactions on a oracle database.
first command: alter session set isolation level serializable
next: they (update) set a flag in a record they will be processing a record (they don't state which record or who is processing)
next: start reading records
next: they insert processed records
next: commit

they do this with 15 sessions at once

they say they have connection pooling set up, but every time sessions are closed and reopened for a new loop

how would you explain to them that of the 45 minutes they need to process 200000 records
there is no point in tuning the 13 seconds oracle is busy with the reads and the inserts.

based on tkprofs results 13 seconds was the time spent by the oracle database, the rest was because of SQL*net wait client
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Oracle Database Administrator III
Commented:
1.  Do you really gain anything by proving them wrong?  Not a win-win scenerio, and it could haunt you later.
2.  I think I would run a mid-level TCP/IP packet trace, and at least give them some idea of when the I/O begins and ends for the DBMS.  IOW the process launches at 22:00, but the trace shows no database load until say 22:40.
Regards,
dvz
Top Expert 2010

Commented:
do they do this from various locations ?
now , since you have  already proven that the dbtime is very less ,(using tools /mechanisms oracle provide) it is  **somebody else"" responsibility to prove you are wrong.

If your tkprof measurements are corret -give them the figures. And request them to come back with figures they derive from some kind of tracing. :)
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Geert GOracle dba
Top Expert 2009

Author

Commented:
we did the measurements with 2 dba's (i am the less experienced)
i know this is a no-win scenario for us.

it would be a challenge to create a app which is so slow as theirs, but they achieved it anyway

we were gonna try and explain like this:
a christmas tree, 20 workers and a  100 christmas balls

best way:
the box is on the terras, the christmas tree is at the end of the garden
each worker 5 times take a ball and hangs it on the tree
that's it

their way:
serializable :
only 1 worker can hold 1 ball at any given time

connection pooling:
each worker makes a path (separate from each other) using stone tiles to the tree for 1 pass from the terras to three.  After the 1 pass they need to clear the path with stone tiles again before a next pass can be made

i think this somewhat resembles their way of working ...
they use serializable on a oracle db
and we advise them not to, but they say they have to

then we advise to use only 1 session because of the serializable, as of the second session they just run in each others way

then we ask them why they keep dropping and recreating sessions : answers: we use connection pooling

and then they ask us why the database is slow ?

so we put a trace on what they were doing (plain insert into 1 table of 200000 records)
they took 45 minutes.  we did this test from eu to us, took us 13 seconds

i was wondering if anyone has used a electric shock therapy on any of such developers and what the results were ?
Emmanuel PASQUIERFreelance Project Manager
Top Expert 2010

Commented:
That's an excellent story Geert !!
It would be exhilarating if it wasn't so sad.

I Hope you'll find a way ! Keep us posted

PS : they are many solutions from spy movies involving torture you might want to try. or in Lethal Weapon.
Geert GOracle dba
Top Expert 2009

Author

Commented:
i'll trying to find the best torture method
"alter session set isolation level serializable" only sets isolation level for transactions in this session to serializable. Serializable means that not only you do not see uncommited transactions but also not see commited ones too from other sessions.

It does not mean that only one session is working in the database.

Also serialization happens on the row level and unless all of the sessions in the connect pool trying to work on the same row they can all perform work simultaneously.

The only way you can help developers to tune is to learn how to develop better then they can.

Finally something interesting today. Listening ;)
Geert GOracle dba
Top Expert 2009

Author

Commented:
that's fine,
but they insert the same duplicate rows too ...
Geert GOracle dba
Top Expert 2009

Author

Commented:
>>to learn how to develop better then they can.
we are allready at this a month trying to convince them it's on their part
electro shock ?
"Electroconvulsive therapy (ECT), also known as electroshock, is a well-established, albeit controversial, psychiatric treatment in which seizures are electrically induced in anesthetized patients for therapeutic effect."

Go.
Geert GOracle dba
Top Expert 2009

Author

Commented:
for a bit less cloud thinking
this is for processing incoming orders

they have 15 workers continuously monitoring the database for an incoming order
1 worker is more than enough to do the job

it's like this:
worker flow:

update job_processing set running = 'Y' WHERE processing = 'INCOMING ORDERS';

process a order

update job_processing set running = 'N' WHERE processing = 'INCOMING ORDERS';

anybody can tell me what the use is of this construct with serializable ?
Hi,
You're right, serializable has no meaning here. Processes will be serialized on the JOB_PROCESSING row lock.
It seems they are processing a queue in the less efficient and less scalable way.

What to do to explain to them ?
Maybe draw a sequence diagram of what happens, adding timing you get from tkprof (with wait events):
time to establish a connection, time to wait for a lock, etc.
So that splits the response time in each layer (application,network,database,...) and you see the responsabilities.

Regards,
Franck.
Top Expert 2010

Commented:
no wonder Hitler pushed panzer divisions against France through Belgium (and caught the French by surprise)....

Mark GeerlingsDatabase Administrator
Commented:
This is very poor application design for a multi-user system:
update job_processing set running = 'Y' WHERE processing = 'INCOMING ORDERS';
process a order
update job_processing set running = 'N' WHERE processing = 'INCOMING ORDERS';

This is *NOT* an Oracle database problem!  It is a problem of faulty system design.  Change the application so the two "update..." statements are not required.  Or, if they are required for some reason, change the table (and the "update" statements) to include an additional column, so each user can update only their own row in this table.
Geert GOracle dba
Top Expert 2009

Author

Commented:
well we finally got their attention with this graph

they finally saw which part to look at for tuning
graph.png
Hi,
That's nice, if it helps them understand the problem.
That was what I suggested by 'split the response time in each layer'
Regards,
Franck.
David VanZandtOracle Database Administrator III

Commented:
nicely done!
Geert GOracle dba
Top Expert 2009

Author

Commented:
Thx for all the ideas.

splitting the time in a graph seems to be something they couldn't overcome
Geert GOracle dba
Top Expert 2009

Author

Commented:
hmmm, seems there is something different in the accepting of multiple solutions

I was gonna accept ID:25642712 as the answer and the others assist.
Seems the accepting of multiple answering sets the first clicked as accepted

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial