Saw a nice option in formatting Oracle explain plans for Oracle 12c for plans that use adaptive plans. Courtesy of Oracle Database 12c: Adaptive Execution Plans with Tom Kyte

 select * from table(dbms_xplan.display_cursor(format=>'+adaptive, allstats last'));

Those options show for adaptive plans exactly which steps were skipped by the optimizer based on row cardinality. Check out the YouTube video above for an example, at 3:41 https://youtu.be/9o9iuxNBciQ?t=221 You may need to do one of the following:

• Add the hint /*+ gather_plan_statistics */ to the statement
• Set the parameter 'statistics_level' is set to 'ALL', at session or system level. Caution: if you set it at the system level, overall performance will be degraded.

Oracle APEX and /*+ result_cache */ of form variables

I was having a tough time with a bug in an Oracle APEX application. I was filling a shuttle-list with a query, and I added the hint /*+ result_cache */ for performance, and thought that Oracle would be peeking at the bind values being used in the query. Wrong! In regular vanilla queries, Oracle will look at bind variables to see if it can use the result cache, but APEX magic prevents that.

Code sample causing issues:

select /*+ result_cache */ PROJECT_ID_NUM as vc1
      from #OWNER#.ru_projects_mvw
      where PROJECT_ID_NUM like v('P215_PROJECT_ID_NUM_SEARCH') 

Well once I took out the hint, all worked as planned!

If you are not familiar with result caching on Oracle, it can make queries that return a small number of rows from large tables run extremely fast.

Actually, if I had coded the query like the following, using bind variable syntax (:P215_PROJECT_ID_NUM_SEARCH) instead of V('') syntax to refer to my form fields, I could probably have used the result_cache hint, and since the V('') syntax is actually calling a PL/SQL procedure, it could be slower than using the bind syntax:

select /*+ result_cache */ PROJECT_ID_NUM as vc1
      from #OWNER#.ru_projects_mvw
      where PROJECT_ID_NUM like :P215_PROJECT_ID_NUM_SEARCH 

Excel VBA "ERROR 2015" returned from Application.GetSaveAsFilename

I was scratching my head over the following Excel Macro code:

Dim vFileName as Variant

...
On Error GoTo ErrorHandler    
vFileName = Application.GetSaveAsFilename(strTaskOrder & ".pdf", _
    "PDF Files (*.pdf), *.pdf", _
    "Select PDF Name for " & strTaskOrder)
On Error GoTo 0

What was interesting is no runtime error was generated, (so I could not use the OnError code; printing the contents of vFileName in the Immediate window was "Error 2015" and the typename(vFileName) was returning "Error", but it was not an "Err" object. But what would work, (thanks to StackOverflow answer to similar issue) I was able to catch whether this was happening:

If IsError(vFileName) Then
    MsgBox "error"
End If

Well the actual problem was that I was omitting a parameter; the FilterIndex (1) of the File Filter; the correct call was:

vFileName = Application.GetSaveAsFilename(strTaskOrder & ".pdf", _
    "PDF Files (*.pdf), *.pdf", _
    1,
    "Select PDF Name for " & strTaskOrder)

Lesson learned: Double-check your parameters!

Federal Tax Cuts Insanity

Well, it looks like the politicians in Washington DC have lost all touch with reality, in order to reach a compromise on the income tax breaks. Seems the compromise includes the worst ideas from both parties. A year-long cut in Social Security taxes will cost $120 billion, but somehow President Obama said there would be no effect on Social Security benefits nor long-term solvency. How could that possibly be?

Another problem is the extension of unemployment benefits, being financed by borrowing, instead of spending cuts. When will the politicians learn that they must stop spending money that they don't have?

Oracle JDBC NullPointerException

Got an interesting Oracle JDBC error with Oracle 10.2.0.4 database and thin JDBC driver. I invoked the method java.sql.Statement.setFetchSize(20000) on a java.sql.PreparedStatement object, and when I invoked the executeQuery() method on the object, I got the following stack trace:

java.lang.NullPointerException
       at oracle.jdbc.driver.DBConversion._CHARBytesToJavaChars(DBConversion.java:974)
       at oracle.jdbc.driver.DBConversion.CHARBytesToJavaChars(DBConversion.java:892)
       at oracle.jdbc.driver.T4CCharAccessor.unmarshalOneRow(T4CCharAccessor.java:199)
       at oracle.jdbc.driver.T4CTTIrxd.unmarshal(T4CTTIrxd.java:919)
       at oracle.jdbc.driver.T4CTTIrxd.unmarshal(T4CTTIrxd.java:843)
       at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:630)
       at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:219)
       at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:970)
       at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1072)
       at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:854)
       at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1154)
       at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3370)
       at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3415)
       at MyOracleProgram.invokeQuery(MyOracleProgram.java:682)

Well I tried setting the value to be 1000 for the parameter:
setFetchSize(1000)
and problem solved!

The default FetchSize for this version of Oracle appears to be 10, and if you know you will usually be getting back more than 10 rows, by all means set it to to a higher value, but if you try something bigger than 1,000 be careful!

Judging from the name of the method oracle.jdbc.driver.DBConversion._CHARBytesToJavaChars(DBConversion.java:974) in the stack trace, I bet there is a array for the result set with a hard-coded maximum array size of something bigger than 1000, but less than 20000. It would be nice if the oracle jdbc driver would catch this.

---

Update: Oracle support noticed I was using the ojdbc14.jar file and using Java JDK 1.6; I had to get the ojdbc6.jar file from the Oracle 11.2 JDBC distribution which is certified for JDK 1.6. Works just fine now, with Oracle 10.2 database, and JDK 1.6.

Health care costs out of control: Solution 3: Don't rush

In the rush to get a health care bill into law, a single amendment to it by Senator Debbie Stabenow was passed by a voice vote. Then later the Only after the amendment passed did the Congressional Budget Office realize it made a mistake in the scoring and under counted the one amendment to the tune of $600 million.

Quoting from the above link "It took Senator John Cornyn (R-TX) to point it out. And now he is offering an amendment of his own to make sure this does not happen again." Basically he suggested congress couldn't vote on something until it had been publicly available for a certain amount of time. His amendment was defeated. Where is the transparency at that the leaders claim this health care reform will have, if no one can look at the proposed law?

Even more telling, is that senators are willing to force Medicaid on those who can't afford it, but are unwilling to accept the same coverage themselves if this bill passes! The New York Times details this unwillingness to be subject to themselves, what they think is good for all us.

Health care costs out of control: Solution 2: Be informed

Read both sides of the issue.

Senator John Cornyn asks some good questions in this commentary on Forbes.com. He mentions everyday Americans are reading the proposed bill H.R. 3200 (text) (PDF), so hey, before I make brash statements about what it will or won't do, I have some light reading to do.

And for a good book on President Obama's health care reform, check out the book Howard Dean's Prescription for Real Healthcare Reform (and glance at my review). I learned a lot about some of the advantages of the proposed reform.

Update: Here's a good starting place to look at the most recent proposals of the Senate version, at a Wall Street Journal blog on health care. 564 amendments to it already! Who could possibly keep up with all this? And what is scary is that this bill will be a long-term change to everything; good or bad; so I think that we need to put more thought into fixing something that took 40 years to go bad, and not try to fix it in 40 days.

The economy is currently far too fragile to risk something this big and costly.