Systems Engineering and RDBMS

Archive for February 28th, 2007

_b_tree_bitmap_plans un-documented parameter in Oracle

Posted by decipherinfosys on February 28, 2007

We typically never start using un-documented parameters in a production environment unless it has been specifically recommended by Oracle support.  However, we do keep ourselves abreast of all the un-documented parameters that are out there and Jonathan Lewis’s latest book covers them as well.  It is an excellent book to have for other reasons as well if you want to learn and hone your Oracle skills for CBO.

Now, regarding this particular parameter – this has caused us issues more than once.  The default value of this parameter is set to TRUE in Oracle 9iR2 and Oracle 10g.  When this parameter is set to TRUE in the init<sid>.ora parameter file, it enables bitmap plans to be generated for tables with only B-Tree indexes.  CBO (Cost Based Optimizer) can choose to use bitmap access paths without the existence of bitmap indexes and in order to do so, it uses BITMAP CONVERSION FROM ROWIDS and BITMAP CONVERSION TO ROWIDS operations. Those operations are CPU intensive.  So, if you have a query in the system for which those operations are performed selects a small number of rows, then there isn’t much of an impact. However, if those queries select a large number of rows, the cost involved gets escalated pretty soon.  The cost is not incurred during the parse, it’s all incurred during the fetching.  In order to prevent issues arising from the wrong plan getting generated, if you are facing any such issues, this parameter should be explicitly set to False either at the database level or at the session level if you can identify a certain portion of the code running into such an issue and can isolate the connections based on program name etc..

This is one of those silver bullet un-documented items.  However, you should check whether you are running into such issues (check the execution plans to see if bitmap conversions are taking place) and then only take an action.

Posted in Oracle | 1 Comment »

Daylight Savings Time Patch for Microsoft Exchange Server 2003

Posted by decipherinfosys on February 28, 2007

As everyone is probably already well aware, Microsoft has released a daylight savings time (DST) patch for its Windows operating systems. The patch is required to comply with the new start and end dates for DST as specified by the Energy Policy Act of 2005. The change goes into affect this spring, and IT administrators everywhere are scrambling to make sure their servers are updated.

DST Patch and Exchange Server 2003

Here we will be dealing specifically with the DST patch for servers running Exchange Server 2003 SP2.

The patch ID is KB926666 and can be downloaded from: http://www.microsoft.com/downloads/details.aspx?amp;amp;amp;amp;amp;displaylang=en&familyid=C16AEA4A-ED33-4CD9-A7C3-8B5DF5471B7A&displaylang=en

NOTE: This is not the Windows daylight savings time patch, but a supplemental patch for Exchange. Before you apply this patch you should first download and install the appropriate DST patch (KB931836) for your operating system: http://support.microsoft.com/kb/931836/

Here are some of the highlights of what you should know about this patch:

  • Make sure to first install the main DST patch (KB931836) as mentioned above.
  • This patch cannot be installed on Exchange Server 2003 SP1 or Exchange Server 2000. You must first upgrade to Exchange 2003 SP2.
  • Exchange Server 2007 does not require this KB926666 patch.
  • You do not have to restart the Windows server after applying the patch. However, the Exchange Server Information Store service and any dependencies will be restarted.
  • This patch may cause problems for mobile device users or users configured with shared mailboxes. Email messages either cannot be sent, or are sent but result in a Non Delivery Report (NDR). Administrators who have users with these configurations should reference Microsoft article KB912918: http://support.microsoft.com/kb/912918/

Why Do I Need This Patch?

The DST patch for Exchange should be considered mandatory. All Microsoft calendar applications, such as Outlook, depend on time information from the operating system. However, other programs that are based on Collaboration Data Objects (CDO), such as Microsoft Outlook Web Access, depend upon separate time zone tables. This patch modifies these tables to match the changes to the time zone settings of the OS. If the patch is not applied, the CDO-based applications will not be aware of the DST change, and will be out of sync with the actual time.

Posted in Exchange, Windows | 2 Comments »

Conditional Ordering of records

Posted by decipherinfosys on February 28, 2007

Sometimes, you come across requirements where you want to order by certain groups i.e. if say 1000 records are being returned in a query and say there are 10 different sub-categories and you want category number 8 to be the first one to appear (and the records within that category can be ordered separately) and then category 2 records etc..  Conditional ordering can be very easily achieved by using a CASE statement in the ORDER BY clause.  Here is an example of doing that (SQL Server Syntax):

SET NOCOUNT ON

CREATE TABLE DEMO_ORDER (ID_VAL INT, CATEGORY INT)
GO
DECLARE @I INT
SET @I = 0
WHILE (@I < 1000)
BEGIN
INSERT INTO DEMO_ORDER (ID_VAL, CATEGORY) VALUES (@I, (@I/100)+1 )
SET @I = @I + 1
END
GO

–We should have 10 buckets now:

SELECT CATEGORY, COUNT(1) CNT FROM DEMO_ORDER GROUP BY CATEGORY ORDER BY CATEGORY

CATEGORY    CNT
———– ———–
1           100
2           100
3           100
4           100
5           100
6           100
7           100
8           100
9           100
10          100

Now, if we were to do conditional ordering and return back category 8 records first followed by category 2, 7 and 4 and then the rest in the order in which the ID_VAL values were created, here is how you can do that by using a CASE statement in the ORDER BY clause:

SELECT * FROM DEMO_ORDER
ORDER BY CASE CATEGORY
WHEN 8 THEN 1
WHEN 2 THEN 2
WHEN 7 THEN 3
WHEN 4 THEN 4
ELSE 5
END, ID_VAL

This was a very simplistic example to just illustrate the feature.  This is a very common requirement for many of the reports in different industry verticals.

Posted in DB2 LUW, Oracle, SQL Server | 1 Comment »

Stolen Identity

Posted by decipherinfosys on February 28, 2007

No, we are not talking about identity thefts over here 🙂 We are talking about the identity property for a column in a table. In SQL Server, when you insert a record in a table that has an identity property associated with a column, in order to get the value that was just generated, there are a couple of ways of getting that value and depending upon how you are getting it, you can get the wrong identity value.

In SQL Server 2000, you have the SCOPE_IDENTITY(), @@IDENTITY and IDENT_CURRENT() functions available. IDENT_CURRENT is not limited to a scope – it is tied to a table so it returns the latest value generated for that table via any session and any scope. So, if you are looking to get your specific value, this would be a bad choice. Both Scope_Identity() and @@Identity return the last inserted identity value that are generated in any table in the current session. However, @@identity is not limited to a specific scope where as scope_identity() is. A classic example that is used to explain this is an auditing example – say you have a table called USER_MASTER and you have an audit trigger defined on it (for an insert/update/delete action) to log the records into say AUD_USER_MASTER table and both have identity property associated with their PK column. When a record gets inserted, it will create a record in this table and then will insert a record in the table AUD_USER_MASTER. This illustrates 2 scopes within the same current session. In this case, @@identity will return the identity value inserted into AUD_USER_MASTER where as SCOPE_IDENTITY() will return the value inserted into the USER_MASTER table.

Another point of caution is that in case there is a failed transaction or a failed statement within a transaction, then also the identity value does get incremented – it does not get rolled back if the transaction is rolled back. This can create gaps in the values.

In SQL Server 2005, in addition to the above, you have the OUTPUT clause as well to capture these values. We had written a whitepaper on it sometime ago which is available at our web-site here.  So, if you follow simple rules, your identity will never be stolen and you will always get what the value that you are looking for.

Posted in SQL Server | Leave a Comment »