Hi,
I've changed the query in our development environment to the one you
suggested, and it works, but I think that the problem is still there.
In prod, we are on log mode overwrite, and database data is at 75% (6 GB
gree). load_systab is not done since long ago. We are monitoring data from
production from 2 weeks ago, and every day, we see results like that: a
small number in CALL_COUNT, but big numbers in ROWS_QUAL, ROWS_READ and
PHYSICAL_IO, leading to high RUNTIMEs for this kind of queries.
Also, something similar happens in our development environment:
SQL_STATEMENT CALL_COUNT ROWS_READ
ROWS_QUAL
SELECT * INTO :dummy FROM DUAL 132 132 132
SELECT * INTO :dummy FROM DUAL 28 230 94
I think the first line is OK, but the second, there are more rows read and
qualified than calls. Although the difference is not so big as in prod, it
seems the same problem.
In development, we also have overwrite log mode, free space for database,
and no load_systab being executed. The only difference is that development
version is 7.5.00.38
Regards,
Jose
From: "Zabach, Elke" <[EMAIL PROTECTED]>
To: "Jose Dillet" <[EMAIL PROTECTED]>, <maxdb@lists.mysql.com>
Subject: RE: Performance on queries over DUAL table
Date: Wed, 11 Apr 2007 14:08:35 +0200
Hi,
the simplest way to be done is
select * into :some_parameter from dual
as (compared to select * from dual) no resultset-description has to be
prepared.
But your monitor-result seem very strange, indeed.
The only idea I have is that your database ran into a full-state
(log/database)
and all commands had to wait for backup of log or something like that.
or did you do a load_systab in parallel? During this the table is
dropped/re-created
and perhaps some kind of locks caused the trouble.
The table dual itself will never have more than one row.
I assume that this effect will not be reproducable, is it?
Elke
SAP Labs Berlin
Sitz der Gesellschaft/Registered Office: Walldorf, Germany
Vorstand/SAP Executive Board: Henning Kagermann (Sprecher/CEO), Shai
Agassi, Léo Apotheker, Werner Brandt, Claus Heinrich, Gerhard Oswald, Peter
Zencke
Vorsitzender des Aufsichtsrats/Chairperson of the SAP Supervisory Board:
Hasso Plattner
Registergericht/Commercial Register Mannheim No HRB 350269
Diese E-Mail kann Betriebs- oder Geschäftsgeheimnisse oder sonstige
vertrauliche Informationen enthalten. Sollten Sie diese E-Mail irrtümlich
erhalten haben, ist Ihnen eine Kenntnisnahme des Inhalts, eine
Vervielfältigung oder Weitergabe der E-Mail ausdrücklich untersagt.
Bitte benachrichtigen Sie uns und vernichten Sie die empfangene E-Mail.
Vielen Dank.
This e-mail may contain trade secrets or privileged, undisclosed, or
otherwise confidential information. If you have received this e-mail in
error, you are hereby notified that any review, copying, or distribution of
it is strictly prohibited. Please inform us immediately and destroy the
original transmittal. Thank you for your cooperation.
> -----Original Message-----
> From: Jose Dillet [mailto:[EMAIL PROTECTED]
> Sent: Dienstag, 10. April 2007 10:30
> To: maxdb@lists.mysql.com
> Subject: Performance on queries over DUAL table
>
> Hello,
>
>
>
> We have an application with JBoss and MaxDB 7.5.00.23 that
> I'm monitoring to
> try to find performance bottlenecks. We've enabled the
> resource monitor as
> explained in MaxDB performance tuning primer article. When I
> check the
> results gathered, I see some strange results with queries
> over dual table:
>
> SQL_STATEMENT CALL_COUNT ROWS_READ ROWS_QUAL PCT_ROWS
> VIRTUAL_READS RUNTIME MIN_RUNTIME MAX_RUNTIME PHYSICAL_IO
>
> SELECT * FROM DUAL 164 1128894 682042
> 0
> 3213934
> 347.271943 .000053 150.694322 58398
>
>
> JBoss has a pool of connections, so, every time a connection
> is taken from
> pool, jboss executes a SELECT * FROM DUAL to check that this
> connection is
> still alive and working (this is configured at jboss
> datasource level). We
> do this query because is the simplest query that could be
> done. But, as you
> see, there was an execution with 150 secs, and cumulated
> runtime of 347
> secs. I think this is too much time for so simple query. But, data of
> ROWS_READ and ROWS_QUAL seems even stranger. Over a million
> rows read for
> 164 queries, and 600k rows qualified (rows that match
> criteria), but DUAL
> has only 1 row (I've checked it and there isn't other DUAL
> tables of other
> users, so this query goes over DBA.DUAL, the one with only
> one row and one
> field).
>
>
>
> Do you think that this behaviour is a bug?
>
>
>
> Best regards,
>
>
>
> Jose Dillet
>
> _________________________________________________________________
> Dale rienda suelta a tu tiempo libre. Mil ideas para exprimir
> tu ocio con
> MSN Entretenimiento. http://entretenimiento.msn.es/
>
>
> --
> MaxDB Discussion Mailing List
> For list archives: http://lists.mysql.com/maxdb
> To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]
>
>
>
_________________________________________________________________
Dale rienda suelta a tu tiempo libre. Mil ideas para exprimir tu ocio con
MSN Entretenimiento. http://entretenimiento.msn.es/
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
