This content has been marked as final. Show 5 replies
This is certainly a major problem. The issue is rather simple, SQL Developer is only retrieving as many rows as are specified in the preferences "SQL Array Fetch Size" (under preferences, database, advanced). A simple query on a table with 10,000 rows will only give stats on at most a retrieval of 500 rows since this is the max setting for the array fetch size. I'm not buying the argument that we don't want to wait for the entire result set. I suppose if your are only interested in seeing if the query is syntactically correct then not waiting for the entire set is fine. But the whole idea of AUTO TRACE is to see the statistics of the entire run so you can know what resources were used to compete the SQL. Seeing only the resources use to get the first set of rows is very misleading. And if you change the array fetch size to be smaller, your SQL will appear to run "better" when in reality you don't know who well it's running at all.
We need to be able to have AUTO TRACE retrieve all rows, not just the setting of array fetch size. There is currently no way to get AUTO TRACE to show the stats of a compete run.
As a workaround you can use the AutoTrace button in the worksheet toolbar. It will retrieve all stats plus a plan.
Jeff the problem there is that those stats are only based on a run which retrieves up to ARRAY FETCH SIZE. Querying from from a set of tables that returns more then 500 rows (which is the max for array size) will give you less then accurate results. For example I run this query with the auto trace feature "select * from aphys1", the table has 10,000 rows. How ever the plan in the output shows for LAST OUTPUTTED ROWS only 500.
Also these stats that are shown are rather worthless for optimizing a query. Regardless of the values shown, what do those stats really tell me about how well my query ran? What pearls of wisdom can I gain from those stats? What does "commit cleanout failures: block lost" really tell me? And what can I do about that? I know what a block clean out is and it's not something I have any control over.
This shows me only 10 stats out of 600 and these are hardly the top 10 stats I'd like to know about. The ones in SQLPlus are far more useful, and I'd like to add "buffer is pinned count" to the list of ones from SQLPlus.
I'm confused - when you execute via F5 - we fetch all rows. How are you executing your query?
'all rows' - up until the max as defined in the preferences for script output
Edited by: Jeff Smith SQLDev PM on Jan 16, 2013 5:06 PM
We we're pulling the wrong metrics. Going forward, we will pull ALL non-zero perf metrics when running autotrace via script execution in your example.