Re: Queries running the longest?

Thom, thanks for your reply. Is there a way to get a plan for a running
SQL statement? The Dark Database(TM) has the following tables, which
make the life of a DBA much easier:

SQL*Plus: Release 11.2.0.1.0 Production on Thu Aug 19 14:17:12 2010

Copyright (c) 1982, 2009, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL> desc v$sql
Name Null? Type
----------------------------------------- --------
----------------------------
SQL_TEXT VARCHAR2(1000)
SQL_FULLTEXT CLOB
SQL_ID VARCHAR2(13)
SHARABLE_MEM NUMBER
PERSISTENT_MEM NUMBER
RUNTIME_MEM NUMBER
SORTS NUMBER
LOADED_VERSIONS NUMBER
OPEN_VERSIONS NUMBER
USERS_OPENING NUMBER
FETCHES NUMBER
EXECUTIONS NUMBER
PX_SERVERS_EXECUTIONS NUMBER
END_OF_FETCH_COUNT NUMBER
USERS_EXECUTING NUMBER
LOADS NUMBER
FIRST_LOAD_TIME VARCHAR2(19)
INVALIDATIONS NUMBER
PARSE_CALLS NUMBER
DISK_READS NUMBER
DIRECT_WRITES NUMBER
BUFFER_GETS NUMBER
APPLICATION_WAIT_TIME NUMBER
CONCURRENCY_WAIT_TIME NUMBER
CLUSTER_WAIT_TIME NUMBER
USER_IO_WAIT_TIME NUMBER
PLSQL_EXEC_TIME NUMBER
JAVA_EXEC_TIME NUMBER
ROWS_PROCESSED NUMBER
COMMAND_TYPE NUMBER
OPTIMIZER_MODE VARCHAR2(10)
OPTIMIZER_COST NUMBER
OPTIMIZER_ENV RAW(895)
OPTIMIZER_ENV_HASH_VALUE NUMBER
PARSING_USER_ID NUMBER
PARSING_SCHEMA_ID NUMBER
PARSING_SCHEMA_NAME VARCHAR2(30)
KEPT_VERSIONS NUMBER
ADDRESS RAW(8)
TYPE_CHK_HEAP RAW(8)
HASH_VALUE NUMBER
OLD_HASH_VALUE NUMBER
PLAN_HASH_VALUE NUMBER
CHILD_NUMBER NUMBER
SERVICE VARCHAR2(64)
SERVICE_HASH NUMBER
MODULE VARCHAR2(64)
MODULE_HASH NUMBER
ACTION VARCHAR2(64)
ACTION_HASH NUMBER
SERIALIZABLE_ABORTS NUMBER
OUTLINE_CATEGORY VARCHAR2(64)
CPU_TIME NUMBER
ELAPSED_TIME NUMBER
OUTLINE_SID NUMBER
CHILD_ADDRESS RAW(8)
SQLTYPE NUMBER
REMOTE VARCHAR2(1)
OBJECT_STATUS VARCHAR2(19)
LITERAL_HASH_VALUE NUMBER
LAST_LOAD_TIME VARCHAR2(19)
IS_OBSOLETE VARCHAR2(1)
CHILD_LATCH NUMBER
SQL_PROFILE VARCHAR2(64)
PROGRAM_ID NUMBER
PROGRAM_LINE# NUMBER
EXACT_MATCHING_SIGNATURE NUMBER
FORCE_MATCHING_SIGNATURE NUMBER
LAST_ACTIVE_TIME DATE
BIND_DATA RAW(2000)
TYPECHECK_MEM NUMBER

SQL> desc v$sql_plan
Name Null? Type
----------------------------------------- --------
----------------------------
ADDRESS RAW(8)
HASH_VALUE NUMBER
SQL_ID VARCHAR2(13)
PLAN_HASH_VALUE NUMBER
CHILD_ADDRESS RAW(8)
CHILD_NUMBER NUMBER
TIMESTAMP DATE
OPERATION VARCHAR2(30)
OPTIONS VARCHAR2(30)
OBJECT_NODE VARCHAR2(40)
OBJECT# NUMBER
OBJECT_OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(30)
OBJECT_ALIAS VARCHAR2(65)
OBJECT_TYPE VARCHAR2(20)
OPTIMIZER VARCHAR2(20)
ID NUMBER
PARENT_ID NUMBER
DEPTH NUMBER
POSITION NUMBER
SEARCH_COLUMNS NUMBER
COST NUMBER
CARDINALITY NUMBER
BYTES NUMBER
OTHER_TAG VARCHAR2(35)
PARTITION_START VARCHAR2(5)
PARTITION_STOP VARCHAR2(5)
PARTITION_ID NUMBER
OTHER VARCHAR2(4000)
DISTRIBUTION VARCHAR2(20)
CPU_COST NUMBER
IO_COST NUMBER
TEMP_SPACE NUMBER
ACCESS_PREDICATES VARCHAR2(4000)
FILTER_PREDICATES VARCHAR2(4000)
PROJECTION VARCHAR2(4000)
TIME NUMBER
QBLOCK_NAME VARCHAR2(30)
REMARKS VARCHAR2(4000)
OTHER_XML CLOB

SQL>

There is also a little something called "wait event interface" which
tells me what is my SQL waiting for and where is the time spent. I am
fully aware that there is one database to rule them all, one database to
find them, one database to bring them all and in the vendor dependency
bind them, but this things make a life of a DBA much, much easier.

Thom Brown wrote:
> On 19 August 2010 08:58, Machiel Richards <machielr(at)rdc(dot)co(dot)za> wrote:
>
>> Good day everyone.
>>
>>
>> Firstly I would like to thank everyone in advance for all the
>> help over the last couple of weeks on this mailing list as it has helped me
>> out a lot in my efforts to try and understand Postgresql better.
>>
>> I am however once again here with some more questions which I
>> hope someone can assist me with as I can not seem to find good documentation
>> on this as yet...
>>
>>
>> As part of our daily health checks and efforts to assist our
>> client to improve their performance on their postgresql databases, we need
>> to look at the queries that takes the longest to run and then use explain or
>> other methods to try and improve the queries, add indexes where required,
>> etc...
>>
>>
>> However, I am trying to find out how I will be able to track /
>> see these queries in order for me to be able to investigate these.
>>
>> the client have a total of 100 databases and to manually monitor
>> these will be too time consuming considering that we have many clients to
>> look after each day.
>>
>>
>> Can anyone perhaps point me in the right direction here, even if it is
>> in the form of documentation that will assist me in this.
>>
>>
>>
>
> Hi Machiel,
>
> If you look in postgresql.conf, you'll see an option called
> log_min_duration_statement. If you set this to a minimum amount of
> time (in milliseconds) that the query must run before it's logged (of
> course you need logging enabled first), you can then analyze the log
> though something like pgFouine
> (http://pgfouine.projects.postgresql.org/) which will be able to rank
> the longest-running queries for you. Note that pgFouine requires that
> you configure your log format beforehand, so check the pgFouine
> documentation for how to do that.
>
> Alternatively, if you really don't wish to use pgFouine, just set your
> log_min_duration_statement setting to a value over which queries would
> become unacceptably long. Then just manually examine the logs.
>
> Regards
>

--

Mladen Gogala
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
http://www.vmsinfo.com
The Leader in Integrated Media Intelligence Solutions