plq
asked on
Oracle going seriously wrong on a "simple" sql statement
Take a look at this statement:
INSERT INTO SAVEDLISTWIDGET(SAVEDLISTI D, WIDGETID) SELECT 2 AS SAVEDLISTID,A.WIDGETID FROM WIDGET A WHERE (A.LOCATIONID IN (2,3,623,22,595,578,579,62 8,589,580, 581,582,59 0,23,583,8 34,594,34, 35,36,37,4 79,38,39,4 0,41,42,43 ,44,521,45 ,512,46,47 ,501,48,49 ,50,51,52, 53,54,55,5 6,57,482,5 04,508,520 ,59,60,61, 62,63,64,4 99,500,66, 67,68,69,4 81,667,668 ,626,532,4 ,596,74,75 ,77,78,79, 489,517,80 ,584,490,8 1,476,82,8 3,84,85,86 ,87,480,88 ,89,506,90 ,477,91,92 ,93,94,485 ,95,491,66 9,6,7,96,4 92,670,671 ,672,97,98 ,99,100,51 9,101,102, 103,104,59 3,106,107, 673,674,67 5,478,514, 510,507,67 6,677,678, 679,680,68 1,682,683, 684,685,68 6,687,108, 688,689,59 1,585,586, 109,110,52 3,111,511, 112,487,11 3,114,649, 115,116,11 7,118,119, 120,121,12 2,123,124, 518,125,12 6,691,127, 692,693,69 4,695,128, 70,696,697 ,698,130,4 83,488,831 ,505,625,5 87,513,131 ,522,132,6 99,700,701 ,133,134,5 24,135,136 ,137,138,1 39,503,140 ,141,142,1 43,144,145 ,146,502,1 47,703,148 ,704,705,7 06,707,708 ,709,710,7 11,712,713 ,714,715,7 16,717,149 ,150,151,1 52,486,718 ,153,154,1 55,156,157 ,158,159,7 19,588,160 ,161,48
4,162,163,164,165,166,167, 168,169,17 0,171,172, 173,174,17 5,176,720, 177,178,17 9,627,533, 13,15,629, 187,721,46 0,188,189, 722,190,19 1,192,193, 194,195,53 8,196,197, 198,451,19 9,200,723, 724,456,72 5,539,423, 726,727,20 1,729,202, 203,204,20 5,730,731, 732,733,54 0,734,735, 736,206,20 7,424,208, 209,210,73 7,541,738, 739,425,74 0,741,742, 211,212,42 6,743,744, 427,428,45 8,213,542, 214,215,74 5,216,746, 543,747,54 4,531,572, 421,573,57 1,16,574,5 62,430,224 ,225,226,2 27,228,181 ,229,549,2 30,563,749 ,231,232,2 33,234,235 ,236,237,2 38,431,565 ,239,240,7 50,751,241 ,655,752,7 53,754,432 ,243,436,7 56,244,245 ,545,246,4 33,526,247 ,434,248,7 57,249,250 ,251,546,7 58,252,253 ,254,759,2 55,256,257 ,453,258,5 47,259,260 ,261,262,4 35,263,761 ,762,763,7 64,765,766 ,767,768,7 69,548,770 ,575,17,56 4,264,561, 272,273,27 4,275,276, 438,277,43 9,440,441, 442,443,27 8,550,279, 280,281,28 2,283,284, 285,558,77 1,286,551, 287,444,28 8,289,290, 291,292,77 2,773,293, 774,775,29 4,295,296, 552,297,77 6,446,298, 299,300,44 7,777,778, 301,302,45 2,779,780, 781,782,30 3,559,304, 553,305,52 7,783,306, 784,528,78 5,307,308
,180,786,787,554,309,310,3 11,312,788 ,313,314,4 48,315,789 ,790,316,5 55,317,318 ,449,319,3 20,321,791 ,792,793,5 56,557,322 ,794,323,3 24,450,14, 525,622,64 3,795,796, 330,331,33 2,333,335, 336,797,79 8,799,535, 801,802,80 3,804,805, 806,807,80 8,800,529, 809,810,81 1,812,813, 814,815,81 6,536,817, 818,819,82 0,821,822, 823,824,82 5,560,826, 827,828,56 9,568,566, 577,420,46 1,570,567, 18,19,644, 611,364,36 5,366,367, 368,462,36 9,370,371, 612,463,37 2,373,374, 375,376,37 7,378,613, 379,380,38 1,382,383, 384,385,38 6,387,388, 389,390,46 4,391,392, 393,465,39 4,395,396, 397,398,39 9,400,401, 614,466,40 2,403,404, 467,615,40 5,406,616, 407,408,40 9,410,411, 412,617,41 3,414,415, 530,20,618 ,337,338,3 39,340,341 ,342,343,4 68,344,345 ,346,347,4 72,624,469 ,470,473,4 74,475,471 ,21,645,59 7,598,599, 600,601,60 2,603,604, 605,606,60 7,608,609, 610,619) AND A.CATEGORYID IN (9,59,66,47,52,68,61,56,49 ,50,71,51, 67,76,64,7 0,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: https://www.experts-exchange.com/questions/22022344/ORA-03114-NOT-CONNECTED-TO-ORACLE.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
INSERT INTO SAVEDLISTWIDGET(SAVEDLISTI
4,162,163,164,165,166,167,
,180,786,787,554,309,310,3
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: https://www.experts-exchange.com/questions/22022344/ORA-03114-NOT-CONNECTED-TO-ORACLE.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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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,7 0,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,7 0,55,58,63 ,75,62,72, 65,57))
AND A.LocationID BETWEEN 2 AND 900
AND A.LocationID NOT IN (......................... .......... .......)
WHERE A.BudgetID = 1
AND A.CategoryID IN (9,59,66,47,52,68,61,56,49
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
AND A.LocationID BETWEEN 2 AND 900
AND A.LocationID NOT IN (.........................
Plq,
correct the line below... there is comma missing.. or you must remove space.
,151,152,486,718,153,154,1 55,156,157 ,158,159,7 19,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'
correct the line below... there is comma missing.. or you must remove space.
,151,152,486,718,153,154,1
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'
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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,Locati onID,Budge tID. 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.....
AngelIII - I firstly tried an additional index for CategoryID, LocationID. That didnt help. Also I tried a covering index on WidgetID,CategoryID,Locati
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.....
ASKER
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...
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...
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.
ASKER
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