A Quick Look at scalability [Part II]: #SQLServer Checktable without physical_only option

In my last post, we looked at the scalability of 'dbcc checktable... with physical_only' for two tables, from dop 1 to dop 8.

A Quick Look at Scalability [Part I]: #SQLServer Checktable with physical_only
http://sql-sasquatch.blogspot.com/2017/05/a-quick-look-at-scalability-sqlserver.html

While the operation achieved high utilization of a single vcpu for both tables at dop 1, as dop increased cpu utilization did not keep pace.  Total cpu_ms for the operation was fairly stable.  The results was that elapsed time did not decrease in the same proportion as cpu resources were added by increasing dop.  The main decreases in elapsed time were realized by dop 4, further decreases from dop 5 to 8 were minimal.  The following two graphs summarize these findings well.

 

All right.   What if we look at full checktable operations, without the physical_only option?  Does that operation scale the same, better, or worse than physical_only?

Here's the results from TableA.  Cool!  Once again, cpu_ms for the operation remained fairly stable.  Rather than continuing to increase as dop increased, idle_ms reached an early peak and remained fairly stable afterward.  The result is decreasing elapsed time with each increase in dop from 1 to 8.  It looks as if elapsed time may continue to decrease beyond dop 8... someday I'll get to test :-) But the gains after dop 8 look as if they'll be smaller and will almost certainly drop off by dop 12.

The results from TableB sent me back to test again, and dig further into the data I gathered.  Check it out.  The cpu_ms is still fairly stable.  Elapsed time decreases with each increase of dop.  But... whoa.  What an unexpected shape from idle_ms.

I thought the 'hill' must be due to a 'one-off' abnormality, not likely to be repeated.  So I repeated tests for dop 2, 3, 4, and 5.  Plugged the results into the graph and... whoa.

Here's one more test run.  This is probably the most characteristic... but so many factors can throw the numbers off.

So the good news for today is that checktable operations without the physical_only option scale farther/better on my test tables than checktable with physical_only.  While with physical_only scaling benefits in elapsed time are primarily seen only to dop 4, without the physical_only option elapsed time benefits to increasing dop extend at least to dop 8.

And we saw that the shape of scalability graphs is pretty volatile :-)  That's largely because modest changes in elapsed time are multiplied by dop in this calculation to arrive at the idle_ms number - that idle_ms number is the one that changes shape most readily.

A Quick Look at Scalability [Part I]: #SQLServer Checktable with physical_only

Ewald Cress and I have been discussing the OLEDB wait for a while - and at this point he's been pulling all the weight in terms of blogging :-)
His post on the nature of OLEDB wait is great - I highly recommend it.

Scheduler stories: OLEDB, the external wait that isn’t preemptive
http://sqlonice.com/scheduler-stories-oledb-the-external-wait-that-isnt-preemptive/

Before I start digging into the waits experienced by checktable, and in particular OLEDB wait, I want to take a high level look at the scalability of checktable operations, starting with checktable with physical_only.  Below are graphs and additional information showing checktable executions on my 8 vcpu VM (with 64 gb vRAM).  Got results from maxdop 1 to maxdop 8. (Although I actually ran the tests in descending order of maxdop for reasons unknown even to me 😜 )

Lets talk about the difference between "performance" and "scalability".  Performance is a measure of the pace of work that a workload can achieve on a given system - or alternatively the measure of time required to complete a unit of work on that system.  Scalability is the capacity of the workload and system to increase pace of work as additional resources are added.

So we can describe the performance of a checktable operation on a given table and system, at a given degree of parallelism (since the degree of parallelism as well as the number of (v)CPUs on the system may both be limiters of compute resources for the workload).  As the degree of parallelism changes from 1 to the number of (v)CPUs on the system, the performance of checktable may change.  Describing that change in performance will characterize the scalability of checktable on that system.  In the final analysis, a scalability limit may come from the workload(application level coding or database level coding), or it may come from the system.    

All of the numbers below are from perfmon - collected with a 1 second interval, logged to a csv.   The X axis is number of seconds since the start of the checktable(1-indexed rather than 0-indexed).  Don't worry - as I continue I'll be pulling in lots of stuff from DMVs that can't easily be gleaned from perfmon. :-)  But any of the info I grab from DMVs will be summary of time period fgrom start to end opf operation, rather than in 1 second increments.  Observer overhead of the DMV queries is too high for my tastes otherwise.

This system is running SQL Server 2016 SP1.

Here's the operation at DOP 1.  All of the checktable operations are running in a Resource Pool named after me.  In this one you'll see something interesting that I can't explain.  For approximately 60 seconds at the tail end of the activity, CPU utilization hovered near 25%.  My DOP 1 checktable was the only active request in SQL Server, though (confirmed by looking for active requests in the default and internal Resource Pools, the only other Resource Pools in the instance).

Something outside of SQL Server was using 12.5% CPU utilization in the VM.  Oh well... it didn't have enough of a footprint in memory or disk to cause an issue, and my checktable was happily running along on its own vcpu.  Notice that neither the rate of disk read bytes and the rate of logical scan read bytes was able to achieve previous maximum sustained levels, and the CPU usage reported for my resource pool remained at a steady 12.5% throughout.  

At DOP 2, on the 8 vcpu vm the checktable could account for up to 25% of vm-wide cpu utilization.  But wait time appears to have increased substantially, and cpu utilization reaches 25% only briefly near the end.

At maxdop 3, checktable could consume 37.5% cpu.  But it seems to be falling farther from its potential maximum.

Maxdop 4 *could* see up to 50% CPU utilization... doesn't get too close.

62.5% maximum possible at maxdop 5 - but it seems that wait time increases must be outpacing declines in elapsed time.  This checktable does not seem to be scaling very well.

Maxdop 6 would allow for a maximum of 75% CPU utilization.

Maxdop 7 would allow for up to 87.5% CPU utilization.  Even the brief spikes don't get too close.

At maxdop 8 on the 8 vcpu vm, it appears about half of the cpu time is spent in a wait state.

I think its very important to look at time series graphs to understand behavior... hope I didn't lose you to boredom by including all eight of the time series.  There's interesting stuff I hope to return to later in there - especially that always-present peak in CPU utilization in the parallel operations about 2/3 of the way in.

But to compare scalability of checktable across tables, and across options such as physical_only, we'll have to look at things from a higher level.

Each of the schedulers engaged by checktable is either accruing cpu_ms for the checktable (as reported by sys.dm_exec_requests) or not at any given time.  Since these tests are running in isolation on the vm, for now we can assume that time for the schedulers/vcpus that isn't accounted for in cpu_ms is idle_ms.

So we can use this formula when dop, elapsed_ms, and cpu_ms are known:

DOP * elapsed_ms = cpu_ms + idle_ms


That allows the 8 checktable operations to be summarized in this graph.  From DOP1 to DOP 8 the cpu_ms of the operation is extremely steady.  From DOP 1 to DOP 4 there are significant decreases in elapsed time as dop increases.  After dop 4, reduction in elapsed time is slight.  Throughout the tested range, idle_ms increased at a nearly linear rate.

I've got another table - TableB that I've been working with extensively.
Here's how scalability for checktable with physical_only looked on TableB.  Remarkably similar to scalability for TableA.  Hmmm...

SQL Server: table & index names from sys.dm_tran_locks

Martin Smith gave a great answer on StackOverflow - as he often does - when someone was looking to get a table name from the information in sys.dm_tran_locks.
http://stackoverflow.com/a/7821066

Here's my take at an answer - trying to cut down on the rows returned to keep the list short in busy databases.

;WITH tLock_cte(request_session_id, dbName, tableName, indexName, resource_type, request_mode) AS
(SELECT tL.request_session_id,
        db_name(tL.resource_database_id) AS dbName,
        CASE WHEN tL.resource_database_id <> db_id() THEN '[in another database]' 
             WHEN tL.resource_type = 'OBJECT' 
                  THEN object_name(tL.resource_associated_entity_id)
             WHEN tL.resource_type = 'ALLOCATION_UNIT' THEN object_name(sp3.[object_id])
             WHEN au1.type = 0 THEN 'DEFERRED DROP'
             WHEN au1.type = 2 THEN object_name(sp2.[object_id])
             ELSE object_name(sp1.[object_id])
        END       AS tableName,
        si.[name] AS indexName,
        tL.resource_type,
        tL.request_mode
 FROM sys.dm_tran_locks               AS tL
 LEFT OUTER JOIN sys.allocation_units AS au1 
 ON au1.container_id = tL.resource_associated_entity_id
 LEFT OUTER JOIN sys.allocation_units AS au2
 ON au2.allocation_unit_id = tL.resource_associated_entity_id
 LEFT OUTER JOIN sys.partitions       AS sp1
 ON sp1.hobt_id = au1.container_id
 LEFT OUTER JOIN sys.partitions       AS sp2
 ON sp2.[partition_id] = au1.container_id
 LEFT OUTER JOIN sys.partitions       AS sp3
 ON sp3.[partition_id] = au2.container_id
 LEFT OUTER JOIN sys.indexes          AS si 
 ON si.[object_id]  = COALESCE(sp1.[object_id], sp2.[object_id], sp3.[object_id])
    AND si.index_id = COALESCE(sp1.index_id, sp2.index_id, sp3.index_id)
 WHERE tL.resource_associated_entity_id IS NOT NULL
 AND   tL.resource_type IN ('OBJECT', 'PAGE', 'RID', 'KEY',
                            'EXTENT', 'ALLOCATION_UNIT', 'HOBT'))
SELECT   request_session_id, dbName, tableName, indexName, resource_type, request_mode
FROM     tLock_cte
GROUP BY request_session_id, dbName, tableName, indexName, resource_type, request_mode;

Let's give it a test.

USE LKN
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
DROP TABLE IF EXISTS test_locks;
CREATE TABLE test_locks
(num BIGINT);
GO
BEGIN TRAN
CREATE INDEX nci_test ON test_locks(num);
INSERT INTO test_locks SELECT 1;
DELETE FROM test_locks WHERE 1 = 0;

--COMMIT TRAN

The results...

Good enough for now.

So let's commit the transaction in order to release the locks.  And I'll have more for you another day...

COMMIT TRAN

SQL Server 2016 checktable - sometimes takes longer with warm cache than cold?!?

First a disclaimer - I've never run these tests on any version prior to SQL Server 2016.  So I am not at all claiming this is a new phenomenon on SQL Server 2016.  Its just an interesting behavior that surfaced while I am testing SQL Server 2016.

The system is a 6 vcpu, 48 GB RAM instance of SQL Server 2016 SP1.

I've been running through a lot of checkdb/checktable scenarios lately, been trying to keep up with blogging whenever I find something interesting.

This particular case is interesting because it defies expectations.

Here's my test, including instrumentation. The test subject is that lonely checktable command nestled in there :-) I use tablock because if not tablock or in an explicit snapshot, each time checktable is run a new internal snapshot will be created and won't be able to compare warm vs cold database cache.

So... its pretty verbose, sorry about that. But stuffing @@io_busy, virtual file stats, wait stats, spinlock stats and latch stats into temp tables before and after so they can be compared. I've got the dbcc dropcleanbuffers in there because I ran this scenario many, many, many times to verify I wasn't being thrown off by something. But my results were stable and reproducible.

So I ran the test below with cold database cache. (My database was still; might have to issue a checkpoint before the dropcleanbuffers to ensure the cache really is cold if issuing checktable against a table that's recently been edited.)

/* set nocount on;
   dbcc dropcleanbuffers;
*/
DECLARE @iobusy BIGINT;
DROP TABLE IF EXISTS #wstats_1;
DROP TABLE IF EXISTS #spins_1;
DROP TABLE IF EXISTS #latch_1;
DROP TABLE IF EXISTS #wstats_2;
DROP TABLE IF EXISTS #spins_2;
DROP TABLE IF EXISTS #latch_2;
DROP TABLE IF EXISTS #virtfile_1;
DROP TABLE IF EXISTS #virtfile_2;
DROP TABLE IF EXISTS #io_busy;
SELECT * into #wstats_1
FROM sys.dm_exec_session_wait_stats where session_id = @@spid;
SELECT * into #spins_1
FROM sys.dm_os_spinlock_stats where spins > 0;
SELECT * into #latch_1
FROM sys.dm_os_latch_stats ls where ls.waiting_requests_count > 0;
SELECT [file_id], num_of_reads, num_of_bytes_read, io_stall_read_ms INTO #virtfile_1
FROM sys.dm_io_virtual_file_stats(db_id(),null);
set @iobusy = @@timeticks * @@io_busy;
set statistics time, IO on;
dbcc checktable ('sasq_list') with tablock;
set statistics time, IO off;
SELECT @@timeticks * @@io_busy - @iobusy AS io_busy_microseconds INTO #io_busy;
SELECT [file_id], num_of_reads, num_of_bytes_read, io_stall_read_ms INTO #virtfile_2
FROM sys.dm_io_virtual_file_stats(db_id(),null);
SELECT * into #latch_2
FROM sys.dm_os_latch_stats ls where ls.waiting_requests_count > 0;
SELECT * into #spins_2
FROM sys.dm_os_spinlock_stats where spins > 0;
SELECT * into #wstats_2
FROM sys.dm_exec_session_wait_stats where session_id = @@spid;

OK.  Well - here's the output from the checktable and statistics time.

I've got all those temp tables for instrumentation - let's cash the check 😀

SELECT * FROM #io_busy;

SELECT vf2.[file_id], vf2.num_of_reads - vf1.num_of_reads AS num_of_reads, 
vf2.num_of_bytes_read - vf1.num_of_bytes_read AS num_of_bytes_read, 
vf2.io_stall_read_ms - vf1.io_stall_read_ms AS io_stall_read_ms
FROM #virtfile_1 vf1
JOIN #virtfile_2 vf2 ON vf1.[file_id] = vf2.[file_id]
WHERE vf2.num_of_reads > vf1.num_of_reads;

select ws2.wait_type, ws2.waiting_tasks_count - ws1.waiting_tasks_count as wait_count,
ws2.wait_time_ms - ws1.wait_time_ms wait_ms, ws2.signal_wait_time_ms - ws1.signal_wait_time_ms signal_ms
from #wstats_2 ws2 join #wstats_1 ws1 on ws1.wait_type = ws2.wait_type
where ws2.waiting_tasks_count - ws1.waiting_tasks_count > 160;

select ws2.name, ws2.spins - ws1.spins as spin_count,
ws2.collisions - ws1.collisions collision_count
from #spins_2 ws2 join #spins_1 ws1 on ws1.name = ws2.name
where ws2.collisions - ws1.collisions > 10;

select ws2.latch_class, ws2.waiting_requests_count - ws1.waiting_requests_count as latch_count,
ws2.wait_time_ms - ws1.wait_time_ms latch_ms
from #latch_2 ws2 join #latch_1 ws1 on ws1.latch_class = ws2.latch_class
where ws2.waiting_requests_count - ws1.waiting_requests_count > 0;

The results...

All right.  Lets test again - this time without dropping clean buffers, so we can compare behavior with warm cache.

Here's the output from checktable and statistics time.

Huh.  Almost 11.5 seconds of elapsed time with warm cache, vs just over 6.2 seconds of elapsed time with cold cache.  That's unexpected.

Let's see what the temp tables caught - maybe they'll shed some light.

Well.  No reads according to virtual file stats - that's what we expected.  No latch waits.  (That varied a little among test runs, but it was never a significant amount of latch waits.)  The spinlocks are uninteresting... I was kind of hoping to find a spinlock-related reason for the warm cache checktable taking longer.  No dice.

So what's going on?  Well - first of all, take a look at CPU ms between the cold cache and warm cache runs.  34534 for cold cache, 33907 for warm cache.  Even though the cold cache run finished substantially faster, it took 627 ms more CPU time to complete.  That's less than 2% variance.  But its stable in the context of this test.  I ran it over and over and although the times shifted around a bit, the cold cache run was always faster but required around 500 more CPU ms to complete.

On the one hand that makes sense: the cold cache run issues 8103 read IOs according to virtual file stats and @@io_busy says 125 milliseconds of CPU time just to issue those IO commands.

 But it still finished faster!

As far as I can tell, the secret is in the wait stats - cxpacket in particular.  Below are the cold cache wait stats again.

And below are the warm cache wait stats again.  The warm cache checktable - which I would have expected to finish faster - somehow incurred almost 3 times as much cxpacket wait time as the cold cache checktable - and over 3 times as much signal wait time for the cxpacket wait type.

The warm cache checktable had less work to do than the cold cache checktable.  And, indeed, it did less work from an accumulated CPU time standpoint.  But somehow, it didn't divvy up its work as well among the parallel workers.  Due to a significant increase in cxpacket wait time and cxpacket signal wait time over the cold cache operation, it finished in almost twice the time!

This is one reason its extremely important to look not just at work, and not just at work + waits... not even at work + waits + management.  Appropriate scheduling and distribution of work among resources is also important to keep everyone happy when they want extreme performance.

SQL Server - DBCC Clonedatabase vs Scripting Objects

Although the 'dbcc clonedatabase' command is new, the idea of clone databases as a 'dataless' database but including table & index definitions as well as optimizer statistics is not new.

Here's a blog post from May of 2010 describing the longstanding strategy of using the scripting functionality from SSMS to create a statistics clone database in SQL Server.

How to create a SQL Server Clone Statistics Only Database
https://www.mssqltips.com/sqlservertip/1999/how-to-create-a-sql-server-clone-statistics-only-database/

I wanted to put this scripting option in SQL Server/SSMS 2016 head-to-head against the clonedatabase command to see how everything turns out.  Why not do that in front of a crowd of 250 people?  (Well - not really.  I did run the clonedatabase in front of everybody though.)

Here's what was in the source database.  The scripting option won't let me script out the Query Store, of course.  But clonedatabase command ill include it unless the option to exclude is specified.

So, to make sure it was apples-to-apples comparison between the 'clone' methods, I toggled a few of the advanced scripting options.

Then I kicked it off. Thank goodness it was several hours before my presentation. I bet a dollar with a colleague over how long it would take to complete. He guessed 56 minutes. I thought 1 hour 25 minutes. Thank goodness it was "Price is Right" rules, cuz we were both way off. It took 2 hours and 2 minutes. Now I'm $1.00 richer.😄

The end result of the scripting exercise was this file clocking in at over 500 mb. I haven't loaded it into a target database yet - I'll report how long it too and resulting database size once I have.

Then... during my presentation late in the day, in front of about 250 people, I launched the dbcc clonedatabase command. It took 37 seconds. Yayy!! 😁

The end result? 1096 mb of transaction log, and 896 mb of row files.  (Remember that 666 mb of the data in the row files is Query Store.)

Let's take a look at the inventory of the Query Store. There's a lot of stuff in there.

There are some concerns with clonedatabase. Sensitive data may be included in Query Store query plans/text if sensitive values are used as literals in queries. Sensitive data may be included in optimizer statistics on sensitive columns. Intellectual property can be a concern in the stored procedures or functions included. Intellectual property may even be a concern for schema design.

I will point out that the only new concern (that I'm aware of) with the dbcc clonedatabase command, versus the scripting option is the possibility of sensitive data included in Query Store query plans and query text.

There's a very good post below for security/sensitivity considerations.

Understanding data security in cloned databases created using DBCC CLONEDATABASE
https://blogs.msdn.microsoft.com/sql_server_team/understanding-data-security-in-cloned-databases-created-using-dbcc-clonedatabase/

In spite of the security/sensitivity considerations, I'm really excited about the clonedatabase command.  Not just because of the incredible speed bump compared to scripting.  Also because including Query Store in the clone is a natural fit.

Even if security/sensitivity issues complicate sharing a clone database with Microsoft or other off-site vendors, the value of using a clone database for investigating query shape, cardinality estimates, or even query store investigation decoupled from a production database should be high.

AIX: from process ID, to physical/virtual memory used, to TCP connection (Half-baked draft)

# ps -ef | grep -i java
xlisten8  8388648        1   1   Apr 11      - 24:58 java -cp sasqconfig.jar:sasqjava.jar:sasqjdbc4.jar -Xms256M -Xmx256M -jar JLC.jar
xlisten8  9633838        1   1   Apr 11      - 25:44 java -cp sasqconfig.jar:sasqjava.jar:sasqjdbc4.jar -Xms256M -Xmx256M -jar JLC.jar
xlisten8  9961686        1   1   Apr 11      - 32:53 java -cp sasqconfig.jar:sasqjava.jar:sasqjdbc4.jar -Xms256M -Xmx256M -jar JLC.jar
xlisten8 10682506        1   0   Apr 11      - 15:14 java -cp sasqconfig.jar:sasqjava.jar:sasqjdbc4.jar -Xms256M -Xmx256M -jar JLC.jar
xlisten8 10748116        1   0   Apr 11      - 28:20 java -cp sasqconfig.jar:sasqjava.jar:sasqjdbc4.jar -Xms256M -Xmx256M -jar JLC.jar
xlisten8 11600048        1   1   Apr 11      - 62:18 java -cp sasqconfig.jar:sasqjava.jar:sasqjdbc4.jar -Xms256M -Xmx256M -jar JLC.jar
xlisten8 13041852        1   1   Apr 11      - 29:10 java -cp sasqconfig.jar:sasqjava.jar:sasqjdbc4.jar -Xms256M -Xmx256M -jar JLC.jar
xlisten8 14745762        1   1   Apr 11      - 15:22 java -cp sasqconfig.jar:sasqjava.jar:sasqjdbc4.jar -Xms256M -Xmx256M -jar JLC.jar

# svmon -P -O summary=basic,unit=MB | head -n 4 ; svmon -P -O summary=basic,unit=MB | grep java
Unit: MB

-------------------------------------------------------------------------------
     Pid Command          Inuse      Pin     Pgsp  Virtual
11600048 java           1679.27   256.48   340.14  2041.33
 9961686 java           1651.38   256.41   330.53  2005.05
 9633838 java           1613.63   256.43   341.31  1978.62
13041852 java           1593.01   256.38   369.52  1998.18
 8388648 java           1585.89   256.39   343.54  1966.96
10748116 java           1430.42   256.29   209.10  1684.59
10682506 java            928.71   256.25   157.50  1130.74
14745762 java            928.30   256.25   158.09  1130.82

# netstat -Aan | grep ESTABLISHED | grep -v 127.0.0.1 | grep -v tcp4 | grep -v tcp6
f100050000c393b8 tcp        0      0  10.xxx.xx.xxx.48323   10.xxx.xx.xxx.50321    ESTABLISHED
f100050000f583b8 tcp        0      0  10.xxx.xx.xxx.48316   10.xxx.xx.xxx.50505    ESTABLISHED
f100050001f4e3b8 tcp        0      0  10.xxx.xx.xxx.48311   10.xxx.xx.xxx.50844    ESTABLISHED
f100050001ea6bb8 tcp        0      0  10.xxx.xx.xxx.48314   10.xxx.xx.xxx.56565    ESTABLISHED
f100050001c773b8 tcp        0      0  10.xxx.xx.xxx.48332   10.xxx.xx.xxx.50531    ESTABLISHED
f100050001ea2bb8 tcp        0      0  10.xxx.xx.xxx.48361   10.xxx.xx.xxx.50839    ESTABLISHED
f100050000f6b3b8 tcp        0      0  10.xxx.xx.xxx.50707   10.xxx.xx.xxx.50419    ESTABLISHED
f100050000de4bb8 tcp        0      0  10.xxx.xx.xxx.33387   10.xxx.xx.xxx.50505    ESTABLISHED

# rmsock f100050000c393b8 tcpcb
The socket 0xf100050000c39008 is being held by proccess 9633838 (java).
# rmsock f100050000f583b8 tcpcb
The socket 0xf100050000f58008 is being held by proccess 14745762 (java).
# rmsock f100050001f4e3b8 tcpcb
The socket 0xf100050001f4e008 is being held by proccess 9961686 (java).
# rmsock f100050001ea6bb8 tcpcb
The socket 0xf100050001ea6808 is being held by proccess 10748116 (java).
# rmsock f100050001c773b8 tcpcb
The socket 0xf100050001c77008 is being held by proccess 13041852 (java).
# rmsock f100050001ea2bb8 tcpcb
The socket 0xf100050001ea2808 is being held by proccess 11600048 (java).
# rmsock f100050000f6b3b8 tcpcb
The socket 0xf100050000f6b008 is being held by proccess 10682506 (java).
# rmsock f100050000de4bb8 tcpcb
The socket 0xf100050000de4808 is being held by proccess 8388648 (java).

Oracle 12cR2 vs SQL Server 2016: Histogram types

In Oracle, there are 4 histogram types:

frequency

height-balanced

top-n frequency

hybrid

Frequency histogram: for low numbers of distinct values. If ndv < bucket count limit, then each ndv gets a histogram bucket. If ndv > limit, frequency histogram is disqualified.

Height-balanced histogram: histogram buckets with approximately the same number of rows in each bucket

Top-n Frequency: lowest value, highest value, n-2 most popular values in between as bucket end-points.

Hybrid: Top-n Frequency not qualified because N end-point values do not cross threshold to qualify.

In SQL Server 2016 (as has been the case for several major versions), one histogram type is available.  Of the histogram types available in Oracle 12cR2, it is most similar to the hybrid histogram.  For a nullable column, a NULL histogram bucket will be available.  For non-null values, up to 200 additional buckets are available. The desired number of buckets cannot be specified. For values of particular significance, or in order to accommodate skew in data, filtered statistics can be created.  For filtered statistics to reliably be consulted by the optimizer, a query filter predicate must match the predicate of the filtered statistics. 

Resources

March 2017 Whitepaper (1.1 mb pdf) 

Understanding Optimizer Statistics with Oracle Database 12c Release 2

http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-statistics-concepts-12c-1963871.pdf

Three part Oracle Database Histogram series from Jonathan Lewis

http://allthingsoracle.com/histograms-part-1-why/

http://allthingsoracle.com/histograms-pt-2/

http://allthingsoracle.com/histograms-part-3-when/

Another three part Oracle Database Histogram series from Jonathan Lewis

https://jonathanlewis.wordpress.com/2013/07/14/12c-histograms/

https://jonathanlewis.wordpress.com/2013/07/30/12c-histograms-pt-2/

https://jonathanlewis.wordpress.com/2013/10/09/12c-histograms-pt-3/

Two part Oracle Database Histogram series from Mohamed Houri

http://allthingsoracle.com/12c-histogram-top-frequency/

http://allthingsoracle.com/12c-hybrid-histogram/