USERS U ,
U.STATUS = 'A' AND
LN.USER_ID = U.USER_ID AND
LN.STATUS = U.STATUS AND
LN.STATUS = 'A' AND
LN.IMAGE_ID > 0 ND
LN.END_TIME > SYSDATE
Then, notice that LN is restricted by 4 fields. An index on one is not going to be as fast as an index on all 4. Since 2 are single restrictions, the fastest index would likeley be USER_ID, STATUS, IMAGE_ID, END_TIME, but that's a guess.
Anyway, always avoid sub-selects when you can, join everything that can be joined and restrict everything that can be restricted. These simple rules will solve most of your performance issues. Follow these by indexing properly and you've got 80% covered.
Oh, I should also probably mention things like dats, range comparisons and histograms. Generally, ranged queries operate best on data that is evenly distributed. That is how the optimizer guesses the data is distributed. Now, to do this on a range query, it must have a real value. Right now, it is looking at that SYSDATE call and seeing it as a bind variable. That means it sees WHERE END_DT > :B1. Not knowing the value, it can't even guess at what percentage of records might come back so it guesses 50% and obviously doesn't then use an index.
Now, if you provided the actual value, the optimizer would have a better shot. Of course, you would now have more than one copy in the statement cache, but that's the trade-off. With an actual value, it would say: Hmmm, where does that value fall between the minimum and maximum values in that field? Now, here's the usual problems with dates:
1 - The data is usually not clean. All that has to happen is some legacy record has a bad data from last century in it that can't be fixed and your distribution is screwed.
2 - The data is always ahead of the stats. You are always getting new dates on the leading edge ahead of what the optimizer last recorded. With other fields, any new data is usually peppered throughout existing ranges - new statuses are not created every day, for example. But new dates *are*.
3 - Dates are rarely evenly distributed. Some years are better than others. Same with Months. There are usually only a few old legacy records and probably a few *bad* records. This means the optimizer will always be wrong with it's calculated distribution.
So, the answer with dates that are range-selected is usually to hard-code the ranges, add histograms to the fields, and re-compute the stats often.
Again, this has inherent overhead in the stats-processing, copies of records in the SGA, etc. But if the field is used a lot this can make a lot of difference.