Oracle: What should be better, alter table CACHE or INDEXES?

I have a database (700 tables / 400GB) running on a VM with 64GB of RAM on a physical server with 384GB of RAM, the database has some indexes on some table fields, but it hasn’t a good performance.

That physical server was reserved to run databases only, so I could allocate as memory as necessary on it to get a good performance.

I’ve been reading about tuning and realized that I could put all non-securefile (blob) tables on memory using the CACHE or KEEP POOL parameter, or create a lot of indexes.

What should I do to make queries run faster on it? CACHE or INDEXES?

indexes and caching serve different purposes:

1. indexes allow you to read only a (hopefully small) part of the table.
2. Caching reduces the time it takes to read a block as you only need logical (in buffer) reads instead of disk reads.

The two mix very well together as Oracle would happily use a cached index if it fits the query and statistics.

For example reading 10 rows via index will be faster then scanning 40gb table in memory, and if the index is cached then even more so.