Skip to Main Content
  • Questions

  • Identifying SQL Queries that are consuming Shared Pool Memory

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, wain.

Asked: October 28, 2016 - 9:24 pm UTC

Last updated: August 22, 2019 - 2:31 pm UTC

Version: 11.2 g

Viewed 10K+ times! This question is

You Asked

Hello Tom,
Is there a way to identify SQL queries that are consuming the majority of the shared pool for e.g
Session #1 is using 56 kb
session #2 is 10mb I would want to identify each query and the memory value it's consuming.

Thanks in advance

Wain

and Connor said...

"Sessions" as such dont consume shared pool in that sense. For example, 100 sessions could be running the same SQL which uses a tiny bit of shared pool, or that SQL could consume megabytes and megabytes.

Its a *shared* pool, because the resources are *shared* by sessions.

Check out v$db_object_cache, but please elaborate more on what you want to achieve here. Sure its not PGA you're worried about ?

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

I would like to know why a query is taking lot of shared pool

Sangeetha, January 30, 2019 - 4:49 am UTC

HI,

Why would a query is consuming lot of SGA ? In my case the query just returns one row. But it is appearing in the "SQL ordered by Sharable Memory" top in AWR.
Connor McDonald
February 06, 2019 - 12:49 am UTC

Its not related to the rows that come back, (or the rows at all) but the complexity of either the SQL statement, the objects it is referencing or the size of the execution plan

See here for an example

https://asktom.oracle.com/pls/apex/asktom.search?tag=vsql-sharable-mem-and-global-temp-tables

SQL ordered by Sharable Memory

A reader, August 22, 2019 - 12:46 pm UTC

Can SQLID listed in SQL ordered by Sharable Memory in AWR report cause ORA-02055: distributed update operation failed; rollback required error.

In my existing system, process stopped with ORA-02055 and next time it worked fine.

I checked for AWR report and I can see some SQLID under SQL ordered by Sharable Memory which is otherwise not there when the process worked fine.
Chris Saxon
August 22, 2019 - 2:31 pm UTC

What exactly is the query you ran? Did you run any other statements over a database link before this in the same session?

Identifying SQL Queries that are consuming Shared Pool Memory

Mansi, August 22, 2019 - 6:15 pm UTC

I did not run anything on database link, but I ran package with multiple queries, which I wont be able to provide here.
and that is scheduled task. only difference is that I ran the package at different time and not at the scheduled one and I see this error, ever since I am trying to reproduce this error.


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.