I have to collect some info in a both of redo log and dba_hist_* table (e.g DBA_HIST_SQLTEXT, DBA_HIST_SQL_PLAN, DBA_SQLSET_PLANS). Because some info i needed not exists in redo log but exists in dba table.
I tried join via transaction id (binary field) but result seem wrong. I also can not find out sql id in redo log. How can i solve it, Thanks
There doesn't exist an exact mapping between a SQL statement and the redo generated.
You could get an approximation of redo impact on a SQL by SQL basis by looking at those SQL statements in V$SQL, V$SQLSTATS or equivalent historical views by looking at the ROWS_PROCESSED column where those statements are INSERT, UPDATE, DELETE or MERGE.
Also, looking at V$SEGMENT_STATISTICS for the 'db block changes' value will give you high redo segments, which you can tie back to SQL statements impacting those segments.
