Friday, February 9, 2018

Using SQL Developer to optimizer your queries in the cloud or not.



At Hotsos we are all about performance.  And at the heart most performance problems is a select  statement more often than not.   In the past my go-to tool to do performance optimization and training has been SQL*Plus.  Of late I’m moving more and more to SQL Developer. Because it has changed from a tool that was so-so several years ago to a powerhouse of features and functionality. 


In this short bit I just want to talk about the autotrace feature in particular and how to use it for performance optimization of a select.   Some key things to focus on when optimizing a query is logical IOs (LIOs), Starts and of course time.  And pretty much in that order.  Time is of course the goal, make it run fast. How to make it faster? Make sure it does less work.  How do you measure work of a query?  LIOs.   Now I take a short look at how to use autotrace to do this.   I’m using the 17.4 drop of SQL Developer.

This is a query that returns some customer information and the last date they placed an order. The code looks like this:



Just a quick tip, avoiding distinct when not needed. It does invoke extra work so make sure it’s really necessary, here the code needs it because a customer could place more than one order per day.  Using autotrace in SQL Developer we can see this for the plan:



The column headings that are cut off are: LAST_STARTS, LAST_OUTPUT_ROWS, CARDINALITY, LAST_CR_BUFFER_GETS, LAST_ELAPSED_TIME and QBLOCK_NAME.  With these columns you have really good idea what is happening with your query. 


Looking the top line you can see the query did 267,478 LIOs (LAST_CR_BUFFER_GETS) to get the result set of 1000 rows (LAST_OUTPUT_ROWS).  That’s and average of 267 LIOs per row returned.  That seems high.  Maybe it’s OK, but one other thing gives me a strong hint that isn’t ideal.  Notice that the query block named MAX_BLOCK is executing 1,310 times.  And this repeated execution is driving the vast majority of the LIOs.  This part of the plan also takes the longest which is often true, but not always.

Rewriting the code to use an analytic to find the max date would look like this:




Using autotrace on this code, this is the plan and statistics:




Wow!  That’s great.  Notice the starts is 1 all the way up and down the plan, the total LIOs is down to an almost unbelievable 238 from 267,478.  And the time of course dropped to .22 seconds from 1.7 seconds. 


A nice thing to do in SQL Developer is put both code blocks in one editor window.  Then do autotrace on both and it’s easy to compare the runs.   Here I’ve done that and after each autotrace finished I did a right click on the tab and renamed each.  Then do a right click on either one lets you compare it to the other (I right clicked on SubQ to get what is shown below).


Below the plan is a boat load of stats collect by the autotrace and doing the compare you can see how these have changed.  Ones that are different are in red, and nearly all of them are in red here. (There are more below this screen shot and some are the same.)  The consistent gets (yet another name for LIOs) dropped way way down.




SQL Developer doesn’t really do anything that you can’t do in something like SQL*Plus.  For example at Hotsos we have a free tool called the Harness that collects all this same information in SQL*Plus.  SQL Developer pulls all this information together for you in a very easy to navigate presentation. This makes it a snap to read and analyze the problem which then helps you make the right decisions. If you're not using SQL Developer you really need to start. 

Also for great info on installing, configuring and using SQL Developer, hop over to Jeff Smith's Blog.  He's the main man for SQL Developer, he with his outstanding team have made it a powerful and indispensable part of SQL development. 

Monday, January 22, 2018

Does performance optimization still matter in the cloud or not?



Computers get faster, massive parallel processing is now standard, spinning disks are disappearing, and gigabit networks are common.  So with all this improvement in hardware speed do we need to optimizer our code anymore?  

If you’ve been in the industry for a while you’ve seen this pattern before.  Hardware gets faster and cheaper and what was a perceived problem before now seems to be solved with faster better machines.  And that is true, if we stayed with the same software and data load of times gone by.  But we don’t.

A good example of this is just data volume.  Many years ago, a 20Megabyte hard drive was considered big.  (My first hard drive was a big as a bread box and was 5 Megabytes, yes Five.)   Newer faster smaller drives came out.  Then it was easy to store 100s of Megabytes, then Gigabytes and now Terabytes are common place. What did this do? In the past a company might only store a year or so of data online as they couldn’t’ afford the storage for more.  Now they can easily store 10 or more years of data.

What did this do? It demanded more of the computer to process all that data, faster CPUs and more of them were needed.  And code had to be more efficient.  In the Oracle world I work in this drove for different ways to store data, partitioning for example.  But the code also needed to access the data the right way.  The right index and predicate could mean the difference between get a response in a reasonable amount of time, or never having it complete.

Today were seeing this all over again.  We have “big data” now, and we want to process this data in a blink of an eye to slice and dice sales data to get that marketing edge on the competition.  Or make that decision on what to invest in, or a thousand other questions.  We will continue to ask for more and want it faster from our data as we accumulate more data.

All the things I mentioned at the start give us even the chance to make this happen.  What it doesn’t mean is that we can write sloppy code and just hope that the hardware will be fast enough to make up for our suboptimal code.

It’s not good enough that code get the right results; it still has to do that efficiently.  The right indexes still matter, the right predicates still matter and the right statistics for the optimizer still matter.  In Oracle land the database engine can do a lot of amazing things.  But it still isn’t as good as we are, we can write better code and we all should strive for that in every piece of code.

Monday, December 4, 2017

Starts and subqueries in the cloud or not.

When doing some work the more efficiently we do it the better.  This is true in real life and with our queries we write in SQL.  Imagine you had a landscape supply company deliver a load of bricks you will use for a patio.  The problem is they are sitting in your drive way and the patio is in the back yard.  You could move them one at a time from the driveway to the back of the house.  Or you could get a wheel barrel and move them several at a time.  Which way do you think you’ll move the bricks faster?

The same can happen with our queries.  And the STARTS column in the run time stats of the plan can help.  You will see this stat using the format option IOSTATS or ALLSTATS.  You can also see it in the STAT lines starting in 12.2 in the 10046 trace files.

You need information from a table within the query.  Certainly a natural way to do this is to write a subquery to get it.  For example I want a report of customers, the number of orders and the average order amount.  I could write it like this (the tables I’m using here are nonsensical but do the same thing):

select /*+ qb_name(mainblk) gather_plan_statistics */ a.username customer_name,
 (select /*+ qb_name(cntblk) */ nvl(count(*),0) from scalar_allobjects b where b.owner = a.username) cnt_orders,
 (select /*+ qb_name(avgblk) */ nvl(round(avg(object_id),2),0) from scalar_allobjects b where b.owner = a.username) avg_order_amt
from scalar_allusers a
order by a.username
/

When it runs, the run time stats look like this, notice the STARTS column in particular.

--------------------------------------------------------------------------------------------------
| Id  | Operation          | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                   |      1 |        |     48 |00:00:00.55 |     128K|
|   1 |  SORT AGGREGATE    |                   |     48 |      1 |     48 |00:00:00.27 |   64320 |
|*  2 |   TABLE ACCESS FULL| SCALAR_ALLOBJECTS |     48 |   2167 |  63383 |00:00:00.28 |   64320 |
|   3 |  SORT AGGREGATE    |                   |     48 |      1 |     48 |00:00:00.28 |   64320 |
|*  4 |   TABLE ACCESS FULL| SCALAR_ALLOBJECTS |     48 |   2167 |  63383 |00:00:00.30 |   64320 |
|   5 |  SORT ORDER BY     |                   |      1 |     48 |     48 |00:00:00.55 |     128K|
|   6 |   TABLE ACCESS FULL| SCALAR_ALLUSERS   |      1 |     48 |     48 |00:00:00.01 |       9 |
--------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - CNTBLK
   2 - CNTBLK  / B@CNTBLK
   3 - AVGBLK
   4 - AVGBLK  / B@AVGBLK
   5 - MAINBLK
   6 - MAINBLK / A@MAINBLK

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("B"."OWNER"=:B1)
   4 - filter("B"."OWNER"=:B1)

So it’s doing some 48 full table scans for each sub query, on the same table.  That’s 96 full table scans on that table alone.  An index might help, but really the problem is that the subqueries are running way to many times. How about a join of the two tables instead? It has to be an outer join, because there are some customers who have not placed any orders yet so they wouldn’t show up at all with an inner join.  Also we can’t do a COUNT(*) because that counts the occurrence of a row, and with an outer join we’d get a row even where there isn’t a match.  So the query would look like this:

select /*+ qb_name(mainblk) gather_plan_statistics */ a.username customer_name,
 nvl(count(b.owner),0) cnt_orders,
 nvl(round(avg(b.object_id),2),0) avg_order_amt
from scalar_allusers a, scalar_allobjects b
where b.owner(+) = a.username
group by a.username
order by a.username
/

When this on runs its stats look like this:

---------------------------------------------------------------------------------------------------
| Id  | Operation           | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                   |      1 |        |     48 |00:00:00.02 |    1349 |
|   1 |  SORT GROUP BY      |                   |      1 |     35 |     48 |00:00:00.02 |    1349 |
|*  2 |   HASH JOIN OUTER   |                   |      1 |  75842 |  63397 |00:00:00.01 |    1349 |
|   3 |    TABLE ACCESS FULL| SCALAR_ALLUSERS   |      1 |     48 |     48 |00:00:00.01 |       9 |
|   4 |    TABLE ACCESS FULL| SCALAR_ALLOBJECTS |      1 |  75829 |  75829 |00:00:00.01 |    1340 |
---------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - MAINBLK
   3 - MAINBLK / A@MAINBLK
   4 - MAINBLK / B@MAINBLK

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("B"."OWNER"="A"."USERNAME")

Now there is only one full table scan on each table.  Notice the starts column this time.  Also it does a significantly lower amount of LIOs (the buffers column), the first one doing over 128,000 and the second one at 1,349.  And to boot, it’s over ten times faster, no surprise when it’s doing about 1% of the LIOs.  I did run this test many times and it was always over ten times faster. 
 

The STARTS column is a great way to see if there is some part of the plan that is running excessively.  If you see this there are 3 basic attack patterns: turn it into a join (as done here), use the WITH clause and turn it into a subquery factor and join that into the main query, or put it into the FROM clause as an inline view which is joined to the other tables.  


All these have the same net effect, join the table into the query don’t select from it as a subquery that is run repeatedly.   More often than not this is going to do less work and hence will be faster. 


Here is the full script I used for this example:

set serveroutput off
column customer_name format a30
column cnt_orders    format 99,999,999
column avg_order_amt format 99,999,999.99

set termout off
select /*+ qb_name(mainblk) gather_plan_statistics */ a.username customer_name,
 (select /*+ qb_name(cntblk) */ nvl(count(*),0) from scalar_allobjects b where b.owner = a.username) cnt_orders,
 (select /*+ qb_name(avgblk) */ nvl(round(avg(object_id),2),0) from scalar_allobjects b where b.owner = a.username) avg_order_amt
from scalar_allusers a
order by a.username
/
set termout on

select * from table(dbms_xplan.display_cursor (format=>'iostats last alias'))
/

set termout off
select /*+ qb_name(mainblk) gather_plan_statistics */ a.username customer_name,
 nvl(count(b.owner),0) cnt_orders,
 nvl(round(avg(b.object_id),2),0) avg_order_amt
from scalar_allusers a, scalar_allobjects b
where b.owner(+) = a.username
group by a.username
order by a.username
/
set termout on

select * from table(dbms_xplan.display_cursor (format=>'iostats last alias'))
/

Creating the two tables:

create table scalar_allobjects as select * from all_objects;

alter table scalar_allobjects add (constraint allobjects_pkey primary key (object_id));

create table scalar_allusers as select * from all_users;

alter table scalar_allusers add (constraint allusers_pk primary key (user_id));

exec dbms_stats.gather_table_stats (user, 'scalar_allobjects', method_opt => 'for all columns size 1', -
     estimate_percent => 100, cascade => TRUE, no_invalidate => FALSE) ;
    
exec dbms_stats.gather_table_stats (user, 'scalar_allusers', method_opt => 'for all columns size 1', -
     estimate_percent => 100, cascade => TRUE, no_invalidate => FALSE) ;