Question:
I'm looking for a way to extract the full text of a
SQL statement from a table into a flat file so that I can
tune the SQL. How do I extract a SQL statement?
Answer. It is easy to purge un-used data from
AWR. Also see my notes on
SYSAUX tablespace
growth tips.
Also see my notes on purging from
scheduler$_event_log
and
scheduler$_job_run_details. In order to collect long-term statistics in AWR, it is necessary to periodically purge details that you not longer need, and SQL statement details do indeed comprise the majority of the data space used within AWR. You can also adjust the number of SQL statements collected in an AWR snapshot by adjusting dbms_workload_repository topnsql.
These tables contain SQL that is rarely useful after a month. Note: You must be connected as SYSDBA to truncate these tables.
- wrh$_sqlstat contains a history for SQL execution statistics and stores snapshots of v$sql view.
- wrh$_sqltext stores actual text for SQL statements captured from v$sql.
- wrh$_sql_plan stores execution plans for SQL statements available in dba_hist_sqlstat.
This script below will show you the total amount of space in
the SYSAUX tablespace used by AWR. To purge ALL of the
statistics for a specific date backwards, you can invoke the
following:
-- Set retention period to 90 days
exec dbms_stats.alter_stats_history_retention(90);
-- Display retention period:
select dbms_stats.get_stats_history_retention from dual;-- Manually purge
after x days and before
exec
dbms_stats_purge_stats(SYSDATE-30);
However, the
data portion of the AWR tables is heavily skewed and more
than 80% of the historical data is kept within the AWR
tables. By periodically removing the SQL table rows,
we keep the information that is important to forecasting and
capacity planning while removing old SQL statements that
will never be needed.
Let's take a closer look at how to purge the AWR SQL
table data rows.
Partial purge of AWR data
For long term capacity planning, it is always a good idea
to run an AWR report that shows the relative amount of data
consumed by each AWR table. If you purge data that is
no longer required, then you are going to have a compact
set of historical data. Let's start by
getting the Oracle table size information:
col c1 heading 'table|name' format a30
col c2 heading 'table size|meg'format
999,999,999
select
segment_name c1,
sum(bytes)/(1024*1024) c2
from
dba_extents
where
segment_type='TABLE'
and
segment_name like 'WR%'
group by
segment_name
order by
c2 desc;
table
table size
name
meg
------------------------------ ----------
wrh$_sql_plan
3
wrh$_sqltext
1
Now we can simply issue a truncate
command to purge all SQL data:
truncate table
wrh$_sql_plan;
truncate table wrh$_sqltext;
If you want to get fancy, you would arrange to delete the
old SQL tables before a particular data.
delete
from
dba_hist_sql_plan
where
sql_id =
(select
sql_id
from
dba_hist_snapshot
where
begin_interval_time < sysdate - 90);
Here is another example of purging highly populated SQL
tables in order to free-up enough space for longer AWR
retention periods.
delete
from
dba_hist_sqltext
where
sql_id =
(select
sql_id
from
dba_hist_snapshot
where
begin_interval_time < sysdate - 90);
 |
|
Get the Complete
Oracle SQL Tuning Information
The landmark book
"Advanced Oracle
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|
