Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Improve this querry!?

Re: Improve this querry!?

From: Daniel Morgan <damorgan_at_x.washington.edu>
Date: Mon, 10 Nov 2003 09:06:13 -0800
Message-ID: <1068483998.189348@yasure>


Comments in-line

Mark Schubert wrote:

>Daniel Morgan <damorgan_at_x.washington.edu> wrote in message news:<1068245369.453336_at_yasure>...
>
>
>>I could write a book on the subject you've asked. Lets start at the
>>beginning.
>>
>>
>
>Holy Molly. I'm not a DBA. I have been to Oracle Admin/DBA-1 class
>years ago - but do not do Oracle stuff regularly. However, I am the
>most knowledgeable person about Oracle so I get these tasks when they
>come up.
>
>
>
>>What is a "min" when used in the context "73 min of records"?
>>
>>
>
>Minutes.
>

Then I don't understand what 73 minutes of records is, or are. Can you rephrase this?

>>But which optimizer mode are you using?
>>
>>
>
>{mark_at_jaost1}>grep optim /intg/oracle/databases/MT/pfile/initMT.ora
>optimizer_mode = rule
>

Then you aren't using the Cost Based Optimizer and any statistics will be ignored

>
>
>
>>If CBO did you run DBMS_STATS to create current statistics for the
>>optimizer?
>>
>>
>
>By CBO - I assume you mean "cost based optimizer"? I have no idea what
>this is. And I'm not familiar with "running DBMS_STATS" - I'm sure I
>could, but would have to look it up.
>

See above and ignore this part.

>>Have you run Explain Plan?
>>
>>
>
>No. I wouldn't readily understand the output anyway. And when I say
>that, I mean I COULD do it, but I figure I'd spend more time trying to
>understand it than it was worth.
>

Running Explain Plan is essential. The fact that indexes exist does not mean that indexes are used.
Go to http://tahiti.oracle.com and print out the documentation for running Explain Plan. Basically
you will need to run the utlxplan.sql script in $ORACLE_HOME/rdbms/admin to build the plan
table and then use the explain plan syntax to create the plan. Printing it out can be done with any
number of scripts available all over the web. Contact me off-line if you can't find a copy.

You may not understand everything about the output ... but you will be able to tell immediately
whether the indexes are being used.

>>What is the cardinality?
>>
>>
>
>Big (to me) - I'm sure by your question you have seen larger
>databases. We get about 13 million messages a day. The database is
>48g. We get about 10000 messages per minute (input) and the ratio is
>1.8 avg output to each input message. The med_mt_tb is the input
>message (1 unique record [ISN]) and the mrd_mt_tb is the output
>messages (1 record for each delivery [same ISN as input]) . The
>database has 5-7 days worth of data.
>

"Big (to me) ... They've really go to start teaching English in schools. ;-)

The question is worthy of your understanding if you are going to take your employers money
for database work. Look this one up in a book or at http://tahiti.oracle.com.

>>What percentage of records are being returns?
>>
>>
>
>A 73 minute period would be less that 1 percent. (using 6 days of
>data). I would have to run this more that 100 times
>

Are you trying to say that if you request the data that was input over a 73 minute period that
corresponds with requesting 1% of the records in the table?

>>Why haven't you patched to 8.1.7.4?
>>
>>
>
>We are running this on IBM Dynix/ptm and I have pressed for them to
>install 8.1.7.4 - however, management is pursuing a port to another
>platform (either SUN, IBM (AIX), etc) and would rather pursue that in
>priority.
>

Typical. Why solve the current problem in an hour when you can delay it for months.

>>and on and on and on.
>>
>>
>
>Would love to entertain more questions if it will bring a solution...
>Thanks!
>
>Mark.
>
>

I don't mean this in a prejorative way but it seems you are clearly not the person to solve
this problem. I'd suggest that your firm hire a consultant to help you through this and make
one condition of that person's employment that they provide you with training.

I'd also strongly suggest that unless you want to make a career change you put great
effort into securing an education on everything from database fundamentals to Oracle
concepts and architecture to SQL. This is not a good time, not that there ever is one,
to be so poorly equipped for the job you are doing. Unemployment lines are full of
people whose skills are marginal.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)
Received on Mon Nov 10 2003 - 11:06:13 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US