I have a very simple query which is as follows:

SELECT blob_column, varchar2_column 
FROM table 
WHERE number_column = ? AND code_column = ?

This will find one row by the primary key.

This shows as the top statement when I look for queries using high amount of sharable memory.

set pagesize 50
set linesize 250
column sharable_k format 99999
column persistent_k format 99999
column runtime_k format 99999
column total_k format 99999
column version_count format 99999
column loaded_Versions format 99999

select * from
      trunc( sharable_mem / 1024 ) sharable_k,
      trunc( persistent_mem / 1024 ) persistent_k,
      trunc( runtime_mem / 1024 ) runtime_k,
      trunc( ( sharable_mem + persistent_mem + runtime_mem ) / 1024 ) total_k,
    ORDER BY ( sharable_mem ) DESC
where rownum < 21;

The first result is this query and the figures are as follows:

------------- ---------- ------------ --------- ------- ------------- --------------- ---------- ------------------------------
fa72fbmfmq40b       6418           18        15    6452             3               2    1000354 APPUSER                   

My understanding is that sharable memory holds the plan. The plan in this case is very simple. Why is this statement taking up so much sharable memory? This is on Oracle Database 11g Enterprise Edition Release - 64bit Production.

  • Maybe related to the BLOB columns? Does the memory go down if you don't select the BLOB column? – a_horse_with_no_name Apr 14 at 7:03
  • @a_horse_with_no_name I ran a one-off equivalent statement without the BLOB and it took 23k of sharable_mem rather than 6418k. This statement runs often as you can see from the executions. It's basically the application fetching a BLOB to work on. I don't have way to simulate non-usage of the BLOB column on a similar scale. – WW. Apr 14 at 23:48
  • Probably buffer space to hold parts of the BLOB as it is being fetched ... But then again: this is 6MB. Is this a problem ? Is the query taking too long ? Is it using too many resources, and by doing so, slowing down other applications ? Or to put it simpler: is anything broken that needs fixing ? – Albert Godfrind Apr 15 at 10:35
  • I was looking into this due to ORA-04031 errors and wondering if there was something being done incorrectly by the application. – WW. Apr 15 at 12:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.