First, re-write the statement
Code:
SELECT
   COUNT(*)
FROM
   USERS          U ,
   LISTINGS_NEW   LN
WHERE
   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.

- Chris