- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - OpenVMS
- >
- Re: Oracle RDB delayed SQL request
Categories
Company
Local Language
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Forums
Forums
Discussions
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Community
Resources
Forums
Blogs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО03-11-2010 12:49 PM
тАО03-11-2010 12:49 PM
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.
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО03-11-2010 01:19 PM
тАО03-11-2010 01:19 PM
Re: Oracle RDB delayed SQL request
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО03-11-2010 03:33 PM
тАО03-11-2010 03:33 PM
Re: Oracle RDB delayed SQL request
Could you please post your SELECT query in terms of the table structure you've posted? What does FIELD correspond to?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО03-11-2010 03:35 PM
тАО03-11-2010 03:35 PM
Re: Oracle RDB delayed SQL request
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО03-11-2010 10:01 PM
тАО03-11-2010 10:01 PM
Re: Oracle RDB delayed SQL request
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО03-12-2010 12:51 AM
тАО03-12-2010 12:51 AM
Re: Oracle RDB delayed SQL request
set flags 'strat, detail(2),estim,exec(10)'
JF
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО03-12-2010 03:16 AM
тАО03-12-2010 03:16 AM
Re: Oracle RDB delayed SQL request
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО03-12-2010 03:42 AM
тАО03-12-2010 03:42 AM
SolutionThe 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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО03-12-2010 04:01 AM
тАО03-12-2010 04:01 AM
Re: Oracle RDB delayed SQL request
http://www.oracle.com/technology/products/rdb/pdf/rdb_journal/bitmapped_scan_1.pdf
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО03-12-2010 04:07 AM
тАО03-12-2010 04:07 AM
Re: Oracle RDB delayed SQL request
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;