|
|
The PQ_DISTRIBUTE hint instructs the optimizer how to distribute rows of joined tables among producer and consumer query servers. Such distribution can improve the performance of parallel join operations.
tablespec is the name or alias of a table to be used as the inner table of a join.
outer_distribution is the distribution for the outer table.
inner_distribution is the distribution for the inner table.
The values of the distributions are HASH, BROADCAST, PARTITION, and NONE. Only six combinations table distributions are valid, as described in the table below:
| Distribution |
Description |
| HASH, HASH |
The rows of each table are mapped to consumer query servers, using a hash function on the join keys. When mapping is complete, each query server performs the join between a pair of resulting partitions. This distribution is recommended when the tables are comparable in size and the join operation is implemented by hash-join or sort merge join.
|
| BROADCAST, NONE |
All rows of the outer table are broadcast to each query server. The inner table rows are randomly partitioned. This distribution is recommended when the outer table is very small compared with the inner table. As a general rule, use this distribution when the inner table size multiplied by the number of query servers is greater than the outer table size.
|
| NONE, BROADCAST |
All rows of the inner table are broadcast to each consumer query server. The outer table rows are randomly partitioned. This distribution is recommended when the inner table is very small compared with the outer table. As a general rule, use this distribution when the inner table size multiplied by the number of query servers is less than the outer table size.
|
| PARTITION, NONE |
The rows of the outer table are mapped using the partitioning of the inner table. The inner table must be partitioned on the join keys. This distribution is recommended when the number of partitions of the outer table is equal to or nearly equal to a multiple of the number of query servers; for example, 14 partitions and 15 query servers.
Note: The optimizer ignores this hint if the inner table is not partitioned or not equijoined on the partitioning key.
|
| NONE, PARTITION |
The rows of the inner table are mapped using the partitioning of the outer table. The outer table must be partitioned on the join keys. This distribution is recommended when the number of partitions of the outer table is equal to or nearly equal to a multiple of the number of query servers; for example, 14 partitions and 15 query servers.
Note: The optimizer ignores this hint if the outer table is not partitioned or not equijoined on the partitioning key.
|
| NONE, NONE |
Each query server performs the join operation between a pair of matching partitions, one from each table. Both tables must be equipartitioned on the join keys.
|
For example, given two tables r and s that are joined using a hash join, the following query contains a hint to use hash distribution:
SELECT /*+ORDERED PQ_DISTRIBUTE(s HASH, HASH) USE_HASH (s)*/ column_list
FROM r,s
WHERE r.c=s.c;
To broadcast the outer table r, the query is:
SELECT /*+ORDERED PQ_DISTRIBUTE(s BROADCAST, NONE) USE_HASH (s) */ column_list
FROM r,s
WHERE r.c=s.c;
This is just reference from Oracle 10g online document.
My examples to demonstrate these concepts
system@ORCL> create table products as select rownum prod_id from all_objects;
Table created.
system@ORCL> create table costs as select mod(rownum,2)+1 prod_id, rownum unit_price from all_tables;
Table created.
system@ORCL> set autot trace expl;
system@ORCL> select /*+ ordered use_hash(p c) parallel(p 4) parallel(c 4) pq_distribute(c hash, hash)*/ c.unit_price from products p, costs c where c.prod_id = p.prod_id;
Execution Plan
----------------------------------------------------------
Plan hash value: 1177066807
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1790 | 69810 | 9 (12)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10002 | 1790 | 69810 | 9 (12)| 00:00:01 | Q1,02 | P->S | QC (RAND) |
|* 3 | HASH JOIN BUFFERED | | 1790 | 69810 | 9 (12)| 00:00:01 | Q1,02 | PCWP | |
| 4 | PX RECEIVE | | 51613 | 655K| 7 (15)| 00:00:01 | Q1,02 | PCWP | |
| 5 | PX SEND HASH | :TQ10000 | 51613 | 655K| 7 (15)| 00:00:01 | Q1,00 | P->P | HASH |
| 6 | PX BLOCK ITERATOR | | 51613 | 655K| 7 (15)| 00:00:01 | Q1,00 | PCWC | |
| 7 | TABLE ACCESS FULL| PRODUCTS | 51613 | 655K| 7 (15)| 00:00:01 | Q1,00 | PCWP | |
| 8 | PX RECEIVE | | 1790 | 46540 | 2 (0)| 00:00:01 | Q1,02 | PCWP | |
| 9 | PX SEND HASH | :TQ10001 | 1790 | 46540 | 2 (0)| 00:00:01 | Q1,01 | P->P | HASH |
| 10 | PX BLOCK ITERATOR | | 1790 | 46540 | 2 (0)| 00:00:01 | Q1,01 | PCWC | |
| 11 | TABLE ACCESS FULL| COSTS | 1790 | 46540 | 2 (0)| 00:00:01 | Q1,01 | PCWP | |
-----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("C"."PROD_ID"="P"."PROD_ID")
Note
-----
- dynamic sampling used for this statement
system@ORCL> select /*+ ordered use_hash(p c) parallel(p 4) parallel(c 4) pq_distribute(c broadcast, none)*/ c.unit_price from products p, costs c where c.prod_id = p.prod_id;
Execution Plan
----------------------------------------------------------
Plan hash value: 1844745828
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1790 | 69810 | 9 (12)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 1790 | 69810 | 9 (12)| 00:00:01 | Q1,01 | P->S | QC (RAND) |
|* 3 | HASH JOIN | | 1790 | 69810 | 9 (12)| 00:00:01 | Q1,01 | PCWP | |
| 4 | PX RECEIVE | | 51613 | 655K| 7 (15)| 00:00:01 | Q1,01 | PCWP | |
| 5 | PX SEND BROADCAST | :TQ10000 | 51613 | 655K| 7 (15)| 00:00:01 | Q1,00 | P->P | BROADCAST |
| 6 | PX BLOCK ITERATOR | | 51613 | 655K| 7 (15)| 00:00:01 | Q1,00 | PCWC | |
| 7 | TABLE ACCESS FULL| PRODUCTS | 51613 | 655K| 7 (15)| 00:00:01 | Q1,00 | PCWP | |
| 8 | PX BLOCK ITERATOR | | 1790 | 46540 | 2 (0)| 00:00:01 | Q1,01 | PCWC | |
| 9 | TABLE ACCESS FULL | COSTS | 1790 | 46540 | 2 (0)| 00:00:01 | Q1,01 | PCWP | |
-----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("C"."PROD_ID"="P"."PROD_ID")
Note
-----
- dynamic sampling used for this statement
system@ORCL> select /*+ ordered use_hash(p c) parallel(p 4) parallel(c 4) pq_distribute(c none broadcast)*/ c.unit_price from products p, costs c where c.prod_id = p.prod_id;
Execution Plan
----------------------------------------------------------
Plan hash value: 2231521328
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1790 | 69810 | 9 (12)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 1790 | 69810 | 9 (12)| 00:00:01 | Q1,01 | P->S | QC (RAND) |
|* 3 | HASH JOIN | | 1790 | 69810 | 9 (12)| 00:00:01 | Q1,01 | PCWP | |
| 4 | PX BLOCK ITERATOR | | 51613 | 655K| 7 (15)| 00:00:01 | Q1,01 | PCWC | |
| 5 | TABLE ACCESS FULL | PRODUCTS | 51613 | 655K| 7 (15)| 00:00:01 | Q1,01 | PCWP | |
| 6 | BUFFER SORT | | | | | | Q1,01 | PCWC | |
| 7 | PX RECEIVE | | 1790 | 46540 | 2 (0)| 00:00:01 | Q1,01 | PCWP | |
| 8 | PX SEND BROADCAST | :TQ10000 | 1790 | 46540 | 2 (0)| 00:00:01 | Q1,00 | P->P | BROADCAST |
| 9 | PX BLOCK ITERATOR | | 1790 | 46540 | 2 (0)| 00:00:01 | Q1,00 | PCWC | |
| 10 | TABLE ACCESS FULL| COSTS | 1790 | 46540 | 2 (0)| 00:00:01 | Q1,00 | PCWP | |
------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("C"."PROD_ID"="P"."PROD_ID")
Note
-----
- dynamic sampling used for this statement
Two execution plans.
Disable optimizer_dynamic_sampling firstly.
alter session set optimizer_dynamic_sampling = 0;
Then, create two simple tables;
CREATE TABLE t1 ( c1 NUMBER, c2 NUMBER );
CREATE TABLE t2 ( c1 NUMBER, c2 NUMBER );
hr@ORCL> SELECT /*+ parallel(t1,2) */ t1.c2,t2.c2 FROM t1,t2 WHERE t1.c1 = t2.c1;
Execution Plan
----------------------------------------------------------
Plan hash value: 869483324
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 82 | 4264 | 5 (20)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 82 | 4264 | 5 (20)| 00:00:01 | Q1,01 | P->S | QC (RAND) |
|* 3 | HASH JOIN | | 82 | 4264 | 5 (20)| 00:00:01 | Q1,01 | PCWP | |
| 4 | PX BLOCK ITERATOR | | 82 | 2132 | 2 (0)| 00:00:01 | Q1,01 | PCWC | |
| 5 | TABLE ACCESS FULL | T1 | 82 | 2132 | 2 (0)| 00:00:01 | Q1,01 | PCWP | |
| 6 | BUFFER SORT | | | | | | Q1,01 | PCWC | |
| 7 | PX RECEIVE | | 82 | 2132 | 2 (0)| 00:00:01 | Q1,01 | PCWP | |
| 8 | PX SEND BROADCAST | :TQ10000 | 82 | 2132 | 2 (0)| 00:00:01 | Q1,00 | P->P | BROADCAST |
| 9 | PX BLOCK ITERATOR | | 82 | 2132 | 2 (0)| 00:00:01 | Q1,00 | PCWC | |
| 10 | TABLE ACCESS FULL| T2 | 82 | 2132 | 2 (0)| 00:00:01 | Q1,00 | PCWP | |
------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T1"."C1"="T2"."C1")
hr@ORCL> SELECT /*+ parallel(t1,3) */ t1.c2,t2.c2 FROM t1,t2 WHERE t1.c1 = t2.c1;
Execution Plan
----------------------------------------------------------
Plan hash value: 3683239666
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 82 | 4264 | 5 (20)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10002 | 82 | 4264 | 5 (20)| 00:00:01 | Q1,02 | P->S | QC (RAND) |
|* 3 | HASH JOIN BUFFERED | | 82 | 4264 | 5 (20)| 00:00:01 | Q1,02 | PCWP | |
| 4 | PX RECEIVE | | 82 | 2132 | 2 (0)| 00:00:01 | Q1,02 | PCWP | |
| 5 | PX SEND HASH | :TQ10000 | 82 | 2132 | 2 (0)| 00:00:01 | Q1,00 | P->P | HASH |
| 6 | PX BLOCK ITERATOR | | 82 | 2132 | 2 (0)| 00:00:01 | Q1,00 | PCWC | |
| 7 | TABLE ACCESS FULL| T1 | 82 | 2132 | 2 (0)| 00:00:01 | Q1,00 | PCWP | |
| 8 | PX RECEIVE | | 82 | 2132 | 2 (0)| 00:00:01 | Q1,02 | PCWP | |
| 9 | PX SEND HASH | :TQ10001 | 82 | 2132 | 2 (0)| 00:00:01 | Q1,01 | P->P | HASH |
| 10 | PX BLOCK ITERATOR | | 82 | 2132 | 2 (0)| 00:00:01 | Q1,01 | PCWC | |
| 11 | TABLE ACCESS FULL| T2 | 82 | 2132 | 2 (0)| 00:00:01 | Q1,01 | PCWP | |
-----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T1"."C1"="T2"."C1")
Why the PQ distribution change from Broadcast to Hash when DOP(degree of parallel) changes from 2 to 3(or above)?
Note that TQ(Table Queue) also change from 2 to 3.
But let’s change to another principle questions, how many PQ distribute way exists? Can I change them by my own? Refer to my next article for answers.
Another principle question: how to monitor the parallel execution except the execution plan? how about the trace, V$ views, or other things? Refer to my next next article for answers!
So why the optimizer chose different execution plan according to different DOP? Simple to say, this is based on the statistics on the tables. And here, remember, the statistics for these tables here are all "default" statistics, which is not suitable for parallel execution actually. Where to use a specific PQ distribute method could be another deep topic.
Create two partitioned tables:
CREATE TABLE t1(c1 NUMBER, c2 NUMBER)
PARTITION BY RANGE (c1)
(
PARTITION p1 VALUES LESS THAN (10),
PARTITION p2 VALUES LESS THAN (20),
PARTITION p3 VALUES LESS THAN (30),
PARTITION p4 VALUES LESS THAN (40)
);
CREATE TABLE t2(c1 NUMBER, c2 NUMBER)
PARTITION BY RANGE (c1)
(
PARTITION p1 VALUES LESS THAN (10),
PARTITION p2 VALUES LESS THAN (20),
PARTITION p3 VALUES LESS THAN (30),
PARTITION p4 VALUES LESS THAN (40)
);
hr@ORCL> SELECT /*+ PARALLEL (t1,1) */ t1.c2 FROM t1,t2 WHERE t1.c1 = t2.c1;
Execution Plan
----------------------------------------------------------
Plan hash value: 1604022418
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 328 | 12792 | 8 (25)| 00:00:01 | | |
| 1 | PARTITION RANGE ALL| | 328 | 12792 | 8 (25)| 00:00:01 | 1 | 4 |
|* 2 | HASH JOIN | | 328 | 12792 | 8 (25)| 00:00:01 | | |
| 3 | TABLE ACCESS FULL| T1 | 328 | 8528 | 3 (0)| 00:00:01 | 1 | 4 |
| 4 | TABLE ACCESS FULL| T2 | 328 | 4264 | 3 (0)| 00:00:01 | 1 | 4 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T1"."C1"="T2"."C1")
hr@ORCL> SELECT /*+ PARALLEL (t1,2) */ t1.c2 FROM t1,t2 WHERE t1.c1 = t2.c1;
Execution Plan
----------------------------------------------------------
Plan hash value: 2042620038
--------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 328 | 12792 | 6 (17)| 00:00:01 | | | | | |
| 1 | PX COORDINATOR | | | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 328 | 12792 | 6 (17)| 00:00:01 | | | Q1,01 | P->S | QC (RAND) |
|* 3 | HASH JOIN | | 328 | 12792 | 6 (17)| 00:00:01 | | | Q1,01 | PCWP | |
| 4 | PX BLOCK ITERATOR | | 328 | 8528 | 2 (0)| 00:00:01 | 1 | 4 | Q1,01 | PCWC | |
| 5 | TABLE ACCESS FULL | T1 | 328 | 8528 | 2 (0)| 00:00:01 | 1 | 4 | Q1,01 | PCWP | |
| 6 | BUFFER SORT | | | | | | | | Q1,01 | PCWC | |
| 7 | PX RECEIVE | | 328 | 4264 | 3 (0)| 00:00:01 | | | Q1,01 | PCWP | |
| 8 | PX SEND BROADCAST LOCAL| :TQ10000 | 328 | 4264 | 3 (0)| 00:00:01 | | | | S->P | BCST LOCAL |
| 9 | PARTITION RANGE ALL | | 328 | 4264 | 3 (0)| 00:00:01 | 1 | 4 | | | |
| 10 | TABLE ACCESS FULL | T2 | 328 | 4264 | 3 (0)| 00:00:01 | 1 | 4 | | | |
--------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T1"."C1"="T2"."C1")
hr@ORCL> SELECT /*+ PARALLEL (t1,4) */ t1.c2 FROM t1,t2 WHERE t1.c1 = t2.c1;
Execution Plan
----------------------------------------------------------
Plan hash value: 2117927142
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 328 | 12792 | 6 (17)| 00:00:01 | | | | | |
| 1 | PX COORDINATOR | | | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 328 | 12792 | 6 (17)| 00:00:01 | | | Q1,01 | P->S | QC (RAND) |
|* 3 | HASH JOIN | | 328 | 12792 | 6 (17)| 00:00:01 | | | Q1,01 | PCWP | |
| 4 | PX BLOCK ITERATOR | | 328 | 8528 | 2 (0)| 00:00:01 | 1 | 4 | Q1,01 | PCWC | |
| 5 | TABLE ACCESS FULL | T1 | 328 | 8528 | 2 (0)| 00:00:01 | 1 | 4 | Q1,01 | PCWP | |
| 6 | BUFFER SORT | | | | | | | | Q1,01 | PCWC | |
| 7 | PX RECEIVE | | 328 | 4264 | 3 (0)| 00:00:01 | | | Q1,01 | PCWP | |
| 8 | PX SEND BROADCAST | :TQ10000 | 328 | 4264 | 3 (0)| 00:00:01 | | | | S->P | BROADCAST |
| 9 | PARTITION RANGE ALL| | 328 | 4264 | 3 (0)| 00:00:01 | 1 | 4 | | | |
| 10 | TABLE ACCESS FULL | T2 | 328 | 4264 | 3 (0)| 00:00:01 | 1 | 4 | | | |
-----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T1"."C1"="T2"."C1")
hr@ORCL> SELECT /*+ PARALLEL (t1,5) */ t1.c2 FROM t1,t2 WHERE t1.c1 = t2.c1;
Execution Plan
----------------------------------------------------------
Plan hash value: 2042834191
--------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 328 | 12792 | 6 (17)| 00:00:01 | | | | | |
| 1 | PX COORDINATOR | | | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 328 | 12792 | 6 (17)| 00:00:01 | | | Q1,01 | P->S | QC (RAND) |
|* 3 | HASH JOIN | | 328 | 12792 | 6 (17)| 00:00:01 | | | Q1,01 | PCWP | |
| 4 | PX PARTITION RANGE ALL | | 328 | 8528 | 2 (0)| 00:00:01 | 1 | 4 | Q1,01 | PCWC | |
| 5 | TABLE ACCESS FULL | T1 | 328 | 8528 | 2 (0)| 00:00:01 | 1 | 4 | Q1,01 | PCWP | |
| 6 | BUFFER SORT | | | | | | | | Q1,01 | PCWC | |
| 7 | PX RECEIVE | | 328 | 4264 | 3 (0)| 00:00:01 | | | Q1,01 | PCWP | |
| 8 | PX SEND PARTITION (KEY)| :TQ10000 | 328 | 4264 | 3 (0)| 00:00:01 | | | | S->P | PART (KEY) |
| 9 | PARTITION RANGE ALL | | 328 | 4264 | 3 (0)| 00:00:01 | 1 | 4 | | | |
| 10 | TABLE ACCESS FULL | T2 | 328 | 4264 | 3 (0)| 00:00:01 | 1 | 4 | | | |
--------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T1"."C1"="T2"."C1")
hr@ORCL>
Also the PQ distribution change from "Broadcast Local" to "Broadcast" to "Part(Key)" when DOP(degree of parallel) changes from 2 to 4 to 5(or above).
Why? Maybe, in the further, I will use an more realistic case to study and investigate why.
There are various ways to enable trace.
1. alter session set sql_trace = true;
require DBA privileage
2. alter session set events ‘10046 trace name context forever, level 8′;
3. oradebug setmypid;
4. execute dbms_session.session_trace_enable(waits default true, binds default false);
can be invoked by any user to enable session-level SQL trace for their own session.
5. execute dbms_session.set_sql_trace(sql_trace);
require DBA privileage, equivalent to "alter session set sql_trace = true"
6. exec dbms_monitor.session_trace_enable(session_id default null, serial_num default null, waits default true, binds default false);
only visable to DBA.
7. dbms_support.start_trace(sid, serial, wait default true, binds default false);
is not present by default, but can be loaded as the SYS user by executing the $ORACLE_HOME/rdbms/admin/dbmssupp.sql script.
8. dbms_system.set_sql_trace_in_session
sys.dbms_system.set_sql_trace_in_session(p_sid, p_serial#, TRUE);
The dbms_system package is not a published package in the Oracle published documentation of 9iR2, 10gR1 or 10gR2..
Method 1-5 can be used to enable SQL trace for own session.
Method 6-8 can be used to enable SQL trace for other sessions, session_id and serial# are required.
1. Introduce
The ASH(Active Session History) is the innovative improvement in 10g.
ASH records the activities of Oracle, sampling per second, if a session is using on CPU or waiting on resource, such as db file sequential read, log file sync, latch etc, then this session is considered to be active, and recorded down in the v$active_session_history view.
ASH is a memory structure in SGA, which is a circular buffer, the number of seconds of session activities stored in this circular buffer is depended on how many activities are carrying on at the moment.
As the data of ASH is huge, one 10th of ASH data is merged into the persistent view, DBA_HIST_ACTIVE_SESS_HISTORY
The data present in ASH can be rolled up on various dimensions that it captures, including the following:
SQL identifier of SQL statement
- Object number, file number, and block number
- Wait event identifier and parameters
- Session identifier and session serial number
- Module and action name
- Client identifier of the session
- Service hash identifier
2. Enterprise Manager Monitor
The Enterprise Manager provides a means to monitor the session activities last hour till last seven days.
The X-axis indicate the one-hour time duration, and Y-axis indicate the number of active sessions at a particular time. One thing to notice is that maybe at one moment there are 20+ sessions existing on your system, but only one session can occupy the CPU, so here the number of active session is dependent on the CPU count in this system, in this environment, we have only one CPU, but the displaying value can be roughly larger than “Maximum CPU”.
3. Analysis Active Session History
So how to attain these diagrams with the views?
Actually, we can induce this diagram just only from v$active_session_history. Some key columns are:
| Column |
Column |
Description |
| SAMPLE_ID |
SAMPLE_ID |
ID of the sample |
| SAMPLE_TIME |
TIMESTAMP(3) |
Time at which the sample was taken |
| SESSION_STATE |
VARCHAR2(7) |
Session state: WAITING or ON CPU |
| EVENT |
VARCHAR2(64) |
If SESSION_STATE = WAITING, then the event for which the session was waiting for at the time of sampling.
If SESSION_STATE = ON CPU, then this column will be NULL. |
| WAIT_CLASS |
VARCHAR2(64) |
Wait class name of the event for which the session was waiting at the time
of sampling. Interpretation is similar to that of the EVENT column. Maps to
V$SESSION.WAIT_CLASS. |
| PROGRAM |
VARCHAR2(48) |
Name of the operating system program |
| SQL_ID |
VARCHAR2(13) |
SQL identifier of the SQL statement that the session was executing at the
time of sampling |
As one second is too frequent to draw in the diagram, we will use 15 seconds interval. so we just need to use the sample_time and wait_class columns, the SQL statement is list below:
1: select stime, wait_class, count(*) / 15 percent
2: from (select trunc(sample_time, 'mi') +
3: NUMTODSINTERVAL(ceil(extract(second from sample_time) / 15) * 15,
4: 'second') stime,
5: case
6: when wait_class is null then
7: 'CPU'
8: else
9: wait_class
10: end wait_class
11: from v$active_session_history)
12: where stime > :1
13: and stime <= :2
14: group by stime, wait_class;
4. OMonitor Result
The OMonitor uses the statement above to capture and visualize the output. Below are some types of diagrams generated by OMonitor, they are similar comparing to the EM-type one, validating that our analysis about active session history is correct.
More about OMonitor, please refer to HERE.
|
Disclaimer The views or opinions expressed on this blog are my own and do not necessarily reflect the views or opinions of Oracle Corporation. The views or opinions expressed by visitors on this blog are theirs solely and may not reflect mine.
|
