Search

OakieTags

Who's online

There are currently 0 users and 42 guests online.

Recent comments

Oakies Blog Aggregator

New mean demo machine

My new Notebook is there! Will spend a couple of hours to do the setup. The specs are quite promising:

CPU:
Intel Core i7-6700 | 4 Cores | 8 Threads | 3,4 – 4,0GHz
Memory:
32GB  SO-DIMM DDR4 RAM 2400MHz Crucial Ballistix Sport LT
6 TB SSD Storage:
1TB m.2 Crucial MX300
1TB m.2 Crucial MX300
2TB Seagate FireCuda | 5400U/Min | 7mm
2TB Seagate FireCuda | 5400U/Min | 7mm

Okay it did cost me a fortune. See the new one on the left:

nc

But that baby should allow for two 2-Node Clusters running with Virtualbox VMs plus a Cloud Control VM. Want to have my own Maximum Availability lab – and it’s still less expensive and easier to transport than an Oracle Database Appliance</p />
</p></div>

    	  	<div class=

Extended Stats

After my Masterclass on indexes at the UKOUG Tech2016 conference this morning I got into a conversation about creating extended stats on a table. I had pointed out in the masterclass that each time you dropped an index you really ought to be prepared to create a set of extended stats (specifically a column group) on the list of columns that had defined the index just in case the optimizer had been using the distinct_keys statistic from the index to help it calculate cardinalities.

Unfortunately there is a limit on the number of column groups (or any other type of extended stats) you can have on a table and that limit is the larger of 20 and ceiling(number of columns / 10) – so you typically run into a problem if you want to take defensive action after dropping more than twenty (multi-column) indexes. (And you wonder how Oracle’s adaptive dynamic stats process that silently creates column groups overnight handles the problem of needing far more column groups than are allowed.)

The conversation led on to the oddity that the column count includes the virtual columns representing the column groups so, for example, if you have 253 columns in your table you can create 26 column groups; but if you have 26 column groups that means you have a total of 279 columns, so you can actually create a total of 28 groups (an extra 2); but if you create those two column groups you now have a total of 281 columns in the table which means you’re allowed a total of 29 column groups so you can add one more column group for a total of 282 columns. Here’s some code (which I’ve run only on 11.2.0.4) to play with – to keep things very simple I’ve generated some trivial extended stats rather than column groups:


rem
rem     Script:         extended_stats_limit2.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Dec 2016
rem

drop table t1 purge;

begin
        for i in 2..253 loop
                execute immediate
                'alter table t1 add (c' || to_char(i,'FM000') || ' number)';
        end loop;
end;
/

desc t1

prompt  ============================================================================================
prompt  This will raise an error on the 30th addition
prompt  ORA-20008: Number of extensions in table TEST_USER.T1 already reaches the upper limit (28.2)
prompt  ============================================================================================

declare
        ext_string varchar2(20);
begin
        for i in 1..30 loop
                ext_string := '(c001 + ' || i || ')';
                dbms_output.put_line(
                        dbms_stats.create_extended_stats(
                                ownname         => user,
                                tabname         => 'T1',
                                extension       => ext_string
                        )
                );
        end loop;
end;
/

column column_name format a32

select
        column_name, hidden_column, virtual_column, segment_column_id, internal_column_id
from
        user_tab_cols
where
        table_name = 'T1'
order by
        internal_column_id
;

This code results in a table with 253 segment columns, and 29 hidden, virtual columns (with names like SYS_STU0#$2X$X1M4NFZVM2O_5A3FC) representing the extended stats. What if I want more extended stats ? There is no limit on virtual columns in general, beyond the inherent table limit of 1,000 columns total, so what if I create a few virtual columns (another 39, say, taking my total column count to 321): would this allow me to increase the number of extended stats to 33 – and if so, what would happen if I then dropped the virtual columns:


prompt  ============================================
prompt  Now we add some virtual columns after which
prompt  we will be able to add more extended stats
prompt  and drop the virtual columns
prompt  ============================================

begin
        for i in 1..39 loop
                execute immediate
                'alter table t1 add (virt' || to_char(i,'fm000') ||
                        ' generated always as ( c002 + ' || i || ') virtual)'
                ;
        end loop;
end;
/

select
        column_name, hidden_column, virtual_column, segment_column_id, internal_column_id
from
        user_tab_cols
where
        table_name = 'T1'
order by
        internal_column_id
;

prompt  ============================================================================================
prompt  We can now get up to 33 extended stats
prompt  This will raise an error on the attempt to add the 34th set
prompt  ORA-20008: Number of extensions in table TEST_USER.T1 already reaches the upper limit (32.5)
prompt  ============================================================================================

declare
        ext_string varchar2(20);
begin
        for i in 30..34 loop
                ext_string := '(c001 + ' || i || ')';
                dbms_output.put_line(
                        dbms_stats.create_extended_stats(
                                ownname         => user,
                                tabname         => 'T1',
                                extension       => ext_string
                        )
                );
        end loop;
end;
/

select
        column_name, hidden_column, virtual_column, segment_column_id, internal_column_id
from
        user_tab_cols
where
        table_name = 'T1'
order by
        internal_column_id
;


select
        column_name, internal_column_id
from
        user_tab_cols
where
        table_name = 'T1'
and     hidden_column = 'YES'
and     virtual_column = 'YES'
order by
        internal_column_id
;

prompt  ============================
prompt  Now drop the virtual columns
prompt  ============================

begin
        for r in (
                select column_name from user_tab_cols
                where  column_name like 'VIRT%'
        ) loop
                execute immediate
                'alter table t1 drop column ' || r.column_name;
        end loop;
end;
/

select
        column_name, internal_column_id
from
        user_tab_cols
where
        table_name = 'T1'
and     virtual_column = 'YES'
order by
        internal_column_id
;

When I ran this code I ended up with a table consisting of 286 columns, of which 253 were my original columns and 33 – with internal column ids of 254 to 286 inclusive – were the extended stats. It seems there is a way to bypass the limit if you really want to – though I’m not sure I’d really want to do it on a production system.

Left as Exercise for the Reader:

Create a table with 5 real columns and the 26 column groups needed to represent all (multi-column) combinations of those five columns. (Remember that the order of columns in a column group is not really significant). (The 26 groups consist of: 1 x 5 column, 5 x 4 column, 10 x 3 column, 10 x 2 column – this may remind some of you of binomial expansions, others may remember it as a row from Pascal’s triangle, you could also view it as a particular subset of the binary representations of the integers from 1 to 31.)

 

#ukoug_tech16 Review with Tweets

UKOUG Tech16 is in its final hours, time for a special review: Tweets about it

It was in Birmingham again – very nice location!

 

//platform.twitter.com/widgets.js

Standard Edition attracts not only my attention more and more

//platform.twitter.com/widgets.js

We have had a well visited Round Table about Standard Edition

//platform.twitter.com/widgets.js

and one about Data Guard as well

//platform.twitter.com/widgets.js

Many top-notch speakers again

//platform.twitter.com/widgets.js

//platform.twitter.com/widgets.js

//platform.twitter.com/widgets.js

(Sorry, forgot to take a pic here)

//platform.twitter.com/widgets.js

//platform.twitter.com/widgets.js

//platform.twitter.com/widgets.js

I did my part and delivered two talks also: One about what changes with Multitenant for the DBA like I did at the DOAG conference and one about what can be done with Partitioning since 8i. Both talks were well received by a friendly crowd, thank you for that</p />
</p></div>

    	  	<div class=

Gluent Podcast with Mark Rittman

Mark Rittman has been publishing his podcast series (Drill to Detail) for a while now and I sat down with him at UKOUG Tech 2016 conference to discuss Gluent and its place in the new world with him.

This podcast episode is about 49 minutes and it explains the reasons why I decided to go on to build Gluent a couple of years ago and where I see the enterprise data world going in the future.

It’s worth listening to, if you are interested in what we are up to at Gluent and hear Mark’s excellent comments about what he sees going on in the modern enterprise world too!

 

NB! If you want to move to the "New World" - offload your data and workloads to Hadoop, without having to re-write your existing applications - check out Gluent. We are making history! ;-)

How to reduce Buffer Busy Waits with Hash Partitioned Tables in #Oracle

fight_contention_2https://uhesse.files.wordpress.com/2016/12/fight_contention_2.png?w=576&... 576w, https://uhesse.files.wordpress.com/2016/12/fight_contention_2.png?w=144&... 144w" sizes="(max-width: 288px) 100vw, 288px" />

Large OLTP sites may suffer from Buffer Busy Waits. Hash Partitioning is one way to reduce it on both, Indexes and Tables. My last post demonstrated that for Indexes, now let’s see how it looks like with Tables. Initially there is a normal table that is not yet hash partitioned. If many sessions do insert now simultaneously, the problem shows:

Contention with a heap tablehttps://uhesse.files.wordpress.com/2016/12/heap_table.png?w=150&h=65 150w, https://uhesse.files.wordpress.com/2016/12/heap_table.png?w=300&h=129 300w, https://uhesse.files.wordpress.com/2016/12/heap_table.png 735w" sizes="(max-width: 620px) 100vw, 620px" />

Contention with a heap table

The last extent becomes a hot spot; all inserts go there and only a limited number of blocks is available. Therefore we will see Buffer Busy Waits. The playground:

SQL> create table t (id number, sometext varchar2(50));

Table created.

create sequence id_seq;

Sequence created.

create or replace procedure manyinserts as
begin
 for i in 1..10000 loop
  insert into t values (id_seq.nextval, 'DOES THIS CAUSE BUFFER BUSY WAITS?');
 end loop;
 commit;
end;
/

Procedure created.

create or replace procedure manysessions as
v_jobno number:=0;
begin
FOR i in 1..100 LOOP
 dbms_job.submit(v_jobno,'manyinserts;', sysdate);
END LOOP;
commit;
end;
/

Procedure created.

The procedure manysessions is the way how I simulate OLTP end user activity on my demo system. Calling it leads to 100 job sessions. Each does 10.000 inserts:

SQL> exec manysessions

PL/SQL procedure successfully completed.

SQL> select count(*) from t;

  COUNT(*)
----------
   1000000

SQL> select object_name,subobject_name,value from v$segment_statistics 
     where owner='ADAM' 
     and statistic_name='buffer busy waits'
     and object_name = 'T';

OBJECT_NAM SUBOBJECT_	   VALUE
---------- ---------- ----------
T			    2985

So we got thousands of Buffer Busy Waits that way. Now the remedy:

SQL> drop table t purge;

Table dropped.

SQL> create table t (id number, sometext varchar2(50))
     partition by hash (id) partitions 32;

Table created.

 
SQL> alter procedure manyinserts compile;

Procedure altered.

SQL> alter procedure manysessions compile;

Procedure altered.

SQL> exec manysessions 

PL/SQL procedure successfully completed.

SQL> select count(*) from t;

  COUNT(*)
----------
   1000000

SQL> select object_name,subobject_name,value from v$segment_statistics 
     where owner='ADAM' 
     and statistic_name='buffer busy waits'
     and object_name = 'T';  

OBJECT_NAM SUBOBJECT_	   VALUE
---------- ---------- ----------
T	   SYS_P249	       0
T	   SYS_P250	       1
T	   SYS_P251	       0
T	   SYS_P252	       0
T	   SYS_P253	       0
T	   SYS_P254	       0
T	   SYS_P255	       0
T	   SYS_P256	       1
T	   SYS_P257	       0
T	   SYS_P258	       0
T	   SYS_P259	       1
T	   SYS_P260	       0
T	   SYS_P261	       0
T	   SYS_P262	       0
T	   SYS_P263	       0
T	   SYS_P264	       1
T	   SYS_P265	       1
T	   SYS_P266	       0
T	   SYS_P267	       0
T	   SYS_P268	       0
T	   SYS_P269	       0
T	   SYS_P270	       0
T	   SYS_P271	       1
T	   SYS_P272	       0
T	   SYS_P273	       0
T	   SYS_P274	       0
T	   SYS_P275	       1
T	   SYS_P276	       0
T	   SYS_P277	       0
T	   SYS_P278	       0
T	   SYS_P279	       2
T	   SYS_P280	       0

32 rows selected.

SQL> select sum(value) from v$segment_statistics 
     where owner='ADAM' 
     and statistic_name='buffer busy waits'
     and object_name = 'T';

SUM(VALUE)
----------
	 9

SQL> select 2985-9 as waits_gone from dual;

WAITS_GONE
----------
      2976

The hot spot is gone:

hash_part_tablehttps://uhesse.files.wordpress.com/2016/12/hash_part_table.png?w=150&h=59 150w, https://uhesse.files.wordpress.com/2016/12/hash_part_table.png?w=300&h=119 300w, https://uhesse.files.wordpress.com/2016/12/hash_part_table.png 705w" sizes="(max-width: 620px) 100vw, 620px" />

This emphasizes again that Partitioning is not only for the Data Warehouse. Hash Partitioning in particular can be used to fight contention in OLTP environments.

Tagged: partitioning, Performance Tuning

12.2 Index Advanced Compression “High” – Part I (High Hopes)

Oracle first introduced Advanced Compression for Indexes in 12.1 as I’ve discussed here a number of times. With Oracle Database 12c Release 2, you can now use Index Advanced Compression “High” to further (and potentially dramatically) improve the index compression ratio.  Instead of simply de-duplicating the index entries within an index leaf block, High Index […]

UKOUG Tech16 Day 0 – Car and Curry Chaos!

This year I decided to come to Birmingham a little early for UKOUG Tech16, coming up on the Saturday. I drove up to Lichfield (15 miles North of Brum – which explains the touch of Brummie in my accent) in the morning to see my mum. Once we had discussed her various ailments and prejudices for a few hours and I’d unblocked her vacuum cleaner I set off into Birmingham to get to Jury’s hotel for 18:00. I was sure the shoppers would be pouring out of the city centre by then and it would be quietening down.

How wrong can you be! The A38 into the centre of the city was a crawl for the last 5 miles, with lots of random lane changes by people confused by the delights of inner-city town-bad-planning – or just frustrated enough to be attempting a freestyle game of “dodgem cars”. I know a sneaky way to get over to the back roads behind Broad Street and my hotel -but either the recent road changes have removed it or the density of traffic hid it from me, so I had to follow the herd.

When I finally got to the hotel – the car park was coned off. And there were dozens of cars circling the area trying to find anywhere to park, avoiding the hoards of revellers set on drunken debauchery who would lurch into the road at irregular intervals (usually going ” Wayheeeyyyy!” or “Heeheheheheheeeehehehee”). I put the car where I felt it would not receive a ticket in the near future and checked into the hotel. “Can you let me into the car park”. No. “I’m here for days!” Go park somewhere else. “Where?” I dunno, over that way somewhere. *sigh*

I decided to check out the car park on foot. There were 2 spaces on the top floor! So I got the car, whipped around to the entrance -and found some git in a blue car already removing the cones to sneak in. I tried to follow but the attendant came over and stopped me. “But I know there are spaces, I checked!”. He’d actually seen me do this and told me to try and take a ticket. It refused to give me one. It was not that the car park was physically full but the tech would not issue a ticket if it thought it was full. I started to back out and he stopped me. “Just wait – someone will leave soon” and he put the cones behind my car. Within 5 mins he was proved right, I got a ticket and went to one of my identified spots. Which was still empty. The blue car had been abandoned in an odd place…
I was very thankful to the attendant who had been so nice to me.

After that fiasco I dropped my stuff in my room and met Dave Roberts & Brendan Tierney in the Jury’s bar for a couple of relaxing pints. Relaaaaaxxxxx. Dave knew of a massive, £4M curry house that had just opened, about 2 mins up the road. It was 3 levels of basement. We decided to give it a go!

The establishment itself had curtains up at the doors -all away across the doors. You could not see in. Was it open? We approached the doors and it was opened for us. Inside was a swish reception area and three people to great us. 3? We asked for a table for 3. And they did that bloody annoying thing all posh restaurants that are up their own arse do.

“Does Sir have a reservation?”
“No”.
Pause, hard stare at us and in a cold voice “I will check if there is space for you….sir” (no capitals in ‘Sir’ anymore).

This check considered of him wandering away for 5 seconds, coming back and saying “I think we can fit you in”. We were led down an odd tubular corridor, down through one floor that was obviously not yet in use, another floor that looked fully kitted out for diners and to the lowest floor with maybe 70, 80 tables in it. And less than 1/4 occupied. Veeeery full! So why the snotty attitude you tits?

So we sat down and quite soon one of the waitresses came over (there seemed to be about 1 for every 2 occupied tables) and she took a drinks order. She was not sure what beers they had but ho hum, the menu had them in. Then another of the under-utilised staff came and took our food order. Everything we asked for, she would look dazed and say “I really need to get used to these names” and we would have to point at the item in the menu. They were highly unusual items of Indian cuisine – such as a Lamb Korai or Chicken Tikka…. Hmmmm.

We chatted and sipped our beer. And chatted. And sipped. And chatted some more about ?how long? – and watched the table next to us get a bit short with waiting staff and the “man in charge” had to come over and appease them. And then he came over to us and asked if everything was OK. Brendan was a stare “We ordered almost an hour ago so shall we just settle for the beers or are we likely to get any food soon?”.

Withing 5 minutes we had our starers. The waitress came over and asked who was having the chicken and I said yes – and she gave me the fish. To be fair it was hard to tell as, like many up-market restaurants they were creating an “atmosphere” by keeping it too dark to clearly make out items on the table. But the added interest was that every couple of minutes or so the lighting would raise a little, only to dim again the next minute.

The starters were very nice, to be fair, and once we had swapped them around we enjoyed them. And the mains came soon after. Mine was too salty but other than that nice enough. But certainly not as nice as the price indicated it would be.

We ate the food and left, pausing on the way out to point out to each other the dust and poor finish in certain areas</p />
</p></div>

    	  	<div class=

GTT and Smart Scan – the importance of the append hint

While writing the previous two posts about GTTs and Smart Scan, I stumbled across an interesting observation. When I started putting my thoughts to (virtual) paper, I thought it was going to be a short post. Well, it wasn’t to be, again. Anyway, you might find this interesting.

Initial Situation

If you read the previous posts this code example I used to populate the GTT might look familiar:

insert /*+ append */ into gtt select * from t4 where rownum < 400000;
 
commit;

In situations like this where I’m moving data I developed a habit of using the append hint. I guess I’ve been working on HCC tables a bit too much and therefore didn’t even make a conscious decision to use that hint. It was the right thing to do, as you will see next.

The full test harness is shown here again for reference:

SQL> !cat test.sql
set lines 120 tab off trimspool on verify off timing on

-- this statement is using the /*+ append */ hint
insert /*+ append using_append */ into gtt select * from t4 where rownum < 400000;

commit;

-- not testing if I can get Smart Scans, I know there are :)
alter session set "_serial_direct_read" = always;

-- make sure the query can be ever so slightly more complex and always return rows
col min_id new_v min_id
col max_id new_v max_id

select min(id) min_id from gtt;
select max(id) max_id from gtt;

-- this is the actual test
@mystats start s=s
set echo on
select /* using_append */ count(*), id from gtt where id in (&min_id, &max_id) group by id;
set echo off
@mystats stop t=1

SQL>

When executing the script and looking at the output I didn’t see anything out of the ordinary. When checking for the “depth” of the Smart Scan, I regularly consult the cell blocks processed by % layer statistics to see if I lose blocks between code layers. It wouldn’t appear so:

------------------------------------------------------------------------------------------
2. Statistics Report
------------------------------------------------------------------------------------------

Type    Statistic Name                                                               Value
------  ----------------------------------------------------------------  ----------------
...
STAT    cell IO uncompressed bytes                                             546,136,064
STAT    cell blocks processed by cache layer                                        66,667
STAT    cell blocks processed by data layer                                         66,667
STAT    cell blocks processed by txn layer                                          66,667
...
STAT    cell physical IO bytes eligible for predicate offload                  546,136,064
STAT    cell physical IO interconnect bytes                                        112,504
STAT    cell physical IO interconnect bytes returned by smart scan                  96,120
...
STAT    physical read IO requests                                                      523
STAT    physical read bytes                                                    546,152,448
STAT    physical read requests optimized                                               317
STAT    physical read total IO requests                                                523
STAT    physical read total bytes                                              546,152,448
STAT    physical read total bytes optimized                                    330,153,984
STAT    physical read total multi block requests                                       521
STAT    physical reads                                                              66,669
STAT    physical reads cache                                                             2
STAT    physical reads direct                                                       66,667
...
STAT    table scans (direct read)                                                        1
STAT    table scans (long tables)                                                        1

66,667 blocks in each code layer. This looks ok to me: the number of all physical reads recorded for this statement is 66,669. I certainly don’t want to endorse tuning by ratio, but in this case it sort-of makes sense: the offload efficiency pretty high in my case:

SQL> @fsx.sql
Enter value for sql_text:
Enter value for sql_id: a4jrkbnsfgk1j

SQL_ID         CHILD  PLAN_HASH  EXECS  AVG_ETIME AVG_PX OFFLOAD IO_SAVED_% SQL_TEXT
------------- ------ ---------- ------ ---------- ------ ------- ---------- ----------------------------------------------------------------------
a4jrkbnsfgk1j      0 2363333961      1        .01      0 Yes         100.00 select /* using_append */ count(*), id from gtt where id in (        7

That should suffice for a recap of the previous posts.

No append hint-different outcome

Now imagine my surprise when I didn’t execute a script but rather typed the insert command in an interactive session. In the next example, created to reproduce the observation, I “forgot” to specify the append hint:

SQL> !cat test2.sql
-- not using the append hint
insert /* not_using_append */ into gtt select * from t4 where rownum < 400000;

commit;

-- not testing if I can get Smart Scans, I know there are :)
alter session set "_serial_direct_read" = always;

-- make sure the query can be ever so slightly more complex and always return rows
col min_id new_v min_id
col max_id new_v max_id

select min(id) min_id from gtt;
select max(id) max_id from gtt;

-- this is the actual test
@mystats start s=s
set echo on
select /* not_using_append */ count(*), id from gtt where id in (&min_id, &max_id) group by id;
set echo off
@mystats stop t=1

The difference between test.sql and test2.sql looks harmless enough – just a hint, or lack thereof. However the result is interesting. Most executions of the above script resulted in Oracle reporting the following statistics:

------------------------------------------------------------------------------------------
2. Statistics Report
------------------------------------------------------------------------------------------

Type    Statistic Name                                                               Value
------  ----------------------------------------------------------------  ----------------
...
STAT    calls to get snapshot scn: kcmgss                                              462
STAT    calls to kcmgcs                                                                 28
STAT    cell IO uncompressed bytes                                             544,800,768
STAT    cell blocks processed by cache layer                                        67,026
STAT    cell blocks processed by data layer                                              1
STAT    cell blocks processed by txn layer                                               1
STAT    cell commit cache queries                                                   67,025
STAT    cell flash cache read hits                                                     633
STAT    cell num smartio automem buffer allocation attempts                              1
STAT    cell physical IO bytes eligible for predicate offload                  544,800,768
STAT    cell physical IO interconnect bytes                                    545,000,688
STAT    cell physical IO interconnect bytes returned by smart scan             544,984,304
STAT    cell scans                                                                       1
STAT    cleanout - number of ktugct calls                                           66,503
STAT    cleanouts only - consistent read gets                                       66,503
STAT    commit txn count during cleanout                                            66,503
...
STAT    consistent gets                                                            133,250
STAT    consistent gets direct                                                      66,504
STAT    consistent gets examination                                                 66,506
STAT    consistent gets examination (fastpath)                                      66,506
...
STAT    immediate (CR) block cleanout applications                                  66,503
...
STAT    physical read IO requests                                                    1,044
STAT    physical read bytes                                                    544,817,152
STAT    physical read requests optimized                                               633
STAT    physical read total IO requests                                              1,044
STAT    physical read total bytes                                              544,817,152
STAT    physical read total bytes optimized                                    329,859,072
STAT    physical read total multi block requests                                       748
STAT    physical reads                                                              66,506
STAT    physical reads cache                                                             2
STAT    physical reads direct                                                       66,504
...

Huh? I was clearly surprised to see so many rows not making it to the higher layers (txn and data) after having been read by the cache layer. Further executions of the select statement within the same session do not necessarily improve this significantly, but I noticed savings by storage index to come into play.

Speaking more scientifically, the listing immediately above shows that out of 67,026 blocks that were read by the cache layer only 1 (!) was processed by txn and data layers. As a direct result Smart Scan does not offer any real benefit: cell physical IO interconnect bytes returned by smart scan is actually ≥ cell physical IO bytes eligible for predicate offload.

Why? What?

There were a few red herrings in the statistic counters I saw:

  • Cleanout statistics have been reported by mystats
    • commit txn count during cleanout
    • cleanouts only – consistent read gets
    • immediate (CR) block cleanout applications
    • etc.
  • There are consistent gets examination that might be peeks at undo information
  • Lots of queries to the commit cache (cell commit cache queries) – but for most of my tests I didn’t see replies. Only 1 out of maybe 20 tests produced some cell transactions found in commit cache, but even then their number was not significant. I will provide an example towards the end of this post.

Further investigation

So this leads me to suspect that the blocks that were created with the append hint are “clean”, e.g. suitable for a consistent read on the cells without further need for processing. It would appear so, judging by this block dump (dump the block from the tempfile, not datafile!):

Start dump data blocks tsn: 3 file#:1 minblk 2305025 maxblk 2305025
...
Block dump from disk:
buffer tsn: 3 rdba: 0x00632c01 (1/2305025)
scn: 0x0.64aaeb9f seq: 0x01 flg: 0x0c tail: 0xeb9f0601
frmt: 0x02 chkval: 0xd11e type: 0x06=trans data
...
Block header dump:  0x00632c01
 Object id on Block? Y
 seg/obj: 0x632c00  csc: 0x00.64aaeb9e  itc: 3  flg: -  typ: 1 - DATA
     fsl: 0  fnx: 0x0 ver: 0x01

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x000a.019.0012beca  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x03   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
bdba: 0x00632c01
data_block_dump,data header at 0x7f6250501074
===============
tsiz: 0x1f88
hsiz: 0x1e
pbl: 0x7f6250501074
     76543210
flag=--------
ntab=1
nrow=6
frre=-1
fsbo=0x1e
fseo=0x746
avsp=0x728
tosp=0x728
0xe:pti[0]      nrow=6  offs=0
0x12:pri[0]     offs=0x1b7d
0x14:pri[1]     offs=0x1772
0x16:pri[2]     offs=0x1367
0x18:pri[3]     offs=0xf5c
0x1a:pri[4]     offs=0xb51
0x1c:pri[5]     offs=0x746
block_row_dump:
tab 0, row 0, @0x1b7d
tl: 1035 fb: --H-FL-- lb: 0x0  cc: 6
col  0: [ 2]  c1 50
col  1: [999]
 37 39 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
...
tl: 1035 fb: --H-FL-- lb: 0x0  cc: 6
...
tl: 1035 fb: --H-FL-- lb: 0x0  cc: 6
...

This is in contrast to the blocks I dumped that have been created without the append hint. To make sure I’m comparing apples with apples these blocks have been created in a different session after having disconnected from the previous one:

Start dump data blocks tsn: 3 file#:1 minblk 2305025 maxblk 2305025
...
Block dump from disk:
buffer tsn: 3 rdba: 0x00632c01 (1/2305025)
scn: 0x0.64aab6bf seq: 0x00 flg: 0x0c tail: 0xb6bf0600
frmt: 0x02 chkval: 0x478e type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
...
Block header dump:  0x00632c01
 Object id on Block? Y
 seg/obj: 0x629300  csc: 0x00.64aab6bf  itc: 2  flg: -  typ: 1 - DATA
     fsl: 0  fnx: 0x0 ver: 0x01

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x000a.01b.0012be5d  0x00c04427.d97f.27  ----    6  fsc 0x0000.00000000
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
bdba: 0x00632c01
data_block_dump,data header at 0x7f53b75e125c
===============
tsiz: 0x1fa0
hsiz: 0x1e
pbl: 0x7f53b75e125c
     76543210
flag=--------
ntab=1
nrow=6
frre=-1
fsbo=0x1e
fseo=0x752
avsp=0x734
tosp=0x734
0xe:pti[0]      nrow=6  offs=0
0x12:pri[0]     offs=0xf6c
0x14:pri[1]     offs=0x1379
0x16:pri[2]     offs=0x1786
0x18:pri[3]     offs=0x1b93
0x1a:pri[4]     offs=0x752
0x1c:pri[5]     offs=0xb5f
block_row_dump:
tab 0, row 0, @0xf6c
tl: 1037 fb: --H-FL-- lb: 0x1  cc: 6
col  0: [ 4]  c3 0f 05 50
col  1: [999]
 31 34 30 34 37 39 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
...
tl: 1037 fb: --H-FL-- lb: 0x1  cc: 6
...
tl: 1037 fb: --H-FL-- lb: 0x1  cc: 6
...

This supports the theory that the block I created without the append hint features some information that prevents it from being passed from cache to txn layer. I didn’t see active txn count during cleanout though, but commit txn count during cleanout and immediate (CR) block cleanout applications instead. So cleanout is definitely needed and performed.

I stated in the second bullet point that commit cache queries were not producing cache-hits most of the time. I managed to get an execution of the select statement against the blocks created without append hint, where at least some commit cache queries produced hits:

------------------------------------------------------------------------------------------
2. Statistics Report
------------------------------------------------------------------------------------------

Type    Statistic Name                                                               Value
------  ----------------------------------------------------------------  ----------------
STAT    cell IO uncompressed bytes                                             544,800,768
STAT    cell blocks helped by commit cache                                           6,981
STAT    cell blocks processed by cache layer                                        66,969
STAT    cell blocks processed by data layer                                          6,982
STAT    cell blocks processed by txn layer                                           6,982
STAT    cell commit cache queries                                                   66,968
STAT    cell flash cache read hits                                                     576
STAT    cell num smartio automem buffer allocation attempts                              1
STAT    cell physical IO bytes eligible for predicate offload                  544,800,768
STAT    cell physical IO interconnect bytes                                    487,801,976
STAT    cell physical IO interconnect bytes returned by smart scan             487,785,592
STAT    cell scans                                                                       1
STAT    cell transactions found in commit cache                                      6,981
STAT    cleanout - number of ktugct calls                                           59,522
STAT    cleanouts only - consistent read gets                                       59,522
STAT    commit txn count during cleanout                                            59,522
...
STAT    physical read IO requests                                                      987
STAT    physical read bytes                                                    544,817,152
STAT    physical read requests optimized                                               576
STAT    physical read total IO requests                                                987
STAT    physical read total bytes                                              544,817,152
STAT    physical read total bytes optimized                                    329,859,072
STAT    physical read total multi block requests                                       723
STAT    physical reads                                                              66,506
STAT    physical reads cache                                                             2
STAT    physical reads direct                                                       66,504

I couldn’t reproduce this at will though, and it wasn’t for lack of trying.

Summary

I am not entirely sure what causes the observed behaviour, especially with regards to the commit cache and/or minscn optimisation but it evidently seems to be better to populate a GTT with the append hint if you want to make best use of Smart Scans against such segments. A very quick and dirty test against a heap table suggests that this is true for this segment types as well.

Amazon announces Performance Insights

Excited to see the announcement of Amazon RDS Performance Insight feature for database performance monitoring and tuning.

Having met the team for this project I can say from my personal view point that the importance and future success of this feature is clear as day to me. The team is awesomely sharp, the architecture is super impressive, and this is by far the most exciting performance monitoring and feedback system I’ve been involved with,  surpassing the work I’ve been involved in on Oracle’s performance monitoring and tuning system and Embarcadero’s DB Optimizer and Quest Foglight and Spotlight. Not only does the feature provide it’s own dashboard but will also provide an API to power your dashboards that already exist in the industry. I expect to see partners leveraging the API to provide new insights in their already existing database performance monitors.

Below are a couple of snippets that showed up on the web this week  as well as a couple of photos of Jeremiah Wilton, who I personally consider the catalyst and moving force behind this feature, giving demonstrations of the feature.

PS the team is also hiring! If you you are interested, check out this post on ycombinator.

On Amazon Blog

https://aws.amazon.com/blogs/aws/amazon-aurora-update-postgresql-compatibility/

perfsights

On Brent Ozar’s  blog

https://www.brentozar.com/archive/2016/12/7-things-learned-aurora-aws-reinvent-2016/

 Amazon’s throwing in free performance monitoring tools. The new Performance Insights tool shows wait stats, top resource-intensive queries, lock detection, execution plans, and 35 days of data retention. It’s designed by a former Oracle (there we go again) performance tuning consultant to match his workflow. You don’t have to install an agent, configure a repository, or keep the thing running. It’s in preview for Aurora PostgreSQL today, and will be rolled out to all RDS databases (including SQL Server) in 2017.

 

aws-performance-insights

 

Live Demo at the re:Invent demo grounds

photo-perfinsight1img_0011

Video of presentation at re:Invent

 

Starting at 39:00

 

Histogram Upgrade

I’ve written notes in the past about the improvements 12c introduces for histograms – particularly the frequency and top-N histograms which can be generated “free of charge” while the basic “approximate NDV” scans are taking place to gather stats. Gathering histograms in 12c is much safer than it used to be in earlier versions of Oracle even in the case of the new hybrid histograms (which are still sampled on a very small sample and therefore still a stability risk).

There is a threat, though, recently highlighted by Franck Pachot, that sneaked in at 11.2.0.4 – the way in which the calculation of endpoint values for histograms on char() and nchar() columns has changed. When you upgrade from anything prior to 11.2.0.4 to either 11.2.0.4 or 12c you need to recreate any historgams on those columns; note that this does not apply to varchar2() and nvarchar2() columns, just the fixed length character types. If you fail to do this then you may find that Oracle produces some very silly estimates of cardinality, which could result in some very inefficient tablescans – in particular you are likely to find (as Franck showed) cases where you “know” that a particular value is IN the histogram but the optimizer behaves as if it isn’t – which means it uses the “half the least popular” estimate for the cardinality.

Here’s a little demo to show the underlying difference:


rem
rem     Script:         histogram_change_11204.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Nov 2016
rem     Purpose:
rem

create table t1 (v1 varchar2(32), c1 char(32));
insert into t1
select
        case when rownum <= 100 then 'N' else 'Y' end,
        case when rownum <= 100 then 'N' else 'Y' end
from
        all_objects
where
        rownum <= 1000
;
begin
        dbms_stats.gather_table_stats( ownname => user,
                tabname          =>'T1',
                method_opt       => 'for all columns size 10'
        );
end;
/

column endpoint_value format 999,999,999,999,999,999,999,999,999,999,999,999
break on column_name skip 1

select
        column_name, endpoint_number, endpoint_value, to_char(endpoint_value,'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx')
from
        user_tab_histograms
where
        table_name = 'T1'
order by
        column_name,
        endpoint_number
;

Here are the results from an instance of 11.1.0.7 (though anything up to 11.2.0.3 should produce the same), and 11.2.0.4 (and later – including 12.2):

Results 11.1.0.7
----------------
COLUMN_NAME          ENDPOINT_NUMBER                                   ENDPOINT_VALUE TO_CHAR(ENDPOINT_VALUE,'XXXXXXXXX
-------------------- --------------- ------------------------------------------------ ---------------------------------
C1                               100  405,650,737,316,592,000,000,000,000,000,000,000    4e20202020203a7bb119d5f6000000
                                1000  462,766,002,760,475,000,000,000,000,000,000,000    59202020202034d998ff0b5ae00000

V1                               100  404,999,154,965,717,000,000,000,000,000,000,000    4e0000000000181f436c7bbb200000
                                1000  462,114,420,409,600,000,000,000,000,000,000,000    590000000000127d2b51b120000000

Results 11.2.0.4
----------------
COLUMN_NAME          ENDPOINT_NUMBER                                   ENDPOINT_VALUE TO_CHAR(ENDPOINT_VALUE,'XXXXXXXXX
-------------------- --------------- ------------------------------------------------ ---------------------------------
C1                               100  404,999,154,965,717,000,000,000,000,000,000,000    4e0000000000181f436c7bbb200000
                                1000  462,114,420,409,600,000,000,000,000,000,000,000    590000000000127d2b51b120000000

V1                               100  404,999,154,965,717,000,000,000,000,000,000,000    4e0000000000181f436c7bbb200000
                                1000  462,114,420,409,600,000,000,000,000,000,000,000    590000000000127d2b51b120000000

Look particularly at the first 6 bytes of the Hex version of the endpoint values for the char() column c1. In 11.1.0.7 you see “4e2020202020”, “592020202020” – that’s ASCII ‘N’ and ‘Y’ respectively, padded to 6 characters with spaces. In 11.2.0.4 the spaces have disappeared – the char() columns are now padded to 6 characters with zeros (which is how varchar2() columns have always been treated).

In 11.1.0.7 the optimizer will find a histogram entry for c1 = ‘Y’ and produce a cardinality of 900; if you upgrade the database to 11.2.0.4 without recreating the histograms the optimizer won’t find a histogram entry for the predicate and will produce a cardinality of 50 (i.e. 100 / 2).

Footnote

There’s a brief summary of the algorithm Oracle uses to generate values for character-based histograms at this URL.