QueryAdvisor.com

Oracle Sunrise

noreply@blogger.com (f.b.) — Sun, 10 Nov 2013 13:26:00 +0000

Looking at the Oracle headquarter during sunrise from my hotel room was one of the highlights last week in California. Just want to share it with you.

Sunrise over Oracle HQ

Oracle SQL - NULL Values and Zero Strings

noreply@blogger.com (f.b.) — Sun, 29 Sep 2013 11:46:00 +0000

Following ANSI SQL 1992 standard empty or zero lenght strings and NULL are different. So I was a little bit surprised after executing the following queries on an Oracle 11R2 database last days:

a) As a Result:
select length(null) from dual; -- NULL as expected
select length('') from dual; -- NULL too, what was a bit unexpected
select length(' ') from dual; -- 1 as expected

b) In a Comparison:
select dummy from dual where '' = ''; -- Instead of 'X' no result, comparison failed
select dummy from dual where '' = NULL; -- Same as above, not a real surprise
select dummy from dual where '' IS NULL; -- The expected result 'X'

Searching at "Ask Tom" I found the following statement:

and we said...
A zero length varchar is treated as NULL.
'' is not treated as NULL.
'' when assigned to a char(1) becomes ' ' (char types are blank padded strings).
'' when assigned to a varchar2(1) becomes '' which is a zero length string and a zero length string is NULL in Oracle (it is no long '')

In the Oracle SQL Language Reference I found in addition the following comment regarding the difference of VARCHAR and VARCHAR2:

VARCHAR Datatype
Do not use the VARCHAR datatype. Use the VARCHAR2 datatype instead. Although the VARCHAR datatype is currently synonymous with VARCHAR2, the VARCHAR datatype is scheduled to be redefined as a separate datatype used for variable-length character strings compared with different comparison semantics.

So what does that all mean from a practical point of view:

Oracle does not handle null values for VARCHAR ANSI SQL 92 conform as the empty string is converted to a null value.
Always use VARCHAR2 out of compatibility reasons as for VARCHAR Oracle has reserved the right to change the comparison semantics at a later stage to become ANSI SQL 92 compatible.
For all CHAR types due to padding for '' everything is padded with spaces.
The behaviour of empty or zerlo length Strings described is the same in all Oracle releases.

This behaviour could become important when we think about query design for Database Optimization and Tuning. Enjoy & experiment on your own!

Oracle Wait Event Forcasting now implemented!

noreply@blogger.com (f.b.) — Sat, 07 Apr 2012 23:57:00 +0000

The forcast functionality has been released for QueryAdvisor. It is now possible to collect and analyze Oracle Wait Events on Oracle instance and session level over long periods of time.

The results of the already existing funtionality for advanced Oracle instance and session analysis are now collected and automatical sorted by database instance in the background. This offers the possiblity to dedect changes in the type, amount and execution-time of Oracle Wait Events on instance and session level.

As possible restarts of the database instance are automatical dedected, the quality of the calculation is exceptional as ambiguous data could be filtered out upfront. The results are graphical presented and could be exported in addition.

A trial copy of our release of QueryAdvisor is waiting for You!

QueryAdvisor now for Mac OSX available too!

noreply@blogger.com (f.b.) — Sat, 04 Feb 2012 12:51:00 +0000

QueryAdvisor is now available as a real 64Bit app on Mac OSX. The final changes has been made and everything has been bundled and packed ready for rollout!

The most time consuming process had been the changes to get the real Mac feeling. Accelerators had been added. All Popups are working. The Dock and the Menu Bar behave as it should be for real Mac software.

If you own an Intel based Mac with Snow Leopard or Lion installed, why not be part of the test team and work with QueryAdvisor for free? Just let me know. If you know a DBA with an Intel based Mac - just let him know.

A free copy of our new Mac OSX release QueryAdvisor is waiting for You! Just drop me a line and I will send you a link for downloading.

Support QueryAdvisor on Google PlusOne!

noreply@blogger.com (f.b.) — Sat, 02 Jul 2011 10:40:00 +0000

The new Google PlusOne button is now available on all content related pages of our QueryAdvisor site. The button is placed on most pages in the right column below the twitter and the other social bookmarking icons.

If you like what we are doing, why not supporting us with a CLICK on one of the Google PlusOne buttons on our QueryAdvisor site?

This will help us to detect the areas you are interested in and - yes - will help us in promoting our site a bit.

Thanks in advance thinking about supporting us!

Newsletter 1/2011 now available!

noreply@blogger.com (f.b.) — Sun, 12 Jun 2011 21:53:00 +0000

Our newsletter 1/2011 is now published in a new fresh design. You will find there information about the exciting new enhancements available in QueryAdvisor V2.90.

The most important new feature - the 360° Tracefile View is described in more detail with a detailed screenshot. There is nothing similar available for Oracle Tracefile Analysis so far!

The newsletter 1/2011 is available here for reading now!

Registration for QueryAdvisor Betatest started!

noreply@blogger.com (f.b.) — Sun, 08 May 2011 14:37:00 +0000

We are starting our official public betatest of QueryAdvisor now. Our target audience are Oracle database administrators independent from the releases they have worked with and the level of individual experience.

If you like to apply for becoming an official betatester with all benefits involved you will find additional information here.

The betatest will start with our new QueryAdvisor V2.90 release offering amazing new functionality in dealing with hugh amounts of Oracle event 10046 tracefiles.

Don't miss this opportunity and learn more here now!

New QueryAdvisor V2.90 Release End of April!

noreply@blogger.com (f.b.) — Sun, 17 Apr 2011 13:15:00 +0000

The new QueryAdvisor V2.90 release will be online end of April. Right now there are the final tests on bulk processing of Oracle trace files runnning.

This release should be the foundation of the overdue betatest. The new 360° views across all processed Oracle event 10046 trcfiles are available there for the first time. I think that crossreferencing all tracefiles and open cursors is a great feature and it was worth waiting!

Before I forget to mention: All licensekeys remain valid! An upgrade to QueryAdvisor V2.90 will therefor be straight forward:
1) Download the new QueryAdvisor release.
2) Deinstall the old installed software. Modified/New data is left untouched!
3) Install the current release of QueryAdvisor. Copy your own data to the same direcory.
4) During the start the used parameterfile will be updated on the fly but only stored after confirmation.

And Voilà... Upgrade completed!

There will be a Tweet with a download link provided when our tests had been completed!

SQL*Net and Transparent Network Substrate

noreply@blogger.com (Unknown) — Wed, 09 Mar 2011 01:49:00 +0000

SQL*Net is the software provided by Oracle to establish connections between local Oracle database clients and the related Oracle database instance or between two different Oracle database instances via database link. It is based on the Transparent Network Substrate (TNS) protocol.

TNS is a generic logical protocol which could be executed on top of all common physical network protocols such as TCP/IP. It is based on individual logic packets which are mapped transparently to physical packets.

TNS must be able to handle the different Oracle database server connectivity scenarios:
- shared server database instance
- dedicated server database instance
- dedicated server bequeath connection
(database client and database instance must be on the same hardware)

Visit the QueryAdvisor Knowledge Center to read more about the Transparent Network Substrate.

YouTube Channel now Online!

noreply@blogger.com (f.b.) — Sun, 16 Jan 2011 10:41:00 +0000

We have loaded new videos to our QueryAdvisor Channel at YouTube. It would be great if you have the time to browse through. Probably there is room for improvement...

QueryAdvisor now on Blogspot.com!

noreply@blogger.com (f.b.) — Sun, 16 Jan 2011 00:02:00 +0000

We have now our own blog at QueryAdvisor.blogspot.com. We will inform you there about new features and ideas. Your comments are highly welcomed! Come & See...