I looked at the base table of the view and found that it had never any stats for it, so I did:

Code:
exec dbms_stats.gather_index_stats (ownname => 'NGSDMS60', indname => 'NGTAGS_PK', estimate_percent => null);
It improved response time dramatically:

Code:
SELECT  *
FROM NGSDMS60.NGTAGS_VIEW T1
WHERE NGPROJGUID='6A018CB9-C15B-443b-8487-5C016EB8251B'
AND  (((UPPER(NGUSRFLD2)   =  ('ROARK'))
AND  (UPPER(NGUSRFLD19)   =  ('BUILTIN'))))
ORDER BY NGID DESC

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.09       0.17          0          0          0           0
Execute      1      0.00       0.01          0          0          0           0
Fetch        4      0.59       5.89        908        931          0          31
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        6      0.68       6.08        908        931          0          31

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 55

Rows     Row Source Operation
-------  ---------------------------------------------------
     31  SORT ORDER BY
     31   TABLE ACCESS BY INDEX ROWID NGTAGS
   1494    INDEX RANGE SCAN NGUSRFLD2_IND (object id 31663)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       4        0.00          0.00
  SQL*Net more data to client                     5        0.00          0.00
  db file sequential read                       908        0.02          5.69
  SQL*Net message from client                     4       32.67         61.10
So it was a case of no statistics for the index.

Now I have another problem....

After seeing that there were no stats for this index, I also saw that a lot of other indexes (on the same table) had no stats. So, I went ahead and got stats for the other indexes.

After I did that, the performance this same qeury went reverted back to the previous poor response time:

Code:
SELECT  *
FROM NGSDMS60.NGTAGS_VIEW T1
WHERE NGPROJGUID='6A018CB9-C15B-443b-8487-5C016EB8251B'
AND  (((UPPER(NGUSRFLD2)   =  ('ROARK'))
AND  (UPPER(NGUSRFLD19)   =  ('BUILTIN'))))
ORDER BY NGID DESC

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.11       0.11          0          0          0           0
Execute      1      0.01       0.01          0          0          0           0
Fetch        4    112.94     268.23     248319     250121          0          31
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        6    113.06     268.35     248319     250121          0          31

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 55

Rows     Row Source Operation
-------  ---------------------------------------------------
     31  SORT ORDER BY
     31   TABLE ACCESS BY INDEX ROWID NGTAGS
1680125    INDEX RANGE SCAN NGUSRFLD6_IND (object id 31665)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       4        0.00          0.00
  SQL*Net more data to client                     5        0.00          0.00
  db file sequential read                    248319        0.54        175.31
  latch free                                      2        0.00          0.00
  SQL*Net message from client                     4        8.04         11.77
I've tried deleting all the stats (dbms_stats.delete_index_stats) I just got for all the other indexes, but I can't get it to perform anymore.