Sunday, July 21, 2013

0 Learning Note: SQL Server VS Oracle–Database architecture

This is my learning note base on the “SQL Server Essentials for Oracle DBAs Jump Start” .


Data block/Extend and Segment


  • Oracle use Extend allocation map to track extend. Extend is continually blocks. Each Extend is always part of one segment. The object is created on the segment.
  • MSSQL use GAM/SGAM to track the page usages. The objects can be uniform extend or in mix extend depends on the object size. Trace 1118 turn on would force the SQL server always use uniform extend.
  • SQL Server is always 8K per page and 64K per extend ( 8 pages X 8K), Oracle has various block size hence has various extend size. Oracle can also has uniform extend size and other various extend size base on how  “Extend management” setting within the table space. ( see here).


Tablespaces and system databases

Because there is always one database per oracle instances so most of the system database mapping to Oracle is tablespace.

  • Model DB: Because there is no need to create the separate user db in the Oracle. The close thing to the SQL Server model db is “database Template” which we can use to define the characteristic of database and we can use it to create the database on another instance.
  • Tempdb: Oracle can have multiple temporary table space. SQL server’s tempdb is shared among entire servers. If we turn on the RCSI for the SQL Server database, the version store is also store in the Tempdb versus Oracle store the version in the UNDO tablespace.  DBA_TEMP_FILES list the temporary table space files. For SQL Server, sp_helpdb tempdb.
  • Log file: SQL Server log files are split internally as multiple VLFs and contain both UNDO and REDO logs. ( Except the tempdb log files , it only has UNDO). Oracle log files only has REDO. UNDO log store in the UNDO table space. In SQL Server, each database has its own log files and the log file within the same db are used sequentially. In Oracle, the log files are being divided as log group, each groups can have multiple log files.



System level INFORMATION

Oracle store the system level information in the SYSTEM tablespace under the schema SYS.

SQL Server store in the master database.

  Oracle SQL Server Master database
Users DBA_USERS syslogins
Objects DBA_OBJECTS sys.objects
Tables DBA_TABLES sys.tables
DataFiles DBA_DATAFILES sys.databases


In SQL Server, we can use sp_help to find out the basic information of the object. In Oracle, we use DESC . See here .

v$datafiles and V$logfile are the system level view which allow as to see the information within oracle even the database is not open.

List all process

Oracle: v$sessoins

MSSQL: sys.dm_exec_requests or sp_who2


server configuration

For SQL Server, a lot of configuration values are defined as advance, therefore , we have to

EXEC sp_configure 'show advanced option', '0';


In SQL Server 2008R2, the basic configuration option is 16. All options including advance are 70.

For Oracle, show parameter would display all the options. there are many advance options but most of time, we don’t really need to change it unless it is asked by Oracle support.


  Oracle MSSQL
List all parameters show parameter sp_configure
List single parameter show parameter X sp_configure ‘X’
list parameter with keyword match show paramter XXX N/A


Oracle:  please refer here for detail.

alter system set PARAMETER = XX scope=[MEMORY|SPFILE|BOTH]

MSSQL :See here for configuration option.

sp_configure 'XXX'.value;




Post a Comment


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