It was pretty long time since I've posted anything in this blog (for various reasons) - too bad, especially since there was a lot of interesting things in the IT around me worth sharing. Today I officially resurrect this blog - just can't keep myself quiet for so long :-)!
Let's start with some good news - the conference season is slowly but steadily starts to roll! Tomorrow (March 12, 2013) I will be presenting at New York Oracle User Group with my brand-new talk:
Top 5 Issues that Cannot be Resolved by DBAs (other than missed bind variables) [complete schedule]
Here is the abstract: Common knowledge says that if there is something wrong with the database, blame DBAs. Fortunately for database administrators, about 95% of such cases have nothing to do with their job responsibility. Unfortunately, it takes a lot of time to explain this to any manager. And it takes even longer for an organization as a whole to look for real solutions to its IT problems other than asking its DBAs to look for “run_faster=TRUE”. This presentation’s goal is to illustrate the most common “pseudo-DBA” issues and proper ways of resolving them.
This presentation is a bit of a challenge for me, because it's the first time I decided to pick "TopN"-style instead of deep-dive into a topic. To be fair, I've got a bit tired with feeding people different answers, when they cannot figure out core conceptual questions. So, I decided to change the style - I ASK questions first. Yes, I would also show my proposed approaches - but that's secondary! The primary goal here would be to make people THINK.
And it will be definitely entertaining to see, how the NYC crowd (the most challenging one I ever presented to!) react. If I survive this experience - promise to report an aftermath, maybe even some snippets out of my white-paper. :-)
Ave Caesar, morituri te salutant!
Monday, March 11, 2013
Thursday, December 1, 2011
File list from the server
I've been asked by one of my architects whether there is a quick way of see a list of file existing in the random folder on the server. The first response was - NO, because all file system communication should go via DIRECTORY object (starting 10g).
But I recalled that I've read sometimes about a slightly nontrivial usage of undocumented package DBMS_BACKUP_RECOVERY that could serve exactly the required purpose. And in a minute I found the article and the following code sample:
DECLARE
pattern VARCHAR2(1024) := 'C:\temp\*';
ns VARCHAR2(1024);
BEGIN
SYS.DBMS_BACKUP_RESTORE.searchFiles(pattern, ns);
FOR each_file IN (SELECT FNAME_KRBMSFT AS name FROM X$KRBMSFT) LOOP
DBMS_OUTPUT.PUT_LINE(each_file.name);
END LOOP;
END;
Yes, you still need some fairly advanced privileges (like logging as SYS and having 'db_recovery_file_dest' parameter set), but still - I was surprised! Thanks a lot to Christopher Poole that I don't need to reinvent at least this bicycle :-)
P.s. More posts about DBMS_BACKUP_RESTORE are coming ;-) !
But I recalled that I've read sometimes about a slightly nontrivial usage of undocumented package DBMS_BACKUP_RECOVERY that could serve exactly the required purpose. And in a minute I found the article and the following code sample:
DECLARE
pattern VARCHAR2(1024) := 'C:\temp\*';
ns VARCHAR2(1024);
BEGIN
SYS.DBMS_BACKUP_RESTORE.searchFiles(pattern, ns);
FOR each_file IN (SELECT FNAME_KRBMSFT AS name FROM X$KRBMSFT) LOOP
DBMS_OUTPUT.PUT_LINE(each_file.name);
END LOOP;
END;
Yes, you still need some fairly advanced privileges (like logging as SYS and having 'db_recovery_file_dest' parameter set), but still - I was surprised! Thanks a lot to Christopher Poole that I don't need to reinvent at least this bicycle :-)
P.s. More posts about DBMS_BACKUP_RESTORE are coming ;-) !
Thursday, November 10, 2011
Amazon Top - PL/SQL for Dummies is #1
To my huge surprise the 5-year-old-book is currently #1 in the Amazon's top Oracle books... H-m-m-m, I have a feeling that with the current economy a lot of people went "back-to-school".
Monday, October 24, 2011
UTF8 vs AL32UTF8
To be fair, I am one of reasonably few people who like Oracle documentation - you indeed can find there everything needed. But... Sometimes it will be either in the unexpected place, or not explicit enough, or not very clear at all. Here is an illustration of such case.
Do not confuse the Oracle Database database character set UTF8 (no hyphen) with the database character set AL32UTF8 or with character encoding UTF-8. Database character set UTF8 has been superseded by AL32UTF8. Do not use UTF8 for XML data. UTF8 supports only Unicode version 3.1 and earlier; it does not support all valid XML characters. AL32UTF8 has no such limitation.
Using database character set UTF8 for XML data could potentially cause a fatal error or affect security negatively.
The beginning of this story is pretty simple - Friday, around 4 p.m. People are already day-dreaming about their weekend plans. And suddenly our main development database starts to behave really funky. Our senior web-architect eventually called me to show that in one of the newly created modules the whole session just crushes with ORA-600 after the call to XML Parser (we've been building XML document manually as a CLOB and than converting it into XMLDocument). Alternative via XMLType.CreateXML completely froze the whole database...
After about an hour of pretty painful debugging the problem was found - and not where we've been expecting. The exact match was found in Oracle's Database Companion CD installation Guide:
-----------------------------------------------
AL32UTF8 is the Oracle Database character set that is appropriate for XMLType data. It is equivalent to the IANA registered standard UTF-8 encoding, which supports all valid XML characters.
Do not confuse the Oracle Database database character set UTF8 (no hyphen) with the database character set AL32UTF8 or with character encoding UTF-8. Database character set UTF8 has been superseded by AL32UTF8. Do not use UTF8 for XML data. UTF8 supports only Unicode version 3.1 and earlier; it does not support all valid XML characters. AL32UTF8 has no such limitation.
-----------------------------------------------
"Fatal errors" indeed! We immediately switched from:
<?xml version="1.0" encoding="UTF-8"?>
<?xml version="1.0" encoding="UTF-8"?>
to
<?xml version="1.0" encoding="AL32UTF8"?>
And our problem was solved! Friday, 5 pm... Perfect timing!
And our problem was solved! Friday, 5 pm... Perfect timing!
Sunday, October 9, 2011
Oracle OpenWorld 2011. Day 5
The closing day of the event started slowly. No wonder... Not that many people returned back to the hotel just after Sting concert (like I did) - the show went lo-o-o-o-ng way past midnight. Also, a lot of attendants had flights already on Thursday, so it was pretty common to see some of my old friends trying to reach a jet speed already in the hotel (running with their bags towards the cab stand :-) ).
My schedule was pretty simple:
- went to the talk about Edition-Based Redefinition from a couple of BankOfAmerica guys. We in Dulcian look at that feature for some years, so it was interesting to see what other people are doing in this area. Summary - it is a VERY powerful concept if you are starting a new project. But transforming existing one to be EBR-compliant is much more fun. Overall, I am planning to watch it closely, but still not sure whether I am ready to jump in the pool with unknown number of crocodiles.
- mine and Paul's last presentation "Achieving great performance for web-application by using only SQL and PL/SQL" got significantly more people than we ever hoped considering the last day of the conference. It seems like that the state of confusion, especially among small-to-medium size IT shops is huge. They do not have budgets/people to jump to the most recent bandwagons, but they still need to somehow support their end-users. After the presentation I've spent at least an hour more talking about the details of our FormSpider implementation - there was a lot of interest about this completely nontrivial way of building contemporary web applications.
- "It's the wrap" party at Yerba Buena - perfect sunny afternoon with "Blue Angels" flying above our heads. I didn't have any energy left even to talk to everybody. Picked up the book, and read for an hour or so.
That's all, folks... Now I need to digest everything I learned. It was a really great conference, so check this blog from time to time - I will be posting more focused stories! Stay in touch!
My schedule was pretty simple:
- went to the talk about Edition-Based Redefinition from a couple of BankOfAmerica guys. We in Dulcian look at that feature for some years, so it was interesting to see what other people are doing in this area. Summary - it is a VERY powerful concept if you are starting a new project. But transforming existing one to be EBR-compliant is much more fun. Overall, I am planning to watch it closely, but still not sure whether I am ready to jump in the pool with unknown number of crocodiles.
- mine and Paul's last presentation "Achieving great performance for web-application by using only SQL and PL/SQL" got significantly more people than we ever hoped considering the last day of the conference. It seems like that the state of confusion, especially among small-to-medium size IT shops is huge. They do not have budgets/people to jump to the most recent bandwagons, but they still need to somehow support their end-users. After the presentation I've spent at least an hour more talking about the details of our FormSpider implementation - there was a lot of interest about this completely nontrivial way of building contemporary web applications.
- "It's the wrap" party at Yerba Buena - perfect sunny afternoon with "Blue Angels" flying above our heads. I didn't have any energy left even to talk to everybody. Picked up the book, and read for an hour or so.
That's all, folks... Now I need to digest everything I learned. It was a really great conference, so check this blog from time to time - I will be posting more focused stories! Stay in touch!
Thursday, October 6, 2011
Oracle OpenWorld 2011. Day 4
Sad day for the IT industry. There is not a lot of people who could think of something - and make the whole world change because of their ideas. So hard to lose such people so early...
Day 4. Oct 5 2011.
Finally, there was at least one day when I could just relax and wonder around between different presentations, demos etc. I think, I've spent it reasonably efficient:
- went to a couple of talks from Oracle tech leads (XMLDB and Database Vault). The first topic requires additional post, but here I will just mention briefly that Oracle finally started to change default behaviour of XMLType that I've been complaining for years:
* in 11.2.0.3 XMLType will be created as Binary SecureFile storage mechanism with CACHE option automatically ON. That parameter was a performance issue for too many people - and not a lot of people know how to internally change it (if interested - read our article about LOB data types in the 3rd quarter of ODTUG Tech Journal)
- spent couple hours at Oracle DemoGrounds(this time with SecureFile team, Java-in-the-database team, Oracle-on-Windows team). Got some useful information - sorry, also not for publishing :-)
- came to Larry's talk where he announced Oracle Cloud and Oracle Social Network... No comments on either of those topics, not my area of expertise.
Last part of the day - sure, you've guessed, PARTY! This time everybody was very worried about the possibility of storms, but we lucked out (storms are today...) The reason was pretty simple - STING! The guy just turned 60 years old, but I envied his level of energy on stage. It was unforgettable to be 20 meters from musicians (I came early enough) in the crowd of 10+ thousand people singing "Englishman in New York" with all accents existing on the planet Earth!
Day 4. Oct 5 2011.
Finally, there was at least one day when I could just relax and wonder around between different presentations, demos etc. I think, I've spent it reasonably efficient:
- went to a couple of talks from Oracle tech leads (XMLDB and Database Vault). The first topic requires additional post, but here I will just mention briefly that Oracle finally started to change default behaviour of XMLType that I've been complaining for years:
* in 11.2.0.3 XMLType will be created as Binary SecureFile storage mechanism with CACHE option automatically ON. That parameter was a performance issue for too many people - and not a lot of people know how to internally change it (if interested - read our article about LOB data types in the 3rd quarter of ODTUG Tech Journal)
- spent couple hours at Oracle DemoGrounds(this time with SecureFile team, Java-in-the-database team, Oracle-on-Windows team). Got some useful information - sorry, also not for publishing :-)
- came to Larry's talk where he announced Oracle Cloud and Oracle Social Network... No comments on either of those topics, not my area of expertise.
Last part of the day - sure, you've guessed, PARTY! This time everybody was very worried about the possibility of storms, but we lucked out (storms are today...) The reason was pretty simple - STING! The guy just turned 60 years old, but I envied his level of energy on stage. It was unforgettable to be 20 meters from musicians (I came early enough) in the crowd of 10+ thousand people singing "Englishman in New York" with all accents existing on the planet Earth!
Wednesday, October 5, 2011
Oracle OpenWorld 2011. Days 2 and 3
This year my schedule here in SanFran is surprisingly busy. And not just because of social events :-) It is my 5th OpenWorld in a row - and for now it holds #1 spot for the technical content. Overall, from Oracle's side there is much less marketing people presenting their products / much more technical leads explaining what is going on under the hood. So, I apologize for a small gap in my story-telling and will try to catch up!
Day 2. Monday, Oct 3
For the first time in all of my conference years I decided to come to hands-on training. The topic was one of my favorites, Cost-Based-Optimizer, but still, I had some doubts about how well it could be illustrated as "hands-on". Big surprise - it worked out pretty seamlessly. Each problem case had a step-by-step analysis option and "do-it-yourself" option. Considering my limited experience with Oracle 11g (we only recently switched our main environment to 11.2.0.2 - so my performance tuning skills in that version are sub-optimal), I picked up a guided approach. Good for me - there was a lot of 11g-only concepts in the process (even so I correctly got one out of four tasks without any hints). Summary - learned a lot, need to spend more time with 11g.
Next presentation I came was done by people whom I know already for years - Bryn Llewellyn and Martin Buchi talked about modularization of PL/SQL code in large projects. That was definitely interesting for me, because looks like Martin's design patterns are very close to my "thick database approach".
After that talk I came to Paul Dorsey's "Building Systems in the Developing World" to provide DBA's support (if needed) for his primarily architectural talk. It was very educating to see how people coming from different backgrounds were reacting on our stories about building government systems in Ethiopia. A lot of confusion to be fair, and not a lot of understanding on real local problems...
As usual, final part of the day was much more optimistic! ODTUG had setup a cocktail party in a very nice French bistro.The combination of old friends+good vine+very creative snacks was well appreciated after so many hours wondering around the conference
Day 3. Tuesday, Oct 4
Yesterday was as busy as Monday. For the first time (thanks to Apress) I've been invited to Oracle Publisher's Seminar. An environment where you get a chance to communicate with such people as, for example, Andy Mendelsohn, was a completely new experience for me... It was a bit scary at the beginning, took me some time to relax a bit! Anyways, a lot of useful info (sorry, internal-usage-only :-) ).
After the seminar (around 2pm) I finally decided to wonder around the vendor hall. And as usual I got stuck at DemoGrounds... I just cannot miss the chance to harass developers/managers from Oracle with the problem I've found. And considering that usually I dig pretty deep, sometimes I surprise even Oracle specialists. This year my poor victims were two great guys developing DataPump engine - the game finished with 1:1 draw: they showed me a fix to one of my problems that I just missed in the documentation (next week I will have a post about it) / I showed them an issue with function-based indexes to be logged as an enhancement request. Jokes aside, such discussions for me is the most critical part of the OpenWorld at all. Thanks for the great job, people...
In the evening...Of course, yet another party! :-) This time one of the publishers was organizing a small meeting between authors/potential authors and their acquisition editors. A lot of insightful discussions about technical books. Definitely a well-spent time!
That's it folks, time to get back to the conference center! Hopefully, the weather today will be at least a bit better than forecast...
Day 2. Monday, Oct 3
For the first time in all of my conference years I decided to come to hands-on training. The topic was one of my favorites, Cost-Based-Optimizer, but still, I had some doubts about how well it could be illustrated as "hands-on". Big surprise - it worked out pretty seamlessly. Each problem case had a step-by-step analysis option and "do-it-yourself" option. Considering my limited experience with Oracle 11g (we only recently switched our main environment to 11.2.0.2 - so my performance tuning skills in that version are sub-optimal), I picked up a guided approach. Good for me - there was a lot of 11g-only concepts in the process (even so I correctly got one out of four tasks without any hints). Summary - learned a lot, need to spend more time with 11g.
Next presentation I came was done by people whom I know already for years - Bryn Llewellyn and Martin Buchi talked about modularization of PL/SQL code in large projects. That was definitely interesting for me, because looks like Martin's design patterns are very close to my "thick database approach".
After that talk I came to Paul Dorsey's "Building Systems in the Developing World" to provide DBA's support (if needed) for his primarily architectural talk. It was very educating to see how people coming from different backgrounds were reacting on our stories about building government systems in Ethiopia. A lot of confusion to be fair, and not a lot of understanding on real local problems...
As usual, final part of the day was much more optimistic! ODTUG had setup a cocktail party in a very nice French bistro.The combination of old friends+good vine+very creative snacks was well appreciated after so many hours wondering around the conference
Day 3. Tuesday, Oct 4
Yesterday was as busy as Monday. For the first time (thanks to Apress) I've been invited to Oracle Publisher's Seminar. An environment where you get a chance to communicate with such people as, for example, Andy Mendelsohn, was a completely new experience for me... It was a bit scary at the beginning, took me some time to relax a bit! Anyways, a lot of useful info (sorry, internal-usage-only :-) ).
After the seminar (around 2pm) I finally decided to wonder around the vendor hall. And as usual I got stuck at DemoGrounds... I just cannot miss the chance to harass developers/managers from Oracle with the problem I've found. And considering that usually I dig pretty deep, sometimes I surprise even Oracle specialists. This year my poor victims were two great guys developing DataPump engine - the game finished with 1:1 draw: they showed me a fix to one of my problems that I just missed in the documentation (next week I will have a post about it) / I showed them an issue with function-based indexes to be logged as an enhancement request. Jokes aside, such discussions for me is the most critical part of the OpenWorld at all. Thanks for the great job, people...
In the evening...Of course, yet another party! :-) This time one of the publishers was organizing a small meeting between authors/potential authors and their acquisition editors. A lot of insightful discussions about technical books. Definitely a well-spent time!
That's it folks, time to get back to the conference center! Hopefully, the weather today will be at least a bit better than forecast...
Subscribe to:
Posts (Atom)