Learning is not a spectator sport
Connor McDonald on SQL and the Oracle Database
Add ORDER BY to make ANY query faster
Yes it’s SCBT day here in Perth!
SCBT = Silly Click Bait Title 
This post is just a cautionary tale that it is easy to get caught up judging SQL performance solely on a few metrics rather than taking a more common sense approach of assessing performance based on the true requirements of the relevant component of the application. I say “true requirements” because it may vary depending on what is important to the application for a particular component.
For the majority of the time, response time is most probably the thing you will care about most. Certainly for customer-facing components of an application, response time is likely to be the dominant factor in defining application “success”. But it does not necessarily always need to be response time. You might have a SQL statement that gets executed millions (or billions) of times, and hence perhaps parse time CPU or overall latching activity might be a critical factor.
My point is this: Not all SQL statements can be simply judged on a rule of “less consistent gets = a better SQL”. Here’s an example that came up on AskTOM recently, hence the click bait title. I’ll start with 100 copies of DBA_OBJECTS to be my source table.
SQL> create table t pctfree 0
2 as select owner, object_id, subobject_name
3 from dba_objects,
4 ( select 1 from dual connect by level <= 100 );
Table created.
SQL>
SQL> exec dbms_stats.gather_table_stats('','t');
PL/SQL procedure successfully completed.
We’ll connect to SQL Plus and just run a simple SELECT to retrieve all of the rows.
SQL> set autotrace traceonly statistics
SQL> select * from t;
8350600 rows selected.
Statistics
------------------------------------------------------
13 recursive calls
17 db block gets
849818 consistent gets
1 physical reads
3024 redo size
294152578 bytes sent via SQL*Net to client
9186257 bytes received via SQL*Net from client
835061 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
8350600 rows processed
Wow…that’s a lot of consistent gets. Here’s my simple solution to reduce that. We’ll just an ORDER BY clause!
SQL> select * from t order by object_id;
8350600 rows selected.
Statistics
-------------------------------------------------------
170 recursive calls
4 db block gets
16469 consistent gets
21597 physical reads
0 redo size
201757283 bytes sent via SQL*Net to client
9186257 bytes received via SQL*Net from client
835061 SQL*Net roundtrips to/from client
0 sorts (memory)
1 sorts (disk)
8350600 rows processed
Woo hoo! Look how much I’ve dropped the consistent gets. Time for me to scream from the roof tops “I am a SQL tuning genius!”
In reality, I don’t need an ORDER BY to improve the “consistent gets” number from the SELECT statement. One of the governing factors for how many consistent gets we will need to do is the fetch size, and my original execution was performed with a fetch size (arraysize in SQL Plus) of just 10. Let’s bump that up and re-run the query:
SQL> set arraysize 1000
SQL> select * from t;
8350600 rows selected.
Statistics
------------------------------------------------------
0 recursive calls
0 db block gets
24805 consistent gets
0 physical reads
0 redo size
133771032 bytes sent via SQL*Net to client
92458 bytes received via SQL*Net from client
8352 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
8350600 rows processed
Voila! An easy “fix” to consistent gets. In reality, don’t forget that consistent gets is just one piece of the performance performance puzzle. If I throw in some response time metrics for the above queries, then (as an application user) I know which query I would prefer to have running!
SQL> set autotrace off
SQL> set timing on
SQL> set feedback only
SQL> select * from t;
8350600 rows selected.
Elapsed: 00:00:02.08
SQL> select * from t order by object_id;
8350600 rows selected.
Elapsed: 00:00:09.70
SQL>
So, click bait titles aside, make sure you don’t get too carried away just focussing on particular metrics (eg consistent gets) when assessing and tuning the performance of your SQL statements.
