Rittman Mead - Delivered Intelligence

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,

I need one
small help from u.

There is to
server  running
in the plant

1. Oracle server          a. IBM RS 6000 AIX 4.0         b. Oracle 7.3         c. Sql*forms 3.0             d. Report writer2.3             e.linux   this server connected with 34 terminals

2. Mail Server a. IBM   m225 server (connected to 80 pc) b.nowel Netware. c.UTP cabling   D.LINUX     this server connected with 80 computers

 

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)

 

scope: same key  board function should happen in
two server while working in SQL* forms 3.0

 

how to key
map.

can
u understand my requirment

 

how to
key map 

 

please let
me know"

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…

"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.

Posted in General |