Searching for FULL table scan

Today I saw this question at OTN:

http://forums.oracle.com/forums/thread.jspa?messageID=9441473&#9441473

“Can someone provide me asql query which will show me the actual query which is going for FTS including how many time that FTS query executes so far.”

=========== My answer  =========

If your goal is to search possible queries with problems, you should try this:

select * from (
select sql_id,buffer_gets,executions,rows_processed from v$sqlarea order by 2 desc)
where rownu<30;

Then you should evaluate if make sense 10 rows query, executed just 5 times, consume 100,000 buffer_gets.
or
why some queries are being executed 1 billion times.

the botton line is , search weird things, and improve it.

You maybe even find out a huge consuming buffer_gets query that need a index,
Just keep in mind, FTS is not synonym of problem.
And even when it´s actually a problem, searching one by one , it´s not for sure the best aproach.

Avoiding logon trigger

Sometimes people can´t access the database any longer due to a system event logon trigger.

If this happen the solution is change the hidden parameter _system_trig_enabled to false.