We're having quite a big problem here with Oracle (of course!)

Basically, we are using a rather large CompareRange - our comparerange consists of 200+ ID's that we have poulated elesewhere. On SQL Server, this works fine, but on Oracle, its extremely slow, and is now failing with the following error:

ORA-04031: unable to allocate 68 bytes of shared memory ("shared pool","SELECT "PERFORMANCE_CACHE"."...","sql area","optdef : apanlg") 

Heres what I know:

• The shared memory pool is used (among other things) for binding to variables,
• LLBL creates the queries by using these binding variables - so this must be where the memory need comes from
• I know how to increase the shared memeory pool, so I added 10 Mb to it, but still get the problem. queries that pass in 200 would stop failingl, but some pass in 300 and these still fail

Is there anything we can do? We're hours away from releasing, so rewriting the query to not use a CompareRange is not really an option

Matt.

I create a list of parameters, so you'll get: SELECT ... FROM Bla WHERE foo in ( 1, 2, ... , N)

If you use 300 or so elements in the range, it gets quite slow as the query has 300 parameters. There was a customer who had 1000 which was the maximum or so, so it should work with large sets.

Did you re-start the Oracle processes after you've increased the shared memory pool? (I'm no oracle expert, so this might be a stupid remark).

In general, these large ranges are not recommended. Try to make them with fieldcompareset predicates where the set is read from the db.

Yeah it should work upto 1000, but for some reason its really unhappy with its SHARED_MEMORY_POOL.

I'm no expert either, but yes the service was restarted.

I'll keep on looking into this....

Quick google on this shows that a lot of people run into this sometimes. Some oracle geek posted:

alter system flush shared pool; from sqlplus connected with dba privileges.

Dunno what it does, but he recommended it on a 24/7 system if a reboot / restart wasn't an option.

Did you already opted for increasing it with, say 50MB?

Yeah, we tried increasing it, but I think only by about 20 meg. The google postings I found seemed to suggest that an increase of aroung 5Mb was sufficient in most cases.

What does worry me is that when I increase the shared pool and restart oracle, the query runs the first time and then subsequent calls might fail with the error - this kind of scenario does make me ponder some sort of dodgy "gargage collecton" type goings on within oracle - maybe the "shared pool" is only released when we explicitly close the connection and maybe we leave it open by mistake.

What also worries me is that elsewhere on the system we have a similar query, in the "classic asp" pages - this query fires a "where ID in (x,y,z)" query with a similar amount of "ins" but always works.

I'll keep looking this morning....

Your remark about the connection might be the case. If connection pooling is on (which is by default if I'm not mistaken) the rdbms sees that the connection is still there, and doesn't release resources perhaps. Also, check if you call dispose of the adapter.

So try to switch off connection pooling, perhaps that works. (you can do that in the connection string)

Thanks Frans - currently battling importing a oracle database from the test server (no UI for that!!!) and then I'll check the pooling. I didn't know that connection pooling could be controlled from the conn string...thanks for that tip!

MattWoberts wrote:

Thanks Frans - currently battling importing a oracle database from the test server (no UI for that!!!) and then I'll check the pooling. I didn't know that connection pooling could be controlled from the conn string...thanks for that tip!

Also make sure you call Dispose on the DataAccessAdapter. That way you're sure the connection is closed and Dispose is called on the connection object. Looking into OracleConnection.Dispose tells me that that call can also make a difference as it does a big pile of maintenance there.

Update:

Firstly, it doesn't seem to be the connection pooling or gargage collection. The query fails the first time it is run - I was wrong when I thought that it worked first, and then subsequent runs were failing. It just doesn't run. I have tried the connection pooling but no joy.

Secondly, it seems to be the format of the query. It comes out of LLBL like this:

SELECT
    "PERFORMANCE_CACHE"."TARGET_ID" AS "Target_ID",
    "PERFORMANCE_CACHE"."LASTRECACHE_DATE" AS "LastReCache_Date",
    "PERFORMANCE_CACHE"."SITE_ID" AS "Site_ID",
    "PERFORMANCE_CACHE"."GROUP_ID" AS "Group_ID",
    "PERFORMANCE_CACHE"."CACHEVALUE" AS "CacheValue",
    "PERFORMANCE_CACHE"."BANDINGZONE" AS "BandingZone",
    "PERFORMANCE_CACHE"."DIRTYYN" AS "DirtyYN",
    "PERFORMANCE_CACHE"."DATASTATUSFLAG" AS "DataStatusFlag" 
FROM
    "ENVOY"."PERFORMANCE_CACHE" 
WHERE
    ( 
    ( "PERFORMANCE_CACHE"."SITE_ID" IN 
        (
                            ** LIST OF 220 SITE ID's **
        ) Or
        "PERFORMANCE_CACHE"."GROUP_ID" IN 
        (
            :Group_ID222,
            :Group_ID223,
            :Group_ID224
        )
    ) And
"PERFORMANCE_CACHE"."TARGET_ID" IN 
(
    ** LIST OF 20 OR SO TARGET ID's **

)
) 

Now, I tried creating a new predicate - much simpler, but with more parameters. I create 600 ID's, and pass that into a CompateRange predicate, so the SQL is thus:

SELECT "PERFORMANCE_CACHE"."TARGET_ID" AS "Target_ID","PERFORMANCE_CACHE"."LASTRECACHE_DATE" AS "LastReCache_Date","PERFORMANCE_CACHE"."SITE_ID" AS "Site_ID","PERFORMANCE_CACHE"."GROUP_ID" AS "Group_ID","PERFORMANCE_CACHE"."CACHEVALUE" AS "CacheValue","PERFORMANCE_CACHE"."BANDINGZONE" AS "BandingZone","PERFORMANCE_CACHE"."DIRTYYN" AS "DirtyYN","PERFORMANCE_CACHE"."DATASTATUSFLAG" AS "DataStatusFlag" FROM "ENVOY"."PERFORMANCE_CACHE" WHERE ( "PERFORMANCE_CACHE"."SITE_ID" IN (:Site_ID1.....:Site_ID600))

Now, this one works fine!! So, it must be something to do with the way the sub-queries are combined in a way which makes oracle really inefficient!!!

I just need to figure out why!

Hmm. So 2 IN queries with a lot of parameters makes it go belly up?

Now, I must say, two IN queries with a lot of parameter values can be pretty intense, as optimizing it can be a real pain. But I don't see why it would eat away all the memory.

Thats right - I can create 2 IN clauses, each of which can specify 100 ID's. This will fail, but a single IN clause consisting of 999 ID's will work!

I dont understand this either, but I have seen similar issues in the past with Oracle.

I'm currently looking at wherther I can rewrite the predicate to not use compareRanges...

Update;

Turning the SQL that fails into a non-parameterised SQL statement makes the statement work - so its something to do with multiple "IN"''s and them being parameterised.

Morning Otis (hey, did you know you were named after a well known lift-company)!

Anyway, just wanted to give you an update. I was going mad yesterday, and about to post the code I was using to ask you to run and see if you had the same problem. Before doing this, I decided that it would be a good idea to check a different table, to confirm that it was recreatable on any table.....well, on any other table, it worked fine! So, on inspection, I noticed 2 indexes had been created on my table one was for coumns (target_id, site_id), and the other was for columns (target_id, group_id). I deleted these indexes and it works fine (and is much quicker at executing the statement)!!

My query was using all 3 columns - target_id, site_id, and group_id, all I can imagine is trhat oracle tried to optomise my query based on the indexes I had, got very confused and ended up doing something very silly!

Phew

whoa, what an odd cause for such an error.

Glad it's solved, especially while you're hours before the release!

(edit) yes, I know it's a lift company Actually I got the name from a stairway, also from the same firm, back in the days I was still hammering 68000 assembler on an A500 )