Re: SQL Query monitoring

Holger,

Thank you very much.

I wrote:
select /*+ordered */ substr(sql_statement,1,2000), strategy
from sysmonitor sm, sysparseid sp
where sm.parseid = sp.parseid and
strategy = 'SCAN'

TO discover who is doing a SCAN.

For example, I got:

SELECT COUNT ( *) FROM HON WHERE ( HON_GMR_COD IS NULL ) OR ( HON_EMP_COD IS
NULL ) OR ( HON_PSV_SOLIC IS NULL ) OR ( HON_CTF_CATEG IS NULL ) OR (
HON_PSV_SOLIC IS NULL ) OR ( HON_IH_ORIGEM IS NULL )

The Explain returns:
DBA  HON    TABLE SCAN                                       1
DBA              RESULT IS COPIED   , COSTVALUE IS           3

What does it mean? (It is a newbie question, I know...)


Thank you very much

2007/5/18, Becker, Holger <[email protected]>:
>
> Daniel Castro wrote:
>
> > Hi,
> >
> > I am using sapdb 7.4
> > Is there any way to do a SQL Query Monitoring.
> > I want to see the queries which are doing a tabls scan.
> > I dont have the system source code.
> > It is not a SAP ERP.
> >
> > Best regards,
> >
> > --
> > Daniel Castro - דניאל
> > MSN: [email protected]
> > Skype: danielti2005
> > ICQ : 316727989
> > Jabber: danielti
> > Linux User: 387864
> > AOL User: Danielti2005
> > Sola Scriptura, Sola Gratia, Sola Fide, Solus Christus, Soli
> > Deo Gloria
> >
>
> Hi,
>
> you could enable diagnose monitor feature with the command "diagnose
> monitor read 0"
> executed from SQL Studio or any other tool.
>
> Than all commands will be written into the system table SYSPARSEID
> and their resource consumption will be written into SYSMONITOR.
>
> With the join
>
> select /*+ordered */ substr(sql_statement,1,2000)
> from sysmonitor sm, sysparseid sp
> where sm.parseid = sp.parseid
>
> you will get some information about the execution of the statements.
> Statements which are doing a table scan have the word SCAN in the column
> STRATEGY.
> But you should keep in mind that not all scans are bad.
> If the table is small enough a scan may be the best access strategy.
>
> The table SYSMONITOR only keeps a certain amount of rows.
> You could adjust the amount with the command "diagnose monitor rowno
> <uint>".
>
> Kind regards
> Holger
>
> Holger Becker
> MaxDB&liveCache
> SAP AG
> www.sap.com
> Sitz der Gesellschaft/Registered Office: Walldorf, Germany
> Vorstand/SAP Executive Board: Henning Kagermann (Sprecher/CEO),  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.
>



-- 
Daniel Castro - דניאל
MSN: [email protected]
Skype: danielti2005
ICQ : 316727989
Jabber: danielti
Linux User: 387864
AOL User: Danielti2005
Sola Scriptura, Sola Gratia, Sola Fide, Solus Christus, Soli Deo Gloria