Re: Queries running the longest? - Mailing list pgsql-novice

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@rdc.co.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