Subnet Masks and Subnetting

I haven't work very much previously with network configuration but I am looking at more and more RAC issues recently.

I have started reading up to: Pro Oracle Database 10g RAC on Linux

This is a very good book, I have seen Julian present at an Oracle SIG before, and was rather baffled by the advanced RAC issues he tackled (I was a newbie DBA at the time). Still he had lots of enthusiasm and I have since read a number of presentation online.

Anyway while reading up about I decided I needed a refresher on the background network concepts:

The "Subnet Masks and Subnetting" webpage gives a simple example of how to use a 255.255.255.128 subnet mask to a create a restricted part in a class C network (e.g. 192.168.1.0) for a small enterprise.

Next I moved on to a more detailed explanation on wikipedia, subnetwork masks:

This logical addressing structure permits the selective routing of IP packets across multiple networks via special gateway computers, called routers, to a destination host if the network prefixes of origination and destination hosts differ, or sent directly to a target host on the local network if they are the same. Routers constitute logical or physical borders between the subnets, and manage traffic between them. Each subnet is served by a designated default router... The routing prefix of an address is written in a form identical to that of the address itself. This is called the network mask, or netmask, of the address. For example, a specification of the most-significant 18 bits of an IPv4 address, 11111111.11111111.11000000.00000000, is written as 255.255.192.0.

another important background concept for CIDR notation :

192.168.0.0, netmask 255.255.0.0 is written as 192.168.0.0/16

192.168.1.0, netmask 255.255.255.0 is written as 192.168.1.0/24

this concept is clearest by looking at the full binary format:

Binary form Dot-decimal notation

IP address 11000000.10101000.00000101.10000010 192.168.5.130

Subnet mask 11111111.11111111.11111111.00000000 255.255.255.0

Network prefix 11000000.10101000.00000101.00000000 192.168.5.0

Host part 00000000.00000000.00000000.10000010 0.0.0.130

so going back to the first example, which has a restricted access server on the 2nd half of there private class C network with IP 192.168.1.131, netmask 255.255.255.128 is written as 192.168.1.3/25

Binary form Dot-decimal notation

IP address 11000000.10101000.00000001.10000011 192.168.1.3

Subnet mask 11111111.11111111.11111111.10000000 255.255.255.128

Network prefix 11000000.10101000.00000101.10000000 192.168.1.1

Host part 00000000.00000000.00000000.00000011 0.0.0.3

ORACLE-BASE - Recompiling Invalid Schema Objects

ORACLE-BASE - Recompiling Invalid Schema Objects

this is a good background summary page... the commands I like to keep at my fingertips are:

spool pre-compile-errors.lst

col owner form a15

col object_name a60

set line 100

select OWNER, object_name, object_type, status from dba_objects where status <> 'VALID';

spool utlrp.lst

@?/rdbms/admin/utlrp

spool post-compile-errors.lst

col owner form a15

col object_name form a30

set line 100

select OWNER, object_name, object_type, status from dba_objects where status <> 'VALID';

70-432 : Index usage (sys.dm_db_index_usage_stats)

This blog article is aimed at people preparing for 70-432 exam.

This DMV is particularly useful and represents one area where SQLServer appears to be ahead of te Oracle database engine (there is no easy to see which indexes have not been used in the last day / week / month ...)

Starting with the msdn documentation for dm_db_index_usage_stats:

sys.dm_db_index_usage_stats (Transact-SQL)

Returns counts of different types of index operations and the time each type of operation was last performed.

Column name	Data type	Description
database_id	smallint	ID of the database on which the table or view is defined.
object_id	int	ID of the table or view on which the index is defined
index_id	int	ID of the index.
user_seeks	bigint	Number of seeks by user queries.
user_scans	bigint	Number of scans by user queries.
user_lookups	bigint	Number of bookmark lookups by user queries.
user_updates	bigint	Number of updates by user queries.
last_user_seek	datetime	Time of last user seek
last_user_scan	datetime	Time of last user scan.
last_user_lookup	datetime	Time of last user lookup.
last_user_update	datetime	Time of last user update.
system_seeks	bigint	Number of seeks by system queries.
system_scans	bigint	Number of scans by system queries.
system_lookups	bigint	Number of lookups by system queries.
system_updates	bigint	Number of updates by system queries.
last_system_seek	datetime	Time of last system seek.
last_system_scan	datetime	Time of last system scan.
last_system_lookup	datetime	Time of last system lookup.
last_system_update	datetime	Time of last system update.

Remarks

Every individual seek, scan, lookup, or update on the specified index by one query execution is counted as a use of that index and increments the corresponding counter in this view. Information is reported both for operations caused by user-submitted queries, and for operations caused by internally generated queries, such as scans for gathering statistics.

The user_updates counter indicates the level of maintenance on the index caused by insert, update, or delete operations on the underlying table or view. You can use this view to determine which indexes are used only lightly by your applications. You can also use the view to determine which indexes are incurring maintenance overhead. You may want to consider dropping indexes that incur maintenance overhead, but are not used for queries, or are only infrequently used for queries.

The counters are initialized to empty whenever the SQL Server (MSSQLSERVER) service is started. In addition, whenever a database is detached or is shut down (for example, because AUTO_CLOSE is set to ON), all rows associated with the database are removed.

When an index is used, a row is added to sys.dm_db_index_usage_stats if a row does not already exist for the index. When the row is added, its counters are initially set to zero.

http://msdn.microsoft.com/en-us/library/ms188755.aspx

Now the following is a good blog post with an example of how this works in practice and also when you should be checking index usage stats. Not only does the following provide a nice simple of example of usage of the DMV sys.dm_db_index_usage_stats but it also makes a good point about focusing your attention and efforts on defragmentating the key indexes with high usage stats (it is too easy to get side tracked by problems where you can see something is clearly wrong, but you aren't necessarily tackling the key performance problems) :

Whenever I’m discussing index maintenance, and specifically fragmentation, I always make a point of saying ‘Make sure the index is being used before doing anything about fragmentation’.

If an index isn’t being used very much, but has very low page density (lots of free space in the index pages), then it will be occupying a lot more disk space than it could do and it may be worth compacting (with a rebuild or a defrag) to get that disk space back. However, usually there’s not much point spending resources to remove any kind of fragmentation when an index isn’t being used. This is especially true of those people who rebuild all indexes every night or every week.

...

If you're interested in whether an index is being used, you can filter the output. Let's focus in on a particular table - AdventureWorks.Person.Address.

SELECT * FROM sys.dm_db_index_usage_stats

WHERE database_id = DB_ID('AdventureWorks')

and object_id = OBJECT_ID('AdventureWorks.Person.Address');

GO

You'll probably see nothing in the output, unless you've been playing around with that table. Let's force the clustered index on that table to be used, and look at the DMV output again.

SELECT * FROM AdventureWorks.Person.Address;

GO

SELECT * FROM sys.dm_db_index_usage_stats

WHERE database_id = DB_ID('AdventureWorks')

and object_id = OBJECT_ID('AdventureWorks.Person.Address');

GO

Now there's a single row, showing a scan on the clustered index. Let's do something else.

SELECT StateProvinceID FROM AdventureWorks.Person.Address

WHERE StateProvinceID > 4 AND StateProvinceId <>

GO

SELECT * FROM sys.dm_db_index_usage_stats

WHERE database_id = DB_ID('AdventureWorks')

and object_id = OBJECT_ID('AdventureWorks.Person.Address');

GO

And there's another row, showing a seek in one of the table's non-clustered indexes.

http://blogs.msdn.com/sqlserverstorageengine/archive/2007/04/20/how-can-you-tell-if-an-index-is-being-used.aspx

70-432 : Locks and Latchs (sys.dm_db_index_operational_stats)

This blog article is aimed at people preparing for 70-432 exam.

The MCTS 70-432 exams introduces some of the dynamic management views (DMVs). I like the ideas of DMVs, as a seasoned Oracle DBA I know many of the key Oracle Instance and Database views well. Once of the advantages of having a SQL view (as opposed to a GUI tool) is that he DMV query can be scheduled to run periodically and log key columns over time (very important for a DBA).

Starting with the msdn documentation for dm_db_index_operational_stats we can that it can capture leaf DML operations plus lock and latch waits:

You can use sys.dm_db_index_operational_stats to track the length of time that users must wait to read or write to a table, index, or partition, and identify the tables or indexes that are encountering significant I/O activity or hot spots.

Use the following columns to identify areas of contention.

To analyze a common access pattern to the table or index partition, use these columns:

• leaf_insert_count
• leaf_delete_count
• leaf_update_count
• leaf_ghost_count
• range_scan_count
• singleton_lookup_count

To identify latching and locking contention, use these columns:

• page_latch_wait_count and page_latch_wait_in_ms
  These columns indicate whether there is latch contention on the index or heap, and the significance of the contention.
• row_lock_count and page_lock_count
  These columns indicate how many times the Database Engine tried to acquire row and page locks.
• row_lock_wait_in_ms and page_lock_wait_in_ms
  These columns indicate whether there is lock contention on the index or heap, and the significance of the contention.

To analyze statistics of physical I/Os on an index or heap partition

• page_io_latch_wait_count and page_io_latch_wait_in_ms
  These columns indicate whether physical I/Os were issued to bring the index or heap pages into memory and how many I/Os were issued.

Column Remarks

The values in lob_orphan_create_count and lob_orphan_insert_count should always be equal.

The value in the columns lob_fetch_in_pages and lob_fetch_in_bytes can be greater than zero for nonclustered indexes that contain one or more LOB columns as included columns. For more information, see Index with Included Columns. Similarly, the value in the columns row_overflow_fetch_in_pages and row_overflow_fetch_in_bytes can be greater than 0 for nonclustered indexes if the index contains columns that can be pushed off-row. For more information, see Row-Overflow Data Exceeding 8 KB.

How the Counters Are Reset

The data returned by sys.dm_db_index_operational_stats exists only as long as the metadata cache object that represents the heap or index is available. This data is neither persistent nor transactionally consistent. This means you cannot use these counters to determine whether an index has been used or not, or when the index was last used. For information about this, see sys.dm_db_index_usage_stats.

The values for each column are set to zero whenever the metadata for the heap or index is brought into the metadata cache and statistics are accumulated until the cache object is removed from the metadata cache. Therefore, an active heap or index will likely always have its metadata in the cache, and the cumulative counts may reflect activity since the instance of SQL Server was last started. The metadata for a less active heap or index will move in and out of the cache as it is used. As a result, it may or may not have values available. Dropping an index will cause the corresponding statistics to be removed from memory and no longer be reported by the function. Other DDL operations against the index may cause the value of the statistics to be reset to zero.

http://msdn.microsoft.com/en-us/library/ms174281(SQL.90).aspx

Next what is the difference between a lock and a latch? A lock (aka "enqueue") is a request for exclusive or shared owner of some data - a row,page or extent (a set of eight contiguous pages makes up an extent). Now for Oracle a latch is lock on an internal data structure in the SGA:

What is the difference between locks, latches, enqueues and semaphores?

A latch is an internal Oracle mechanism used to protect data structures in the SGA from simultaneous access. Atomic hardware instructions like TEST-AND-SET are used to implement latches. Latches are more restrictive than locks in that they are always exclusive. Latches are never queued, but will spin or sleep until they obtain a resource, or time out.

Enqueues and locks are different names for the same thing. Both support queuing and concurrency. They are queued and serviced in a first-in-first-out (FIFO) order.

Semaphores are an operating system facility used to control waiting. Semaphores are controlled by the following Unix parameters: semmni, semmns and semmsl. Typical settings are:

• semmns = sum of the "processes" parameter for each instance (see init.ora for each instance)
• semmni = number of instances running simultaneously;
• semmsl = semmns

http://www.orafaq.com/wiki/Oracle_database_Internals_FAQ#What_is_the_difference_between_locks.2C_latches.2C_enqueues_and_semaphores.3F

while for SQLServer a latch is often described as a "lightweight lock" and again is about protect internal database engine structures like buffers:

Tips for Using SQL Server Performance Monitor Counters

By : Brad McGehee Aug 24, 2005

A latch is in essence a "lightweight lock". From a technical perspective, a latch is a lightweight, short-term synchronization object (for those who like technical jargon). A latch acts like a lock, in that its purpose is to prevent data from changing unexpectedly. For example, when a row of data is being moved from the buffer to the SQL Server storage engine, a latch is used by SQL Server during this move (which is very quick indeed) to prevent the data in the row from being changed during this very short time period. This not only applies to rows of data, but to index information as well, as it is retrieved by SQL Server.

Just like a lock, a latch can prevent SQL Server from accessing rows in a database, which can hurt performance. Because of this, you want to minimize latch time.

SQL Server provides three different ways to measure latch activity. They include:

• Average Latch Wait Time (ms): The wait time (in milliseconds) for latch requests that have to wait. Note here that this is a measurement for only those latches whose requests had to wait. In many cases, there is no wait. So keep in mind that this figure only applies for those latches that had to wait, not all latches.
• Latch Waits/sec: This is the number of latch requests that could not be granted immediately. In other words, these are the amount of latches, in a one second period, that had to wait. So these are the latches measured by Average Latch Wait Time (ms).
• Total Latch Wait Time (ms): This is the total latch wait time (in milliseconds) for latch requests in the last second. In essence, this is the two above numbers multiplied appropriately for the most recent second.

When reading these figures, be sure you have read the scale on Performance Monitor correctly. The scale can change from counter to counter, and this is can be confusing if you don't compare apples to apples.

Based on my experience, the Average Latch Wait Time (ms) counter will remain fairly constant over time, while you may see huge fluctuations in the other two counters, depending on what SQL Server is doing.

http://www.sql-server-performance.com/tips/sql_server_performance_monitor_coutners_p3.aspx

To finish the following query looks useful, a good example of how to use the sys.dm_db_index_operational_stats view:

Tables where the most latch contention is occurring

select object_schema_name(ddios.object_id) + '.' + object_name(ddios.object_id) as objectName,
indexes.name, case when is_unique = 1 then 'UNIQUE ' else '' end + indexes.type_desc as index_type,
page_latch_wait_count , page_io_latch_wait_count
from sys.dm_db_index_operational_stats(db_id(),null,null,null) as ddios
join sys.indexes
on indexes.object_id = ddios.object_id
and indexes.index_id = ddios.index_id
order by page_latch_wait_count + page_io_latch_wait_count desc

http://sqlblog.com/blogs/louis_davidson/archive/2007/08/26/sys-dm-db-index-operational-stats.aspx

MCTS 70-433 SELECT .. INTO

Another "gotcha" when working with Oracle and SQL Server, is that in Oracle you have the "CREATE TABLE AS" statement where as in SQL Server you use the "SELECT .. INTO " statement

http://bytes.com/topic/sql-server/answers/640806-create-table-copy-existing-table

Question: 

May 2nd, 2007, 12:57 PM

Shrutisinha (Newbie) 

Join Date: Feb 2007  Posts: 25

Hi Guys 

Really need your help I donno what I am doing wrong in here 

Want to create a table with another existing table 

Here is the syntax I am using 

create table pctemp1

As 

(SELECT distinct a.Promo,b.Ban,b.[Ban Status],

b.[BAn Statys Reson Code],b.[Last Ban Status Date]

FROM PC_FUSION_070424 a

LEFT OUTER JOIN ARCL05_070423 b

ON a.BAN = b.BAN

WHERE b.BAN is not null )

and it says Syntax error with AS clause, tried removing AS clause but no go , can anybody help me please ...

thanks

Answer:

May 2nd, 2007, 03:51 PM

iburyak (Expert) 

Join Date: Nov 2006  Posts: 1,017

re: Create table as copy of existing table

Try this:

SELECT distinct a.Promo,b.Ban,b.[Ban Status],

b.[BAn Statys Reson Code],b.[Last Ban Status Date] into pctemp1

FROM PC_FUSION_070424 a

LEFT OUTER JOIN ARCL05_070423 b

ON a.BAN = b.BAN

WHERE b.BAN is not null 

Good Luck.

MCTS 70-433 XACT_ABORT

To turn implicit transactions on (off is the default - unlike oracle):

SET XACT_ABORT { ON | OFF }

  Remarks

When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back.

When SET XACT_ABORT is OFF, in some cases only the Transact-SQL statement that raised the error is rolled back and the transaction continues processing. Depending upon the severity of the error, the entire transaction may be rolled back even when SET XACT_ABORT is OFF. OFF is the default setting.

http://msdn.microsoft.com/en-us/library/ms188792.aspx

MCTS 70-433 Nested Transactions in SQL Server

Nested transaction are always fun, I remember once chasing own a transaction (J2EE Weblogic / Oracle) which the developer saw as committed in their code but was somehow getting roled backed ...

http://stackoverflow.com/questions/851441/nested-transactions-in-sql-server

Question:  Does sql server allow nested transactions? If so then whats the priority of transactions?

Most Popular Answer: From the MSDN documentation on SQL Server. Nesting Transactions:

Committing inner transactions is ignored by the SQL Server Database Engine. The transaction is either committed or rolled back based on the action taken at the end of the outermost transaction. If the outer transaction is committed, the inner nested transactions are also committed. If the outer transaction is rolled back, then all inner transactions are also rolled back, regardless of whether or not the inner transactions were individually committed.

MCTS 70-433 implicit transaction and default behaviour

Coming from an oracle background, I'm used to having explicitly commit my transactions as the default behaviour in the database, in SQL Server this is not always the default:

After implicit transaction mode has been set on for a connection, SQL Server automatically starts a transaction when it first executes any of these statements:

ALTER TABLE	INSERT
CREATE	OPEN
DELETE	REVOKE
DROP	SELECT
FETCH	TRUNCATE TABLE
GRANT	UPDATE

The transaction remains in effect until you issue a COMMIT or ROLLBACK statement...

Implicit transaction mode is set either using the Transact-SQL SET statement, or through database API functions and methods.

http://msdn.microsoft.com/en-us/library/aa213064(SQL.80).aspx

The API mechanisms used to set implicit transactions are ODBC and OLE DB.

ODBC

• Call the SQLSetConnectAttr function with Attribute set to SQL_ATTR_AUTOCOMMIT and ValuePtr set to SQL_AUTOCOMMIT_OFF to start implicit transaction mode.
  
• The connection remains in implicit transaction mode until you call SQLSetConnectAttr with Attribute set to SQL_ATTR_AUTOCOMMIT and ValuePtr set to SQL_AUTOCOMMIT_ON.
  
• Call the SQLEndTran function with CompletionType set to either SQL_COMMIT or SQL_ROLLBACK to commit or roll back each transaction.
  
• When SQL_AUTOCOMMIT_OFF is set by an ODBC application, the Microsoft® SQL Server™ ODBC driver issues a SET IMPLICIT_TRANSACTION ON statement

http://msdn.microsoft.com/en-us/library/aa213067(SQL.80).aspx

MCTS 70-433 SQL Server (and DB2) do not allow more than one NULL value in a unique index column!

As primarily an Oracle DBA (Oracle allowing multiple NULL values within a unique index column):

Back to the Basics: Difference between Primary Key and Unique Index

Posted by decipherinfosys on July 4, 2007

Here is another post in the back to the basics section: What is the difference between a Primary Key and a Unique Index? Both can be declared on one or more columns, both can be used to enforce foreign keys (if the unique index is on not null column(s)), both can be declared as clustered/non clustered indexes (SQL Server lingo), both can be used on computed columns as well (SQL Server).

The differences between the two are:

1. Column(s) that make the Primary Key of a table cannot be NULL since by definition, the Primary Key cannot be NULL since it helps uniquely identify the record in the table. The column(s) that make up the unique index can be nullable. A note worth mentioning over here is that different RDBMS treat this differently –> while SQL Server and DB2 do not allow more than one NULL value in a unique index column, Oracle allows multiple NULL values. That is one of the things to look out for when designing/developing/porting applications across RDBMS.
2. There can be only one Primary Key defined on the table where as you can have many unique indexes defined on the table (if needed).
3. Also, in the case of SQL Server, if you go with the default options then a Primary Key is created as a clustered index while the unique index (constraint) is created as a non-clustered index.  This is just the default behavior though and can be changed at creation time, if needed.

http://decipherinfosys.wordpress.com/2007/07/04/back-to-the-basics-difference-between-primary-key-and-unique-index/

MCTS 70-433 SQL Server Concurrency and Phantom Reads

To prevent dirty / phantom / non-repeatedable reads, you need an "isolation level" with high degree of concurrency

.

In SQL2005 there are five types of isolation, show here in order of increasing degree of concurrency:

• READ UNCOMMITTED
  
• READ COMMITTED
  
• REPEATABLE READ
  
• SNAPSHOT
  
• SERIALIZABLE
  

 

The difference between SNAPSHOT and SERIALIZABLE is quite complex (SNAPSHOT working via a table lock and row versiosns and SERIALIZABLE working via page and row lock), but both prevent phantom reads.

SERIALIZABLE

This isolation level specifies that all transactions occur in a completely isolated fashion; i.e., as if all transactions in the system had executed serially, one after the other. The DBMS may execute two or more transactions at the same time only if the illusion of serial execution can be maintained. At this isolation level, phantom reads cannot occur.

However, many databases (e.g. Oracle[1], PostgreSQL[2]) do not guarantee that there is some serial ordering of the transactions that will result in the same outcome, instead implementing snapshot isolation. This explicitly contradicts the ANSI/ISO SQL 99 standard (see ISO/IEC9075-2:1999(E), page 83). Other databases (MS SQL Server[3]) support both serializable and snapshot isolation modes.

With a lock-based concurrency control DBMS implementation, serializability requires that range locks are acquired when a query uses a ranged WHERE clause. When using non-lock concurrency control, no lock is acquired; however, if the system detects a concurrent transaction in progress which would violate the serializability illusion, it must force that transaction to roll back, and the application will have to restart the transaction.

http://en.wikipedia.org/wiki/Isolation_(database_systems)

MCTS 70-433 Check Constraints - Logical Operators and Sub-queries

Coming from an Oracle background I tend to think of check contraints as simpler checks. I'm pretty sure a subquery isn't allow by Oracle in a check constraint (ORA-02251: subquery not allowed here).

The SQL Server check constraint clause is more open/powerful:

In the Check Constraints dialog box, type an expression in the Check Constraint Expression dialog box using the following syntax:

{constant

column_name

function

(subquery)}

[{operator

AND

OR

NOT}

{constant

column_name

function

(subquery)}]

http://msdn.microsoft.com/en-us/library/58xz3zdd.aspx

The following extract illustrates the limitation of check constraints in Oracle:

ALTER TABLE order

ADD CONSTRAINT ck_fk CHECK( 

   0 < (select count(1) from business_customer b 

        where b.cust_id=order.cust_id)

   OR  

   0 < (select count(1) from home_customer h

        where h.cust_id=order.cust_id)  

)

Unfortunately, the RDBMS that I use leaves no doubt that, today, this is not possible:

ORA-02251: subquery not allowed here

ALTER TABLE emp

ADD CONSTRAINT ck_fk CHECK( 

   0 < (select count(1) from dept 

        where dept.deptno=emp.deptno) 

)

What about User Defined SQL functions (UDF)? Like subquery, UDF returns a scalar value as well, which is normally allowed in any place of expression where ordinary scalar variable or constant is allowed. However, if a RDBMS of my choice were allowing UDF calls within check constraint expression, then I could claim that the implementation is inconsistent. Indeed, UDF can contain embedded SQL inside its body:

function deptno_cnt( deptno integer ) 

RETURN integer IS 

   ret_val integer; 

BEGIN 

   select count(1) into ret_val from dept  

   where dept.deptno = deptno

   RETURN ret_val; 

END;

http://www.dbazine.com/oracle/or-articles/tropashko8

tech: Introduction to my weekly technology blog

The idea behind this blow, is sit back at least once a week and focus on all the useful and interesting stuff I have learnt about computing and IT.

I have always been fascinated by computers and there is always more to learn and understand in the wonndeful world of IT.

For the last few years (since 1994) I have focused on databases (Oracle and SQL Server). Since 1997 I have been working in SAP Basis (infrastructure for SAP installations), which has a large element of DBA work and so this was a natural progression.

I also periodically help charities with there administration, building simple bespoke applications in MS Access

I am going to integrate these technology articles within my main blog, but I label each article "tech:" to clear mark technology articles.