In the Tuning Lab, the elapsed time of an optimized query does not match the actual run time in S
説明
When I optimized my query in the Tuning Lab, the fastest alternative has an elapsed time in seconds. Why is the actual time in minutes when executing this query in SQL*PLUS?
対策
The time measured in SQL Optimizer can be different from that measured in SQL*Plus.
If you check the Options in SQL Optimizer, there is a setting in the Options under Tuning Lab | Execution | Execution Method to control whether to "Run on server" or to "Run on client". When "Run on server" is selected, the SQL to measure run time will be packed into a PL/SQL and executed using DBMS_SQL in such a way that no data is returned to the client machine. The PL/SQL will still fetch all records from the SQL but will discard the records returned. It will measure the time taken to run the SQL and fetch all records, but it will not include in the measurement the time to send the records through the network. In other words, the network travel time is not included in the run time measured. The advantage of it is that this way you can run the SQL faster and to eliminate the network time.
When you run the SQL in SQL*Plus, the data from the SQL is actually sent back to the client machine to display. When you use "set timing on" in SQL*Plus to measure the time, the measurement will include the network travel time, and also the time to display the records.
