Systems Engineering and RDBMS

Archive for February, 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 »

Oracle’s Recycle Bin

Posted by decipherinfosys on February 27, 2007

Oracle introduced the concept of recycle bin in 10g. This provides a safety net for the end users since if an object gets dropped by mistake, Oracle automatically stores it in the recyclebin.  Just like in windows recyclebin (or Mac’s trash), this is essentially a virtual container where all the dropped objects reside.  Underneath beneath the covers, those objects still occupy the same space as when they were created.  The table segments are still sitting there in your tablespace, unchanged, taking up space. This space still counts against your user tablespace quotas, as well as filling up the tablespace. It will not be reclaimed until you get the table out of the recyclebin.  They just get renamed with a prefix of BIN$$.  There are two recyclebin views: user_recyclebin and dba_recyclebin.  The synonym recyclebin points to the user_recyclebin.

Each user has their own recycle bin and here is how you can access it:

select * from recyclebin;

You can also see the contents of the bin by just doing:

show recyclebin

In order to remove the contents of the bin, you can purge it:

purge recyclebin;

And here is how you can disable/enabled recyclebin in 10g:

10gR1
——

Disable:
ALTER SYSTEM SET “_recyclebin”=FALSE SCOPE = BOTH;
Enable:
ALTER SYSTEM SET “_recyclebin”=TRUE SCOPE = BOTH;

10gR2
——

Disable:
ALTER SYSTEM SET recyclebin=OFF scope=BOTH;
Enable:
ALTER SYSTEM SET recyclebin=ON scope=BOTH;

In order to recover the table from the recyclebin, you can use the flashback command:

flashback table xyz to before drop;

It just renames the dropped object back to it’s original name.

Posted in Oracle | Leave a Comment »

Tablespace Space Usage Information in Oracle

Posted by decipherinfosys on February 27, 2007

Here is a handy SQL that you can use to get the space usage for your tablespaces:

rem  Script: db_space_info.sql
rem  Purpose: This script returns the tablespace space usage information

set linesize 120 pagesize 1000 feedback off echo off
set underline =

ttitle left –
“Tablespace Usage Report” skip 2

col tablespace_name     heading ‘Tablespace’      format a30 truncate
col total_maxspace_mb   heading ‘Megs|Max Size’   format 9G999G999
col total_allocspace_mb heading ‘Megs|Allocated’  format 9G999G999
col used_space_mb       heading ‘Megs|Used’       format 9G999G999D99
col free_space_mb       heading ‘Megs|Free Till Max’       like used_space_mb
col free_space_ext_mb       heading ‘Megs|Free Till Ext’       like used_space_mb
col pct_used            heading ‘%|Used’          format 999D99
col pct_free            heading ‘%|Free’          like pct_used

break on report
compute sum label “Total Size:” of total_maxspace_mb total_allocspace_mb used_space_mb –
free_space_mb (used_space_mb/total_maxspace_mb)*100 on report

select /*+ALL_ROWS */
alloc.tablespace_name,
alloc.total_maxspace_mb,
alloc.total_allocspace_mb,
(alloc.total_allocspace_mb – free.free_space_mb) used_space_mb,
free.free_space_mb+(alloc.total_maxspace_mb-alloc.total_allocspace_mb) free_space_mb,
free.free_space_mb free_space_ext_mb,
((alloc.total_allocspace_mb – free.free_space_mb)/alloc.total_maxspace_mb)*100 pct_used,
((free.free_space_mb+(alloc.total_maxspace_mb-alloc.total_allocspace_mb))/alloc.total_maxspace_mb)*100 pct_free
FROM (SELECT tablespace_name,
ROUND(SUM(CASE WHEN maxbytes = 0 THEN bytes ELSE maxbytes END)/1048576) total_maxspace_mb,
ROUND(SUM(bytes)/1048576) total_allocspace_mb
FROM dba_data_files
WHERE file_id NOT IN (SELECT FILE# FROM v$recover_file)
GROUP BY tablespace_name) alloc,
(SELECT tablespace_name,
SUM(bytes)/1048576 free_space_mb
FROM dba_free_space
WHERE file_id NOT IN (SELECT FILE# FROM v$recover_file)
GROUP BY tablespace_name) free
WHERE alloc.tablespace_name = free.tablespace_name (+)
ORDER BY pct_used DESC
/
prompt
prompt
clear columns
clear breaks
clear computes
ttitle off

Posted in Oracle | Leave a Comment »

VMWare Releases Workstation 6.0 Beta

Posted by decipherinfosys on February 27, 2007

VMWare recently released the 6.0 version of its Workstation virtualization solution. VMWare Workstation is intended as a single-user product, and is essentially an application installed on top of a Windows operating system.

New in Workstation 6.0

The big news with this latest release is that Workstation 6.0 supports Windows Vista as the host operating system. Previous versions of Workstation provided support for Vista virtual machines, but had to run on either a Windows 2000/2003/XP host operating system. Other features include support for both 32-bit and 64-bit operating systems, dual-monitor capability, and support for USB 2.0 devices.

In addition to the above-mentioned features, Workstation 6.0 offers two other new features that will be of particular interest to developers:

Virtual Debugging– Workstation 6.0 now comes with virtual debugging capabilities, which integrates with development tools such as Visual Studio and Eclipse so that developers can deploy, test, and debug programs directly within the virtual machine.

VM Record/Play – This is an experimental feature offered in the 6.0 Beta release that allows users to record the activity and configuration changes within a virtual machine over a given period of time, which then allows you to accurately duplicate the operations and configuration of the virtual machine during the time of the recording. This will be an incredibly handy tool to help pinpoint when certain changes (good or bad) were made to the virtual machine.

VMWare Workstation 6.0 is available for download at: http://www.vmware.com/products/beta/ws/ , also be sure to review the release notes and documentation of this interesting new release.

Posted in VMWare | Leave a Comment »

What is Microsoft Forefront?

Posted by decipherinfosys on February 27, 2007

Microsoft Forefront is a complete line of business security products that encompasses security for all aspects of an organization’s infrastructure. The goal of Forefront is to bundle security features into one comprehensive package that provides ease of use through central administration. Forefront can be integrated with existing third-party security solutions.

Most organizations today have security measures in place for their information technology infrastructure, including anti-virus, firewalls, intrusion detection, spam filters, etc. Unfortunately, administration of theses measures is often disbursed, and can even be the responsibility of completely different groups. So, this essentially means that an organization’s security is only as effective as how well these different groups interact. Ensuring that various security solutions are administered effectively can be a headache in even the smallest of organizations, and with Forefront, Microsoft hopes to ease this pain.

Forefront is divided up into 3 realms: Server Security, Client Security, and what Microsoft calls Edge Security. Server Security includes protection for Exchange and SharePoint, Client Security provides malware protection for laptops and desktop PCs, and Edge Security revolves around Microsoft’s Integrated Security and Acceleration (ISA) Server, which provides network security along with VPN capabilities for remote client access. The Forefront product line breaks down as follows:

  • Client Security
  • Forefront Security for Exchange Server
  • Forefront Security for SharePoint
  • Forefront Security for Office Communications Server
  • Internet Security and Acceleration (ISA) Server 2006
  • Intelligent Application Gateway(IAG) 2007

Client, Server, and Edge Security solutions can be trial downloaded separately directly from the Microsoft Forefront homepage: http://www.microsoft.com/forefront/default.mspx

Is a bundled security solution like Forefront too good to be true? Perhaps. Past experience has shown most administrators that separate, dedicated security solutions are much better at what they do than a solution that offers multiple security functionality. However, if one looks more closely at the Forefront solution, one realizes that it is comprised of many components that have previously been available as separate standalone solutions. Microsoft’s attempt to combine these components under one administrative umbrella would certainly be a welcome improvement.

Posted in Security, Windows | Leave a Comment »

Apple Delays iTV Release

Posted by decipherinfosys on February 27, 2007

Apple Computer’s Apple TV product, or iTV as it has been commonly called, will not ship until sometime in March. The device is a box that is hooked up to a television, and can download movies and TV shows to a Mac or PC to be played on other televisions. The unit also allows users to synch up any number of iTunes libraries located on different components (PCs, laptops, etc).

You can view all of the details of Apple TV on Apple’s website: http://www.apple.com/appletv/

Posted in News, Technology | Leave a Comment »

Using getdate() in a UDF

Posted by decipherinfosys on February 27, 2007

You might have tried using the getdate() non-deterministic function within a UDF some time and would have gotten an error stating:

“Invalid use of ‘getdate’ within a function”

That is because getdate() is a non-deterministic function (functions that do not always return the same result from the same input) and because of that restriction in SQL Server, it cannot be used within a function.  There are ways to get around this limitation though.  One of the ways is to create a view and then use it, for example:

create view GETDATE_VIEW
as
select getdate() as CurrentDateTime
go

create function dbo.GETDATE_FUNCTION
(
@datetime datetime
)
returns bit
as
begin
declare @rc bit
select @rc = case when @datetime < CurrentDateTime then 1 else 0 end
from GETDATE_VIEW

return @rc
end
go

select dbo.GETDATE_FUNCTION(getdate())

—–
0

However, do note that this view hack can return un-expected results depending upon how you are using it.  Here are two UDFs, one using the view and the other one does not:

CREATE FUNCTION UDF_1()
RETURNS datetime AS
BEGIN
RETURN (SELECT currentdatetime FROM getdate_view)
END
GO
CREATE function UDF_2
(@dt datetime)
RETURNS datetime
AS
BEGIN
RETURN @dt
END
GO
–Now, let’s see what we get when we execute a query with both the UDFs

declare @dt datetime
set @dt = current_timestamp
select distinct dbo.UDF_1(), xtype from sysobjects
select distinct dbo.UDF_2(@dt), xtype from sysobjects

On my instance and the database that I was using, the first one returned 16 rows and the second one returned 10 rows.  This has to do with when the function gets evaluated and thus the view one can return different results for records and end up with more rows within the same statement execution since it is getting evaluated per record.

Another way of doing is by using an openquery():

create FUNCTION dbo.UDF_3
()
RETURNS datetime
AS
BEGIN
DECLARE @dt datetime
SELECT @dt = getdate_data FROM OPENROWSET(‘SQLOLEDB’, ‘Server=.;Trusted_Connection=yes;’,’SELECT getdate() AS getdate_data’)
RETURN @dt
END
GO

select distinct dbo.UDF_3(), xtype from sysobjects

This returned a lot more distinct values for the getdate() values.  So, your best bet would be to pass in a parameter after assigning it the getdate() value and then use it within the UDF or if possible, use a stored procedure instead of the UDF.

Posted in SQL Server | 2 Comments »