RE: Does Oracle keep a history of which blocks are/were loaded into the buffer cache?
Date: Tue, 14 Dec 2010 22:31:52 -0500
Message-ID: <6AFC12B9BFCDEA45B7274C534738067F61C9D288_at_AAPQMAILBX02V.proque.st>
Cool idea, Job. I have zero experience, and clearly far too little insight, into DBMS_FGA. Just another item to add to the list of stuff to learn next year… ☺
-Mark
From: Job Miller [mailto:jobmiller_at_yahoo.com]
Sent: Tuesday, December 14, 2010 9:44 PM
To: Bobak, Mark; sacrophyte_at_gmail.com
Cc: ORACLE-L
Subject: Re: Does Oracle keep a history of which blocks are/were loaded into the buffer cache?
Charles,
You can use DBMS_FGA to audit any sql that actually saw "old data" as defined by an audit condition on a particular column for a date older than a particular value.
It will capture full sql+ binds when data was queried where your audit condition is met and it can call a handler that records it for counter purposes.
So you set up a few of these policies that act as counters, for different date ranges.
and monitor the results.
Job
--- On Tue, 12/14/10, Charles Schultz <sacrophyte_at_gmail.com> wrote:
From: Charles Schultz <sacrophyte_at_gmail.com> Subject: Re: Does Oracle keep a history of which blocks are/were loaded into the buffer cache? To: "Bobak, Mark" <Mark.Bobak_at_proquest.com> Cc: "ORACLE-L" <oracle-l_at_freelists.org> Date: Tuesday, December 14, 2010, 8:31 PM 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_at_proquest.com<http://us.mc539.mail.yahoo.com/mc/compose?to=Mark.Bobak@proquest.com>> 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_at_freelists.org<http://us.mc539.mail.yahoo.com/mc/compose?to=oracle-l-bounce_at_freelists.org> [mailto:oracle-l-bounce_at_freelists.org<http://us.mc539.mail.yahoo.com/mc/compose?to=oracle-l-bounce@freelists.org>] 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
--
http://www.freelists.org/webpage/oracle-l
Received on Tue Dec 14 2010 - 21:31:52 CST