Thanks for your advice. You have always been of help, even in the past. I collected fresh statistics on both tables using dbms_stats.gather_table_stats and then started the test. The difference between 2 tables is that first has around 10 indexes and second has only primary key. Will exporting the statistics from original to replicated table create any difference?
However, after lot of investigation, I found that the column COACH_CURR_LOCATION_ID had cardinality of around 200. I was able to trim the execution of query to 2 seconds by creating a bitmap index on join column COACH_CURR_LOCATION_ID of heavy AKHIL_TEST1 table.
I have removed the last trace file as my team member just pushed me to do that. However, for your information and as it is moral responsibility, I am attaching the new execution plan with fictitious table names.
