Submitted by dmann on Tue, 02/28/2012 - 00:06
Submitted by dmann on Thu, 02/16/2012 - 23:57
Yes, the mention of Cartesian Joins usually makes a DBA's heart skip a beat. The cartesian joins caused by missing join conditions are definitely a pain to deal with - these are the types of joins that can "blow up" temp space and cause all types of alarms to go off.
I didn't find anything in Oracle's official 11g documentation on this particular join method but I did find plenty of articles about issues with it in their support DB. I have chased down a few of these in the past couple of weeks and here is what I found.
Submitted by dmann on Tue, 02/14/2012 - 12:05
I came across this PL/SQL page today which has pointers to a lot of interesting stuff you can do with PL/SQL.
From the page:
"This library is a collection of various utility packages for PL/SQL, as well as links to useful libraries hosted and maintained elsewhere."
Submitted by dmann on Tue, 01/17/2012 - 15:43
...well not yet...
An Infoworld article was released today describing an Oracle vulnerability. The SCN space can be exhausted via bugs or attackers exploiting bugs. Metalink doc: 1376995.1 [Bulletin : Information on the System Change Number (SCN) and how it is used in the Oracle Database] covers a technical description for what SCNs are used for.
You can use this query to find out how close you are to the SCN limit.
Submitted by dmann on Tue, 01/10/2012 - 13:48
Here are two runs of the same SQL from different programs. The top is an automated process, the bottom is Toad. (SQL is the same, but SQL_ID is different because of white space issues):
So why is one finished in 7.2 minutes and one still at 4.7 hours and still counting up?
Submitted by dmann on Mon, 12/05/2011 - 12:44
I normally get a lot of mileage out of Application Express interactive reports. Unfortunately there are some restrictions on the SELECT statements that can be used with them. Here is a demonstration of a technique I use when I want to add some interactivity to a plain old Apex report.
I will create a Select List dropdown that submits and refreshes the report data automagically. The value in the select list will be applied as a filter to the report when it refreshes.
1) Create the Select List
• Items -> New Item -> Select List
• Choose "Select List with Submit"
Submitted by dmann on Wed, 11/30/2011 - 17:33
I just noticed there is a 11gR2 version of the Developer Days VM available now. You can have a running copy of 11gR2,
Oracle TimesTen In-Memory Database, Oracle XML DB, Oracle SQL Developer, Oracle SQL Developer Data Modeler, Oracle Application Express, Oracle JDeveloper, and Hands-On-Labs (accessed via the Toolbar Menu in Firefox) on a copy of Oracle Enterprise Linux 5.
This is great as a sandbox for development or an easy way to stand up a DB for messing around with. The image supplied by Oracle runs inside Oracle VirtualBox VM engine which is also free.
Submitted by dmann on Sat, 11/26/2011 - 08:39
We moved to a new web host on Black Friday. My old host doesn't appreciate repeat business so it was time to make the change and try something new. I just started up with DreamHost and so far I am impressed. Migration of the site went quicker than expected and so far I only made one mistake that caused any issues but I will blame that on waiting for DNS changes to propagate :)
Submitted by dmann on Tue, 11/22/2011 - 17:34
Had an odd one come across my desk today. I have seen Bind variables used in the WHERE clause thousands of times. I have seen Binds in the SELECT lists hundreds of times. I have seen it used in the HAVING clause zero times... until today when it turns out it was causing a performance issue.
With the Bind variable in the HAVING clause we were seeing an execution plan with lots of Full Table Scans and 12+ second runtimes. When editing the query and using a literal in the HAVING clause we saw 100ms or less performance and very efficient Execution Plans.
Submitted by dmann on Mon, 11/14/2011 - 15:23
If you are using SQL Developer 2.x you can reduce your startup time by about 20% by disabling any unneeded extensions. I was able to reduce my startup time from 11 seconds to 8 seconds by turning off all extensions (none of which I really needed anyway).