Home » RDBMS Server » Performance Tuning » high buffers (oracle 11gr2 linux)
high buffers [message #675094] Sat, 09 March 2019 09:51 Go to next message
dba4oracle
Messages: 100
Registered: June 2010
Senior Member
Hi Guys,


from buffer gets of sql can find amount of buffer cache used by query

(like "buffer gets * blocksize=buffer cache usage" makes sense)


or any query to find buffer cache used by particular sqlid


Thanks
Re: high buffers [message #675095 is a reply to message #675094] Sat, 09 March 2019 09:55 Go to previous messageGo to next message
John Watson
Messages: 8075
Registered: January 2010
Location: Global Village
Senior Member
Quote:
like "buffer gets * blocksize=buffer cache usage" makes sense
What if the query uses one buffer and gets it a zillion times? What if the gets are from a direct read, which doesn't use cache at all?
Re: high buffers [message #675096 is a reply to message #675095] Sat, 09 March 2019 10:07 Go to previous messageGo to next message
dba4oracle
Messages: 100
Registered: June 2010
Senior Member
so whats the way of finding how much buffer cache particular sqlid is using

any query like using sysstat etc which can give this details
Re: high buffers [message #675097 is a reply to message #675094] Sat, 09 March 2019 10:10 Go to previous messageGo to next message
BlackSwan
Messages: 26640
Registered: January 2009
Location: SoCal
Senior Member
dba4oracle wrote on Sat, 09 March 2019 07:51
Hi Guys,


from buffer gets of sql can find amount of buffer cache used by query

(like "buffer gets * blocksize=buffer cache usage" makes sense)


or any query to find buffer cache used by particular sqlid


Thanks

What do you do differently after you have the desired number?

Will the number always be the same?
Will the number depend upon what other sessions are doing & if they requires some or all the same blocks?
Re: high buffers [message #675098 is a reply to message #675097] Sat, 09 March 2019 10:12 Go to previous messageGo to next message
dba4oracle
Messages: 100
Registered: June 2010
Senior Member
yes it will be helpful for getting pointers
Re: high buffers [message #675099 is a reply to message #675098] Sat, 09 March 2019 10:16 Go to previous messageGo to next message
BlackSwan
Messages: 26640
Registered: January 2009
Location: SoCal
Senior Member
dba4oracle wrote on Sat, 09 March 2019 08:12
yes it will be helpful for getting pointers

What does above mean?
Quantify helpful?
How is helpful measured?

Post specific examples of pointers.
Re: high buffers [message #675100 is a reply to message #675099] Sat, 09 March 2019 10:19 Go to previous messageGo to next message
dba4oracle
Messages: 100
Registered: June 2010
Senior Member
simple question:
query to find buffer cache used by particular sqlid
Re: high buffers [message #675101 is a reply to message #675100] Sat, 09 March 2019 10:24 Go to previous messageGo to next message
BlackSwan
Messages: 26640
Registered: January 2009
Location: SoCal
Senior Member
dba4oracle wrote on Sat, 09 March 2019 08:19
simple question:
query to find buffer cache used by particular sqlid
WHY?
What do you do with this number after you have it?
Re: high buffers [message #675102 is a reply to message #675100] Sat, 09 March 2019 10:25 Go to previous messageGo to next message
John Watson
Messages: 8075
Registered: January 2010
Location: Global Village
Senior Member
I'm no sure that the question has an answer. The buffer cache is shared, it isn't assigned to particular queries. What is important is how much PGA the statement needs and uses. That you can see in v$sql_workarea: the amount needed to run optimally or one-pass.
Re: high buffers [message #675103 is a reply to message #675102] Sat, 09 March 2019 10:31 Go to previous messageGo to next message
dba4oracle
Messages: 100
Registered: June 2010
Senior Member
Thanks

ok,

actually if can find buffer cache used by particular sqlid,then i can plan and avoid running not so important at peak load time
which significantly use buffer cache ,as currently db showing high cpu,io


Re: high buffers [message #675104 is a reply to message #675103] Sat, 09 March 2019 10:40 Go to previous messageGo to next message
BlackSwan
Messages: 26640
Registered: January 2009
Location: SoCal
Senior Member
dba4oracle wrote on Sat, 09 March 2019 08:31
Thanks

ok,

actually if can find buffer cache used by particular sqlid,then i can plan and avoid running not so important at peak load time
which significantly use buffer cache ,as currently db showing high cpu,io


SQL is the SGA is a shared resource.
data blocks in the buffer cache are NOT directly related to any specific SQL statement or any schema.
Do you realize & understand that Oracle manages buffer caches such that it is always 100% full?

Post SQL & results that show a problem that needs to be solved.


Re: high buffers [message #675107 is a reply to message #675104] Sat, 09 March 2019 11:07 Go to previous messageGo to next message
dba4oracle
Messages: 100
Registered: June 2010
Senior Member
yes true but if not required query fetching large amount of rows could occupy buffer cache till completion of execution impacting others like wait,or disk reads due to non availability of buffer cache /blocks
Re: high buffers [message #675108 is a reply to message #675107] Sat, 09 March 2019 11:17 Go to previous messageGo to next message
BlackSwan
Messages: 26640
Registered: January 2009
Location: SoCal
Senior Member
dba4oracle wrote on Sat, 09 March 2019 09:07
yes true but if not required query fetching large amount of rows could occupy buffer cache till completion of execution impacting others like wait,or disk reads due to non availability of buffer cache /blocks

Post SQL & results that show above to be true

IMO, your imagination exceeds your technical knowledge.
Re: high buffers [message #675109 is a reply to message #675103] Sat, 09 March 2019 11:19 Go to previous messageGo to next message
John Watson
Messages: 8075
Registered: January 2010
Location: Global Village
Senior Member
dba4oracle wrote on Sat, 09 March 2019 16:31
Thanks

ok,

actually if can find buffer cache used by particular sqlid,then i can plan and avoid running not so important at peak load time
which significantly use buffer cache ,as currently db showing high cpu,io


Are your users telephoning you to complain about "db showing high cpu,io"? If not, what are they complaining about? You have to focus on a problem. A business problem. Not on some arbitrary figure (specially not a figure that doesn't exist).
Re: high buffers [message #675110 is a reply to message #675107] Sat, 09 March 2019 11:20 Go to previous messageGo to next message
dba4oracle
Messages: 100
Registered: June 2010
Senior Member
lets say qry1 run every 1 hr is important to run,qry2 run sometimes and not so important but fetch large data,due to qry2 execution and using buffer cache will qry1 be impacted,if so i can plan to run qry2 different non peak time
Re: high buffers [message #675111 is a reply to message #675109] Sat, 09 March 2019 11:25 Go to previous messageGo to next message
dba4oracle
Messages: 100
Registered: June 2010
Senior Member
Smile
Re: high buffers [message #675112 is a reply to message #675111] Sat, 09 March 2019 11:30 Go to previous messageGo to next message
dba4oracle
Messages: 100
Registered: June 2010
Senior Member
is there way to block cadot,i just click ignore his messages not sure he is blocked from all of my post,he is frustated person
Re: high buffers [message #675117 is a reply to message #675110] Sat, 09 March 2019 12:50 Go to previous messageGo to next message
BlackSwan
Messages: 26640
Registered: January 2009
Location: SoCal
Senior Member
dba4oracle wrote on Sat, 09 March 2019 09:20
lets say qry1 run every 1 hr is important to run,qry2 run sometimes and not so important but fetch large data,due to qry2 execution and using buffer cache will qry1 be impacted,if so i can plan to run qry2 different non peak time


Oracle does NOT behave as you imagine it does.

Consider to actual Read The Fine Manual below

https://docs.oracle.com/database/121/TGDBA/tune_buffer_cache.htm#TGDBA310
Re: high buffers [message #675120 is a reply to message #675112] Sat, 09 March 2019 13:33 Go to previous messageGo to next message
Michel Cadot
Messages: 66714
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
dba4oracle wrote on Sat, 09 March 2019 18:30
is there way to block cadot,i just click ignore his messages not sure he is blocked from all of my post,he is frustated person

Just a remark: I didn't post anything in this topic so why do you want to block me?
How am I frustrating, post evidences?
And what I can see in your previous topics is that I tried to help you.
And what I can see in your previous topics is that you don't (really) thank people for spending time to help you (which is quite painful as far as I can see).

Re: high buffers [message #675127 is a reply to message #675120] Sat, 09 March 2019 21:30 Go to previous message
dba4oracle
Messages: 100
Registered: June 2010
Senior Member
Sorry it was black swan ,by mistake i mention your name
Previous Topic: Table Order in Join
Next Topic: Retrieving 8k rows from one table takes too long
Goto Forum:
  


Current Time: Fri Dec 06 10:02:36 CST 2019