The script I find most useful is a select from V$SQLAREA, so it actually helps with SQL statement tuning, not exactly PL\SQL tuning. (But most tuning problems in PL\SQL are actually SQL statements that need tuning.)
set linesize 1200;
set pagesize 999;
column Text format a1000;
column Ratio format 99999.99;
column Hash format 999999999999;
-- The hash_value column displays the "key" that can be used to retrieve the full text of
-- the sql statement, since the view, v$sqlarea, displays only the first 1000 characters.
spool C:\temp\readstat.txt;
select disk_reads "Reads", executions "Executed",
rows_processed "Rows", round(disk_reads / executions,2) "Ratio", hash_value "Hash",
sql_text "Text"
FROM v$sqlarea
where disk_reads > 1
and executions > 5
order by 4 desc;
spool off;
prompt 'Output saved to file C:\temp\readstat.txt'
set linesize 200;
Main Topics
Browse All Topics
by: schwertnerPosted on 2001-05-16 at 07:18:30ID: 6086562
I met in Oracle Magazine (November/December 2000) the article "Mining Gold from the Dictionary". And also two Pl/SQL scripts in the on line edition of the magazine.
If you run the scripts agaist the Oracle Database you will get a ststistics which tables experience "Full table Scan" and how many time, which tables experience "Range Index Scan", the used indexes etc.
Using indexes the DBA can eliminate the Full Table Scans. Another method is to place the small tables in the SGA memory.
The information is a compressed statistical data, the scripts run half of hour but I appretiate this article and the method - it works good. It is worth to read the article.