Thanks for the question, Matthew.
Asked: December 20, 2011 - 8:53 am UTC
Last updated: December 21, 2011 - 9:45 am UTC
Version: 10.2.0.2
Viewed 1000+ times
You Asked
I just wanted to take this opportunity to ask about a mystery I encountered a while a back and never understood. The problem is no longer occurring, so I don't expect a full resolution. I just wanted to post what I saw and see if maybe you had some insight.
(By the way, your submission form is not taking carriage returns, apparently. I apologize for the lack of formatting.) Anyway, the situation was in a production Oracle e-BS environment. Performance started tanking and we noticed excessive (and I mean EXCESSIVE) hard-parsing going on in the Oracle-delivered code.
Looking at V$SQL, sorting descending by SHARABLE_MEM, I saw that there were a large number of statements with SHARABLE_MEM on the order of 300Mb!
These statements were all INSERTS, UPDATES, or SELECTS into/from global temporary tables.
So, my question is -- what causes a statement to consume sharable memory in V$SQL? Is there anything special about GTTs that could make such a large amount of memory consumption legitimate? The excessive size of these statements was crowding everything else out of V$SQL, causing the excessive hard parsing (it seems to me).
I look forward to your thoughts / comments. Thanks in advance, Matt
and Tom said...
So, my question is -- what causes a statement to consume sharable memory in V$SQL?a big plan generally. The bigger the plan, the more memory. Consider:
ops$tkyte%ORA11GR2> declare
2 l_stmt long := 'select /*+ RULE */ * from t where x in ( 1';
3 l_rec t%rowtype;
4 l_cursor sys_refcursor;
5 begin
6 for i in 2 .. 1000
7 loop
8 l_stmt := l_stmt || ', ' || i;
9 end loop;
10 l_stmt := l_stmt || ' ) or x in ( 1';
11 for i in 1002 .. 2000
12 loop
13 l_stmt := l_stmt || ', ' || i;
14 end loop;
15 open l_cursor for l_stmt || ' )';
16 fetch l_cursor into l_rec;
17 close l_cursor;
18 end;
19 /
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2> select count(*), to_char(sum(sharable_mem),'999,999,999') sm
2 from v$sql
3 where sql_text like 'select /*+ RULE */ * from t where x in ( 1%';
COUNT(*) SM
---------- ------------
1 2,626,171
that seemingly simple SQL statement used up 2.5 mb of shareable memory.
It is not the GTTs, nothing special about them, it was the size of the plan, I would guess they had extremely complex plans associated with them.
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment