Monday, May 30, 2016

Using Indexed View to improve query performance


select concat(c.FirstName,' ',c.LastName) CustomerName,subtotal.TotalAmount
 from
DimCustomer c left outer join
(
select p.EnglishProductName, s.CustomerKey,sum(s.SalesAmount) TotalAmount
from DimProduct p join FactInternetSales s
on p.ProductKey=s.ProductKey
group by p.EnglishProductName, s.CustomerKey
) subtotal
on c.CustomerKey=subtotal.CustomerKey
order by CustomerName
OPTION (MAXDOP 1);



(58922 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'DimCustomer'. Scan count 1, logical reads 975, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'FactInternetSales'. Scan count 1, logical reads 1234, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'DimProduct'. Scan count 1, logical reads 251, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)


--Set the options to support indexed views. 
--These are default SQL Server settings as well
-- But not default for ODBC/OLEDB
-- https://msdn.microsoft.com/en-sg/library/ms191432.aspx

SET NUMERIC_ROUNDABORT OFF; 
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, 
    QUOTED_IDENTIFIER, ANSI_NULLS ON; 
GO 

IF OBJECT_ID ('vCustProdSales', 'view') IS NOT NULL 
drop view vCustProdSales
GO

CREATE VIEW vCustProdSales
WITH SCHEMABINDING 
AS 
select p.EnglishProductName, s.CustomerKey,sum(s.SalesAmount) TotalAmount, count_big(*) Sales_Count
from dbo.DimProduct p join dbo.FactInternetSales s
on p.ProductKey=s.ProductKey
group by p.EnglishProductName, s.CustomerKey
GO 
--Create an index on the view. 
CREATE UNIQUE CLUSTERED INDEX IDX_vCustProdSales
    ON vCustProdSales (EnglishProductName, CustomerKey); 
GO 


select concat(c.FirstName,' ',c.LastName) CustomerName,subtotal.TotalAmount
 from
DimCustomer c left outer join
(
select p.EnglishProductName, s.CustomerKey,sum(s.SalesAmount) TotalAmount
from DimProduct p join FactInternetSales s
on p.ProductKey=s.ProductKey
group by p.EnglishProductName, s.CustomerKey
) subtotal
on c.CustomerKey=subtotal.CustomerKey
order by CustomerName
OPTION (MAXDOP 1);




(58922 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'DimCustomer'. Scan count 1, logical reads 975, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'vCustProdSales'. Scan count 1, logical reads 530, physical reads 0, read-ahead reads 3, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


Friday, May 6, 2016

Fix SSH slow login issue in RHEL7/OL7

Setting highlighted in green are final setting modified in sshd_conf.

[root@localhost ~]# diff /etc/ssh/sshd_config /etc/ssh/sshd_config.20160506
93,94c93,94
< #GSSAPIAuthentication yes
< #GSSAPICleanupCredentials no
---
> GSSAPIAuthentication yes
> GSSAPICleanupCredentials no
129c129
< UseDNS no
---
> #UseDNS yes

[root@localhost ~]# service sshd restart
Redirecting to /bin/systemctl restart  sshd.service

Reference URL: http://ask.xmodulo.com/fix-slow-ssh-login-issue-linux.html

Thursday, March 24, 2016

Use SQL Server Resource Governor to throttle IO (slow down the backup)

It's practical to limit backup speed specially in VM environment, which could create IO storms during backup time. (Enterprise edition ONLY feature)

-- Create Windows user DEV-PC\MaintUser with password "Password", and grant full privilege on following folders:
-- \MSSQL\Log
--  Backup Dest Folders

USE [master]
GO
CREATE LOGIN [DEV-PC\MaintUser] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO
ALTER SERVER ROLE [sysadmin] ADD MEMBER [DEV-PC\MaintUser]
GO


USE [master]
GO
CREATE CREDENTIAL [MaintUser] WITH IDENTITY = N'DEV-PC\MaintUser', SECRET = N'Password'
GO

USE [msdb]
GO
EXEC msdb.dbo.sp_add_proxy @proxy_name=N'MaintUser',@credential_name=N'MaintUser', 
@enabled=1, 
@description=N'User to run maintenance job with IO Throttling'
GO

USE [msdb]
GO
EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N'MaintUser', @subsystem_id=11
GO


-- Create resource pool & workload groups
CREATE RESOURCE POOL MaintPlanPool
GO
CREATE WORKLOAD GROUP MaintPlanGroup using MaintPlanPool
GO

-- Create classifier function
CREATE FUNCTION fnUserClassifier()
RETURNS SYSNAME
WITH SCHEMABINDING
AS
BEGIN
     IF ORIGINAL_LOGIN() = 'DEV-PC\MaintUser'
     BEGIN
          RETURN 'MaintPlanGroup'
     END

     RETURN 'default'
END
GO

-- Set the classifier function and enable RG
ALTER RESOURCE GOVERNOR 
  WITH (CLASSIFIER_FUNCTION = dbo.fnUserClassifier)
ALTER RESOURCE GOVERNOR RECONFIGURE
GO

ALTER RESOURCE POOL MaintPlanPool 
  WITH (MIN_IOPS_PER_VOLUME=0, MAX_IOPS_PER_VOLUME=100)
ALTER RESOURCE GOVERNOR RECONFIGURE
GO



Sunday, March 13, 2016

Quick way to install RPMs for Oracle 12c installation

https://docs.oracle.com/database/121/LADBI/pre_install.htm#LADBI7534
 
Packages for Oracle Linux 7 and Red Hat Enterprise Linux 7
The following packages (or later versions) must be installed:

binutils-2.23.52.0.1-12.el7.x86_64 
compat-libcap1-1.10-3.el7.x86_64
compat-libstdc++-33-3.2.3-71.el7.i686 
compat-libstdc++-33-3.2.3-71.el7.x86_64
gcc-4.8.2-3.el7.x86_64 
gcc-c++-4.8.2-3.el7.x86_64 
glibc-2.17-36.el7.i686 
glibc-2.17-36.el7.x86_64 
glibc-devel-2.17-36.el7.i686 
glibc-devel-2.17-36.el7.x86_64 
ksh
libaio-0.3.109-9.el7.i686 
libaio-0.3.109-9.el7.x86_64 
libaio-devel-0.3.109-9.el7.i686 
libaio-devel-0.3.109-9.el7.x86_64 
libgcc-4.8.2-3.el7.i686 
libgcc-4.8.2-3.el7.x86_64 
libstdc++-4.8.2-3.el7.i686 
libstdc++-4.8.2-3.el7.x86_64 
libstdc++-devel-4.8.2-3.el7.i686 
libstdc++-devel-4.8.2-3.el7.x86_64 
libXi-1.7.2-1.el7.i686 
libXi-1.7.2-1.el7.x86_64 
libXtst-1.2.2-1.el7.i686 
libXtst-1.2.2-1.el7.x86_64 
make-3.82-19.el7.x86_64 
sysstat-10.1.5-1.el7.x86_64 
 
=======================================
 
yum install binutils.x86_64 \
compat-libcap1.x86_64 \
compat-libstdc++-33.i686  \
compat-libstdc++-33.x86_64 \
gcc.x86_64  \
gcc-c++.x86_64  \
glibc.i686  \
glibc.x86_64  \
glibc-devel.i686  \
glibc-devel.x86_64  \
ksh \
libaio.i686  \
libaio.x86_64  \
libaio-devel.i686  \
libaio-devel.x86_64  \
libgcc.i686  \
libgcc.x86_64  \
libstdc++.i686  \
libstdc++.x86_64  \
libstdc++-devel.i686  \
libstdc++-devel.x86_64  \
libXi.i686  \
libXi.x86_64  \
libXtst.i686  \
libXtst.x86_64  \
make.x86_64  \
sysstat.x86_64

Saturday, March 12, 2016

Number of MySQL table columns affects binlog size with row-based reolication


Introduced5.6.2
Command-Line Format--binlog-row-image=image_type
System VariableNamebinlog_row_image=image_type
Variable ScopeGlobal, Session
Dynamic VariableYes
Permitted ValuesTypeenumeration
Defaultfull
Valid Valuesfull (Log all columns)
minimal (Log only changed columns, and columns needed to identify rows)
noblob (Log all columns, except for unneeded BLOB and TEXT columns)
In MySQL row-based replication, each row change event contains two images, a before image whose columns are matched against when searching for the row to be updated, and an after image containing the changes. Normally, MySQL logs full rows (that is, all columns) for both the before and after images. However, it is not strictly necessary to include every column in both images, and we can often save disk, memory, and network usage by logging only those columns which are actually required.
Note
When deleting a row, only the before image is logged, since there are no changed values to propagate following the deletion. When inserting a row, only the after image is logged, since there is no existing row to be matched. Only when updating a row are both the before and after images required, and both written to the binary log.
For the before image, it is necessary only that the minimum set of columns required to uniquely identify rows is logged. If the table containing the row has a primary key, then only the primary key column or columns are written to the binary log. Otherwise, if the table has a unique key all of whose columns are NOT NULL, then only the columns in the unique key need be logged. (If the table has neither a primary key nor a unique key without any NULL columns, then all columns must be used in the before image, and logged.) In the after image, it is necessary to log only the columns which have actually changed.
In MySQL 5.6, you can cause the server to log full or minimal rows using the binlog_row_image system variable. This variable actually takes one of three possible values, as shown in the following list:
  • full: Log all columns in both the before image and the after image.
  • minimal: Log only those columns in the before image that are required to identify the row to be changed; log only those columns in the after image that are actually changed.
  • noblob: Log all columns (same as full), except for BLOB and TEXT columns that are not required to identify rows, or that have not changed.
Note
This variable is not supported by MySQL Cluster; setting it has no effect on the logging of NDB tables. (Bug #16316828)
The default value is full. In MySQL 5.5 and earlier, full row images are always used for both before images and after images. If you need to replicate from a MySQL 5.6 (or later) master to a slave running a previous version of MySQL, the master should always use this value.
When using minimal or noblob, deletes and updates are guaranteed to work correctly for a given table if and only if the following conditions are true for both the source and destination tables:
  • All columns must be present and in the same order; each column must use the same data type as its counterpart in the other table.
  • The tables must have identical primary key definitions.
(In other words, the tables must be identical with the possible exception of indexes that are not part of the tables' primary keys.)
If these conditions are not met, it is possible that the primary key column values in the destination table may prove insufficient to provide a unique match for a delete or update. In this event, no warning or error is issued; the master and slave silently diverge, thus breaking consistency.
Setting this variable has no effect when the binary logging format is STATEMENT. When binlog_format is MIXED, the setting for binlog_row_image is applied to changes that are logged using row-based format, but this setting no effect on changes logged as statements.
Setting binlog_row_image on either the global or session level does not cause an implicit commit; this means that this variable can be changed while a transaction is in progress without affecting the transaction.   

================================================================

 Length Name
    ------ ----
  72251750 server1-bin.000001
   4381411 server1-bin.000002 <-- update t1 set last_modified=last_modified + interval 1 second;
    788494 server1-bin.000003 <-- update t2 set last_modified=last_modified + interval 1 second;
    229497 server1-bin.000004 <-- create table t3 (id integer auto_increment primary key, flag char(1));
    458175 server1-bin.000005 <-- update t3 set flag='y';
       810 server1-bin.000006 <-- alter table t3 add column c1 char(200) default 'a';alter table t3 add column c2 char(200) default 'b';alter table t3 add column c3 char(200) default 'c';
    850512 server1-bin.000007 <-- update t3 set c1='c',c2='b',c3='a';
    850512 server1-bin.000008 <-- update t3 set flag='z';
       194 server1-bin.000009
       297 server1-bin.index


mysql> create database test;
mysql> use test
mysql> create table t1 (id integer, c1 char(200), c2 char(200), c3 char(200), last_modified timestamp(6));
mysql> alter table t1 modify column id int auto_increment primary key;
mysql> create table t2 (id integer auto_increment primary key, last_modified timestamp(6));

mysql> flush logs;


mysql> update t1 set last_modified=last_modified + interval 1 second;
Query OK, 32550 rows affected (0.54 sec)
Rows matched: 32550  Changed: 32550  Warnings: 0
mysql> flush logs;

mysql> update t2 set last_modified=last_modified + interval 1 second;
Query OK, 32689 rows affected (0.36 sec)
Rows matched: 32689  Changed: 32689  Warnings: 0
mysql> flush logs;


mysql> create table t3 (id integer auto_increment primary key, flag char(1));
mysql> insert into t3 (flag) select 'x' from t1;
Query OK, 32550 rows affected (0.33 sec)
Records: 32550  Duplicates: 0  Warnings: 0
mysql> flush logs;

mysql> update t3 set flag='y';
Query OK, 32550 rows affected (0.87 sec)
Rows matched: 32550  Changed: 32550  Warnings: 0
mysql> flush logs;


mysql> alter table t3 add column c1 char(200) default 'a';
Query OK, 0 rows affected (0.58 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> alter table t3 add column c2 char(200) default 'b';
Query OK, 0 rows affected (0.98 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> alter table t3 add column c3 char(200) default 'c';
Query OK, 0 rows affected (1.46 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> flush logs;


mysql> select * from t3 limit 1\G
*************************** 1. row ***************************
  id: 1flag: y
  c1: a
  c2: b
  c3: c
1 row in set (0.00 sec)

mysql> update t3 set c1='a',c2='b',c3='c';
Query OK, 0 rows affected (0.11 sec)
Rows matched: 32550  Changed: 0  Warnings: 0
mysql> update t3 set c1='c',c2='b',c3='a';
Query OK, 32550 rows affected (1.02 sec)
Rows matched: 32550  Changed: 32550  Warnings: 0
mysql> flush logs;

mysql> update t3 set flag='z';
Query OK, 32550 rows affected (0.35 sec)
Rows matched: 32550  Changed: 32550  Warnings: 0
mysql> flush logs;
Query OK, 0 rows affected (0.02 sec)