topic Oracle RDB delayed SQL request in Operating System - OpenVMS

Oracle RDB delayed SQL request

Dmitry V. Sinelnikov — Thu, 11 Mar 2010 20:49:39 GMT

Hi All,

Not sure if subject theme is appropriate for this forum branch but since the issue runs on OpenVMS - I think I should start from here...

Config: OpenVMS V8.3-1H1; Itanium rx4640, Oracle RDB 7.3. Database has one table (let it be T1) with over 13 million records.

Would like to execute simple SQL statement with the output to the telnet window:
SELECT * FROM T1 WHERE FIELD < 2000;
It runs in two seconds, returns 1999 records, everything is ok. Make the threshold lower:
SELECT * FROM T1 WHERE FIELD < 1500;
It runs even faster, returns 1499 records, as expected. Now decrease the threshold to 1000:
SELECT * FROM T1 WHERE FIELD < 1000;
It starts running and suddenly got stuck at 511 records with blinking cursor... After 7-10 minutes (!) it wakes up and shows the rest for us to have 999 records as expected...

That's kinda strange because both first and second requests show this 512 and etc records in less than a second. The issue is reproducing constantly. No deadlocks or timeouts; no one else is working at the system during the test. The same database being restored at the AlphaServer (the same OpenVMS and RDB versions) shows the same strange behavior.

I suspect something wrong is with the database / table indexes / storage area... But unfortunately don't have much experience to understand what exactly. Could anyone give any hints on this issue?

p.s. table structure is attached.

Re: Oracle RDB delayed SQL request

Richard J Maher — Thu, 11 Mar 2010 21:19:24 GMT

Hi Dimitry,

For two things to look at (if you haven't already): -

1) Use $rmu/show statistics to display "stall messages" while it's running

2) Turn on Optimizer output and see if the strategies change when the selection criteria changes

Cheers Richard Maher

PS. Also how long since anyone did and rmu/analyze on any of those indexes?

Re: Oracle RDB delayed SQL request

John Gillings — Thu, 11 Mar 2010 23:33:20 GMT

Dmitry,

Could you please post your SELECT query in terms of the table structure you've posted? What does FIELD correspond to?

Re: Oracle RDB delayed SQL request

Chris Barratt — Thu, 11 Mar 2010 23:35:56 GMT

In addition to Richard's suggestions, using rmu/show stats when running the query - look at the "Logical Area Information", " Logical Area Overview (Tables)" and see how many records of tables and indexes it is reading in each case.

IS "FIELD" the first item in any of the indices ?

If it is, then I would be thinking a call to Rdb support might be in order.

cheers,
chris

Re: Oracle RDB delayed SQL request

Jean-François Piéronne — Fri, 12 Mar 2010 06:01:28 GMT

Dimitry,

can you post the result of:
set transaction 'read only';
set flags 'strat, detail(2),estim';
SELECT * FROM T1 WHERE FIELD < 2000;
SELECT * FROM T1 WHERE FIELD < 1500;
SELECT * FROM T1 WHERE FIELD < 1000;

where field is the real column name.

Jean-FranÃ§ois

Re: Oracle RDB delayed SQL request

Jean-François Piéronne — Fri, 12 Mar 2010 08:51:10 GMT

Looking at your indexes, I suggest you use
set flags 'strat, detail(2),estim,exec(10)'

JF

Re: Oracle RDB delayed SQL request

Dmitry V. Sinelnikov — Fri, 12 Mar 2010 11:16:12 GMT

Hi all,

Thanks for replies.

The original request that slows dramatically is as follows:
SELECT * FROM NR_DOC_COMPLEX WHERE A_SYSID_DOC <1000 AND A_STAT_CHECK = 0;
The one that runs just fast:
SELECT * FROM NR_DOC_COMPLEX WHERE A_SYSID_DOC <2000 AND A_STAT_CHECK = 0;

I ran both 'fast' and 'slow' SQL statements with the flags as Jean suggested. Telnet output is attached for both sessions. Slow point is at

~E#0003.01(1) BgrNdx1 EofBuf DBKeys=1024 Fetches=1+7 RecsOut=512

record of output.

p.s. Forgot to mention RDB version:
$ rmu /show version
Executing RMU for Oracle Rdb V7.2-310

Re: Oracle RDB delayed SQL request

Jean-François Piéronne — Fri, 12 Mar 2010 11:42:42 GMT

Your request's strategy use 2 indices, XPK_NR_DOC_COMPLEX1 and SI_NR_DOC_COMPLEX_DATE.

The interesting traces are
in the fast request:
~E#0003.01(1) BgrNdx1 EofBuf DBKeys=1024 Fetches=1+7 RecsOut=512
~E#0003.01(1) BgrNdx2 EofBuf DBKeys=1024 Fetches=4+433 RecsOut=512
...
~E#0003.01(1) Fin TTbl DBKeys=3998 Fetches=0+121 RecsOut=1999

in the slow one:
~E#0003.01(1) BgrNdx1 EofBuf DBKeys=1024 Fetches=1+7 RecsOut=512
~E#0003.01(1) BgrNdx2 EofData DBKeys=999 Fetches=4+239808 RecsOut=512 #Bufs=126
~E#0003.01(1) FgrNdx FFirst DBKeys=512 Fetches=0+68 RecsOut=512`ABA
...
~E#0003.01(1) Fin Buf DBKeys=999 Fetches=0+65 RecsOut=999

Rdb use for background index a buffer of 1024 dbkeys. If you overflowed this buffer it will switches to another index.

In the fast one this buffer is quickly overflowed, so the scan of the second index is abandoned. But i the slow query Rdb can't find 1025 records using the BgrNdx2 index, so the index is fully scan which give "4+239808" I/O.

you can try to use the flags MAX_STABILITY for test, put a outline, or add an alternate index (A_STAT_CHECK, A_SYSID_DOC , ...)

JFP

Re: Oracle RDB delayed SQL request

Jean-François Piéronne — Fri, 12 Mar 2010 12:01:18 GMT

You can, also, read the excellent article form the Rdb journal:
http://www.oracle.com/technology/products/rdb/pdf/rdb_journal/bitmapped_scan_1.pdf

Re: Oracle RDB delayed SQL request

Richard J Maher — Fri, 12 Mar 2010 12:07:05 GMT

Hi Dmitry,

Or you could cross you fingers and stick in an ORDER BY A_SYSID_DOC, A_SYSID_ACC_ANALYT, A_SYSID_SYMBOL and see if it makes any diference?

BTW to you really need all the columns "*"?

Cheers Richard Maher

PS. If that doesn't help then I try a derived table first before going the query-plan eg: -

select * from
(select a,b,c order by where < 1000) as myTabl (x,x,z) where z = condition;

Re: Oracle RDB delayed SQL request

Richard J Maher — Mon, 15 Mar 2010 22:12:44 GMT

Hi Dmitry,

Just checking on this post and see you gave me 10 points. Can I take it that the ORDER BY convinced the optimizer to use the correct (optimum :-) index/strategy ?

Performance any better over all ?

Cheers Richard Maher

Re: Oracle RDB delayed SQL request

Dmitry V. Sinelnikov — Mon, 29 Mar 2010 12:00:55 GMT

Hi Richard,

Yes, you are correct, adding ORDER BY at the end of the statement eliminates this delay (ordering by all three columns).

Also thanks to Jean-Francois, MAX_STABILITY flag resolves the issue as well.

Two solutions, in fact... But working further with the customer we figured out that current database was corrupter for some reason. New instance of DB was provided and it didn't contain any issues like this.

Anyway, thanks to everyone for input and discussion, I appreciate this.
Closing the thread since issue is resolved.

Re: Oracle RDB delayed SQL request

Dmitry V. Sinelnikov — Mon, 29 Mar 2010 12:01:31 GMT

See comment above.