Daniel Morgan <damorgan_at_x.washington.edu> wrote in message news:<1068483998.189348_at_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)
>
>
> --
Mark;
One thing that frequently helps is to phrase queries in the positive.
Equal is quicker than <> even when indexes are used. Try to eliminate
the inner select from your query. If there are a list of predictable
acceptable values
use IN ('A','B' ...) rather than IN (select ...).
Dan;
Pretty harsh comments for one that pretends to help.
Received on Tue Nov 11 2003 - 13:46:20 CST
