ZFS file system monitoring

Cumulative I/O stat

$ zpool iostat

Current I/O stat

$ zpool iostat 1 10
$ zpool iostat –v 1 10

Zpool status

$ zpool status –v

You may get the ZFS ARC (physical memory Cache – Adaptive Replacement Cache)

$ kstat -n arcstats


Get DBMS_STATS preferences

select dbms_stats.get_prefs(pname=>'METHOD_OPT') from dual;

Where preferences can be any of the following –
Oracle stores the stats history in : DBA_TAB_STATS_HISTORY
Statistics comparison with old 3 days statistics of a table –

select * from table(dbms_stats.diff_table_stats_in_history(
                    ownname => user,
                    tabname => upper('&tabname'),
                    time1 => systimestamp,
                    time2 => systimestamp - 3,
                    pctthreshold => 0));

Oracle patch : 17325413 – if we are upgrading to 12c from 11g, make sure apply the patch to avoid NULL column corruptions after the upgrade. See the below blog:
Also see the MOS Note: 2017572.1

Node-oracledb is an open source driver :

SQL can be use the RESULT_CACHE in specific row source as

select a.* from 
(with s as (select /*+ RESULT_CACHE */ OWNER,object_type,count(*) from a group by OWNER,object_type)
select s.* from s ) a, 
(with s as (select /*+ RESULT_CACHE */ OWNER,object_type,count(*) from a group by OWNER,object_type)
select s.* from s) b
where a.owner=b.owner and a.object_type=b.object_type and a.owner='SYSTEM';

This will help to reduce the SQLs re-reading some part of the SQL and reduce the over all I/O and enhance the performance.

Approximate NDV (Number of Distinct values): This Oracle gathers the NDV without sorting the data sets, but using the full data set. It is using row sampling method to gather the statistics.

One pass distinct sampling:
• The column values are hashed into 64-bit column buckets
• This hash bucket is divide into two equal domains such way that each domain will contain equal number of hash values
• This hash memory structure is allocated in the users PGA and known and known as Column Synopsis
• Each column read and placed into one of this domain and ignore the value is already existing
• At the end, the number of distinct values will be 2 * number of values in one domain
This method is clearly shows that, the number of distinct values are calculated without doing any sorting.

user_tab_col_statistics.num_distinct – is the number of distinct columns without NULLs.


%d bloggers like this: