We have a slow query that has a low optimizer_cost value but a very high user_io_wait_time value. Does this just indicate that there is an I/O bottleneck?
Should we allocate more memory to Oracle? Get faster disks?
Note: the stats were gathered by querying V$SQL
Allocating more memory to Oracle will tend to make a database faster because enlarging the DB cache reduces I/O. We can get a feel for the potential improvement with the DB cache advisor.
However, there is no guarantee that adding memory will improve your specific query. It is a blunderbuss approach, and whether it works in this case really depends on the nature the query.
There are all sorts of reasons why the cost in the explain plan doesn't match the actual elapsed time. One could be that your statistics are stale, and the plan fits a much smaller table. Another is that you are suffering from skewed distrubution, so you have a plan which suits certain query values but not others. Or you might be suffering from an unhappy choice of bind variable peeking. Then again it could be down to hardware: bad disk, dodgy interconnects, poor SAN configuration.
As Chekhov observed, poorly performing queries are all poorly performing in their own way. So, if you want some more detailed help you will have to supply more details about your specific scenario.
Before you buy anything (before you make any capital expenditure), do some more digging. Explain plan is like a weather forecast. It may collapse in the face of reality.
There are a few things you can do, while the offending query is running.
(1) log into the server machine and monitor the use of CPU cycles and disk io. In Linux the command sar -u 1 120 will give you two minutes' worth of cpu %idle data. If you have no idle cpu, then your query is overusing computational cycles. Similarly sar -d 1 120 will tell you whether your disk IO is saturated. Microsoft's perfmon and Windows Task Manager will tell you the same things if that's where your Oracle instance is hosted.
(2) use the Oracle Enterprise Manager console. Log in to the data base, look at the session list, and take a look at various cpu-intensive sessions. The enterprise manager can show you what queries are running, and what exact execution plans they are using. They also can tell you if they're doing something super time consuming like full-scanning a huge table.
Take a detailed look at the explain plan output. It may show you a full table scan, or some other nasty performance hit, that suggests a solution like adding an index.