[Theforum] [---Dev] RE: [---tent] Article cleanup issue

Paul Cowan evolt at funkwit.com
Tue Jul 23 21:55:51 CDT 2002


Matt Liotta wrote:
> I don't know what teo is, but another way to get information on SQL
> queries with Oracle is to get the execution plan. This is done simply by
> using the SQL command explain plan. For coverage of the syntax as well
> as an example please see the following URL,
> http://download-west.oracle.com/otndoc/oracle9i/901_doc/server.901/a9012
> 5/statements_911.htm. Additionally, coverage of Oracle optimizer hints
> is available at the following URL,
> http://download-west.oracle.com/otndoc/oracle9i/901_doc/server.901/a9012
> 5/sql_elements7a.htm#8477.

I agree with Matt. As someone who knows next to zero about thesite code,
and hence is qualified maybe to do only two things in this debate
(those things being "sit down" and "shut up" respectively), but it seems
to me that there are a lot of people out here who might be able to help
with the query.

I know nothing at all about Oracle, but I DO know about DB performance
on other platforms, and I'm sure there are some Oracle heads around: if
not, I know some.

Some things I would dearly love, and which I think might help
someone (if not me) improve query performance:

1. Query execution plan
2. Stats about dataset returned (particularly rowsizes)
3. Execution time on server
4. time taken on CF call (not the same as #3: it might, as someone
   said, be a transport issue). Maybe some can on teo have the
   CF equivalent of:
        - write out current time
        - execute query
        - write out current time
   so we can compare to #3 and see what's happening.

I'm no Oracle expert, but it seems insane to me that the above query
would take anything at all over half a second on a well-indexed and
-keyed database.

I'm not arguing the 'archiving articles' point at all, because I don't
think I know enough about what I'm talking about. But I do gather
that EVERYONE wants this to be a 'temporary fix', so if we can fix the
query for good then, hey, everyone's happy!

Also, I don't understand the caching, if it has to be executed once
for each person because of the 'comments since last login' thing.

Just my 2 cents,

Paul




More information about the theforum mailing list