Solved

Oracle going seriously wrong on a "simple" sql statement

Posted on 2006-11-07
10
584 Views
Last Modified: 2010-07-27
Take a look at this statement:

INSERT INTO SAVEDLISTWIDGET(SAVEDLISTID, WIDGETID) SELECT 2 AS SAVEDLISTID,A.WIDGETID FROM  WIDGET A  WHERE (A.LOCATIONID IN (2,3,623,22,595,578,579,628,589,580,581,582,590,23,583,834,594,34,35,36,37,479,38,39,40,41,42,43,44,521,45,512,46,47,501,48,49,50,51,52,53,54,55,56,57,482,504,508,520,59,60,61,62,63,64,499,500,66,67,68,69,481,667,668,626,532,4,596,74,75,77,78,79,489,517,80,584,490,81,476,82,83,84,85,86,87,480,88,89,506,90,477,91,92,93,94,485,95,491,669,6,7,96,492,670,671,672,97,98,99,100,519,101,102,103,104,593,106,107,673,674,675,478,514,510,507,676,677,678,679,680,681,682,683,684,685,686,687,108,688,689,591,585,586,109,110,523,111,511,112,487,113,114,649,115,116,117,118,119,120,121,122,123,124,518,125,126,691,127,692,693,694,695,128,70,696,697,698,130,483,488,831,505,625,587,513,131,522,132,699,700,701,133,134,524,135,136,137,138,139,503,140,141,142,143,144,145,146,502,147,703,148,704,705,706,707,708,709,710,711,712,713,714,715,716,717,149,150,151,152,486,718,153,154,155,156,157,158,159,719,588,160,161,48
4,162,163,164,165,166,167,168,169,170,171,172,173,174,175,176,720,177,178,179,627,533,13,15,629,187,721,460,188,189,722,190,191,192,193,194,195,538,196,197,198,451,199,200,723,724,456,725,539,423,726,727,201,729,202,203,204,205,730,731,732,733,540,734,735,736,206,207,424,208,209,210,737,541,738,739,425,740,741,742,211,212,426,743,744,427,428,458,213,542,214,215,745,216,746,543,747,544,531,572,421,573,571,16,574,562,430,224,225,226,227,228,181,229,549,230,563,749,231,232,233,234,235,236,237,238,431,565,239,240,750,751,241,655,752,753,754,432,243,436,756,244,245,545,246,433,526,247,434,248,757,249,250,251,546,758,252,253,254,759,255,256,257,453,258,547,259,260,261,262,435,263,761,762,763,764,765,766,767,768,769,548,770,575,17,564,264,561,272,273,274,275,276,438,277,439,440,441,442,443,278,550,279,280,281,282,283,284,285,558,771,286,551,287,444,288,289,290,291,292,772,773,293,774,775,294,295,296,552,297,776,446,298,299,300,447,777,778,301,302,452,779,780,781,782,303,559,304,553,305,527,783,306,784,528,785,307,308
,180,786,787,554,309,310,311,312,788,313,314,448,315,789,790,316,555,317,318,449,319,320,321,791,792,793,556,557,322,794,323,324,450,14,525,622,643,795,796,330,331,332,333,335,336,797,798,799,535,801,802,803,804,805,806,807,808,800,529,809,810,811,812,813,814,815,816,536,817,818,819,820,821,822,823,824,825,560,826,827,828,569,568,566,577,420,461,570,567,18,19,644,611,364,365,366,367,368,462,369,370,371,612,463,372,373,374,375,376,377,378,613,379,380,381,382,383,384,385,386,387,388,389,390,464,391,392,393,465,394,395,396,397,398,399,400,401,614,466,402,403,404,467,615,405,406,616,407,408,409,410,411,412,617,413,414,415,530,20,618,337,338,339,340,341,342,343,468,344,345,346,347,472,624,469,470,473,474,475,471,21,645,597,598,599,600,601,602,603,604,605,606,607,608,609,610,619) AND A.CATEGORYID IN (9,59,66,47,52,68,61,56,49,50,71,51,67,76,64,70,55,58,63,75,62,72,65,57)) AND A.BUDGETID = 1

After about 12 minutes this thing raises

ORA-03113: end-of-file on communication channel - OraOLEDB

SavedListWidget contains 8844 rows, and is Pkeyed on SavedListID,WidgetID
Widget contains 2187 rows
Location contains 715 rows
Category contains 55 rows

Database is fully normalised with LocationID and CategoryID indexed and the FK/PK relationships in place

There's no locking issues, I restarted the Oracle box and ran it from a simple vb program (.net and vb6 drivers give same error).

On smaller volumes of LocationIDs the statement works fine.

Oracle version is 9.2. Note some numbers might've got split in the paste, but it is syntactically correct. This is the cause of my earlier and still open thread: http://www.experts-exchange.com/Databases/Oracle/Q_22022344.html

Please let me know if you have any ideas on this, and whether any settings can be put in place to fix it without changing the sql.

thanks
0
Comment
Question by:plq
  • 4
  • 2
  • 2
  • +2
10 Comments
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 300 total points
Comment Utility
well, this looks like a rather insane number of values for a IN() clause, additionally I see there are alot of ranges.
could that not be implemented a bit differently?

a first try would be to replace the
WHERE FIELD IN ( val1, val2, val3 ... )
by
WHERE FIELD IN ( select val1 from dual union select val2 from dual union select val3 from dual ... )

this would make the query longer, but the difference would be to change the enumerated list in a table of values.

0
 
LVL 8

Author Comment

by:plq
Comment Utility
I will try that. Thanks

But I'm also really interested in whether we can get this sql statement working as it is, by changing settings, oledb parameters, etc.

We can change oracle settings but the sql is coded into an enterprise product and to rebuild, retest, redeploy the product would be a lot of work and expense.

And if I may just say this..., if the statement was outside of oracles capabilities it should tell you that before it sits there grinding for 12 minutes and then kills the connection ? !! OK I've worked on oracle for a while too.

thanks
0
 
LVL 34

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 200 total points
Comment Utility
This statement is "simple" in one way, but that huge list of "in" values is not a good way to get the job done in Oracle.  I agree that Oracle should either accept the statement and process it promptly, or return an error that could be handled.  The ORA-03113 is about the worst one to get, since that one cannot be handled in an Oracle program.

I realize this may not be easy, but the best solution to this problem (at least with Oracle) is to avoid constructing huge "in" lists like this.  One option would be to create another table that contains LOCATIONID and one or more "flag" or date columns, especially if this list is fairly static.  Then you set up a mechanism to update those records and use a sub-query here something like this:

INSERT INTO SAVEDLISTWIDGET(SAVEDLISTID, WIDGETID) SELECT 2 AS SAVEDLISTID,A.WIDGETID
FROM  WIDGET A, new_table b
WHERE A.LOCATIONID = b.LOCATION_ID
and b.[some_column] = [some_criteria]
AND A.CATEGORYID IN (9,59,66,47,52,68,61,56,49,50,71,51,67,76,64,70,55,58,63,75,62,72,65,57)) AND A.BUDGETID = 1

You may want the same technique for CATEGORYID also, if that list can grow large.

If this large "in" list is not at all static, you could still use a sub-query in your insert.  Just create a global temporary table for the LOCATIONID values, and insert each of them into the global temporary table first, then base the sub-query on a select from the global temporary table.
0
 
LVL 18

Expert Comment

by:Jinesh Kamdar
Comment Utility
Try changing the order of the conditions in ur WHERE clause i.e.

WHERE A.BudgetID = 1
AND A.CategoryID IN (9,59,66,47,52,68,61,56,49,50,71,51,67,76,64,70,55,58,63,75,62,72,65,57))
AND A.LocationID IN (....................................................................................................................................................................................................................................................................................................................................................)

An alternative as angelIII suggested would be to specify the range of values between the min & max and then exclude those not in the range since that would be a much smaller list i suppose i.e.

WHERE A.BudgetID = 1
AND A.CategoryID IN (9,59,66,47,52,68,61,56,49,50,71,51,67,76,64,70,55,58,63,75,62,72,65,57))
AND A.LocationID BETWEEN 2 AND 900
AND A.LocationID NOT IN (..........................................)
0
 
LVL 2

Expert Comment

by:syed555
Comment Utility
Plq,

correct the line below... there is comma missing.. or you must remove space.

,151,152,486,718,153,154,155,156,157,158,159,719,588,160,161,48
4,


also remove the '(' before A.LocationID
use where cond. in the following manner.. no need to change sequece of cond.
where A.locationID in (1,2,3,..)
 and A.SomeOtherID in (6,7,8...)
 and A.SomeOtherCond = 'test'
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 8

Author Comment

by:plq
Comment Utility
Thanks for all the feedback. The missing comma was just a paste issue as commented earlier.

I am running a series of tests this morning to get to understand whats going on a bit better. I'm going to cut this back to find the "minimum scenario" where the problem occurs, and will report back here.

My initial tests show that

- Remove the category clause and it runs sub-second
- Remove the location clause and it runs sub-second
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 300 total points
Comment Utility
I guess you have 2 indexes, 1 on Category field, and 1 on location field.

could you change one of the 2 to add the other field to it?
I suggest to change the index on the Category field to add the location field as second indexed field...
0
 
LVL 8

Author Comment

by:plq
Comment Utility
I forgot to mention, but its probably obvious, that the INSERT statement is not relevant, and the problem is reproduced in just the select statement.

AngelIII - I firstly tried an additional index for CategoryID, LocationID. That didnt help.  Also I tried a covering index on WidgetID,CategoryID,LocationID,BudgetID. Still didnt help

I have done several experiments with changing the sql around. The order of where conditions is not relevant. Adding extra where clauses on location doesnt make any difference.

So then I went to the oracle server, login as sys/sysdba, and change the following:

Setting        Old Value        New Value
--------------------------------------------
Shared Pool    48                    96
Buffer Cache   24                    48
Large Pool       8                     80

And the problem went away. consistently

So I put the oracle server back to its original settings.........and the problem is still gone away

Several expletives and profanities parted my lips at this stage, and Larry Ellisons parentage was brought into question.

Now I reboot the oracle server, start the db, and try again. Its STILL working. Original settings are still showing too.

Any ideas what could have chaned ?

I'm going to try this on the customers live server where the problem is happening.....

0
 
LVL 8

Author Comment

by:plq
Comment Utility
Hmmm customers server has Shared Pool 184, Buffer Cache 560. Their PGA target was 245 MB (my server was 24MB)

So I try increasing Large Pool to 80 MB, and cut the others back..

Problem still happening. So now increase everything.

One thing I did do on our server which I didnt mention above was "compute statistics" on the widget table.

----------------------------------------------------------------------------------------------------------------

ACCEPTED ANSWER

So... did compute statistics on the widget table, and now its working at the customer server too.

----------------------------------------------------------------------------------------------------------------
Points coming up...
0
 
LVL 34

Expert Comment

by:Mark Geerlings
Comment Utility
I'm glad that the problem seems to be solved, or has at least quit happening.  Your init settings (for: shared_pool, buffer_cache and large_pool) look unusually small.  The values from your customer's server look more typical.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now