Opps.... "Working with SQL Plan Directives" was deleted. It will be back soon.

OTech Magazine issue Spring 2015

Hey!  I'm in Print!  Well in the internet age kind of "print".  Check out the latest issue of the OTech Magazine. 


Click Here or on the image -->

My article is "Know your Histograms".  Lots of other great articles in here too.

Enjoy!

Which indexes are NOT being used? UPDATED!

UPDATE!  Ok I really shouldn't write posts when I'm sick... The query was a not correct.  Thanks to Mikhail Velikikh  for pointing this it out.

Correct query is:

SELECT INDEX_NAME, INDEX_TYPE, TABLE_NAME

 FROM DBA_INDEXES

 WHERE OWNER = 'OP' AND INDEX_TYPE != 'LOB'

 and (owner, index_name) not in (

 SELECT object_owner, OBJECT_NAME

 FROM DBA_HIST_SQL_PLAN

 WHERE OBJECT_OWNER = 'OP' AND OPERATION = 'INDEX')

/

 

Have a great day!  

There is a lot of chatter about indexes and folks are realizing that more indexes isn’t better and too many can be bad.  The impact on DML is real.  The more indexes the more maintenance there is for all INSERTS and DELETES and maybe the UPDATES too (just depends on what you update of course).   Also more indexes can even cause the optimizer to slow down as it considers indexes that it ends up not using for selects.

I had a post a while back to show which indexes are being used (http://ricramblings.blogspot.com/2012/11/is-this-index-being-used.html).  Which certainly is nice to know but what about a system with lots of indexes out there, which ones aren’t being used?  It’s likely faster to zero in on those unused ones and figure out if you should keep it or not.  

The index monitoring is an option and it might give you an idea of which ones aren’t, however it’s not a great tool really and unfortunately the results aren’t reliable. (If you’d like, check out Richard Foote’s blog (https://richardfoote.wordpress.com/) and search for “Index Monitoring”, he has several posts talking about its limitations.)  Really what you want to know is “Which indexes are not being used in my SQL?”

For this the best approach is to use DBA_HIST_SQL_PLAN.  However you must be warned that this view is part of the Oracle Diagnostic Pack.  If you’re not licensed for this you cannot use this view.  Most large shops are, so this shouldn’t be an issue.  Assuming you have the Diagnostic Pack, here is a simple query that will tell you which indexes haven’t been used in SQL for a given schema for as long as you have history in the view:

SELECT INDEX_NAME, INDEX_TYPE, TABLE_NAME FROM DBA_INDEXES

WHERE OWNER = 'OP' AND INDEX_TYPE != 'LOB'

MINUS

SELECT OBJECT_NAME, NULL, NULL FROM DBA_HIST_SQL_PLAN

WHERE OBJECT_OWNER = 'OP' AND OPERATION = 'INDEX'

That’s it.  I’ve excluded LOB indexes since they are mostly used internally and dropping those would be a very bad thing, but otherwise this will show you all the indexes that haven’t been used in a plan that is in the plan history. 

If you don’t have the Diagnostic Pack, you could change this to:

SELECT INDEX_NAME, INDEX_TYPE, TABLE_NAME FROM DBA_INDEXES

WHERE OWNER = 'OP' AND INDEX_TYPE != 'LOB'

MINUS

SELECT OBJECT_NAME, NULL, NULL FROM V$SQL_PLAN

WHERE OBJECT_OWNER = 'OP' AND OPERATION = 'INDEX'

The only issue here is that this only shows plans that are currently in the shared pool, so you might have used an index in some query that has aged out for example.  But even this should give you a decent list of indexes worth investigation.  

Once you find some indexes that appear to not be used, now what?  Well if you’re a cowboy you can just drop ‘em and see what happens!  Of course that might not really be a great idea, so this is a case where making the index INVISIBLE for a while might be good, then after a few weeks (or months) drop them.

Are you using the QB_NAME hint? You should be!

In Oracle land each time the optimizer sees the work SELECT that is a new query block within the query.  Each query block gets a name even when you don’t name them.  But they are typical system type generated names; they aren’t very informative by themselves.  They get names like SEL$1 and SEL$2 and so on.    But we can them is such a way that they do make sense to us humans.

It’s really a good idea to do this.  These names for the query blocks are also used in the 10053 trace files.  And again it can just be easier to read things if you name them in more human understandable ways.  Here’s a quick couple of examples to show this hint in action.

select /*+ QB_NAME(mainblock) */ a.username,

  (select /*+ QB_NAME(cntblock) */  count(*)

   from scalar_allobjects b where b.owner = a.username) cnt

 from scalar_allusers a;

This is a simple enough query to demonstrate the point.  Note there are two query blocks.  The main query and then a sub query to get a count.  So I named them MAINBLOCK and CNTBLOCK.  Pretty tricky eh?

Now when I run the query and use DBMS_XPLAN.DISPLAY_CURSOR to see the plan I can use the ALIAS format option to see where in the plan the query blocks are getting evaluated.

SQL> SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR

  2  ('g7pt8bj8jjy8r',0,'ALLSTATS LAST ALIAS'))

  3  /

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------

SQL_ID  g7pt8bj8jjy8r, child number 0

-------------------------------------

select /*+ QB_NAME(mainblock) */ a.username,   (select /*+

QB_NAME(cntblock) */  count(*)    from scalar_allobjects b where

b.owner = a.username) cnt  from scalar_allusers a

Plan hash value: 2801865168

--------------------------------------------------------------------------------------------------

| Id  | Operation          | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers |

--------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |                   |      1 |        |     43 |00:00:00.01 |       6 |

|   1 |  SORT AGGREGATE    |                   |     43 |      1 |     43 |00:00:00.26 |   65059 |

|*  2 |   TABLE ACCESS FULL| SCALAR_ALLOBJECTS |     43 |   2805 |  52617 |00:00:00.25 |   65059 |

|   3 |  TABLE ACCESS FULL | SCALAR_ALLUSERS   |      1 |     43 |     43 |00:00:00.01 |       6 |

--------------------------------------------------------------------------------------------------

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

-------------------------------------------------------------

   1 - CNTBLOCK

   2 - CNTBLOCK  / B@CNTBLOCK

   3 - MAINBLOCK / A@MAINBLOCK

Predicate Information (identified by operation id):

---------------------------------------------------

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

29 rows selected.

Of course nothing is perfect.  If you query block gets merged into another one, then some names get lost.  Rats!

In this query there are several query blocks, but because the query block ORDERED_STDS gets merged into MAIN we lose the MAIN name and it gets a new name SEL$DDAF0A40. Even with this, it’s still easier to see which lines of the plan go to which subqueries.  Also this little example uses the good old explain plan to show they are used with explain plan as well.

SQL> explain plan set statement_id = 'EX2_2_1' for

  2        select /*+ QB_NAME(MAIN) */ student_id, last_name

  3         from (select /*+ QB_NAME (ORDERED_STDS) */  * from student order by student_id) s

  4         where (select /*+ QB_NAME(ENROLL_CNT) */ count(*)

  5               from enrollment e

  6               where s.student_id = e.student_id) >

  7                   (select /*+ QB_NAME(ENROLL_AVG) */ avg(count(*))

  8                      from enrollment

  9                      group by student_id);

Explained.

SQL>

SQL> SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE','EX2_2_1','TYPICAL ALIAS'));

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------------

Plan hash value: 3191484131

---------------------------------------------------------------------------------------

| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |         |   268 |  3216 |     3   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| STUDENT |   268 |  3216 |     2   (0)| 00:00:01 |

|*  2 |   INDEX FULL SCAN           | STU_PK  |    13 |       |     1   (0)| 00:00:01 |

|   3 |    SORT AGGREGATE           |         |     1 |     4 |            |          |

|*  4 |     INDEX RANGE SCAN        | ENR_PK  |     1 |     4 |     1   (0)| 00:00:01 |

|   5 |    SORT AGGREGATE           |         |     1 |     4 |     1   (0)| 00:00:01 |

|   6 |     SORT GROUP BY           |         |     1 |     4 |     1   (0)| 00:00:01 |

|   7 |      INDEX FULL SCAN        | ENR_PK  |   226 |   904 |     1   (0)| 00:00:01 |

---------------------------------------------------------------------------------------

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

-------------------------------------------------------------

   1 - SEL$DDAF0A40 / STUDENT@ORDERED_STDS

   2 - SEL$DDAF0A40 / STUDENT@ORDERED_STDS

   3 - ENROLL_CNT

   4 - ENROLL_CNT   / E@ENROLL_CNT

   5 - ENROLL_AVG

   7 - ENROLL_AVG   / ENROLLMENT@ENROLL_AVG

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - filter( (SELECT /*+ QB_NAME ("ENROLL_CNT") */ COUNT(*) FROM

              "ENROLLMENT" "E" WHERE "E"."STUDENT_ID"=:B1)> (SELECT /*+ QB_NAME

              ("ENROLL_AVG") */ AVG(COUNT(*)) FROM "ENROLLMENT" "ENROLLMENT" GROUP BY

              "STUDENT_ID"))

   4 - access("E"."STUDENT_ID"=:B1)

33 rows selected.

SQL>

Hotsos Symposium Speakers and Topics 2015

WOOT-WOOT!  

The selections have been made!  Check out the list and start drawing up your strategy to get the most out of the Symposium. Another great one!!

Who's your favorite? 

http://www.hotsos.com/sym15/sym_speakers.html

Selecting one row

If the table is small do I really need an index to select just one row?

Yes you do.  Here is a simple test you can use yourself to prove this to anyone one who asks.

If I have a small table with say 100 rows that all fit in a single block, it seems reasonable that a full table scan to find the one row is just fine.  After all it’s just one block so what does it matter?  Here is a simple table and some code to fill it with just 100 rows, and they all fit in one 8K block.

CREATE TABLE ONEHUNDREDROWS (ID NUMBER, FILLER VARCHAR2(2))

/

BEGIN

  FOR X IN 1..100 LOOP

      INSERT INTO ONEHUNDREDROWS VALUES (X,'AA');

  END LOOP;

END;

/

Once the table is created you can run this to see that all the rows are in one block, this returns the distinct block numbers in all the ROWIDs for the table:

SQL> SELECT distinct dbms_rowid.rowid_block_number(ROWID, 'BIGFILE') BLOCK from onehundredrows;

          BLOCK

---------------

       26209107

Now here is a block of code to select one row from this table, 5000 times.  The block will time how much elapsed and CPU time are consumed when doing this.   The select used after running the block will show use the resources used.  Of particular interest to us is the BUFFERs column, this shows how many LIOs were done.  Make sure STATISTICS_LEVEL is set to all. 

ALTER SESSION SET STATISTICS_LEVEL=ALL

/

create or replace procedure one_row_test is

   l_start_time pls_integer;

   l_start_cpu  pls_integer;

   y varchar2(2);

begin

   l_start_time := DBMS_UTILITY.GET_TIME;

   l_start_cpu  := DBMS_UTILITY.GET_CPU_TIME;

   for i in 1 .. 5000 loop

      select filler into y from onehundredrows where id = 42;

   end loop;

   DBMS_OUTPUT.put_line ('******* Select one row 5000 times *******');

   DBMS_OUTPUT.put_line ('Times in hundredths of a second');

   DBMS_OUTPUT.put_line ('**** TIME   - '||to_char(DBMS_UTILITY.get_time - l_start_time));

   DBMS_OUTPUT.put_line ('**** CPU    - '||to_char(DBMS_UTILITY.GET_CPU_TIME - l_start_cpu));

end;

/

EXEC ONE_ROW_TEST

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('9mxb9qk546vu6',NULL,'ALLSTATS LAST'))

/

Here is a run doing a full table scan:

SQL> EXEC ONE_ROW_TEST

******* Select one row 5000 times *******

Times in hundredths of a second

**** TIME   - 26

**** CPU    - 25

SQL>

SQL>

SQL> SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('9mxb9qk546vu6',NULL,'ALLSTATS LAST'))

  2  /

PLAN_TABLE_OUTPUT

---------------------------------------------------------------------------------------------------------------------------------

SQL_ID  9mxb9qk546vu6, child number 0

-------------------------------------

SELECT FILLER FROM ONEHUNDREDROWS WHERE ID = 42

Plan hash value: 35615928

----------------------------------------------------------------------------------------------

| Id  | Operation         | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |

----------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |                |      1 |        |      1 |00:00:00.01 |       7 |

|*  1 |  TABLE ACCESS FULL| ONEHUNDREDROWS |      1 |      1 |      1 |00:00:00.01 |       7 |

----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("ID"=42)

Note

-----

   - dynamic statistics used: dynamic sampling (level=2)

Notice that the buffers is 7 for the full scan even though all 100 rows fit in one block, the scan has to go all the way to the high water mark of the table.  

Now let’s run the same thing but this time there will be an index on the ID column:

SQL> create index one_id on onehundredrows(id)

  2  /

SQL>

SQL>

SQL> EXEC ONE_ROW_TEST

******* Select one row 5000 times *******

Times in hundredths of a second

**** TIME   - 22

**** CPU    - 22

SQL>

SQL>

SQL> SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('9mxb9qk546vu6',NULL,'ALLSTATS LAST'))

  2  /

PLAN_TABLE_OUTPUT

---------------------------------------------------------------------------------------------------------------------------------

SQL_ID  9mxb9qk546vu6, child number 0

-------------------------------------

SELECT FILLER FROM ONEHUNDREDROWS WHERE ID = 42

Plan hash value: 3262481358

--------------------------------------------------------------------------------------------------------

| Id  | Operation                   | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |

--------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |                |      1 |        |      1 |00:00:00.01 |       2 |

|   1 |  TABLE ACCESS BY INDEX ROWID| ONEHUNDREDROWS |      1 |      1 |      1 |00:00:00.01 |       2 |

|*  2 |   INDEX RANGE SCAN          | ONE_ID         |      1 |      1 |      1 |00:00:00.01 |       1 |

--------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("ID"=42)

Note

-----

   - dynamic statistics used: dynamic sampling (level=2)

The LIOs for this one is 2.  You may say that what is the big deal from 7 to 2?  True enough on a one run bases, this isn’t much to worry about.  But imagine doing this millions of times.  Now that extra 5 LIOs start to add up.

Also notice that in just 5000 look ups the time goes from 22 to 26 centiseconds, about an 18% increase.  Again doesn’t seem like much but it adds up.  Also what happens if this table does grow over time?  The full scan will continue to look at more and more blocks taking more and more time and resources.  The index scan will likely stay about the same for a long time before it starts to change.  The table could easily be about 10 times the size and the index would still be about the same for LIOs and time.

If the index on ID is unique that the time drops a bit more down to 20 centiseconds, so yes it’s even better to have a unique index just to select a single row from a small table.

SQL> drop index one_id

  2  /

SQL> create unique index one_id on onehundredrows(id)

  2  /

SQL> EXEC ONE_ROW_TEST

******* Select one row 5000 times *******

Times in hundredths of a second

**** TIME   - 20

**** CPU    - 20

SQL> SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('9mxb9qk546vu6',NULL,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------

SQL_ID  9mxb9qk546vu6, child number 0

-------------------------------------

SELECT FILLER FROM ONEHUNDREDROWS WHERE ID = 42

Plan hash value: 3462298723

--------------------------------------------------------------------------------------------------------

| Id  | Operation                   | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |

--------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |                |      1 |        |      1 |00:00:00.01 |       2 |

|   1 |  TABLE ACCESS BY INDEX ROWID| ONEHUNDREDROWS |      1 |      1 |      1 |00:00:00.01 |       2 |

|*  2 |   INDEX UNIQUE SCAN         | ONE_ID         |      1 |      1 |      1 |00:00:00.01 |       1 |

--------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("ID"=42)