Friday, November 30, 2012

0 db2top

To analysis DB2 performance issue, we commonly collect the snapshot. DB2top is the command line tool that help us to analysis the snapshot . when I first look at it, it looks like the command line version of the taskmgr but focus on the DB2 system level.

db2top -d DBNAME

image

You can hit the shortcut keys to navigate to the monitor features. If you forge what keys, just hit “h” to go to the help page. Once you find what you need, just hit the enter key to go back to previous session and go from there.

m: Memory usage

image

B: Bottleneck

image

 

Reference

Wednesday, November 28, 2012

0 Linux Monitor command: top, sar, iostat, /proc/meminfo …

Monitor overall system

top
-p   Show particular PID
-d   refresh secs
1   show individual CPU information
sorting P Sort by CPU usage
  M sort by memory usage
  N sort by PID
image

0 Oracle Password File

Long time ago, when os authentication is not that safe. Oracle have to create its own password file for its own security. The result of its is password file.
Password file is only used to authenticate for SYSDBA,SYSOPER and SYSASM. Not for the normal user.
password file location: $ORACLE_HOME/dbs/orapw$ORACLE_SID
image

orapwd is the utility to build the password file.

REMOTE_LOGIN_PASSWORDFILE

  • EXCLUSIVE : Can login remotely as SYSDBA/SYSOPER and grant those permission to others
  • SHARED : Can login remotely as SYSDBA/SYSOPER but can not grant those permission to others
  • NONE: Can NOT login remotely as SYSDBA/SYSOPER . Has to login locally to get the SYSDBA/SYSOPER  and user must be in the DBA GORUPs ( OSDBA or OSOPER )

image

User connect from remote

User must have the SYSDBA/SYSOPER permission and must have password in the password file. REMOTE_LOGIN_PASSWORD_FILE must be EXCLUSIVE or SHARED.

User connect from local

Check if user is the member of OSDBA or OSOPER. If user is OSDBA or OSOPER, no password require. If user is not the member of OSDBA/OSOPER, check the password file.

$ORACLE_HOME/rdbms/lib.config.cs

lib.config.cs is the file configure which local group allow to login locally as SYSDBA/SYSOPER. To change the group needs to recompile the file.

image

0 TABLESPACE(7): Temporary Tablespace

Temporary tablespace

Temporary table is used for operation like sorting and grouping function etc

Each user would be assign for one temporary table space. If no temporary table space is assigned. The user would use the default temporary table space..

List User and its default temporary tablespace

select USERNAME,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE,profile from DBA_USERS;

image_thumb5

How to find the file location for the TEMP tablespace?

The information can be found at V$TEMPFILE and DBA_TEMP_FILE

select * from V$TEMPFILE;
select * from DBA_TEMP_FILES;

image_thumb7

How to find out the current usage for the temp tablespace?

select TABLESPACE_NAME, BYTES_USED, BYTES_FREE from V$TEMP_SPACE_HEADER;

image_thumb9

Create the user temporary table space

Below exercise is to create the temporary tablespace and assign to the user.

Create temporary tablespace myusertemp01 TEMPFILE '/home/oracle/app/oracle/oradata/orcl/usertemp01.dbf' size 100M;
 
col tablespace_name format a20
col "FILE_NAME" format a20
select DT.TABLESPACE_NAME,DT.contents,DT.STATUS,DT.SEGMENT_SPACE_MANAGEMENT, DT2.FILE_NAME,DT2.BYTES
from DBA_TABLESPACES DT, DBA_TEMP_FILES  DT2
where DT.TABLESPACE_NAME= DT2.TABLESPACE_NAME;
 
create user po3 identified by oracle;
select username,temporary_tablespace from dba_users where username='PO3';
alter user po3 temporary tablespace myusertemp01;
select username,temporary_tablespace from dba_users where username='PO3';
 

image_thumb14

Drop temp tablespace

If we don’t use “INCLUDING CONTENTS AND DATAFILES”, the tablespace would ONLY be drop from data dictionary but the file still exist on the operation. We then need to use OS level command to remove it.

-- Clean env
drop user PO3;
drop tablespace myusertemp01 INCLUDING CONTENTS AND DATAFILES;

temp table space group

Tempgroup

tempgroup is another layer of the logical grouping for the multiple temp tablespace. The purpose for this is to have more then one temp tablespace file to reduce the IO contention or if the temp tablespace has reach the max file system size then we have to add the another file on the different directory to increase the temp tablespace size.

In this example, we create 2 tablespace and assign them to tempgroup_a. If there the tempgroup_a does not exist, it would be created the tempgroup_a automatically when first temp tablespace is added.

select  tablespace_name, group_name from DBA_TABLESPACE_GROUPS;
Create temporary tablespace myusertemp01 TEMPFILE '/home/oracle/app/oracle/oradata/orcl/usertemp01.dbf' size 50M;
Create temporary tablespace myusertemp02 TEMPFILE '/home/oracle/app/oracle/oradata/orcl/usertemp02.dbf' size 50M;
 
--  If there the tempgroup_a does not exist, it would be created the tempgroup_a automatically when first temp tablespace is added.
ALTER TABLESPACE myusertemp01 TABLESPACE GROUP tempgroup_a;
ALTER TABLESPACE myusertemp02 TABLESPACE GROUP tempgroup_a;
select  tablespace_name, group_name from DBA_TABLESPACE_GROUPS;
 
create user po3 identified by oracle;
select username,temporary_tablespace from dba_users where username='PO3';
alter user po3 temporary tablespace tempgroup_a;
select username,temporary_tablespace from dba_users where username='PO3';

image_thumb16

Remove the Tempgroup

Use alter tablespace to remove the tablespace from tempgroup. When there is no tablespace left in the tempgroup, tempgroup would be remove automatically.

ALTER TABLESPACE myusertemp01 TABLESPACE GROUP '';
ALTER TABLESPACE myusertemp02 TABLESPACE GROUP '';
select  tablespace_name, group_name from DBA_TABLESPACE_GROUPS;
select username,temporary_tablespace from dba_users where username='PO3';

image_thumb18

Even the TEMPGROUP_A does not exist anymore, but user PO3 still be able to run the query because at this point, the user PO3 is using the default tablespace.

Shrink temporary tablespace

KEEP keyword would shrink the tablespace to the keep size.

Without the keep keyword, it would shrink to its min size.

Create temporary tablespace myusertemp01 TEMPFILE size 100M;
alter tablespace myusertemp01 add tempfile size 100M;
select SUM(bytes) from dba_temp_files where tablespace_name='MYUSERTEMP01';

alter tablespace myusertemp01 shrink space keep 50M;
select SUM(bytes) from dba_temp_files where tablespace_name='MYUSERTEMP01';
alter tablespace myusertemp01 shrink space;
select SUM(bytes) from dba_temp_files where tablespace_name='MYUSERTEMP01';

image

0 TABLESPACE(6): Tablespace encryption

The data store in the datafile is in the binary format. However, it is still possible to use convert the binary data to text and read the data.

create tablespace mytbs1 datafile size 10M autoextend on next 10M MAXSIZE 100M;
 
col TABLESPACE_NAME format a15
col FILE_NAME format a40
select  TABLESPACE_NAME,FILE_ID,FILE_NAME
from DBA_DATA_FILES
Where TABLESPACE_NAME='MYTBS1';
 
create table PO1 (NAME VARCHAR(20)) tablespace MYTBS1;
INSERT INTO PO1 VALUES ( 'PANDA');
COMMIT;
--  This would convert binary to string but we may not able to see the data because even the data is commit but it only exsit in the buffer pool
! strings /home/oracle/app/oracle/oradata/orcl/ORCL/datafile/o1_mf_mytbs1_8cc6wdgt_.dbf
-- force the checkpoint and DWR would write the data back to the disk
alter system checkpoint;
! strings /home/oracle/app/oracle/oradata/orcl/ORCL/datafile/o1_mf_mytbs1_8cc6wdgt_.dbf

image

Tuesday, November 27, 2012

0 TABLESPACE(5): Datafile operations

Tablespace can have more than 1 data files.

Add an datafile to the tablespace

Below example is creating the new tablespace and add the datafile to the tablespace.

create tablespace mytbs1 datafile size 10M autoextend on next 10M MAXSIZE 100M;
-- Add datafile: Only for small file tablespace
alter tablespace mytbs1 add datafile size 10M  autoextend on next 10M MAXSIZE 100M;
 
col TABLESPACE_NAME format a15
col FILE_NAME format a40
select  TABLESPACE_NAME,FILE_ID,FILE_NAME
from DBA_DATA_FILES
Where TABLESPACE_NAME='MYTBS1';
 
select  TABLESPACE_NAME,SUM(BYTES)/1024/1024 SIZE_MB
from DBA_DATA_FILES
Where TABLESPACE_NAME='MYTBS1'
group by TABLESPACE_NAME;

image

1 TABLESPACE(4): Tablespace operations

Tablespace is the logical allocation unit to manage the actual database file on the file system. It is equivalent of the file group in Microsoft SQL Server.

Tablespace –> Segment –> Extent –> Data block.

  • Tablespace is the logical container of segments. Also provide a unit of the backup.
  • Segment is the logical grouping of the extents. Oracle objects is create on the segment. One table is one segment.( For partition, each partition is one segment). One index is one segment. Segment can not cross tablespaces. One segment must only exist in one tablespace.
  • Extents is the continuously data blocks. Segment can contain one or more extents. Extents is the smallest oracle allocation unit.
  • Data block is the smallest space unit. Multiple blocks form a Extents.

Create Tablespace

  • Autoextend: When the space has been used up, it will automatically grow the space.
  • Next: How much it would grow.
  • MAXSIZE: the maxim size of the tablespace.
create tablespace mytbs1 datafile size 10M autoextend on next 10M MAXSIZE 100M;
 
select  TABLESPACE_NAME,BYTES,AUTOEXTENSIBLE,INCREMENT_BY, MAXBLOCKS
from DBA_DATA_FILES Where TABLESPACE_NAME='MYTBS1';
show parameter block_size

image

In the example above, I want to increment grow by 10M, which is 1280 blocks * 8192 bytes . The maxim size is 12800 blocks which is 100 MB.

Create tablespace block size other than default block size

From Oracle 9i, we can create the tablespace with block size other then default block size.

show parameter db_block_size
create tablespace mytbs1_16K datafile size 10M autoextend on next 10M MAXSIZE 100M blocksize 16K;
select tablespace_name,block_size from dba_tablespaces;

image

Shrink Tablespace

Permanent tablespace can not be shrink.

image

Read Only Tablespace

Below example, we create the table on the tablespace and change it to the read only. Since it is read only, the drop column and add column with default value would fail. Because it change the table data itself. However, the add column without default and drop the table would work because this operation only update the data dictionary.

--- Set to read only
create table system.po1 tablespace MYTBS1 AS SELECT * from dba_objects;
alter tablespace mytbs1 read only;
 
-- can not update but table can be drop
col tablespace_name format a21
SELECT
  DT.TABLESPACE_NAME,
  DT.STATUS,
  DT.contents,
  DT.BLOCK_SIZE,
  DT.bigfile,
  DT.EXTENT_MANAGEMENT,
  DT.ALLOCATION_TYPE
FROM
  DBA_TABLESPACES DT
Where DT.TABLESPACE_NAME='MYTBS1';
 
-- Drop column and add column with default fail because it will change the data on the table
alter table system.po1 drop column status;
alter table system.po1 add ( testcol1 varchar2(10) default 'test');
-- add column without default works because it only change the Data dictionary not the table data itself
alter table system.po1 add ( testcol1 varchar2(10) );
-- Drop table works because it only change the Data dictionary not the table data itself
drop table system.po1;

image

Read Write tablespace

by default, all tablespaces are read write.

alter tablespace mytbs1 read write;

image

Offline Tablespace

Below example, we create the table on the tablespace and change it to the offline. Since it is offline, the drop column and add column with default value would fail. Because it change the table data itself. However, the add column without default and drop the table would work because this operation only update the data dictionary.

Offline tablespace has 4 different modes:

  • Offline normal: default mode. DBWR would write the dirty buffer and run check point. If for some reason checkpoint fail. the offline would fail.
  • Offline temporary: DBWR would write the dirty buffer and run check point.  If the check point fail, then skip the checkpoint. When online the tablespace, we need to do the recovery first.
  • Offline immediate: does not check point
  • Offline recovery: Same as immediate. for backward compatible.
create table system.po1 tablespace MYTBS1 AS SELECT * from dba_objects;
alter tablespace mytbs1 offline;
 
-- can not update but table can be drop
col tablespace_name format a21
SELECT
  DT.TABLESPACE_NAME,
  DT.STATUS,
  DT.contents,
  DT.BLOCK_SIZE,
  DT.bigfile,
  DT.EXTENT_MANAGEMENT,
  DT.ALLOCATION_TYPE
FROM
  DBA_TABLESPACES DT
Where DT.TABLESPACE_NAME='MYTBS1';
 
-- Drop column and add column with default fail because it will change the data on the table
alter table system.po1 drop column status;
alter table system.po1 add ( testcol1 varchar2(10) default 'test');
-- add column without default works because it only change the Data dictionary not the table data itself
alter table system.po1 add ( testcol1 varchar2(10) );
-- Drop table works because it only change the Data dictionary not the table data itself
drop table system.po1;

image

Online Tablespace

alter tablespace mytbs1 online;

image

Rename Tablespace

  • SYSTEM,SYSAUX can not be changed.
  • Tablespace must be health and online before we rename it.
Alter tablespace mytbs1 rename to mynewtbs1;
select tablespace_name from dba_tablespaces;

image

Drop Tablespace

If tablespace is not empty, we have to use “INCLUDING CONTENTS” option to drop. Otherwise we would get ORA-01549 error.

If tablespace is managed by OMF, simply drop tablespace would drop the tablespace in the data dictionary and the files on the OS path. ( With including contents if there are objects on the tablespace)

If tablespace is not managed by OMF, we have to use “INCLUDING CONTENTS AND DATAFILES”, this would drop the tablespace and drop the datafile as well.

image

drop tablespace mynewtbs1;

image

 

Reference:

http://docs.oracle.com/cd/B28359_01/server.111/b28318/logical.htm

Monday, November 26, 2012

0 TABLESPACE(3) : Undo tablespace

Undo tablespace is to replace the roll back segment before 9i.

  • Rollback the un commit transaction.
  • Provide the consistent read for other user. Oracle use the snapshot isolation transaction by default. undo tablespace store the the un commit data, so other user can access it.
  • Used by flash back query

image

  • undo_management : It is recommend to set to AUTO and let oracle manage the space.
  • undo_retention: The number is in secs. That means how long oracle would keep the data in the extent after it is commit. The higher number would avoid the “Snapshot too old error” and flashback query can query order data. However, the higher number it is set, the more space would be used.
  • undo_tablespace is defined the undo tablespace name.

Sunday, November 25, 2012

0 TABLESPACE(2): SYSTEM,SYSAUX

Tablespace is the logical allocation unit to manage the actual database file on the file system. It is equivalent of the file group in Microsoft SQL Server.

List all the tablespace in the database: dba_tablespaces

select tablespace_name,contents,status,SEGMENT_SPACE_MANAGEMENT from dba_tablespaces

image

0 TABLESPACE(1) : Tablespace types

Tablespace is the logical allocation unit to manage the actual database file on the file system. It is equivalent of the file group in Microsoft SQL Server.

SELECT
  DT.TABLESPACE_NAME,
  DT.contents,
  DT.BLOCK_SIZE,
  DT.bigfile,
  DT.EXTENT_MANAGEMENT,
  DT.ALLOCATION_TYPE
FROM
  DBA_TABLESPACES DT
image  

Base on the Block size : Smallfile/Bigfile Tablespace

Before the 10g, each Oracle tablespace can have max to 1022 data file. Each data file has limit number of data blocks ( Platform dependent; typically 222 - 1 blocks). This become the limitation of the size of the tablespace.

From 10g, Oracle introduce the Bigfile tablespace. Each table space can have 232 blocks but only allow single data file.

Block Size Smallfile Storage Max Capacity Bigfile Storage Max Capacity
4K 16GB 16TB
8K 32GB 32TB
16K 64GB 64TB
32K 128GB 128TB

Base on Extent Management: Dictionary Management/Locally Management Tablespace

Oracle File allocation is base on the extend. Every time, when Database instance request the space, such as find the free space to insert data, it is looking for the free extend.  Extent is a continently data blocks.

There are 2 ways for manage the extend in the Oracle: Dictionary Management and Locally Management.

Dictionary Management

It is default extend management in 8i. Oracle track the used and free extend in the SYSTEM Tablespace. This eventually create the IO contention on the SYSTEM tablespace as whenever there is the space change, the oracle needs to update the SYSTEM tablespace.

create tablespace mytb1 datafile size 10M extent management dictionary;

In this example, I get the ORA-12913 error, that is because the SYSTEM tablespace is already local management therefore we can not create the dictionary management tablespace.

image

Locally Management

The “Locally” words mean the Oracle no longer track the free/used extent in the SYSTEM tablespace but “locally” in the tablespace itself.  Extent is a continently data blocks. There are 2 way to determine the extent size. UNIFORM and AUTOALLOCATE.

 
create tablespace mytb1 datafile size 10M extent management local UNIFORM size 1M;
create tablespace mytb2 datafile size 10M extent management local AUTOALLOCATE;

image

In the UNIFORM type, all extents in the tablespace are in the same size. In this example, all extents are 1MB.

In the AUTOALLOCATE type, the extent size is manage by Oracle server. The size is determine as below:

Number of extents Each Extents size size Total Size
1 ~ 16 extents 64K 16*64K=1MB 1 MB
17~79 1 MB 63*1MB = 63MB  64MB
80~199 8 MB 120*8MB=960MB 1024MB
200~ 64MB    

Base on Content: Temporary/UNDO/Permanent

  • Temporary Tablespace is used for store temporary objects and sort operations ..etc.
  • Undo tablespace is to replace the roll back segment before 9i. It is to rollback the un commit transaction, provide the consistent read for other user and flash back query.
  • Permanent tablespace is store the permanent objects such as USER tablespace and SYSTEM, SYSAUX tablespace.

 

Reference:

http://docs.oracle.com/cd/B28359_01/server.111/b28320/limits002.htm

0 Oracle Management Files (OMF)

Oracle Management Files is the feature allow oracle instance manage the database file, such as the tablespace file. It can name the files base on the OFA rule in the predefine location and delete the file when the file is no longer require in the even such as drop tablespace. It is new feature available after Oracle 9i.

DB_CREATE_FILE_DEST Require Data File, Temporary File, Block Change Tracking File. When no DB_CREATE_ONLINE_LOG_DEST, it can be use by control file and log file
DB_CREATE_ONLINE_LOG_DEST_n(1~5) Optional Control File, Log File.

DB_RECOVERY_FILE_DEST

Optional Default location for Archive log and RMAN backup files

When DB_CREATE_FILE_DEST is not set, the OMF is not enable.

-- If no db_create_file_dest is set, the OMF is not enable
show parameter db_create
 
show parameter db_recovery

image

Saturday, November 24, 2012

0 Oracle Database Control File

Each Oracle database has at least one control file. The control store the database default configuration, such as database file/log file location and size. If the database has more then 2 control files, both control files are identical. The main purpose for having more than 1 control file is for availability.

How to check the current database control file location 

show parameter control_file
select name from v$controlfile;
select value from v$system_parameter where name = 'control_files';

image

Thursday, November 22, 2012

0 SQL Server Trace

SQL Server Trace gives us ability to see what is happening inside the SQL Server.  There are many events that we can defined what information we like to see from the trace. The more we enable and the more it may cost SQL Server to split those information.

Server Side Trace  Client  Trace

SQL Server Profiler is the GUI tool to create the trace event. By default, it is running the Client side trace, unless you explicitly want the server side trace. After SQL Server generate the trace, it would pass the trace to the IO Provider. SQL server Support 2 types of IO Providers.

  • Flat-file provider
  • Rowset provider

Because the Profiler use the Rowset provider by default in the Client side trace mode. It add more load on SQL Server .  Linchi Shea’s benchmarking also has elaborate more detail on this point.

The screenshot below show how to enable the Server Trace when use profiler.

image

Wednesday, November 21, 2012

0 Establish the baseline for SQL Server Performance tuning using Perfmon and PAL

It is important to establish the baseline while doing the performance tuning. Having consistent method to collect the performance metrics make it easy to see the affect of the changes we made during the performance tuning exercise.

Perfmon.exe is the utility that we can set up and collect the performance metrics on the Windows. 

Here is the Perfmon screenshot capture on my Win2008R2 SP1

image

PAL:  PAL is stands for Performance Analysis of Logs. It is the open source application from codeplex and can help us to analysis the perform log. We can also use it to defined the perform template.

Here is the screenshot of the PAL.

image

Edit and export the Perfmon template

image

The defend window has better readability for counter description. More easy to read in the native perfmon window.

image

After you export the template, you can also open it in the text editor to review or edit it.

image

Import the Perfmon template

Select User Defined under Data Collector Sets for New –> Data Collector Set

image

image

Click Browser and select the template

image

image

Start the Perfmon

image

In the meantime, I am using HAMMERORA to generate some load on the SQL Server. You can refer here for how to set it up.

Analysis the Perfmon log

The Perfmon log default is output to C:\PerfLogs\Admin\[Collector Name].

PAL is CPU intense process. DO NOT run the PAL on your production SQL Server host.

Select the log path

image

Make sure the environment are mating on the target SQL Server host.

image

This to determine the interval for the log. Keep the default “AUTO”, unless you have good reason.

image

After you execute, it will call the PowerShell script to process.

image

After the process complete, it would open the report in the default browser

image

We can review the report and do some analysis on it.

Known issue with PAL

  • Not yet fully support SQL Server 2012. However, there is the workaround for it.
  • Require Office Web components. As far as I know, it is not install by default on Win8/Win Server 2012. MSFT also has deprecate that technology.

Would perfmon adding more performance cost to the system?

In general, Perfmon would take 1-2% of COU load, but it depends on:

  • Sample Interval
  • Disk Performance
  • Number of counters

0 Database Default Port/log file location

 

Default Port number

  • MSSQL: 1433
  • Oracle: 1521
  • DB2: 50001
  • Sybase: 5000

How to find the current port number

MSSQL

The listen port can be found in the error log and sql server configuration manage

image

image

You can also get it by running the below query

select local_tcp_port from sys.dm_exec_connections where local_tcp_port is not null

image

Oracle

The port number is defined in the local listener.

lsnrctl status

image

DB2
db2 get dbm cfg |grep SVCE
grep db2c_db2inst1 /etc/services

image

Sybase

I don’t have Sybase ASE install in my lab yet. As far as I know, you can check the SQL.ini/Interface.ini to find the current Port number. The ini file is locate in the $SYBASE\ini directory.

How to find the error log

MSSQL
SELECT SERVERPROPERTY(‘ErrorLogFileName’);

image

We can also find it in the SQL Server configuration manager or register key

image

reg query "HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\services\MSSQLSERVER"
image
Oracle
select value from v$parameter where name = 'background_dump_dest';

image

DB2
db2 get dbm cfg | grep DIAGPATH

image

Sybase

$SYBASE/$SYBASE_ASE/install/servername.log

Reference:

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates