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] > > > -- MaxDB Discussion Mailing List For list archives: http://lists.mysql.com/maxdb To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
