AskTOM TV episode 8

On AskTOM episode 8, I’ve taken a look at locating the SQL Plan Directives used for a particular query.  Here is the script output from the video if you want to use this for your own exploration


SQL>
SQL> create table t as
  2  select *
  3  from dba_objects
  4  where owner = 'SYS' and rownum <= 20
  5  union all
  6  select *
  7  from dba_objects
  8  where owner = 'SYSTEM'
  9  and rownum <= 200;

Table created.

SQL>
SQL> create index ix on t ( owner);

Index created.

SQL>
SQL> select COLUMN_NAME,NUM_DISTINCT,AVG_COL_LEN, num_nulls, density
  2  from   user_tab_cols
  3  where  table_name = 'T'
  4  order by COLUMN_ID;

COLUMN_NAME                    NUM_DISTINCT AVG_COL_LEN  NUM_NULLS    DENSITY
------------------------------ ------------ ----------- ---------- ----------
OWNER                                     2           7          0         .5
OBJECT_NAME                             199          18          0 .005025126
SUBOBJECT_NAME                            1           3        199          1
OBJECT_ID                               220           4          0 .004545455
DATA_OBJECT_ID                          167           4         49 .005988024
OBJECT_TYPE                               9           8          0 .111111111
CREATED                                  12           8          0 .083333333
LAST_DDL_TIME                            16           8          0      .0625
TIMESTAMP                                13          20          0 .076923077
STATUS                                    1           6          0          1
TEMPORARY                                 2           2          0         .5
GENERATED                                 2           2          0         .5
SECONDARY                                 1           2          0          1
NAMESPACE                                 4           3          0        .25
EDITION_NAME                              0           0        220          0
SHARING                                   2          10          0         .5
EDITIONABLE                               1           2        206          1
ORACLE_MAINTAINED                         1           2          0          1
APPLICATION                               1           2          0          1
DEFAULT_COLLATION                         1           7        136          1
DUPLICATED                                1           2          0          1
SHARDED                                   1           2          0          1
CREATED_APPID                             0           0        220          0
CREATED_VSNID                             0           0        220          0
MODIFIED_APPID                            0           0        220          0
MODIFIED_VSNID                            0           0        220          0

26 rows selected.

SQL>
SQL> insert into t  select * from dba_objects;

78329 rows created.

SQL> insert into t  select * from dba_objects;

78329 rows created.

SQL> insert into t  select * from dba_objects;

78329 rows created.

SQL> insert into t  select * from dba_objects;

78329 rows created.

SQL> commit;

Commit complete.

SQL>
SQL> select /*+ gather_plan_statistics */ count(created)
  2  from t
  3  where owner = 'SYS'
  4  and object_type = 'JAVA CLASS';

COUNT(CREATED)
--------------
        138424

1 row selected.

SQL>
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  3qyuxjtjy92m5, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(created) from t where owner
= 'SYS' and object_type = 'JAVA CLASS'

Plan hash value: 2143077847

-------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |      |      1 |        |      1 |00:00:00.08 |    5991 |
|   1 |  SORT AGGREGATE                      |      |      1 |      1 |      1 |00:00:00.08 |    5991 |
|*  2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T    |      1 |     12 |    138K|00:00:00.07 |    5991 |
|*  3 |    INDEX RANGE SCAN                  | IX   |      1 |    110 |    207K|00:00:00.03 |    1218 |
-------------------------------------------------------------------------------------------------------

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

   2 - filter("OBJECT_TYPE"='JAVA CLASS')
   3 - access("OWNER"='SYS')


22 rows selected.

SQL>
SQL> select sql_id, child_number,is_reoptimizable  from v$sql where sql_id = '3qyuxjtjy92m5';

SQL_ID        CHILD_NUMBER I
------------- ------------ -
3qyuxjtjy92m5            0 Y

1 row selected.

SQL>
SQL> exec dbms_spd.FLUSH_SQL_PLAN_DIRECTIVE

PL/SQL procedure successfully completed.

SQL>
SQL> select /*+ gather_plan_statistics */ count(created)
  2  from t
  3  where owner = 'SYS'
  4  and object_type = 'JAVA CLASS';

COUNT(CREATED)
--------------
        138424

1 row selected.

SQL>
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  3qyuxjtjy92m5, child number 1
-------------------------------------
select /*+ gather_plan_statistics */ count(created) from t where owner
= 'SYS' and object_type = 'JAVA CLASS'

Plan hash value: 2143077847

-------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |      |      1 |        |      1 |00:00:00.06 |    5383 |
|   1 |  SORT AGGREGATE                      |      |      1 |      1 |      1 |00:00:00.06 |    5383 |
|*  2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T    |      1 |    138K|    138K|00:00:00.05 |    5383 |
|*  3 |    INDEX RANGE SCAN                  | IX   |      1 |    220 |    207K|00:00:00.02 |     610 |
-------------------------------------------------------------------------------------------------------

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

   2 - filter("OBJECT_TYPE"='JAVA CLASS')
   3 - access("OWNER"='SYS')

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - statistics feedback used for this statement
   - performance feedback used for this statement
   - 1 Sql Plan Directive used for this statement


29 rows selected.

SQL>
SQL> select count(*) from dba_sql_plan_directives;

  COUNT(*)
----------
       354

1 row selected.

SQL>
SQL> set lines 60
SQL>
SQL> desc v$sql_plan
 Name                          Null?    Type
 ----------------------------- -------- --------------------
 ADDRESS                                RAW(8)
 HASH_VALUE                             NUMBER
 SQL_ID                                 VARCHAR2(13)
 PLAN_HASH_VALUE                        NUMBER
 FULL_PLAN_HASH_VALUE                   NUMBER
 CHILD_ADDRESS                          RAW(8)
 CHILD_NUMBER                           NUMBER
 TIMESTAMP                              DATE
 OPERATION                              VARCHAR2(30)
 OPTIONS                                VARCHAR2(30)
 OBJECT_NODE                            VARCHAR2(40)
 OBJECT#                                NUMBER
 OBJECT_OWNER                           VARCHAR2(128)
 OBJECT_NAME                            VARCHAR2(128)
 OBJECT_ALIAS                           VARCHAR2(261)
 OBJECT_TYPE                            VARCHAR2(20)
 OPTIMIZER                              VARCHAR2(20)
 ID                                     NUMBER
 PARENT_ID                              NUMBER
 DEPTH                                  NUMBER
 POSITION                               NUMBER
 SEARCH_COLUMNS                         NUMBER
 COST                                   NUMBER
 CARDINALITY                            NUMBER
 BYTES                                  NUMBER
 OTHER_TAG                              VARCHAR2(35)
 PARTITION_START                        VARCHAR2(64)
 PARTITION_STOP                         VARCHAR2(64)
 PARTITION_ID                           NUMBER
 OTHER                                  VARCHAR2(4000)
 DISTRIBUTION                           VARCHAR2(20)
 CPU_COST                               NUMBER
 IO_COST                                NUMBER
 TEMP_SPACE                             NUMBER
 ACCESS_PREDICATES                      VARCHAR2(4000)
 FILTER_PREDICATES                      VARCHAR2(4000)
 PROJECTION                             VARCHAR2(4000)
 TIME                                   NUMBER
 QBLOCK_NAME                            VARCHAR2(128)
 REMARKS                                VARCHAR2(4000)
 OTHER_XML                              CLOB
 CON_ID                                 NUMBER

SQL> set lines 200
SQL>
SQL>
SQL> select other from v$sql_plan
  2  where other is not null
  3  and sql_id = '3qyuxjtjy92m5'
  4  and child_number > 0;

no rows selected

SQL>
SQL> select remarks from v$sql_plan
  2  where remarks is not null
  3  and sql_id = '3qyuxjtjy92m5'
  4  and child_number > 0;

no rows selected

SQL>
SQL> select other_xml from v$sql_plan
  2  where other_xml is not null
  3  and sql_id = '3qyuxjtjy92m5'
  4  and child_number > 0;

OTHER_XML
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
<other_xml><info type="performance_feedback" note="y">yes</info><info type="cardinality_feedback" ...


1 row selected.

SQL>
SQL> select xmltype(other_xml) from v$sql_plan
  2  where other_xml is not null
  3  and sql_id = '3qyuxjtjy92m5'
  4  and child_number > 0;

XMLTYPE(OTHER_XML)
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
<other_xml>
  <info note="y" type="performance_feedback">yes</info>
  <info note="y" type="cardinality_feedback">yes</info>
  <info type="db_version">12.2.0.1</info>
  <info type="parse_schema"></info>
  <info note="y" type="dynamic_sampling">2</info>
  <info type="plan_hash_full">1068910003</info>
  <info type="plan_hash">2143077847</info>
  <info type="plan_hash_2">1068910003</info>
  <spd>
    <cv>0</cv>
    <cu>1</cu>
  </spd>
  <outline_data>
    <hint></hint>
    <hint></hint>
    <hint></hint>
    <hint></hint>
    <hint></hint>
    <hint></hint>
    <hint></hint>
  </outline_data>
</other_xml>


1 row selected.

SQL>
SQL> explain plan for
  2  select count(created)
  3  from t
  4  where owner = 'SYS'
  5  and object_type = 'JAVA CLASS';

Explained.

SQL>
SQL> select * from table(dbms_xplan.display(format=>'all'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2143077847

---------------------------------------------------------------------------------------------
| Id  | Operation                            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |      |     1 |    23 |     5   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE                      |      |     1 |    23 |            |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T    |   143K|  3226K|     5   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN                  | IX   |   220 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

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

   1 - SEL$1
   2 - SEL$1 / T@SEL$1
   3 - SEL$1 / T@SEL$1

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

   2 - filter("OBJECT_TYPE"='JAVA CLASS')
   3 - access("OWNER"='SYS')

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=0) COUNT("CREATED")[22]
   2 - "CREATED"[DATE,7]
   3 - "T".ROWID[ROWID,10]

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - 1 Sql Plan Directive used for this statement

35 rows selected.

SQL>
SQL> select * from table(dbms_xplan.display(format=>'all +metrics'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2143077847

---------------------------------------------------------------------------------------------
| Id  | Operation                            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |      |     1 |    23 |     5   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE                      |      |     1 |    23 |            |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T    |   143K|  3226K|     5   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN                  | IX   |   220 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

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

   1 - SEL$1
   2 - SEL$1 / T@SEL$1
   3 - SEL$1 / T@SEL$1

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

   2 - filter("OBJECT_TYPE"='JAVA CLASS')
   3 - access("OWNER"='SYS')

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=0) COUNT("CREATED")[22]
   2 - "CREATED"[DATE,7]
   3 - "T".ROWID[ROWID,10]

Sql Plan Directive information:
-------------------------------

  Used directive ids:
    14906410523430420431

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - 1 Sql Plan Directive used for this statement

41 rows selected.

SQL>
SQL>

Top Ten Travel hints and Tips

Well, let me be honest right at the top here.  These are not travel hints Smile  These will not help you in any way.

This is me having a whine and a rant about a minority of people that I occasionally encounter when travelling.

Yes, this can probably be best described as me and my first world problems, but I need to expunge these so that next time I travel, I don’t lose my head, and stuff some poor unsuspecting innocent passenger“under the seat in front of me or in the overhead locker” Smile

So sit back, relax and enjoy Connor’s “Travel Tips” Smile  (possibly NSFW)

1) Boarding pass

The term “boarding pass” stems from the Latin derivation: “The pass you need to present in order to board the bloody plane!”

So you know what ? At some stage, there is going to be a person who wants to see your boarding pass.  Incredible eh ?  And do you know where this happens ?  In every freakin’ airport !  We don’t need to suffer while you present a bemused expression to the security person asking for boarding pass, whilst you say “Oh….do I need my boarding pass ?  Let me hunt for it in the bottom of my bag for 15 minutes”.  On a recent flight, I even saw someone launch into a debate with the ground staff about why they have to present their pass!  Seriously ?  Were you trying out for the school debating team ?  Just keep it in your pocket or in your hand, and you’re done.  Easy ! 

2) Security check

There’s also going to be some people who want to X-ray your stuff.  Do you know where this happens ?  In every freakin’ airport !  And that huge placard that just about hit you on the head as you entered the security checkpoint said something along the lines of:

  • Take out your laptop
  • Empty your pockets

or we can take that down to real simple terms…. Metal and electrical stuff – bad.  Human body – good. Pretty….simple….concept.

But that’s ok, you can just ignore all that, because nothing makes a security officer feel more complete, than watching that bag of yours go though the X-ray machine 4 times, each time with one less electronic device in it, combined with your silly grin and shrug of the shoulders…. And they’ll ever happier when you follow that up with you carrying your wallet, phone, pocket knife, kitchen utensils, meccano set, your complete IKEA Applaro outdoor furniture setting, and a Milwaukee ride-on lawn mower, all stuffed in your jeans pockets, so that body scanner sounds more like Tchaikovsky’s Dance of the Sugar Plum fairy.

3) Boarding the plane

A few budget airlines have a policy where the seats are no allocated, you simply take what is available as you enter plane.  

But the huge majority of airlines offer an incredible, amazingly sophisticated service for you as a passenger.

It’s called …. AN ALLOCATED SEAT.

You’re going to get one.  Incredible isn’t it ?!?!  Whether you like it or not… there is a seat on that plane that is waiting for you… JUST FOR YOU!  It doesn’t matter if you are the first person on the plane, or the last person on the plane, you are going to get that seat.  Because we’ve all seen the unholy hell of a nightmare that results when a passenger does not arrive for their seat after they’ve checked in.  Staff run around in a total panic like headless chickens yelling out “MR SMITH !!! URGENTLY PAGING MR SMITH!!!!”.  Every airline employee involved with that airline’s imminent departure is thoroughly invested in getting you to the your seat so the plane can leave, and they can see the back of you and go grab a coffee.

Do you know what this means ?  It means that when the boarding announcement is made, you do not have to charge the gate like the Orc army in the Lord of the Rings.  Do you know why airlines board the plane in a particular order ?  So they get can the damn plane into the air and on it’s way! That is sortta a prerequisite of travelling by plane to a destination – at some stage the plane has to get into the air for this to work ! You are not trying to storm a battlefront, or escape a stampede of bison, or get a limited edition of the AskTom commemorative sticker Smile

So surprise surprise … if you wait for your boarding zone to be called, you will actually get to your destination faster.  And as a bonus, we can all get away faster.

4) Carry on

There is probably some unique set of circumstances out there, or some incredibly rare set of events put in motion, that means on this particular day, on this particular flight:

  • you are emigrating to another country never to return, AND
  • the aircraft is doing an emergency shipment of food to a stranded herd of Nepalese mountain goats, and hence the cargo hold is full of hay.

but unless both of those conditions are true, then sorry, you do not need to bring a metric tonne of belongings spread across 34 bags into the cabin.  If you don’t carry so much junk with you, you’re also less likely to needing riot gear to rush the door (see 3 above) worrying that you won’t be able to find storage space for those 34 bags you’re lugging.  And if you had listened to me in #1, you’d have your boarding pass in one hand, and only one hand left for carry-on luggage.  That’s ample!

5) Your seat

“Wow, I walked onto the plane, and every row number was just in a random order throughout the plane”

… said no passenger on any airline ever.

It’s pretty simple.  The numbers start low and get higher.  It’s a lot like …. hmm… what’s the term I’m looking for …. oh yeah, counting! Smile  Don’t get me wrong – we’ve all done the “walk mistake” or “sit mistake” where we end up 1 row adjacent to where we should have gone.  No problems with that – it’s easy to fix.  But how on earth did you get down to row 64 when your boarding pass said “Row 17”.  What happened in that long slow walk down the plane where you missed the numbers 18 through 63 ?  I reckon I know why you missed those numbers – you were looking for storage bins to put your 34 carryon bags, plus the IKEA Applaro outdoor furniture setting that’s in your back pocket (which would be uncomfortable to sit on for the flight) Smile

Now, if you’re going to take me to task on this one and tell me that things might be more complicated on an A380 because of it’s multi-deck system, then I’m still not budging.  See #3 above.  When the boarding call announcements are made, the ground staff will tell you which door to take to the plane.  But you might have missed that during your Ussain Bolt impersonation trying to be the first person on the plane Smile

(Caveat: If the standard decimal Hindu-Arabic number system is not native to your language…you get a leave pass for this one)

6) The loo (toilet, rest room)

There’s plenty of ridiculous research studies performed each year, so perhaps somewhere, in some remote corner of our planet, there is a study being conducted to see in how many ways in a confined space a male can pee and deliberately not hit the target.

An aircraft is not one of the places…. Ugh.

7) Your phone

I think I side with the majority here, in that I’m pretty confident that using mobile phone is not going to cause any problems to the plane.

But … unless I work as an electronics/avionics engineer for that airline I’m flying on, that is not my decision to make.  It’s not yours either.

So when the announcement to turn off or flight mode your phone is made – try this technique:  Stop using your damn phone.

Because even if there is only a 0.001% chance of your phone usage causing an issue on the plane, there is no way that you “Poking” or “Liking” your cousin’s new gluten-free sugar-free chocolate chip muffin recipe on Facebook even comes close to taking even that infinitesimal risk.  When they’re crawling through the wreckage of the plane to find your remains, that’s not so great a eulogy to have read out at your funeral: “Yes, John did bring the plane down with his phone by messing up its navigation, but at least he enjoyed cousin Susie’s Paleo muffins”

8) The seat belt sign

I’ve travelled a lot.  Like most people, I’m not a fan of turbulence, but there are some times when I am literally appealing to a higher power for sudden, unexpected, near catastrophic turbulence to hit the plane – even if we’re still on the ground!  It’s when we’re about to take off, or we’re about to land, because someone has decided it’s time to get out of their seat and and embark upon a voyage of exploration throughout the entire cabin.  You can see their mindset – “I paid for my ticket. Why should I have to abide by the instructions of the flight attendant?”. If they just could take their over-inflated sense of self-importance offline for a few minutes, they’d probably realise that that trip to the galley area to demand an apple juice, (because hey, apple juice is much more critical than the tasks that flight attendants are currently doing in preparation for take off) could probably wait for a few minutes until the seat belt sign is switched off.  Which it will be for 99% of the flight!

The other day, I was in a plane that was seriously 20 seconds from touching down, and some joker gets up and makes his way to the toilet.  They had been locked by the flight attendants because …. we’re busy landing the bloody plane!  But that’s lets face it, if a seat belt sign hasn’t deterred the passenger, then nothing as simple as a locked door is going to stop him either.  So he’s standing there trying to break down the door like a SWAT team doing a drug raid, whilst the poor flight attendant has to put her safety at risk to get this numpty to the sit the hell back down.
 
9) The baggage carousel

As well as SpaceX and cross-continental transport tunnels, Elon Musk has already invested billions of dollars into baggage carousels at airports. Baggage carousels are incredibly hi-tech pieces of equipment.  There is a series of highly sophisticated Bluetooth devices that scan every single passenger as they approach via the entrance hall – devices that measure each passenger’s exact distance from their shoes to the carousel down to the nearest millimetre.  These measurements are sent wirelessly to the luggage delivery staff who then proceed to deliver the suitcases in exactly the order of proximity of passengers to the carousel.  The closer you stand, the faster your bag will come out.

I don’t have any concrete evidence of Elon’s involvement in baggage carousels – but surely that must be the case, because what other reason could there be for people to jam themselves around the thing like sardines in a can, making it impossible for anyone to see or even pick up their bag. Oh, and here’s a great idea – grab that airport luggage trolley and jam it right up against the carousel as well.  Heaven knows, if you packed 34 items of carry-on, you probably have an aircraft carrier plus a 4-storey building jammed into your suitcase.  No way you’ll be able to lift that more than a couple of inches.

Pro tip:  Standing just 1 meter back from the carousel lets everyone easily see and grab their bags.  And there’s no Bluetooth either, or Elon Musk for that matter.  He’s got more important stuff to do than watch you make a fool of yourself around a baggage carousel. Smile

10 ) Your airline staff

This is perhaps the motivation for this entire post.  On a few flights recently, I’ve seen the flight attendants over stretched in their duties because they are spending so much time trying to get passengers to do what passengers are meant to be doing without explicit instruction.  Or even worse, people being just plain rude to them.  Man, that gets me wound up.  So wound up it makes me want to blog about it Smile

Whatever your profession, nothing is more insulting than someone coming up to you and basically announcing “Whatever task you are doing is not as important as you catering to my most trivial of needs right now”.  It’s disrespectful and demeaning.  At the end of my work day, I feel best when I look back at the day and think “I achieved a lot today”.  More and more nowadays, when I’m a plane sitting within eyeshot of a flight attendants, when the plane lands I don’t see a look of job satisfaction; I see “Thank heavens, these rude and ignorant people will be getting off this damn plane so I won’t have to deal with them again”.  That’s a really sad reflection on us as passengers.

So there’s my “travel tips” for you.  Happy flying Smile

DIY parallel task execution

We had a question on AskTOM recently, where a poster wanted to rebuild all of the indexes in his schema that had a status of UNUSABLE.  Running the rebuild’s in serial fashion (one after the other) seemed an inefficient use of the server horsepower, and rebuilding each index with a PARALLEL clause also was not particularly beneficial because the issue was more about the volume of indexes rather than the size of each index.

An obvious solution was to use the DBMS_PARALLEL_EXECUTE facility.  Our poster was pleased with our response but then came back asking for help, because they were stuck languishing on an old release for which DBMS_PARALLEL_EXECUTE was not yet present.

If you’re in this situation, it’s surprisingly easy to throw together your own rudimentary version using DBMS_JOB (or DBMS_SCHEDULER). Here’s a simple demo (using our poster’s original issue of rebuild indexes)

Step 1

First we’ll identify the list of operations needed.  In our case, that’s trivial – just a query to the dictionary.  But we will store that list in table because as we rebuild indexes, the list changes.  We want the initial static list of indexes so that we can spread this over a number of “worker” processes.


SQL> create table ind_rebuild as
  2  select
  3    rownum seq,
  4    owner,
  5    index_name,
  6    'NOT STARTED' job_status
  7  from
  8    dba_indexes
  9  where status = 'UNUSABLE'
 10  and ...

Table created.

SQL> create index ind_rebuild_ix on ind_rebuild ( owner, index_name );

Index created.

SQL> select * from ind_rebuild;

       SEQ OWNER                          INDEX_NAME                               JOB_STATUS
---------- ------------------------------ ---------------------------------------- -----------
         1 SCOTT                          EMP_PK                                   NOT STARTED
         2 SCOTT                          DEPT_PK                                  NOT STARTED
         3 MCDONAC                        SYS_C0013656                             NOT STARTED
         4 MCDONAC                        PK_CONTAINER                             NOT STARTED
         5 MCDONAC                        UN_CONTAINER                             NOT STARTED
         6 MCDONAC                        PK_ELEMENTS                              NOT STARTED
         7 MCDONAC                        UN_ELEMENTS                              NOT STARTED
...
...
...

Step 2

Now we’ll create a simple procedure that will perform the rebuild.  This procedure will be run from several worker sessions concurrently, so we need a way splitting the workload out.  For any sequenced list, MOD will do the job quite nicely.  In a real production scenario, you might have more sophisticated requirements (for example, you might have some sort of weighting system so that there is an estimate of how much effort each rebuild would need, and split rebuilds out accordingly in order for all processes to finish at approximately the same time).


SQL> create or replace
  2  procedure worker_bee(p_jobs pls_integer default 4, p_this_job pls_integer) is
  3    l_progress     pls_integer := 0;
  4    l_err          int;
  5  begin
  6    if p_this_job not between 0 and p_jobs-1 then
  7       raise_application_error(-20000,'0 and '||p_jobs||' for modulo');
  8    end if;
  9
 10    for i in ( select * from ind_rebuild )
 11    loop
 12        if mod(i.seq,p_jobs) = p_this_job and
 13           i.job_status != 'DONE'
 14        then
 15          l_progress := l_progress + 1;
 16          dbms_application_info.set_client_info('Task '||l_progress||','||i.owner||'.'||i.index_name);
 17          begin
 18            execute immediate 'alter index '||i.owner||'.'||i.index_name||' rebuild';
 19            update ind_rebuild
 20            set    job_status = 'DONE'
 21            where  owner = i.owner
 22            and    index_name = i.index_name;
 23          exception
 24            when others then
 25              l_err := sqlcode;
 26              update ind_rebuild
 27              set    job_status = to_char(l_err)
 28              where  owner = i.owner
 29              and    index_name = i.index_name;
 30          end;
 31          commit;
 32        end if;
 33    end loop;
 34  end;
 35  /

Procedure created.

Line 12 shows we pick up only those rows that should be picked up by this particular worker process (as nominated by parameter “p_this_job”).  This of course could have been in the WHERE clause itself, but I’ve taken this example from one where we did instrumentation for all rows, including those rows which were skipped as not being appropriate for a particular worker process.  As we rebuild each index, we pop a message into V$SESSION.CLIENT_INFO we can monitor activity, and if the index rebuild fails, we’ll store the sqlcode in the job status.  I’ve kept the logic pretty simple to assist with understanding the demo, but it would relatively straightforward to extend it to handle index partitions as well.

Step 3

Now it just a simple matter of submitting jobs up to the number of concurrent workers you want. 


SQL> variable j number
SQL> declare
  2    c_jobs pls_integer := 4;
  3  begin
  4    for i in 0 .. c_jobs-1
  5    loop
  6      dbms_job.submit(:j,'worker_bee('||c_jobs||','||i||');');
  7    end loop;
  8  end;
  9  /

PL/SQL procedure successfully completed.

SQL>
SQL> commit;

Commit complete.

Once we commit, the jobs will commence, and we can easily monitor the jobs either by polling our candidate indexes static table, or by monitoring the job queue itself.


SQL> select * from ind_rebuild;

       SEQ OWNER                          INDEX_NAME                               JOB_STATUS
---------- ------------------------------ ---------------------------------------- -----------
         1 SCOTT                          EMP_PK                                   DONE
         2 SCOTT                          DEPT_PK                                  NOT STARTED
         3 MCDONAC                        SYS_C0013656                             DONE
         4 MCDONAC                        PK_CONTAINER                             NOT STARTED
         5 MCDONAC                        UN_CONTAINER                             -1418
         6 MCDONAC                        PK_ELEMENTS                              DONE
         7 MCDONAC                        UN_ELEMENTS                              NOT STARTED
...
...
...

So if you are on a current release, DBMS_PARALLEL_EXECUTE can do this (and more) without much fuss, but even without that facility, using the job / scheduler queue to parallelise tasks is simple to do.

It’s just bad code or bad design … most of the time

Some years ago I wrote an article for the UKOUG magazine called “Want a faster database – Take a drive on the M25”.  For those not familiar with the United Kingdom, the M25 is one of its busiest roads (M = “motorway”) and because it moves so much traffic, and runs so close to capacity, it has often been referred to as “the world’s largest car park”.  Many people have probably spent a good part of their lives on the M25 Smile  I used the M25 as a metaphor for how database professionals can focus on the wrong things when trying to solve a performance problem, such as:

“I’m stuck in traffic…perhaps a faster car will help”

ie, throwing CPU at a problem that is not CPU bound will not help things, or

“I’m stuck in traffic…it must be the width of the paint on the lane markings”

ie, looking at the mutex hit rates is perhaps not the first place you should look at to solve a performance issue.

I concluded the article with the statement:

“…many Oracle professionals have forgotten the most important weapon in planning, designing, and implementing a successful Oracle database – that is, common sense.”

 

I wrote the article (I think) around 15 years ago, and yet our zeal for digging around metrics and internals when trying to solve performance issues before we examine the very basic components of a problem remain.

I saw a tweet recently that led to a thread on the database community forum recently that demonstrates this most effectively.  The were very early warning signs that the poster had jumped to the detail before looking holistically at the problem , simply due to the title of the post – “AWR Report High DB CPU Time and low SQL execute elapsed time”. 

Because that is not a problem. 

Namely, it is not a problem  in the sense that, I’ve never heard a customer call up a support department and say “I have some concerns over the ratio of DB CPU time to my SQL elapsed time” Smile  Customers simply say “My application is too slow”, or if that “customer” is the operations department, they will say “Our server is reaching its capacity limits” or similar.  In fact, it took 9 more posts before our poster finally described what the problem was: “We have a billing time approx. 20 hours currently. If we’ll reduce billing time to 8 hours, the problem will be solved”.  And when people start their description of an issue with the too low level of detail, it is like click-bait to the well intentioned people in the community that feel inspired to help.  What followed in the thread was a series of interactions along the lines of (paraphrased):

  • “What is the latch activity like?”
  • “What background waits are dominating?”
  • “What is the CPU consumption like?”
  • “What is the Oracle version to 4 decimal places?”
  • “How many cores?”
  • “Bare metal or virtualised”

And such detailed questions are then just catalysts for low level options being proposed as solutions.  In this case, there were discussions such as to whether the parameter filesystemio_options be altered, or whether some asynchronous I/O tunables should be examined.  And then discussions about whether the database needed to be patched or upgraded.  As I mentioned, it took some time before someone finally asked what the actual problem they were trying to solve was, and a full AWR report was made available.  Once the AWR report was visible, some people posted on the fairly obvious issue, an average over 25,000 calls to the database per second.  That might be reasonable depending on the size and activity on the database, but when combined with another statistic, namely 44,000 calls per transaction,  it starts to sing out as poor application design.  Because if the application was a busy but efficient OLTP system, we might see 25,000 calls per second, but small calls per transaction.  And if it was a busy but efficient batch system, we might see larger calls per transaction but unlikely to see so many calls per second.  Of course, maybe this system is both, but later in the AWR report we could see that a single SQL was executed over 75,000,000 times in the sample period so the evidence of poor design was mounting up fast.

Eventually some learned minds contributed to the thread pointing out the strong likelihood of an application doing row by row processing (or “slow by slow” as its often called) to achieve the requirements of the batch run.  It’s probably the most inefficient way of getting work done in an Oracle database.  The database is not the problem.  Now people may accuse me of bias on that statement (because I work for Oracle) but it is true.  The Oracle database software is astoundingly good at getting every last drop of performance out of a server.  And I’ve had that view for the 20 years I’ve been involved with the Oracle database software before I joined the company Smile Yes, there are some times when the core issue is due to a bug or defect in the product, but in the overwhelmingly majority of cases – if you write good code on top of a good design, the database layer will meet or exceed your most demanding of requirements.  Especially in these days where modern hardware is just so incredibly quick and scalable, both in the computing tier and storage tier, there are very few applications that will ever need any bespoke facilities over and above what comes with the delivered database product.

Similarly, I want to stress – I’m not trying to be critical of the the original poster of the thread, and the subsequent replies.  I’m just as guilty as anyone, in that it’s tremendously interesting to dive down into the nitty gritty, to swim in the mire of latches, buffers, mutexes, CPU cycles and wait events looking for some nugget of gold.  And I’m also not dismissing the importance of being knowledgeable in these detailed lower level areas of the database technology, because ultimately we may need to perform investigations at this level to solve some of the more niche problems we encounter in our jobs.  But it is a question of timing – these are not the first port of call.  We should always start with the simple:

  • What are you trying to achieve?
  • How are you trying to achieve it?
  • Are you doing so in a sensible way?
  • Do you even know the way in which you are trying to do it?

We didn’t need to look at the DB CPU to SQL elapsed ratio in this example.  We didn’t need to look at latches.  We just needed to consider the task that was being attempted (billing run, which by its very name sounds like a batch-style process) and then look at the how the task was being tackled, and 25,000+ user calls per second doesn’t sound “batch-like”.

So please, step back from the detail, at least initially. 

Index compression–quick tip

If you’re appropriately licensed and want to use advanced index compression, you can take advantage of the setting a tablespace to automatically add compression as a default at a nominated level in the database.  From the docs:

Here is an example of that in action.   We’ll set our tablespace default accordingly


SQL> create tablespace demo
  2  datafile 'C:\ORACLE\ORADATA\DB122\DEMO.DBF'
  3  size 100M
  4  default index compress advanced high;

Tablespace created.

Now we’ll create a table and an index on it in that tablespace


SQL> column index_name format a10
SQL> column tablespace_name format a15
SQL> create table t tablespace demo as
  2  select *
  3  from dba_objects;

Table created.

SQL> create index t_idx on t(owner) tablespace demo;

Index created.

SQL> select index_name,compression
  2  from user_indexes
  3  where table_name ='T';

INDEX_NAME COMPRESSION  
---------- ------------- 
T_IDX      ADVANCED HIGH   

1 row selected.

You can see that even though we did not specify any kind of compression for the index, it picked up the setting from the tablespace. Thus existing DDL scripts you have for indexes et al will not need to be changed.

Similarly, rebuilding the index does not lose the setting


SQL> alter index t_idx rebuild tablespace demo;

Index altered.

SQL> select index_name,compression
  2  from user_indexes
  3  where table_name ='T';

INDEX_NAME COMPRESSION   
---------- ------------- 
T_IDX      ADVANCED HIGH 

1 row selected.

You can see what the defaults are for a tablespace by querying DBA_TABLESPACES as per normal


SQL> select tablespace_name,compress_for,index_compress_for
  2  from user_tablespaces where tablespace_name = 'DEMO';

TABLESPACE_NAME COMPRESS_FOR                   INDEX_COMPRES
--------------- ------------------------------ -------------
DEMO                                           ADVANCED HIGH

Instrumentation … not just for debugging

Yeah I know.  You’re probably thinking “Here’s another blog post from someone telling us how important it is to instrument our code, so we can get better debugging, better performance, blah blah blah”.

If that is your sentiment, then I’m thrilled because it means the instrumentation message has got through to the extent that it is now considered just a statement of the obvious.  As many will know, my predecessor was a huge advocate of extensive instrumentation in your application code.  And there is no shortage of examples out there of other Oracle professionals who similarly wax lyrical on the incredible importance of well instrumented code.  For the record, I’m in that camp as well, and we now even have contributions to the Oracle community for everyone to have a robust instrumentation framework in their code.  So there is plenty of information out there, and it is pretty safe to say that there are no more excuses left for un-instrumented or poorly instrumented code.

So why the blog post ?  Well…I’ve got a slightly different assertion to make when it comes to instrumentation, namely, instrumentation can help your user interface.  You will be able to provide a better experience to the users of your application by having your code instrumented.  Now you might be thinking that I’m just banging the same drum here, that is, by instrumenting I’ll be fixing bugs faster, or picking up performance issues more easily, or collecting metrics to help improve application quality.  These are of course true benefits of instrumentation, but we all know that right ?

No…my motivation for this blog post stems from an activity on one of my laptop’s last week.

I went to upgrade it to Windows 10.  I fired off the process and after a short while, the following window appeared

image

and the familiar “something is happening” feedback swirling dots…

image

I have several laptops at home, so there was no need to sit and watch this one, so I left it and worked on another tackling some AskTom questions.  The laptop I upgrading just sits on the shelf, and occasionally plays Netflix for my children.  Hence, naturally enough… I totally forgot about it!

That was Saturday.  Now on Monday… 48 hours later, I’ve just been over to and waved the mouse to restore the display.  And this is what is on the screen:

image

My oh my …. there must be a lot of things to get ready Smile

The obvious question now is – has anything actually happened ?  Has the program hung ?  Or is it downloading a huge Windows 10 OS bundle ?  As a consumer of this “application” (the upgrade process) I have no idea.  There is nothing that tells me what state the application is in.  Now let me give the Microsoft developers the benefit of the doubt here, and make the assumption that the upgrade is extensively instrumented, and that if I looked in a logging file somewhere on the laptop hard drive, there would be a capture of the current state which would tell me whether any true progress is being made.  And that is typically how most us of implement instrumentation – a means to capture information for diagnosis.

But to do so is to miss an opportunity to directly assist the user.  Even if the content of the instrumentation would appear to be gibberish to a typical Windows consumer, just the presence of that detail on the user interface  becomes an improvement to the user experience.  If my upgrade dialog box had looked something like this:

image

then even if I have no idea what “getting platform details” means, the fact that I was on Step 17 at 9:00:12 helps me as a consumer, because I have immediate feedback as to whether progress is being made or not.  If I see that message at 9:05am, then I’m likely to let it sit a little longer to see if a “Step 18” ever eventuates.  But if I’m seeing that message at 5pm, then I can be reasonably sure that all is not well.

And no, I don’t think a swirling icon is sufficient.  We all used to criticize the “Progress Bars” on older versions of Windows and the like, when it would either exceed 100% or never get there, or jump back and forth.  So I can understand the motivation for the swirly icons we see in all modern devices and operating systems, because they avoid the task of giving any true prediction of completion.  And don’t get me wrong, I’m not saying that a return to progress bars is the solution, because that would be understating the difficultly of being able to know how long a task will take.  But simply taking advantage of the instrumentation code that I know you already have Smile, and presenting that in some way to the consumers of your application can be a better user experience for them.

Keep that in mind.