“AskMark” - Or, The Bonkers Questions I Sometime Get Sent Through
Duncan Mills' recently posted about the questions he sometimes receives from readers, and it got me thinking about the type of questions I often get sent through to me. Like Duncan, I put my email address on the front page of my blog (in case someone wants to write to me and offer me a job with a million pounds salary), and what I infact get is around about half a dozen emails through each week asking for help with some Oracle problem or other. Unfortunately, because I work full time for a consultancy and do a lot of travelling, I don't usually get much time if any to respond to these, and I seem to spend most of my time feeling perpetually guilty about not even getting the chance to send a quick acknowledgment, let alone a full answer. Some of the questions I get through though are actually quite bizarre, so I thought I'd go back through my inbox and dig out a few to show you.
"Hi Mark. i am a big of you and keep reading your articals, i have a question for you today, the question is,What does Oracle s query rewrite capability do? List and discuss the techniques the Oracle RDBMS employs in the process of analyzing a query for a potential rewrite. i will be thankful and glad if you answer me before 6.pm dec 14 2004 thanks"
This one started off nice, with a complement (always nice to get) and a question that's on-topic. However - "list and discuss the techniques the Oracle RBDMS employs in the process of analyzing a query .... I will be glad if you can answer me before 6.pm dec 14th 2004"? Excuse me? The "list and discuss" was bizarre enough in itself - query rewrite is something that is very well documented in the online docs - but giving me a deadline? Is this for a school project? Excuse me whilst I stop whatever I'm doing and get this done, and I hope it's OK if I'm a couple of hours late...
"Dear Mr. Mark Rittman,
How are you,hope your ok.
There are some question that i would ask to you.
We got problem about our oracle database and need ur advice A.S.A.P.
We upgrade our oracle server memory from 2 Gb into 3 Gb but after that our Oracle Can't start.
Here the error message "shared memory realm already exists " the error code is Ora-27100.
For your reference : We using Oracle 91 with OS Win2K Advance Server"
These ones always baffle me as I can't for the life of me work out why I get sent them. Sure, if I was Tom or Jonathan Lewis I could understand, but even they would probably point you to metalink or the online docs. Funnily enough I asked Jonathan whether he gets many questions like this, and apparently he doesn't (maybe everyone's scared of him) but I always wonder why I get through mails like this when I've never even discussed anything remotely like it. Of course, if you do post a reply, you never hear anything back ever again, as whoever sent it to you probably mailed a dozen other people, then sorted it out themselves anyway, and ignored all the replies. Sheesh...
"Hi Mark,
I tried to compile the procedure from the your article at http://dba-oracle.com/oracle_news/2004_1_31_plsqlL_bulk_binds_FORALL.htm
SQL> create or replace procedure fast_proc is
2 type TObjectTable is table of ALL_OBJECTS%ROWTYPE;
3 ObjectTable$ TObjectTable;
4 begin
5 select
6 * BULK COLLECT INTO ObjectTable$
7 from ALL_OBJECTS;
8
9 forall x in ObjectTable$.First..ObjectTable$.Last
10 insert into t1 values ObjectTable$(x) ;
11 end;
12 /
and got the following error:
PLS-00597: expression OBJECTTABLE$ in the INTO list is of wrong type
What did I do wrong?
Thank you,
Boris"
Christ on a bike, if there was ever an article I wished I'd never written, it was that one and the other around that time around PL/SQL collections. In the article I commented on a challenge Daniel Morgan set on c.d.o.s. where he took some Oracle 7-era PL/SQL code and challenged readers to come up with a better version using newer PL/SQL constructs. Ever since then I've had a steady stream of people telling me that the code (that Daniel Morgan!) put together sometimes doesn't compile, isn't the most efficient way of doing it, and so on, when it wasn't my code and all I was doing was commenting on what an interesting challenge it was. I'd say I honestly still get at least one email a month about that posting, usually asking me to debug some code they've gone on to write using collections, and unfortunately I've not got the time (or the inclination) to look at arrays any further.
Here's one where I still can't work out what the person is actually asking about:
"dear sir,
<div> <i> <p></p>I need one small help from u.</i></div> <div> <p><i>There is to server running in the plant </i></p><font size="2"> <div> <table cellSpacing="0" cellPadding="0" border="1"> <tr> <td vAlign="top" width="295"> <p> <i> </i></p> <p> <b> 1. Oracle server</b></p> <font size="2" face="Arial"> <p> </font><i><b> </b>a. IBM RS 6000 AIX 4.0</i><font size="2"></p> <p> </font><i><b> </b>b. Oracle 7.3</i><font size="2"></p> <p> </font><i> c. Sql*forms 3.0 </i> </p> <p> <i> d. Report writer2.3 </i> </p> <p> <i> e.linux</i></p> <p> <i> </i></p> <p> <i>this server connected with 34 terminals</i></td> <td vAlign="top" width="295"> <p> <i> </i></p> <p> <b> 2. Mail Server</b></p> <p> <i>a. IBM m225 server (connected to 80 pc)</i></p> <p> <i>b.nowel Netware.</i></p> <p> <i>c.UTP cabling </i></p> <p> <i> D.LINUX</i></p> <p> <i> </i></p> <p> <i> </i></p> <p> <i>this server connected with 80 computers</i></td> </tr> </table> <p> </p> </font> <p> <i>Now i am connecting oracle server from the mail server through telnet vt100 and accessing sql* forms 3.0.now i want whatever key board functionality available in oracle server sql* forms3.0 the same key functionality i want in mail server vt100 telnet(telnet connection)</i></p> <font size="2"> <p> <i> </i></p> </font> <p> <i><strong> scope: same key board function should happen in two server while working in SQL* forms 3.0</strong></i></p> <font face="Arial" size="2"> <p> </font><i> </i></p> <p> <i>how to key map.</i></p> <p> <i>can u understand my requirment</i></p> <p> <i> </i></p> <p> <i> how to key map </i></p> <p> <i> </i></p> <p> <i>please let me know"</i></div> </div> </blockquote> <p>All I can hope is that the plant is still running ok. Again though - why does anyone think I can help out with something like this (which I guess is something to do with Forms 3.0) - I guess it's probably the fact that the site is called "Mark Rittman's Oracle Weblog" rather than Mark's Oracle BI&W Weblog, but even so...</p>"What are the got you scenarios when upgrading or moving an Oracle 8i application to 10g?"
Yes I really have got time to do that. In a similar vein:
"hi sir i want some help in designing. can u help me. i will send u my paper work and have to check it."
At least that one's got the virtue of being short and sweet.
This next one arrived with three attachments - a three page word document detailing the query and tuning strategy, a full explain plan and an Excel file with a complete set of initialisation parameters with additional commentary;
"Hello,
I have read your Oracle Weblog articles, etc. on Oracle Database, Warehousing, etc.
Attached is detail on a query, a view def., for which I seek comments/tuning suggestions. The detail will Serve as background detail on the system, database and the query and will provide a good profile of the suspect view def query and environment. I respect your time but hope you will be able to Offer comments/suggestions re: tuning this query.
I will forward list of comments/questions later. If you have any tuning or other comments now, please don't hesitate to send.
However, I guess my main question now is whether or not the FIRST_ROWS run(see attached explain plan, etc.) is possibly optimum, best attainable for this query; given the (possibly buggy) Oracle8 8.0.4 RDBMS and nature of the query with low Selectivity(only 1 constant equality constraint in WHERE), several tables in FROM, several table joins and outer joins. The outer joins unfortunately are a necessary requirement. You will note none of the tables is super large but the query does involve many tables/table joins. ALL_ROWS Generally errors out with space problems; FIRST_ROWS completes with no errors but response time Is several mins. Is that time expected and possible "normal" and acceptable given this type of query??
In testing I have noticed just the addition of a single constant equality constraint to the WHERE clause improves performance (response time to receive first set of output) considerably.
NOTE: regarding (3) below, unless a param value is blatantly wrong, really do not intend for the instance init params to be an issue, unless you see differently. I am assuming the main tuning area to be pure SQL tuning and not the system(AIX) or physical database or DB instance.
However, if any key areas of the database and instance init params appear to you to be out of line and possibly causing the 5-10+ min response times Then please consider and comment on these area(s)."The query arrived just as my wife went into hospital to have our second child, so I sent a note back saying that I was a bit busy at the moment and I doubted I'd be able to look at it in the near future (i.e. ever). However, a couple of weeks later I got a follow up:
"Mark -
Hope wife and baby are well. If you now have time, etc. - this is just a reminder to take a look at detail (sent 03/09/2005). Any tech comments/suggestions you have regarding tuning this query; Whether materialized view help or whether the FIRST_ROWS query plan is optimal is very much appreciated."I guess (here I go contradicting myself) in an ideal world, I'd love to answer all of these questions - by answering questions and participating in the community, you help build up your own knowledge and I do really like to help out people who've got an interest in Oracle business intelligence and data warehousing. And I'm always really glad that people find the site useful, and have taken the time to get in touch and give me some nice feedback. Even so - if your question is interesting, on-topic or just happens to be on something I've just worked on - chances are I might actually answer it, and in some detail (such as here, here and here)- but some of the questions I get through, all of which are unsolicited, i..e. the site isn't called "Ask Mark"), are a bit bonkers, although I do have a little chuckle sometimes especially when I get set a deadline as well as a question.