Query not showing in dba_hist_active_sess_history

oracleoracle-12coracle-racperformancequery-performance

The application team claims there was a statement that took about 30s to execute at about a certain time.

I tried to find out what queries had been executed in during this time with the following statement:

select a.sql_id, sample_time from dba_hist_active_sess_history a, dba_hist_sqltext b
        where sample_time between to_date('20170330:13:15','yyyymmdd:hh24:mi')
        and to_date('20170330:13:25','yyyymmdd:hh24:mi') and b.sql_id=a.sql_id
        order by 2;

However, only statements concerning rman, or the cloud control agent show up and no statement that seems to be from the application.

Could it be that some queries executed do not show up in the dba_hist_active_sess_history table and where else could I look for it?

Best Answer

Afer checking that you are indeed on the correct database and if ASH- and AWR reports for both instances don't show any long running queries it's time to get in touch with your application developers. Try to find out which statement exactly is slown and why.

I had similiar issues with various applications and application environments and issues I encountered where

jdbc:thin behaviour different form jdbc:oci
issues with different ojdbc driver versions
code changes that split a transaction in multiple small connections resulting in the database doing nothing but opening and closing connections
configuration issues with connection pools

So you might have to look in different locations.

Related Solutions

Mysql – Why are the queries getting really slow

Take a look the queries in your question

/* Execution time 7.0095062E-5 */
SELECT t.*, tt.* FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id WHERE tt.taxonomy IN ('category') AND tt.count > 0 ORDER BY t.name ASC

/* Execution time 5.3167343E-5 */
SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN (77375,77379,77381)

/* Execution time 9.2029572E-5 */
SELECT wp_posts.* FROM wp_posts WHERE ID IN (77381,77379,77375)

Look at the execution time above each query

7.0095062E-5 is .000070095062 seconds
5.3167343E-5 is .000053167343 seconds
9.2029572E-5 is .000092029572 seconds

The SQL queries themselves are fast.

Please read any release notes on WordPress 3.4.1 to see if WordPress upgrade installed any DB-related modules that need tuning or deactivation. Also, check for any data that was added during the upgrade process that may have bloated your tables.

Postgresql – Postgres performance difference on insert

Very likely your problem is that you have an index on the source table which is making the first hits easy. However at some point you are hitting rows that are not in the cache and so this is requiring a sudden increase in disk I/O accounting for the performance drop. In essence suddenly you start missing the cache and so you end up having to read a bunch of things from disk again.

Best Answer

Related Solutions

Mysql – Why are the queries getting really slow

Postgresql – Postgres performance difference on insert

Related Question