Proactive Tuning - finding out the full table scans

Let's do some query/index tuning where we could have some problems due to data growth over time. I am looking for those big tables for which queries are doing full scans. I love the following small but useful sqls to find out those areas of tuning -

SET LONG 1000000

COL object_name FORMAT A25
COL object_name FORMAT A25
COL sql_fulltext FORMAT A50


SELECT sp.object_name, dtab.num_rows, sa.sql_fulltext, sa.executions
FROM v$sql_plan sp
JOIN dba_tables dtab ON (dtab.table_name = sp.object_name)
JOIN v$sqlarea sa ON (sa.address = sp.address AND sa.hash_value =sp.hash_value)
WHERE sp.operation = 'TABLE ACCESS'
AND sp.options = 'FULL'
AND sp.object_owner NOT IN ('SYS','SYSTEM', 'SYSMAN')
AND sp.object_owner = 'PROD'
ORDER BY sa.executions DESC;

Another faster sql version could be -

SELECT sp.object_name
,(SELECT num_rows FROM dba_tables WHERE table_name = sp.object_name AND owner = sp.object_owner) num_rows
,(SELECT sql_fulltext FROM v$sqlarea sa WHERE sa.address = sp.address AND sa.hash_value =sp.hash_value) sql_fulltext
,(SELECT executions FROM v$sqlarea sa WHERE sa.address = sp.address AND sa.hash_value =sp.hash_value) full_scans
FROM v$sql_plan sp
WHERE sp.operation = 'TABLE ACCESS'
AND sp.options = 'FULL'
AND sp.object_owner NOT IN ('SYS','SYSTEM', 'SYSMAN')
AND sp.object_owner = 'PROD'
ORDER BY full_scans DESC;


From the results above, we could easily avoid those small tables where we have less number of records (say, < 30,000).

Now we need to apply both technical and business knowledge to find out why those sqls are doing full scans and if re-indexing would be helpful or something out of the box!

***

Quick Steps for upgrading/patching database

These are few quick steps I performed to patch the database. Keeping here for future references -

1. Get the patch downloaded from metalink

2. Run the installation process with interactive mode - follow instruction in
the downloaded document


3. When above steps done, start database in upgrade mode
SQL> STARTUP UPGRADE

4. Run catalog upgrade script and monitor errors (if any)
SQL> @$ORACLE_HOME/rdbms/admin/catupgrd.sql

5. SQL> SHUTDOWN IMMEDIATE

6. SQL> STARTUP

7. Recompile invalid PL/SQL packages -
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql

We are done!

***