Home › Discussions › Questions & Answers › Measuring SQL Data Extraction Efficiency
Customer · · Questions & Answers · 5 replies
We have several custom data extraction SQLs that seem inefficient. How can I quantitatively assess whether the I/O expenditure for a given SQL is reasonable or if the query is performing poorly?
In the DBA AWR SQL Performance Summary report, examine the ‘IO Row’ column. This represents the average I/O per individual row retrieved. For data extraction SQLs without aggregation, this metric is a good indicator of efficiency.
If a report takes a long time, but doesn’t seem to spend much time waiting on I/O, where else might the time be going?
The time could be spent on CPU, indicating complex processing or reading massive amounts of data from the buffer cache, or on PL/SQL execution, captured in the ‘Plsql Exec Time’ column. You should also check for network waits if there are database links involved.
If we decide to perform extensive PL/SQL tuning, what tool is recommended by Oracle and detailed in the Blitz Report sources?
Oracle’s recommended tool for PL/SQL performance analysis is `DBMS_PROFILER`. You can analyze its output using the DBA DBMS Profiler Data report.
Sign in with LinkedIn or Google to take part.
