Tuesday, August 14, 2012

resmgr:pq queued | enq: JX - SQL statement queue | PX Queuing: statement queue

We have 4 Node RAC on 11.2.0.2 on exadata machine.

Few days back we had problem of Trucate statment was running slow. it took around 1 hour.
when truncate is running slow.
either object is locked or its bug. But here it was something else.

TRUNCATE TABLE DUMMY_USAGE.DUMMY_CUST_BILL_CYC_USG_F_BLD1 DROP STORAGE;

After pulling AWR , we found that "resmgr:pq queued" is in Top 5 wait events.
It looks that database has been suffered from "lack of enough parallel servers".
even TRUNCATE statement also tried to ran into parallel.


in 11.2.0.2

resmgr:pq queued is The time the session waited for sufficient parallel query processes to become available to run this session with the requested degree of parallelism

in 11.2.0.1

Wait event "PX Queuing: statement queue" is the event When statement waits on about to run.

Wait event "enq: JX - SQL statement queue" is event when statement have few more statments lined up ahead of it.


Wait event on DB. So database has heavily waited on "resmgr:pq queued"

MIN(SAMPLE_TIME) MAX(SAMPLE_TIME)                 COUNT EVENT
8/10/2012 3:39:15.324 AM 8/10/2012 7:00:26.064 AM 11812 resmgr:pq queued
8/10/2012 2:13:36.850 AM 8/10/2012 7:00:16.054 AM 3045 CPU
8/10/2012 3:05:19.667 AM 8/10/2012 6:59:03.635 AM 2638 direct path read temp
8/10/2012 2:00:45.579 AM 8/10/2012 6:59:15.940 AM 1987 cell smart table scan

Which SQL has waited Most from "lack of Enogh parallel servers"

SELECT MIN(SAMPLE_TIME),MAX(SAMPLE_TIME),COUNT(*) AS COUNT , EVENT,SQL_ID FROM DBA_HIST_ACTIVE_sESS_HISTORY WHERE SNAP_ID BETWEEN 1594 AND 1598 AND EVENT='resmgr:pq queued' group by EVENT,SQL_ID ORDER BY COUNT DESC;

MIN(SAMPLE_TIME)         MAX(SAMPLE_TIME)        COUNT    EVENT           SQL_ID
---------------------------------------------------------------------------------------------
8/10/2012 4:24:47.633 AM 8/10/2012 6:25:40.210 AM 723 resmgr:pq queued brjbmruuj4qca

8/10/2012 4:06:58.060 AM 8/10/2012 5:49:08.703 AM 540 resmgr:pq queued 1ztm7k9fbawks
8/10/2012 4:07:05.815 AM 8/10/2012 5:38:45.312 AM 477 resmgr:pq queued 8fdq282ydmj9y
8/10/2012 4:06:58.060 AM 8/10/2012 5:37:57.564 AM 473 resmgr:pq queued 5pgx9fqn6b18k
8/10/2012 4:06:58.060 AM 8/10/2012 5:37:07.457 AM 470 resmgr:pq queued bfyb7a00r9m97
8/10/2012 4:06:58.060 AM 8/10/2012 5:34:07.155 AM 452 resmgr:pq queued dck4f08ywn2gk
8/10/2012 4:07:05.815 AM 8/10/2012 5:33:34.783 AM 448 resmgr:pq queued 2tjdy0su2yyrg
8/10/2012 4:06:58.060 AM 8/10/2012 5:32:26.963 AM 442 resmgr:pq queued a770bk5gnyp49
8/10/2012 4:06:58.060 AM 8/10/2012 5:31:56.913 AM 439 resmgr:pq queued 8qgm5az2pstpw
8/10/2012 4:07:05.815 AM 8/10/2012 5:31:24.561 AM 435 resmgr:pq queued 3gytqk6h6hy46
8/10/2012 4:07:05.815 AM 8/10/2012 5:31:14.513 AM 434 resmgr:pq queued 73bxznwgh04yf

8/10/2012 4:49:52.568 AM 8/10/2012 5:49:08.703 AM 356 resmgr:pq queued 3qkqq4w2z03jh
8/10/2012 4:06:58.060 AM 8/10/2012 5:03:13.976 AM 338 resmgr:pq queued g0b4p5zsn5xwp
8/10/2012 4:06:58.060 AM 8/10/2012 5:02:23.885 AM 333 resmgr:pq queued 0k5796sr9sgrq
8/10/2012 4:07:05.815 AM 8/10/2012 5:01:01.402 AM 324 resmgr:pq queued a8m336ddng5z7
8/10/2012 4:07:05.815 AM 8/10/2012 4:59:41.282 AM 316 resmgr:pq queued 8wz2qpu6t3b4r
8/10/2012 4:06:58.060 AM 8/10/2012 4:58:53.520 AM 312 resmgr:pq queued c1a03akcb57nm
8/10/2012 4:39:51.493 AM 8/10/2012 5:31:06.810 AM 308 resmgr:pq queued bsuwb8ksjgg7f
8/10/2012 5:38:15.272 AM 8/10/2012 6:32:10.874 AM 287 resmgr:pq queued 8tkfm8yg6fy3z

SQL> SELECT * FROM DBA_HIST_SQLTEXT WHERE sql_id in ('brjbmruuj4qca','1ztm7k9fbawks');

      DBID SQL_ID        SQL_TEXT                                                                         COMMAND_TYPE
---------- ------------- -------------------------------------------------------------------------------- ------------
1664458898 brjbmruuj4qca TRUNCATE TABLE DUMMY_USAGE.DUMMY_TOP_TALKERS_DLY_SUM_SWP DROP STORAGE                        85
2309640764 8fdq282ydmj9y SELECT Last_day(cduf.time_key)         AS time_key                                          3


when Oracle put statments to Queue for parallel servers.

When the parameter PARALLEL_DEGREE_POLICY is set to AUTO, Oracle Database queues SQL statements that require parallel execution if the necessary parallel server processes are not available. After the necessary resources become available, the SQL statement is dequeued and allowed to execute. The default dequeue order is a simple first in, first out queue based on the time a statement was issued.

The following is a summary of parallel statement processing.

1) A SQL statements is issued.

2) The statement is parsed and the DOP is automatically determined.

3) Available parallel resources are checked.

A) If there are enough parallel resources and there are no statements ahead in the queue waiting for the resources, the SQL statement is executed.

B) If there are not enough parallel servers, the SQL statement is queued based on specified conditions and dequeued from the front of the queue when specified conditions are met.

Parallel statements are queued if running the statements would increase the number of active parallel servers above the value of the PARALLEL_SERVERS_TARGET initialization parameter. For example, if PARALLEL_SERVERS_TARGET is set to 64, the number of current active servers is 60, and a new parallel statement needs 16 parallel servers, it would be queued because 16 added to 60 is greater than 64, the value of PARALLEL_SERVERS_TARGET.

The default value is described in "PARALLEL_SERVERS_TARGET". This value is not the maximum number of parallel server processes allowed on the system, but the number available to run parallel statements before parallel statement queuing is used. It is set lower than the maximum number of parallel server processes allowed on the system (PARALLEL_MAX_SERVERS) to ensure each parallel statement gets all of the parallel server resources required and to prevent overloading the system with parallel server processes. Note all serial (nonparallel) statements execute immediately even if parallel statement queuing has been activated.

If a statement has been queued, it is identified by the resmgr:pq queued wait event.

Parameter to define parallelism were defined something like below,
SQL> show parameter parallel

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
fast_start_parallel_rollback         string      LOW
parallel_adaptive_multi_user         boolean     FALSE
parallel_automatic_tuning            boolean     FALSE
parallel_degree_limit                string      16
parallel_degree_policy               string      AUTO   --AUTO DOP is used. 
parallel_execution_message_size      integer     16384
parallel_force_local                 boolean     FALSE
parallel_instance_group              string
parallel_io_cap_enabled              boolean     FALSE
parallel_max_servers                 integer     32
parallel_min_time_threshold          string      AUTO
parallel_server                      boolean     TRUE
parallel_server_instances            integer     4   
parallel_servers_target              integer     16
parallel_threads_per_cpu             integer     1

SQL> show parameter cpu

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cpu_count                            integer     24  
parallel_threads_per_cpu             integer     1


It have only 32 Parallel servers and queuing will start even if 16 servers are Used.

so Both parallel_max_servers & parallel_servers_target have set incorrect.

Auto DOP is calculated as (parallel_threads_per_cpu × parallel_server_instances × cpu_count )= 96

The default value for parallel_server_target is set to 4 times the default DOP.

((4 × CPU_count) × parallel_threads_per_cpu) × active_instances = PARALLEL_SERVER_TARGET

((4 × 24) × 1) × 4) = 384 should be PARALLEL_SERVER_TARGET


Managing Parallel Statement Queuing with Hints

NO_STATEMENT_QUEUING

When PARALLEL_DEGREE_POLICY is set to AUTO, this hint enables a statement to bypass the parallel statement queue. For example:

SELECT /*+ NO_STATEMENT_QUEUING */ emp.last_name, dpt.department_name
FROM employees emp, departments dpt
WHERE emp.department_id = dpt.department_id;

STATEMENT_QUEUING

When PARALLEL_DEGREE_POLICY is not set to AUTO, this hint enables a statement to be delayed and to only run when parallel processes are available to run at the requested DOP. For example:

SELECT /*+ STATEMENT_QUEUING */ emp.last_name, dpt.department_name
FROM employees emp, departments dpt
WHERE emp.department_id = dpt.department_id;

There is also a hidden parameter which control parallel queuing.
_parallel_statement_queuing=TRUE





No comments:

Post a Comment