Embarcadero Blogs

Latest Posts


Subqueries in select clause : ouch

Better formatted at http://tinyurl.com/yfrjbwx

Query 2
The VST diagram looks like

There are two interesting things about this diagram.
Every thing is OUTER JOINED to F_OUTER
There are correlated subqueries in the select
There are two things to check - what is the effect of the OUTER JOINS. The OUTER JOINS can easily mean that all joins into F_OUTER don't change the result set size. Let's confirm by looking at the TTJ sizes:

The only thing that bounds the number of rows returned by F_OUTER is it's self join (the line going in and out of F_OUTER) on the bottom left of F_OUTER.
What this means is that it doesn't matter what order we join tables into F_OUTER.
Now we can turn to the other interesting thing about the query. There are 4 correlated subselects in the select clause. This is generally a bad idea. AFAIK, Oracle doesn't merge subqueries in the select, and certainly not the ones in this query.
In other words, we have 4 queries in th select clause that are going to be run 845,012 times - that is one heck of a lot of work. BAD IDEA.

The subqueries in the select clause look like

select CASE WHEN F.f1 IS NULL
THEN NULL
ELSE (SELECT X.f2
FROM X
WHERE code_vl = F.f1)
END AS f0
from F;


and should be merged into the query like:
select CASE WHEN F.f1 IS NULL
THEN NULL
ELSE ( X.f2)
END AS f0
from F , X
where code_vl(+) = F.f1;


( NOTE: the first query will break if the correlated sub query returns more than one value where as the second query will return the mulitple rows.)

The VST diagram can't tell you that this is the solution, but they can point out that the place to spend your time is the subqueries in the select.

posted @ Mon, 08 Feb 2010 16:13:00 +0000 by Kyle Hailey


I found TButton!!!

TButton with a bonus Backstage Pass - Buy It Now!

RAD enough for Delphi? Buy It Now!

Can you tell I’m testing if someone will hit the Buy It Now? ;)


posted @ Sun, 07 Feb 2010 15:07:56 +0000 by Anders Ohlsson


Random Thoughts on the Passing Scene #143

  • Hey, feel free to weigh in on Delphi as your favorite language over at StackOverflow. Hat tip to Moritz Beutel for his answer.
  • The Haitian Relief Auctions are going like gangbusters.  We even made the Santa Cruz Sentinel!  (Way to go, Anders! And hey, I even got my name in the papers!)  Many of the items end on Sunday evening here on the US west coast, so you only have a little bit of time left to bid.  But we have been adding items as the week goes on, and we will be adding more this week as well, so if you want to  jump in, please feel free. (For instance, Anders just added this “only one in the whole world” Borland Skateboard! )  We are very grateful for all the bidding that has gone on so far, as are the people of Haiti, I’m sure.
  • Don’t freak out too much, but I am actually doing some coding for the next release. Well, I’m actually adapting some great work done by John Kaster and making it fit into the RTL, as well as componentizing it.  Anyway, as I look at it, I realize that it is a good opportunity to use class constructors.  But I wasn’t 100% sure on how they worked, and luckily for me, Allen wrote all about it while back.  This is a pretty cool feature, so if you haven’t given it a look, you should.
  • WooHoo!  I finally got my first gold badge on StackOverflow
  • As I’ve mentioned before, we are cleaning up and generally getting rid of things in anticipation of moving to new offices at the end of March.  Some of these guys around here have been in their offices for a while, and so when they start excavating, they find some interesting things. Chris Hesik found a copy of an original marketing analysis for the Pascal Language from 1993.  Pretty fun to read – especially given that I created a Windows95 Virtual PC and put Borland Pascal for Windows on it.  I’ll be loading up Delphi 1.0 this weekend.  Man, how did we ever get any work done with those basic tools?  ;-)
  • Alex has been busy improving the amazing DeHL framework.  He’s working on an object serialization framework.   If you haven’t taken a look, you should.  Very impressive stuff.  We are quite happy to have Alex on our RTL team now.
  • Robert Love has been on the bleeding edge of using Delphi 2010’s new RTTI features.  He’s done some pretty cool stuff with it – attributes, databinding, and more.  Here’s a look at how he’s using all this new power to create a prototype for an object binding framework.

posted @ Sat, 06 Feb 2010 22:50:29 +0000 by Nick Hodges


Oracle: SQL*Net Waits

article better formatted at

Introduction




Unfortunately, what Oracle calls "Network Waits" have little to do with Network but and almost exclusively to do with the time it takes to pack messeges for the network before they are sent.

Client = you, the tool, sqlplus, application
the shadow process is communicating to the client

Of the three waits, only "more data" is possibly related to network issues and that's not even clear, the other two are simply the time it takes to pack a message before sending it.

SQL*Net message to client - time to pack a message (no network time included) possibly tune SDU
SQL*Net more data from client - possible network issues, possibly tune SDU
SQL*Net more data to client - time to pack a message (no network time included) possibly tune SDU

The same events exist, but where the client is the shadow process and another database plays the roll of shadow process:

SQL*Net message to dblink
SQL*Net more data from dblink - possible network issues, possibly tune SDU
SQL*Net more data to dblink

SQL*Net Wait Events


SQL*Net message from client



Idle Event
Waiting for work from Client
Includes network transmission times for messages coming from shadow
Typically indicative of Client “think time” or “processing time”

SQL*Net message to client



Time it takes to pack a message to be sent to the client
Doesn’t include network timing
see Tanel Poder's analysis of SQL*Net message to client

SQL*Net more data to client

Same as SQL*Net message to client except this is for data that spans SDU packets.
Wait represents the time it takes to pack data.
Doesn’t include network timing

SQL*Net more data from client



The only SQL*Net wait that can indicate a possible NETWORK problem
Client is sending data to shadow that spans packets (think large data inserts, possibly large code blocks, large SQL statements)
Shadow waits for next packet.
Can indicate network latency.
Can indicate a problem with the client tool

Here is an example with ASHMON where the application server died mid-stream on inserts. The shadow processes were left waiting for completion of the message. You can see the regular load on the database on the left, then just past the middle the load crashes, and all that's left is waits on "SQL*Net more data from client"



Possibly set SDU=32768 as well as setting RECV_BUF_SIZE and SEND_BUF_SIZE to 65536.

SQL*Net break/reset to client

Error in sql statement
Control C
Usually highlights and error in application

Example:

CREATE TABLE T1 (C1 NUMBER);
ALTER TABLE T1 ADD
(CONSTRAINT T1_CHECK1 CHECK (C1 IN ('J','N')));
ALTER SESSION SET EVENTS
'10046 TRACE NAME CONTEXT FOREVER, LEVEL 12';
INSERT INTO T1 VALUES (1);

Trace File

PARSING IN CURSOR #2 len=25 dep=0 uid=0 oct=2 lid=0 tim=5009300581224 hv=9816834
09 ad='8e6a7c10'
INSERT INTO T1 VALUES (1)
END OF STMT
PARSE #2:c=0,e=2770,p=0,cr=2,cu=0,mis=1,r=0,dep=0,og=1,tim=5009300581220
BINDS #2:
EXEC #2:c=0,e=128,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=5009300581418
ERROR #2:err=1722 tim=512952379
WAIT #2: nam='SQL*Net break/reset to client' ela= 31 driver id=1650815232 break?
=1 p3=0 obj#=-1 tim=5009300581549
WAIT #2: nam='SQL*Net break/reset to client' ela= 92 driver id=1650815232 break?
=0 p3=0 obj#=-1 tim=5009300581662

DBLINK SQL*Net Waits



These waits are the same as

SQL*Net message to dblink
SQL*Net more data from dblink
SQL*Net more data to dblink
SQL*Net break/reset to dblink

Analysis and Tuning

There isn't much to do on the Oracle side for tuning. You can try optimizing the SDU and SEND_BUF_SIZE and RECV_BUF_SIZE.
For actually getting information on network speeds you will have to use something like
  • ping
  • tnsping
  • network sniffe

SDU

The default SDU can be set in the sqlnet. ora
If it's not set, the default is 2048
The max is 32768
The default,or the value in sqlnet.ora, can be overridden in the tnsnames. ora and the listener.ora. The client and server negotiate the size aggreeing on the smaller of the two settings.
(TDU – Transmission Data Unit – see note 44694.1 The TDU parameter has been deprecated in the Oracle Net v8.0 and beyond and is ignored. It is only mentioned here for backward compatibility.)


tnsnames.ora

V10G = (DESCRIPTION =
(SDU=32768)
(ADDRESS = (PROTOCOL = TCP)(HOST = fuji)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED) (SERVICE_NAME = v10g)
) )

listener.ora

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SDU=32768)
(SID_NAME = v10g)
(ORACLE_HOME = /export/home/oracle10)
))

Tracing

sqlnet.ora

trace_level_client=16
trace_directory_client=/tmp
trace_file_client=client.trc
trace_unique_client = true
trace_level_server=16
trace_directory_server=/tmp
trace_file_server=server.trc

client.trc

client_3582.trc:[12-JAN-2008 11:37:39:237] nsconneg: vsn=313, gbl=0xa01, sdu=32768, tdu=32767


RECV_BUF_SIZE and SEND_BUF_SIZE

The recommended size for these buffers (from Oracle's docs) is at least

Network bandwidth * roundtrip = buffer min size

For example if the network bandwidth is 100mbs and the round trip time (from ping) is 5ms then

100,000,000 bits 1 byte 5 seconds
---------------- x ------ x --------- = 62,500 bytes
1 second 8 bits 1000


tnsnames.ora

V10G = (DESCRIPTION =
(SEND_BUF_SIZE=65536)
(RECV_BUF_SIZE=65536)
(ADDRESS = (PROTOCOL = TCP)(HOST = fuji)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED) (SERVICE_NAME = v10g)
) )

listener.ora

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SEND_BUF_SIZE=65536)
(RECV_BUF_SIZE=65536)
(SID_NAME = v10g)
(ORACLE_HOME = /export/home/oracle10)
))


sqlnet.ora

RECV_BUF_SIZE=65536
SEND_BUF_SIZE=65536


posted @ Sat, 06 Feb 2010 13:50:00 +0000 by Kyle Hailey


Signed books, mouse, stickers, and more

I just added two signed books, Xavier Pacheco’s and Steve Teixeira’s "Delphi 2 Developer’s Guide" (my copy) and Danny Thorpe’s "Delphi Component Design" (Steve Trefethen’s copy). Both books were signed by the authors.

I also added two floppy disk sets - Delphi 1, and BP7, David I’s leather jacket and a Borland mouse.

Borland Memorabilia Auctions for Haitian Relief


posted @ Sat, 06 Feb 2010 20:33:43 +0000 by Anders Ohlsson


Santa Cruz Sentinel article

"SCOTTS VALLEY — If you are secretly pining over old Borland memorabilia — the classic Pascal 3.0 software still in its original shrink wrap, a Borland varsity letter jacket once reserved for executives or a shiny yellow bumper sticker proclaiming that "Delphi Developers Do It Faster" — this is your chance to bid in an unusual auction and have your purchase support recovery efforts in Haiti."

"Borland memorabilia auctioned for Haiti recovery" - Jennifer Pittman


posted @ Sat, 06 Feb 2010 14:16:39 +0000 by Anders Ohlsson


Haitian Relief Auction Update - $3,582!

Borland Memorabilia Auctions for Haitian Relief

First wave of auctions (about 50) end this Sunday at 6pm PST.

Happy bidding!


posted @ Fri, 05 Feb 2010 21:23:24 +0000 by Anders Ohlsson


A Walk Down Delphi’s Memory Lane: "Pascal Market Trends" circa 1993

As Nick mentioned in this post, we will soon be moving to a new building here in Scotts Valley.  We have been in our current building since 1993.  As Nick states:  "So when you move, you generally use that as an opportunity for cleaning up, clearing out and generally clearing away the build up of years and years of hard work.  As we are doing that, we are finding tons of cool old stuff from “back in the day”."

As I have been gradually cleaning things up in my office over the last few weeks, I came across a file folder in a drawer with some interesting old documents.  One, in particular, is an internal document, titled "Languages Two Year Plan, Second Draft - March 25, 1993".  It describes the market, opportunities, competitive landscape, and plans for Borland’s C++ and Pascal product lines.  Quoting from the "Executive Overview", it was "intended to be an overview of the development activities for Languages for the 24 months beginning January 1993".

Since the 15th anniversary of Delphi’s initial release is approaching, I though it might be fun (and interesting) to look back at some items in this document — though the document was produced nearly two years before Delphi’s initial release, and though it still focuses on the "Borland Pascal" product line, it clearly shows the trend towards what would become Delphi.  I’ll have a couple of posts over the next week or so, leading up to Delphi’s 15th anniversary on February 14th, 2010.  Note, since I only have a hard-copy of this document, I’ll be transcribing it manually, so it is likely that any typos, misspellings, etc. are my own and not part of the document.

To start, here are the first two sections of the "Pascal" part of the document:

Pascal Overview

In January 1994, we will release major new versions of Borland Pascal for Windows and DOS.  The Windows product will be built around easy-to-use visual tools that leverage existing Borland technology in order to bring our DOS customers into Windows.  The DOS product will provide incremental improvements so that we continue to generate upgrade revenues.  Six to nine months later, we will follow up with 32-bit versions of Pascal for both Windows and DOS.  This 32-bit compiler code base will enable us to continue to harvest upgrade revenues with new versions.

Pascal Market Trends

There are three important trends affecting the language market place in 1993.  These are:

  1. Increasing demand for visual tools on Windows
  2. Increasing awareness of 32 bit tools
  3. Decreasing importance of DOS

Microsoft’s Visual Basic and imminent Visual C++ have changed the competitive landscape by shifting the emphasis from OOP to visual tools.  Visual tools have a greater impact on simplifying Windows development, so we must address this area if we are to compete successfully, just as other language companies had to supply integrated development environments and OOP features in response to innovations that first appeared in Turbo Pascal and Turbo C.

Visual Basic has significant weaknesses in language, performance and scalability, that limit its acceptance.  This gives us an opportunity to deliver products which simplify Windows programming and offer more power and performance.  Many Pascal customers are asking for a "Visual Pascal" that provides the ease of use of Visual Basic with the extensibility, power and performance of Turbo Pascal.

Just as Microsoft leveraged their domination of the Basic language market to create Visual Basic, we can leverage our domination of the Pascal market to make changes to the language, compiler, and libraries to make Windows development easy.  This is the best way to bring our large installed base of DOS customers into the Windows market.

There is also an increasing awareness of 32 bit development tools.  Shipment of Win32s and NT in 1993 will make "32 bit" a requirement of any language.  However, large volume deployment of 32 bit applications will probably not happen until Microsoft ships the "Chicago" version of 32 bit Windows on 32 bit DOS, in mid 1994.  Pascal customers continue to ask for 32 bit versions to overcome long standing limitations in Pascal’s global data space, data structure sizes, ability to link with standard OBJ files etc.

Although Windows development tools get the most press coverage, DOS products continue to bring in significant revenues.  Many DOS programmers will continue to upgrade as long as we offer incremental improvements in the language, libraries and tools.  But this revenue stream will decline over time if there is no clear commitment to Pascal on 32 bit DOS and Windows.

Up Next:  Excerpts from the "Pascal Strategy" section


posted @ Fri, 05 Feb 2010 16:48:36 +0000 by Chris Hesik


A case against FreeAndNil

I really like the whole idea behind Stackoverflow. I regularly read and contribute where I can. However, I’ve seen a somewhat disturbing trend among a lot of the answers for Delphi related questions. Many questions ask (to the effect) “why does this destructor crash when I call it?” Invariably, someone would post an answer with the seemingly magical incantation of “You should use FreeAndNil to destroy all your embedded objects.” Then the one asking the question chooses that answer as the accepted one and posts a comment thanking them for their incredible insight.

The problem with that is that many seem to use FreeAndNil as some magic bullet that will slay that mysterious crash dragon. If using FreeAndNil() in the destructor seems to solve a crash or other memory corruption problems, then you should be digging deeper into the real cause. When I see this, the first question I ask is, why is the instance field being accessed after that instance was destroyed? That typically points to a design problem.

FreeAndNil itself isn’t the culprit here. There are plenty of cases where the use of FreeAndNil is appropriate. Mainly for those cases where one object uses internal objects, ephemerally. One common scenario is where you have a TWinControl component that wraps some external Windows control. Many times some control features can only be enabled/disabled by setting style bits during the creation of the handle. To change a feature like this, you have to destroy and recreate the handle. There may be some information that is stored down on the Windows control side which needs to be preserved. So you grab that information out of the handle prior to destroying and park that data in an object instance field. When the handle is then created again, the object can look at that field and if it is non-nil, it knows there was some cached or pre-loaded data available. This data is then read and pushed back out to the handle. Finally the instance can then be freed by FreeAndNil(). This way, when the destructor for the control runs you can simply use the normal “FCachedData.Free;” pattern since Free implies a nil check.

Of course there is no hard-and-fast rule that says you should not use FreeAndNil() in a destructor, but that little “fix” could be pointing out that some redesigning and refactoring may be in order.


posted @ Fri, 05 Feb 2010 16:10:20 +0000 by Allen Bauer



Server Response from: blog1.codegear.com