Any solution if I have to spool out information of what are the tables being accessed and in what way (either full or index scan etc..) whenever an .exe of an application is fired from a user. please help me on this
I would suggest that on a day when the workload is normal, set sql_trace to true in init.ora file.This would generate lots of trace files in your user_dump_dest directory.This is one way.
The other way is, invoke your .exe application,identify the sid of the session, and use dbms_system.set_sqltrace_in_session (I am not sure with the spelling,pls.refer documentation), procedure to invoke the trace for this particular sid.This will generate less no. of trace files than the first method.
Once the trace files are obtained, you can use the tkprof utility with an option to generate the explain plans and store the output in a system defined/user defined table.From this table,you can select the appropriate columns,identify the tables and other info what you wanted.(You may refer tkprof help on this).
The following query does not zero in on a particular session, but it will give you a good idea of the general ratio of small table scans to to full table scans. Generally, I look for a ratio less than 75 otherwise you may have untuned sql out there.
SELECT sc.value "short tbl.", ls.value "LONG tab. scans",
FROM v$sysstat ls, v$sysstat sc
WHERE sc.name='table scans (short tables)'
AND ls.name='table scans (long tables)';