I was hoping somebody could answer this one way or another. I am
relatively new to Ingres coming from MS SQLServer. One of the
extremely useful things I used to be able to do in SQLServer was to
trace (profile) all SQL running on the server using a utility called
profiler. In the profiler I could then filter out SQL coming from
particular nodes or users, how long they ran etc etc. The main reason
for doing this is that it allowed tuning of SQL independently of the
application sending the SQL. The raw SQL and timings could be sent to
a table and queried for things like 'the top 5 worst performing
queries' or 'how many times' a particular piece of SQL was running.
O.K., here is my question. Is there technique in Ingres that can be
used to do a similar thing? Most of the connections in my new job
here are via JDBC. I would just like to know so that I don't continue
chasing something which Ingres can't do in the first place.

-------------------------------------------------------------------
For better; or for worse, these are my thoughts.

While there are some powerful auditing and tracing options in Ingres, I've
not come across an equivalent to MSSQL profiler.

Ingres security auditing is the closest thing you have to centralised SQL
query tracking. It provides database object and event tracking for queries,
updates, execution, users etc. There is a performance impact on the system.
The manual recommends you are selective in what you wish to audit to reduce
the impact. You need to plan what to do when the audit logs fill.

But, in my opinion, Ingres debug tools cover most requirements. Everything
from tracking changes to a specific table to monitoring communications
between client and server and profiling a specific process on one client.
These options are available without having to interfere with the client
application. (there are of course many other debug/tracing options which you
can embed into your code.)

The server level monitoring resources are audit logs, journals, real time
monitoring via IMA, visual performance monitor.

Get yourself registered for a training course or bring in an expert for a
day or two to show you some shortcuts.

I think you will find what you need in these manuals.
System Admin guide.
Security Guide.
Interactive Performance Monitor user guide.

Paul


-----Original Message-----
From: info-ingres-bounces (AT) kettleriver...ting (DOT) com
[mailto:info-ingres-bounces (AT) kettleriverconsulting (DOT) com] On Behalf Of togbabe
Sent: Thursday, 23 July 2009 10:33 AM
To: info-ingres (AT) kettleriverconsulting (DOT) com
Subject: [Info-Ingres] SQLServer profiler Ingres equivalent??

I was hoping somebody could answer this one way or another. I am
relatively new to Ingres coming from MS SQLServer. One of the
extremely useful things I used to be able to do in SQLServer was to
trace (profile) all SQL running on the server using a utility called
profiler. In the profiler I could then filter out SQL coming from
particular nodes or users, how long they ran etc etc. The main reason
for doing this is that it allowed tuning of SQL independently of the
application sending the SQL. The raw SQL and timings could be sent to
a table and queried for things like 'the top 5 worst performing
queries' or 'how many times' a particular piece of SQL was running.
O.K., here is my question. Is there technique in Ingres that can be
used to do a similar thing? Most of the connections in my new job
here are via JDBC. I would just like to know so that I don't continue
chasing something which Ingres can't do in the first place.

-------------------------------------------------------------------
For better; or for worse, these are my thoughts.
_______________________________________________
Info-Ingres mailing list
Info-Ingres (AT) kettleriverconsulting (DOT) com
http://www.kettleriverconsulting.com...fo/info-ingres

Our most useful monitoring tool is an in-house OpenROAD application that lists the sessions and locks
out of IMA. It displays the query and sevaral other useful things including the client hostname.

It could be enhanced to run automatically to get statistics about queries that are there often,
but that does not do the job that a process built in to the database engine might do.

If you are using the OpenROAD Application Server theres a new product that does exactly what you want, I think.

Robert

-----Original Message-----
From: info-ingres-bounces (AT) kettleriver...ting (DOT) com [mailto:info-ingres-bounces (AT) kettleriverconsulting (DOT) com] On Behalf Of Paul White
Sent: Thursday, 23 July 2009 2:20 p.m.
To: 'Ingres and related product discussion forum'
Subject: Re: [Info-Ingres] SQLServer profiler Ingres equivalent??

While there are some powerful auditing and tracing options in Ingres, I've not come across an equivalent to MSSQL profiler.

Ingres security auditing is the closest thing you have to centralised SQL query tracking. It provides database object and event tracking for queries, updates, execution, users etc. There is a performance impact on the system.
The manual recommends you are selective in what you wish to audit to reduce the impact. You need to plan what to do when the audit logs fill.

But, in my opinion, Ingres debug tools cover most requirements. Everything from tracking changes to a specific table to monitoring communications between client and server and profiling a specific process on one client.
These options are available without having to interfere with the client application. (there are of course many other debug/tracing options which you can embed into your code.)

The server level monitoring resources are audit logs, journals, real time monitoring via IMA, visual performance monitor.

Get yourself registered for a training course or bring in an expert for a day or two to show you some shortcuts.

I think you will find what you need in these manuals.
System Admin guide.
Security Guide.
Interactive Performance Monitor user guide.

Paul


-----Original Message-----
From: info-ingres-bounces (AT) kettleriver...ting (DOT) com
[mailto:info-ingres-bounces (AT) kettleriverconsulting (DOT) com] On Behalf Of togbabe
Sent: Thursday, 23 July 2009 10:33 AM
To: info-ingres (AT) kettleriverconsulting (DOT) com
Subject: [Info-Ingres] SQLServer profiler Ingres equivalent??

I was hoping somebody could answer this one way or another. I am relatively new to Ingres coming from MS SQLServer. One of the extremely useful things I used to be able to do in SQLServer was to trace (profile) all SQL running on the server using a utility called profiler. In the profiler I could then filter out SQL coming from particular nodes or users, how long they ran etc etc. The main reason for doing this is that it allowed tuning of SQL independently of the application sending the SQL. The raw SQL and timings could be sent to a table and queried for things like 'the top 5 worst performing queries' or 'how many times' a particular piece of SQL was running.
O.K., here is my question. Is there technique in Ingres that can be used to do a similar thing? Most of the connections in my new job here are via JDBC. I would just like to know so that I don't continue chasing something which Ingres can't do in the first place.

-------------------------------------------------------------------
For better; or for worse, these are my thoughts.
_______________________________________________
Info-Ingres mailing list
Info-Ingres (AT) kettleriverconsulting (DOT) com
http://www.kettleriverconsulting.com...fo/info-ingres


_______________________________________________
Info-Ingres mailing list
Info-Ingres (AT) kettleriverconsulting (DOT) com
http://www.kettleriverconsulting.com...fo/info-ingres
Confidentiality/Privilege Notice:
This communication is confidential and may be legally privileged. If you are not the intended recipient please delete the message and notify the sender at Ports of Auckland Limited. Any use, disclosure, copying, distribution or retention of this communication is strictly prohibited.

There has been work done in this area, however, it's not yet near
completion nor stable enough for real-life use.

Take a look at
http://ieeexplore.ieee.org/xpl/freea...=235&index=218
and
http://community.ingres.com/wiki/Ingres_Design_Analyzer

This version of Ingres will log all queries with runtime statistics such
as optimizer-estimated disk io/cpu time, actual io/time, used indexes, etc.

Another work, based on that, is currently in progress to include those
statistics graphically into Eclipse.
Still, the core would need a lot more work to be included into main
Ingres...

Cheers,
Alex

togbabe wrote:

togbabe wrote:

I don't have anything very different to add to what has already been
posted: the necessary data stream is available via the security auditing
facility, but currently there is no nice tool to present or analyse
the data. No doubt such tools are in development.

You may also be interested in my recent IUA presentation:
http://www.rationalcommerce.com/papers/tuner.htm. In it I show how the
diverse tools can be applied to discover performance problems. (See
also http://www.rationalcommerce.com/courses/aipol.htm.)

--
Roy

UK Ingres User Association Conference 2010 will be on Tuesday June 8 2010
Go to http://www.iua.org.uk/join to get on the mailing list.

Hello,

it would be worth checking 'Dynamic Playback - Ingres Community Wiki'
(http://community.ingres.com/wiki/Dynamic_Playback)

This project was designed initially to replay the queries, but it may
give also some hints about the query performance.
Unfortunately big pain is, we do not see, when a query completes.
Currently we see only when the query is being started.

Bilgihan


--
bilgihan

Ingres Forums wrote:

Yes, but it is often a reasonable guess that the application sends
queries to the server as fast as it can within a single transaction,
so the starting time of the next query is approximately the completion
time of the previous one. When you have the source code of the
application you can verify how reliable that guess is likely to be; you
can check for user think-time within a transaction, and for intensive
client-side processing, and so on.

--
Roy

UK Ingres User Association Conference 2010 will be on Tuesday June 8 2010
Go to http://www.iua.org.uk/join to get on the mailing list.