Children

Nothing work or oracle related here... I have been lucky enough to have two wonderful children, a great daughter who I am proud of , a very artistic and quiet girl with many friends. Then there is my son, Honour roll 3 years in a row, principles roll, achievement awards and generally a wonderful boy who I am proud of as well. I tend to talk about my kids far too much, I am sure my coworkers are thoroughly tired of hearing about me going on about my children. But all things must come to an end. Nothing horrible here, or tragic. Just my son even though he is 12, has decided that being a teenager is the thing for him. With the "teen angst" to go with it. I was hoping it would be put off for at least a few more years. But, I have a "wannabe" teen in the house... I hope it is worse than having a real teen, I can't see how it can really be any worse. My mother is probably STILL laughing, it is amazing how cruel parents can be. ;)

Google does it again

at http://pages.google.com Now that is easy to use web page development. Simply, easy. And I found a great use for it, storing files that can be referenced from this blog. I modified the testing I did with Jonathon Lewis's script testing of FTS or not with an impossible predicate and instead of having a huge blog post, now it is a short blog post with a link to the output - much much cleaner. Good for posting scripts too. You can expect more scripts to be posted shortly. http://herodt.googlepages.com/home is the page I banged together.

Contractors

We have a contractor working remotely helping us with performance problems with one of our off the shelf applications, nice enough fellow, very knowledgeable about the application as well as the business logic on why the application does what it does. This was to a particular module in the application, not application wide. The database isn't the performance issue, it is of course the first thing blamed. But luckily management has been proven the fact that the database isn't the issue, instead it was bad design from the vendor in the application and database - hence the good, but brutily expense contractor. I had a bit of a run in with him today, he must have had a bad day and I somehow earned the right to have him rip a strip off of me. A few weeks ago I had started monitoring on all of the indexes in the production database (built in 9i monitoring) and had earlier in the week passed him a spreadsheet of the data. This was being done because the module in production was just not performing well. Well, he was very upset at the results as it appeared about $8K worth of his consulting time of creating and modifying indexes had shown very little result in the production database - even though the results had been stellar in the test database. He started in on how some configuration differences had to exist between the two databases and I had better find the differences and fix them immediately. Not his exact words, but I wouldn't communicate language like that to others sensitive ears. After a few minutes of this, I calmly hung up on him in mid sentence and went about my business trying hard to not resist the urge to pop into the gas guzzler and pay him a visit (only about 2 hours away) and have a nice face to face conversation with him. A period of time passed, and one of our application support people came by asking what I had done to upset the contractor who was now currently on the phone with a 3 managers painting a unlikable picture of me. I explained the contractors issues to the support person, who looked at me with a twinkle in his eye and asked me to follow him. At his cube dwelling he popped open a change request, from about 2 months ago from the contractor asking the in the production database, an application wide parameter be modified to not append a site code defination to all of the SQL being passed to the database. There was a fair bit of communication on the subject on the change, but in the end it was approved and implemented. I am sure you can guess where this is going. He had in his original effort used the site code to reduce the selectivity on the driving tables in his index creating. We bypassed the change protocols and put the parameter back into place the way it had been. The support person started to use the module to generate reports and generally move about the application to start to execute queries. I trundled on back to my cube and monitored what the kindly support person was doing. Low and behold - the indexes where being used and the SQL was executing effeciently and quickly. The application person took a print out of the change and went to visit with the managers on my behalf. I felt that would be better to do instead of it looking like I was trying to cover my own behind. Much better when others have the capability of doing it for you. A short time passed - under 10 minutes and I receive a call from the contractor. Very apologetic for his own stupidity and promises of un-expensed lunchs for me when he is at the office next week. sigh.

Mr. Jonathan Lewis's article "When 2+2=5"

"From web page of Jonathan Lewis comes his debunking of the myth (Urban legend?) that using the predicate of 1=2 causes a full table scan. On the page When 2 +2=5 Mr. Lewis goes through in detail his tests to prove this myth wrong. This is simply a continuation of his tests on versions 7 through 10xe of oracle to validate or invalidate his conclusions as Mr. Lewis does state this is the same on many versions of oracle but doesn’t provide proof. I did this because a colleague of mine disagreed with Mr. Lewis’s findings and just “knew” that Mr. Lewis was wrong and I had some time at home to kill. I will not draw any conclusions, or make any statements. I have simply provided the complete result sets and let you come to your own conclusions. All of these tests were done in production databases that were busy doing other work at the time, I thought this an appropriate environment instead of a sterile environment. I have provided the output from V$VERSION in every log. The entire output is here I moved the post to a file on my googlepages site as the post was massive and difficult to read. Draw your own conclusions based on the information supplied above.

Oracle Enterprise Manager 10g V2

Finally Oracle has a decent tool for administering the oracle enterprise - and so much more. I have been a staunch well documented and card carrying member of the anti-oem group for many years now. We just got OEM installed onto a linux server and agents on our big HPUX iron and our windoze box. I was concerned when I saw that the agent download for HPUX was 600+ meg. But it installed quick and easy. The install blew up our one and only windows server, but to me, that is a good thing - one more nail in the coffin for us getting rid of the server. It didn't blow up the test windows box, so we were a little surprised. The information is correct, the overhead is minimal, and it just plain works. We are in the process of reducing the number of checking scripts that run and populate a home grown monitoring database and are beginning to rely on OEM. One of the items that pushed me over the edge to allow it to be installed was the appartenly vapourware OEM2GO product. I should have guessed it wasn't as documented since the documentation reads like a marketing brochure. If anybody has managed to use OEM2GO on their PDA's please speak up any useful tips or hidden documentation would be wonderful. I can connect to OEM with my PDA, but it is the full blown version of OEM and the poor PDA can't handle it.

Almost an entry for Oracle WTF

Ok, I know "why" but WHY!! Names have been changed to protect the guilty. SELECT * FROM T1 WHERE (STATUS IN ('STATUS1', 'STATUS2', 'STATUS3') AND T1TYPE NOT IN ('PRICE','BLANK') AND 'SOMETEXTVALUE' IN ((SELECT COMPANY FROM COMPANIES WHERE COMPANY = 'SOMETEXTVALUE' ) UNION (SELECT COMPANY FROM COMPANIES WHERE PARENTCOMPANY = 'SOMETEXTVALUE' )) AND ORGID= 'SOMEORGID') AND ( ((T1TYPE NOT IN ('PRICE', 'BLANK') OR PRIVATE= 'Y' ) AND SITEID= 'SOMESITE' ) OR (T1TYPE IN ('PRICE', 'BLANK') AND PRIVATE= 'N' ) ) / PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------ -------------------------------------------------------------------------------- Id Operation Name Rows Bytes Cost -------------------------------------------------------------------------------- 0 SELECT STATEMENT 49362 7230K 334 1 MERGE JOIN CARTESIAN 49362 7230K 334 2 VIEW VW_NSO_1 2 100 9 3 SORT UNIQUE 2 90 9 4 UNION-ALL * 5 INDEX SKIP SCAN COMPANY_NDX 1 20 2 * 6 TABLE ACCESS BY INDEX ROWID COMPANIES 1 70 3 * 7 INDEX SKIP SCAN COMPANY_NDX 1 2 8 BUFFER SORT 24681 2410K 334 * 9 TABLE ACCESS FULL T1 24681 2410K 163 -------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 5 - access("COMPANIES"."COMPANY"='SOMECOMPANY') filter("COMPANIES"."COMPANY"='SOMECOMPANY') 6 - filter("COMPANIES"."PARENTCOMPANY"='SOMECOMPANY') 7 - access("COMPANIES"."COMPANY"='SOMECOMPANY') filter("COMPANIES"."COMPANY"='SOMECOMPANY') 9 - filter(("T1"."STATUS"='STATUS1' OR "T1"."STATUS"='STATUS2' OR "T1"."STATUS"='STATUS3') AND "T1"."T1TYPE"<>'PRICE' AND "T1"."T1TYPE"<>'BLANK' AND "T1"."ORGID"='SOMEORIGID' AND (("T1"."T1TYPE"<>'PRICE' AND "T1"."T1TYPE"<>'BLANK' OR "T1"."PRIVATE"='Y') AND "T1"."SITEID"='SOMESITE' OR ("T1"."T1TYPE"='BLANK' OR "T1"."T1TYPE"='PRICE') AND "T1"."PRIVATE"='N')) Get a call from a really nice application support person - "Did YOU do anything, our "T1" reports are taking a long time." After a bit of digging - "We applied a patch on the weekend". Took about 30 seconds to track down SQL like the one above. Was ran for every entry (about 1,000 rows) in another table to "validate" the presence of data in T1. My answer "Back the patch out, call the vendor, remind them what a bind variable is, send this nice explanation of a cartesion join to them too." I love my job! (really I do)