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>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>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] *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 >