Re: Does Oracle keep a history of which blocks are/were loaded into the buffer cache?
- From: Stefan Knecht <knecht.stefan@xxxxxxxxx>
- To: sacrophyte@xxxxxxxxx
- Date: Thu, 16 Dec 2010 14:30:15 +0100
Hey Charles
Buy them a beer and have a nice chat! Works all the time, even with
developers ;-)
Cheers
=========================
Stefan P Knecht
CEO & Founder
s@xxxxxxxx
10046 Consulting GmbH
Schwarzackerstrasse 29
CH-8304 Wallisellen
Switzerland
Cell +41 (0) 79 571 36 27
info@xxxxxxxx
http://www.10046.ch
=========================
On Thu, Dec 16, 2010 at 2:24 PM, Charles Schultz <sacrophyte@xxxxxxxxx>wrote:
> Thanks for all the comments. Niall, I think you hit the nail on the head.
> The problem is that my functional users and application support folks do not
> have a complete picture on how the data is used in the first place, thus I
> was researching a possibility of a "back door" analysis. Time to fire back
> to the application support team, and the vendor especially, and have _them_
> tell _me_ how they use the data.
>
> Cheers!
>
>
> On Thu, Dec 16, 2010 at 04:27, Niall Litchfield <
> niall.litchfield@xxxxxxxxx> wrote:
>
>> Petr,
>>
>> Cary's suggestion was to analyze the timed events that correspond
>> directly to O/S physical read calls - even if these are satisfied from the
>> O/S filesystem or storage cache - these calls still correspond to the time
>> that Oracle found it needed to read the block for the first time - i.e a
>> buffer cache miss.
>>
>> Charles,
>>
>> I'm pretty sure that unless you have a very good idea of which blocks
>> contain which rows then you'll not be able definitively to determine what
>> you seem to be after - after all the read calls are for blocks not rows. I'd
>> have thought that you'd have better luck arguing for/against partitioning
>> based on the data model and application use cases. You need really to know
>> how people are going to want to retrieve data before considering
>> partitioning - but you (or at least the business folk) probably do know
>> this.
>>
>> On Thu, Dec 16, 2010 at 9:28 AM, Petr Novak <Petr.Novak@xxxxxxxxxxxx>wrote:
>>
>>> Hallo Charles,
>>>
>>> is the counting /aggregation of readed blocks reasonable ? Using this
>>> information is probably misleading.
>>> Even if you have blocks only with 'new' and 'old' rows and no mixture -
>>> the whole table can be loaded in cache and you have no reads associated with
>>> your query.
>>> The other possibility for getting wrong information is missing
>>> appropriate index - you could do full scan and need only small number of
>>> rows.
>>>
>>>
>>>
>>> Best Regards,
>>> Petr
>>>
>>>
>>>
>>>
>>>
>>> --------------------------------
>>> Von: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx]"
>>> im Auftrag von "Cary Millsap [cary.millsap@xxxxxxxxxxxx]
>>> Gesendet: Donnerstag, 16. Dezember 2010 05:45
>>> Bis: sacrophyte@xxxxxxxxx
>>> Cc: Bobak, Mark; ORACLE-L
>>> Betreff: Re: Does Oracle keep a history of which blocks are/were loaded
>>> into the buffer cache?
>>>
>>> Charles,
>>>
>>> If you had a directory full of trace files and wanted to see what times
>>> your blocks were read in, you could grep for the 'db.*read' events and then
>>> sort by the tim value on each line (assuming you're using Oracle release
>>> 10.2+).
>>>
>>> We have two software tools that you might be interested in knowing about.
>>> If you passed your "grep ... | sort" data that I mentioned above to our mrnl
>>> tool (http://method-r.com/component/content/article/116), it would show
>>> you the wall time at which each read call was made. If you wanted to filter
>>> or aggregate your trace data (using group-by and where-type features),
>>> that's what our mrskew tool does. You can see some examples at
>>> http://method-r.com/component/content/article/117.
>>>
>>>
>>> Cary Millsap
>>> Method R Corporation
>>> http://method-r.com
>>>
>>>
>>> On Tue, Dec 14, 2010 at 7:31 PM, Charles Schultz <sacrophyte@xxxxxxxxx
>>> <mailto:sacrophyte@xxxxxxxxx>> wrote:
>>> Mark,
>>>
>>> Yeah, I realized my goof with logminer as soon as I sent it. =) Oh well.
>>>
>>> Fortunately, I am not overly worried about the overhead associated with
>>> SQL_TRACE; that is an interesting idea. I am not so sure I could sell that
>>> on a Production database, though. v$segment_stats sounds like the best way
>>> to at least get some idea. As I mentioned to another lister, partitioning is
>>> an option on the table but I really want to justify it first before I
>>> blindly offer it as a solution. *grin* The table is not currently
>>> partitioned, hence my analysis.
>>>
>>> Thanks for the ideas.
>>>
>>> On Tue, Dec 14, 2010 at 17:31, Bobak, Mark <Mark.Bobak@xxxxxxxxxxxx
>>> <mailto:Mark.Bobak@xxxxxxxxxxxx>> wrote:
>>> Charles,
>>>
>>> I’m pretty sure you’re out of luck. Consider that blocks are loaded from
>>> disk to satisfy queries, as well as DML, and logminer will have nothing to
>>> say about that. You can enable SQL_TRACE, and it will capture file#/block#
>>> data, but at great overhead and expense.
>>>
>>> Oracle just doesn’t track to that level of detail, as far as I know. You
>>> can look at V$SEGMENT_STATISTICS, to get an idea of which segments have more
>>> physical I/O happening on them, but no detail about which set of blocks in a
>>> table were most recently loaded from disk. If the table is partitioned by
>>> date, and the partitions closely align w/ the level of granularity that you
>>> require for your analysis, that may allow you to use V$SEGMENT_STATISTICS
>>> and periodic snapshots to determine if newer data is read from disk more
>>> recently than old data.
>>>
>>> Just some thoughts….hope that helps,
>>>
>>> -Mark
>>>
>>> From: oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>
>>> [mailto:oracle-l-bounce@xxxxxxxxxxxxx<mailto:
>>> oracle-l-bounce@xxxxxxxxxxxxx>] On Behalf Of Charles Schultz
>>> Sent: Tuesday, December 14, 2010 5:47 PM
>>> To: ORACLE-L
>>> Subject: Does Oracle keep a history of which blocks are/were loaded into
>>> the buffer cache?
>>>
>>> Good day,
>>>
>>> I am trying to analyze a given table in an effort to determine how often
>>> the older records are used compared to the newer records. I know I can go
>>> against the AWR and determine the age of some statements by searching for a
>>> date column in the table and grabbing the associated bind variable, but this
>>> obviously misses any queries that limit data via a join (ie, no bind
>>> variable). Is there a way to determine when a particular range of datafile
>>> blocks have been last loaded from disk, or how often? I did some digging in
>>> x$bh but that has no history (that I know of). In the meantime, I'll start
>>> up a LogMiner session and scrape all interested ROWIDs, but this is not a
>>> pretty solution. Maybe the only solution?
>>>
>>> --
>>> Charles Schultz
>>>
>>>
>>>
>>> --
>>> Charles Schultz
>>>
>>> --
>>> //www.freelists.org/webpage/oracle-l
>>>
>>>
>>>
>>
>>
>> --
>> Niall Litchfield
>> Oracle DBA
>> http://www.orawin.info
>>
>
>
>
> --
> Charles Schultz
>
Other related posts:
