Page 1 of 1
Canary Query Queuing
Posted: Wed Nov 25, 2015 4:43 am
by VerDBA
I have a Canaray query that runs every minute in database. This query run every minutes. It just does: select count(1) from sessions; Most of the time i see this query hitting database on first or sencond seconds. Like 10:09:01, 10:10:01, 10:11:02. But sometimes i see this query wait and gets in the database like 10:12:08. I dont understand why it takes 8 seconds to get in the database. What is blocking it to get in the db?
Need some help on this.
Re: Canary Query Queuing
Posted: Wed Nov 25, 2015 3:46 pm
by NorbertKrupa
It's probably awaiting resources. Did you check locks, resource acquisitions? If you want it to run immediately, then put it in a higher priority pool. If you're just doing an alive check, consider just using SELECT 1 or date.
Re: Canary Query Queuing
Posted: Thu Nov 26, 2015 1:25 am
by VerDBA
Thanks for Reply...After more analysis I found Input Queue Wait number is outstanding large....Do you ahve any information why or how to tune this ?
Re: Canary Query Queuing
Posted: Thu Nov 26, 2015 8:03 pm
by NorbertKrupa
The Input Queue Wait is "time in microseconds that an operator spends waiting for upstream operators." What are the upstream operations taking place in the query?
Re: Canary Query Queuing
Posted: Thu Nov 26, 2015 9:57 pm
by VerDBA
this is the query I am running: select count(1) from sessions; I dont understand why there will be upstream perations. Is there any way to check: What are the upstream operations taking place in the query?
Re: Canary Query Queuing
Posted: Sat Nov 28, 2015 10:52 pm
by NorbertKrupa
Code: Select all
dbadmin=> EXPLAIN SELECT COUNT(1) FROM SESSIONS;
------------------------------
QUERY PLAN DESCRIPTION:
------------------------------
EXPLAIN SELECT COUNT(1) FROM SESSIONS;
Access Path:
+-GROUPBY NOTHING [Cost: 3, Rows: 1 (NO STATISTICS)] (PATH ID: 1)
| Aggregates: count(1)
| Execute on: Query Initiator
| +---> STORAGE ACCESS for SESSIONS [Cost: 2, Rows: 10K (1 RLE) (NO STATISTICS)] (PATH ID: 2)
| | Projection: v_monitor.sessions_p
| | Execute on: Query Initiator
Storage access probably. You should try adding a predicate if you insist on using sessions.
Otherwise, SELECT COUNT(1);
Code: Select all
dbadmin=> EXPLAIN SELECT COUNT(1);
------------------------------
QUERY PLAN DESCRIPTION:
------------------------------
EXPLAIN SELECT COUNT(1);
Access Path:
+-GROUPBY NOTHING [Cost: 11, Rows: 1] (PATH ID: 1)
| Aggregates: count(1)
| Execute on: Query Initiator
| +---> STORAGE ACCESS for dual [Cost: 10, Rows: 1] (PATH ID: 2)
| | Projection: v_catalog.dual_p
| | Execute on: Query Initiator