OOW 2011 Day 4 and Blogger's meetup

Day four started well with Oracle Optimizer: Best Practices for Managing Optimizer Statistics presented by Maria Colgan. Then followed SQL Tuning Expert Roundtable with Jagan Athreya, Benoit Dageville, Tom Kyte and Graham Wood. These two presentations had a lot in common, correct statistics should be checked if plan is wrong, note correct, but not necessarily updated statistics. If an automatic routine for stats gathering cannot be found the metadata for the table and columns should be set manually and locked. More people talk about skews this day, it was mentioned here also, but I guess James Morle and Cary has more to say on that. The problem with histograms, bind variables and bind peeking is somewhat easier in 11g with adaptive cursor sharing, but not a 100% solution according to the panel. One 11g feature I'm going to look more into is extended statistics.

The third and last presentation for me was Powerful New Ways to Use Oracle Data Guard for Planned Maintenance with Larry Carpenter. This stuff requires a lot of consideration and planning. Downtime, complexity and risk. Finding a solution that lowers especially the last two is important. Another presentation I have to digest.

At 17:00 I went to Pythian's bloggers meetup. Met a lot of guys IRL that I've been following on Twitter and whose blogs I've been reading for years. Impressive event and very social. Pythian just ranked even higher.  Tim Hall won the prize for having collected most signatures, my collection in the picture. I was too busy talking to people, but got a fair share of signatures. Loved it all.

OOW 2011 Day 3

Went to five presentations, two of them stood out. Doug Burn spoke about SQL plan management in 11g and I learned some new tricks. The material and the content was quite good, also I like these comments that may not be in the slides, e.g. he quoted Jonathan Lewis who said that if you have a narrow problem you should apply a narrow solution, an advice against changing a system wide parameter just because you have one SQL statement gone astray.

The other presentation I liked a lot was about mining the AWR data with SQL, by Yury Velikanov from Pythian. The presentation was packed with useful examples and based on experience he had with customers. When he started almost all seats were taken, and then some people left, maybe it was over their head, but for me, I like these kind of presentations. Yes, it is cool with fancy colorful slides where rule number one is max six words pr slide, but I don't mind seeing code in the slides once in a while. I think that if you strive to understand the AWR repository and analyze it the way Yury did you will have a better understanding on how things are connected, in a way you cannot have when looking on nice graphs in EM. Also you see the limitations in AWR, most data are aggregated which creates other limitations; see Cary's presentation about skew. He made a point in the beginning that there are different concepts to use when attacking optimization and troubleshooting, he gave pointers to tools and methods. All in all a presentation that I have to download and study for a while.

Another great day.

OOW 2011 Day 2

Went to the keynote and five presentations. Keynote was a lot of show, but not much to bring home. Two presentations regarding database migration assistant for unicode, and Real Application Testing were relevant, but so uninspiring and lacked the extra stuff that it was like attending someone reading from the manual. Not much of a take away there either. Also went to a presentation on PL/SQL - Divide and Conquer, modularizing your code. I had hoped to hear more from Bryn Llewellyn, Oracle, but it was really Martin Büchi from Avaloq that had the whole show. Lots of usefull info, interesting real life case on how to structure enormous amount of code. Will remember this next time I get a chance to work with developers and have to look into refactoring of PL/SQL. It does seem to be something one can apply elsewhere in any large environment. Bryn is an excellent speaker, by the way. The two were clear that they did not want to sell consulting or anything else, just to share some goals and passion. I liked that, Bryn talks in way that could fool any DBA into write some code; wish he had a bigger part of it.

Next presentation was of course the highlight of the day. Cary Millsap talked about skew. I've heard some of this before, but again there was something new to learn and reconsider. A reminder of how skew occurs in many levels and groupings and is really the reason why he and most of us has a job. (Just that Method R gets the job done sooner, I think). Cary is the best presenter I can think of in the Oracle world, and made the room laugh many times at a potentially boring subject. Two of my friends heard him speak for the first time and was impressed. One of them considered to skip the remaining presentations for the day so the day would have a good ending. Couldn't have made a better compliment myself.

I finished of with a presentation regarding best practices for Oracle on Windows. I'm not much a fan of best practices, but this was a full packed check-list on what can go wrong on Windows; a unix/linux guy would suspect that there is enough to keep you busy. Very useful to use later since I have been sent to windows land on an assignment for 6-12 months.

I also met briefly Dan Norris from Oracle and Alex Gorbatchev from Pythian; two guys I've been following on Twitter for a while. Always great to see people IRL once in a while (MOW 2011 and Hotsos 2008 in that order). These guys remind me why I like working with this (the database), people outside, especially the open source java cool coding guys, wonder how can one have a life at all working with a comercial database that is anything than simple and inexpensive. Lots of smart people work with this and create an atmosphere beside the work - e.g. OOW has become a great event. Since they don't compete in my market it does not cost much to write this; but Method R and Pythian are two companies I admire; they create and share a lot with others, and appearantly have lots of fun at work and outside.

Now breakfast, no time to spell check. Love my new android tablet, by the way.

Update:
-Breakfast over, some spell checking done.
-Power tip for the Android tablet: Check the Wi-Fi disconnection policy, set it to disconnect when screen is turned off; battery lasts much longer.
-I remember that I met a smart guy on Rac Attack yesterday; Martin Nash. From UK, good for us we have such experts we can ask to come over to OUGN.

OOW 2011 Day 1

First day at OOW 2011 started with interesting presentations arranged by IOUG, very usefull stuff and I have to read the presentations again to get all the details regarding Oracle on VMware, and deploying APEX with security in mind.

Highlight of the day was OUGN's encounter for Norwegians at OOW 2011 and invited guests (invited VIP speakers for next years OUGN 2012 conference). This took place at The Norwegian Church Abroad. The church has a spectacular view over the city. Among the guests were Jonathan Lewis and Cary Millsap, two of my heroes in the Orace World. I had a long conversation with Cary regarding work, relationship between DBAs and developers, something we both have thought a lot about . I also tried to convince him to come next year and speak at OUGN 2012. Hopefully we can arrange for one of his one day classes a day before the conference.

OOW 2011 Day 0

This year when I was on my way to MOW 2011 in Denmark, a few months after I started at Keystep, my manager called me and asked if I wanted to go to Oracle Open World. That has not happened before in any other job I've had and of course I said yes, so here I am in San Francisco. Arrived late Friday, and spent Saturday adjusting to new timezone and walk around. First observations: really nice city, want to bring my family and see more. Also lots of nice people, with some exceptions from those driving car through the crossing of Lombart street and Hyde Street; they were pointing to the sky with wrong finger.

Going to San Francisco...

...tomorrow to meet a lot of fellow Oracle nerds at Oracle Open World. Looking especially forward to Pythian's bloggers meetup on Wednesday, though my blog has been quite dormant for a while and I might not deserve it that much.  We are planning for next year's OUGN conference, and hopefully we'll have a chance to meet some of them we want to invite. All I know is that we'll have more of what has been a success so far. I might bring a cuddly toy to hand over to that guy who knows so much about partioning.... Until another time zone.

Rebuild Oracle Text indexes for Portal

The job portal.wwv_context.sync stopped working with the following error message:

ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "PORTAL.WWERR_API_EXCEPTION", line 71
ORA-06512: at "PORTAL.WWV_CONTEXT", line 2530
ORA-20000: Oracle Text error:
DRG-10502: index WWSBR_CORNER_CTX_INDX does not exist

We opened a Service Request at Oracle support but never figured out how the index disappeared, if that was the case and not a corruption somewhere. Support suggested that we executed the procedure given in Doc ID 340439.1; reinstall Oracle Text, then connect as the portal user from Portal server and execute the three scripts from middle tier home:

@?/portal/admin/plsql/wws/ctxdrind.sql
@?/portal/admin/plsql/wws/inctxgrn.sql
@?/portal/admin/plsql/wws/ctxcrind.sql

But the last script failed with:

SQL> @?/portal/admin/plsql/wws/ctxcrind.sql
Creating Context Preferences and Indexes...

PL/SQL procedure successfully completed.

declare
*
ERROR at line 1:
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "PORTAL.WWERR_API_EXCEPTION", line 164
ORA-06512: at "PORTAL.WWV_CONTEXT", line 2189
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "PORTAL.WWERR_API_EXCEPTION", line 71
ORA-06512: at "PORTAL.WWV_CONTEXT", line 2118
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "PORTAL.WWERR_API_EXCEPTION", line 164
ORA-06512: at "PORTAL.WWV_CONTEXT", line 2077
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "PORTAL.WWERR_API_EXCEPTION", line 71
ORA-06512: at "PORTAL.WWV_CONTEXT", line 1705
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-20000: Oracle Text error:
DRG-50857: oracle error in drvxtab.create_index_tables
ORA-00955: name is already used by an existing object
ORA-06512: at line 6

The Application Server Portal Configuration Guide section 8.3.4.1 shows that the last script executes wwv_context.createindex and the error was isolated first to this procedure. The same section shows that wwv_context.createindex is equivalent to:

wwv_context.drop_prefs;  /* Drop all Oracle Text preferences for the indexes, except Lexer preferences */
wwv_context.drop_invalid_indexes; /* Drop all invalid indexes */
wwv_context.create_prefs; /* Create all Oracle Text preferences,except Lexer preferences */
wwv_context.create_missing_indexes(l_indexes);  /* Create missing indexes and record them in l_indexes */
wwv_context.touch_index(l_indexes); /* Mark all rows for created indexes as requiring synchronization */
wwv_context.sync;     /* Synchronize indexes */
wwv_context.optimize; /* Optimize indexes */

By executing each procedure in steps the error was isolated to wwv_context.create_missing_indexes. All of the indexes in question are listed in 8.3.4.2 and can be created one by one with procedure wwv_context.create_index.

To get around the error I executed the following procedure in stead of the script ctxcrind.sql, that is, after I executed the scripts inctxgrn.sql and ctxcrind.sql:

EXEC WWV_CONTEXT.CREATE_PREFS

EXEC WWV_CONTEXT.CREATE_INDEX(WWV_CONTEXT.PAGE_TEXT_INDEX)
EXEC WWV_CONTEXT.CREATE_INDEX(WWV_CONTEXT.DOC_TEXT_INDEX)
exec WWV_CONTEXT.CREATE_INDEX(wwv_context.PERSPECTIVE_TEXT_INDEX)
EXEC WWV_CONTEXT.CREATE_INDEX(WWV_CONTEXT.ITEM_TEXT_INDEX)
EXEC WWV_CONTEXT.CREATE_INDEX(WWV_CONTEXT.CATEGORY_TEXT_INDEX)
EXEC WWV_CONTEXT.CREATE_INDEX(WWV_CONTEXT.URL_TEXT_INDEX)

EXEC  WWV_CONTEXT.TOUCH_INDEX(WWV_CONTEXT.PAGE_TEXT_INDEX)
EXEC  WWV_CONTEXT.TOUCH_INDEX(WWV_CONTEXT.DOC_TEXT_INDEX)
EXEC  WWV_CONTEXT.TOUCH_INDEX(WWV_CONTEXT.PERSPECTIVE_TEXT_INDEX)
EXEC  WWV_CONTEXT.TOUCH_INDEX(WWV_CONTEXT.ITEM_TEXT_INDEX)
EXEC  WWV_CONTEXT.TOUCH_INDEX(WWV_CONTEXT.CATEGORY_TEXT_INDEX)
exec  wwv_context.touch_index(WWV_CONTEXT.URL_TEXT_INDEX)

exec wwv_context.sync; 
exec wwv_context.optimize;

We did not verified if it was really necessary to reinstall Oracle Text, you may try the procedure above before reinstall Oracle Text if you encounter the same error with wwv_context.sync.