The SQL I inquired about once on Oracle has not returned the results since the second time.

In the Oracle database, the results will be returned in a few seconds, but if you contact the exact same SQL immediately afterwards, the results will not be returned immediately.

I'm sorry that I can't write down the SQL and execution plan, but the situation is as follows.

• External combination of about 10 tables
• 700,000 records for many tables, tens of thousands others
• Each table has just been annalyzed
• The execution plan costs around 10000

I checked and found that [alter system flush shared_pool] could erase the cache of the execution plan, so I tried it, and immediately got the results back again.

We are currently in the development stage, so we are flushing every time, but is there any other way to improve it?Do you still need SQL tuning?

The environment is as follows:

• Windows Server on Virtual Machines
• Oracle 11.2
• Masquerade Machine Memory 8GB
• Other detailed settings are unknown (I don't know how to check/caution)

This is a poor question, but I appreciate your cooperation.

add
I forgot to write, but when I looked at the virtual machine task manager, the CPU utilization increased when I got the first result, but after the second time, it didn't look like CPU utilization was running. (This is mostly a guess.)

You do not have permission to monitor Enterprise Manager.(It is possible to ask about the situation every time.)

Additional 2
After obtaining SQL traces for the first and second calls,
The following conditions were observed below.
Does this mean that I stumbled on Fetch for the second time?

call count cpu elapped disk query current rows
Parse 10.57 0.58000
Execute 10.000.000
Fetch 11.18 1.200 249222061
total3 1.76 1.780 249222061

Trace After Second Contact

call count cpu elapped disk query current rows
Parse2 1.20 1.20000
Execute 20.000.000
Fetch22723.883392.8245574362 23778826400
total 62725.08 3394.02 45574362 23778826400