sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 运维DBA >

【学习笔记】Oracle advance queue高级队列详细研究笔记

时间:2016-11-30 21:30   来源:Oracle研究中心   作者:网络   点击:

天萃荷净 Oracle研究中心学习笔记:分享一篇关于Oracle数据库高级队列的测试笔记,该笔记记录了Oracle advance queue测试过程与结果分析。

本站文章除注明转载外,均为本站原创: 转载自love wife & love life —Roger 的Oracle技术博客
本文链接地址: oracle advance queue 简单测试

某客户遇到一个高级队列方面的问题,由于自己本身也不熟悉,所以进行了简单的测试,虽然目前高级队列用的非常少,但是该特性其实已经跟streams集成到一起了,回头会写几篇关于streams方面的文章。

'###### Create user and grant ######'

SQL> CREATE USER aq IDENTIFIED BY aq;
USER created.

SQL> GRANT CONNECT, resource, aq_administrator_role TO aq;
GRANT succeeded.

SQL> GRANT EXECUTE ON dbms_aqadm TO aq;
GRANT succeeded.

SQL> GRANT EXECUTE ON dbms_aq TO aq;
GRANT succeeded.

SQL> ALTER USER aq DEFAULT tablespace roger;
USER altered.

SQL> CONNECT aq/aq
Connected.

SQL> CREATE SEQUENCE aq_sequence START WITH 1 INCREMENT BY 1;
SEQUENCE created.

'###### Create type and queue table ######'

SQL> CREATE TYPE message AS object (
  2    city VARCHAR2(30)
  3  );

TYPE created.

SQL> BEGIN
  2     DBMS_AQADM.create_queue_table (queue_table             => 'input_queue_table',
  3                                    sort_list               => 'priority',
  4                                    multiple_consumers      => TRUE,
  5                                    queue_payload_type      => 'message',
  6                                    COMMENT                 => 'Creating input queue table'
  7                                   );
  8  END;
  9  /

PL/SQL PROCEDURE successfully completed.

'###### Create queue ######'

SQL> BEGIN
  2     DBMS_AQADM.create_queue (queue_name       => 'input_queue',
  3                              queue_table      => 'input_queue_table',
  4                              COMMENT          => 'Demo Queue'
  5                             );
  6  END;
  7  /

PL/SQL PROCEDURE successfully completed.

'###### Start queue ######'

SQL> BEGIN
  2     DBMS_AQADM.start_queue (queue_name => 'input_queue');
  3  END;
  4  /

PL/SQL PROCEDURE successfully completed.

SQL> DECLARE
  2     subscriber   SYS.aq$_agent;
  3  BEGIN
  4     subscriber := SYS.aq$_agent ('prog1', NULL, NULL);
  5     DBMS_AQADM.add_subscriber (queue_name      => 'input_queue',
  6                                subscriber      => subscriber
  7                               );
  8  END;
  9  /

PL/SQL PROCEDURE successfully completed.

'###### Create enqueue procedure ######'

SQL> CREATE OR REPLACE PROCEDURE demo_enqueue (userinfo MESSAGE)
  2  AS
  3     enq_msgid   RAW (16);
  4     eopt        DBMS_AQ.enqueue_options_t;
  5     mprop       DBMS_AQ.message_properties_t;
  6     priority    NUMBER;
  7  BEGIN
  8     SELECT aq_sequence.NEXTVAL
  9       INTO priority
10       FROM DUAL;
11
12     mprop.priority := priority;
13     DBMS_AQ.enqueue (queue_name              => 'input_queue',
14                      enqueue_options         => eopt,
15                      message_properties      => mprop,
16                      payload                 => userinfo,
17                      msgid                   => enq_msgid
18                     );
19     COMMIT;
20  END demo_enqueue;
21  /

PROCEDURE created.

SQL> DECLARE
  2     payload1   MESSAGE;
  3     payload2   MESSAGE;
  4     payload3   MESSAGE;
  5     payload4   MESSAGE;
  6  BEGIN
  7     payload1 := MESSAGE ('BELMONT');
  8     payload2 := MESSAGE ('REDWOOD SHORES');
  9     payload3 := MESSAGE ('SUNNYVALE');
10     payload4 := MESSAGE ('BURLINGAME');
11     demo_enqueue (payload1);
12     demo_enqueue (payload2);
13     demo_enqueue (payload3);
14     demo_enqueue (payload4);
15  END;
16  /

PL/SQL PROCEDURE successfully completed.

'###### Create Dequeue procedure ######'

SQL> CREATE OR REPLACE PROCEDURE demo_dequeue (appname VARCHAR2)
  2  AS
  3     deq_msgid   RAW (16);
  4     dopt        DBMS_AQ.dequeue_options_t;
  5     mprop       DBMS_AQ.message_properties_t;
  6     payload     MESSAGE;
  7  BEGIN
  8     dopt.consumer_name := appname;
  9     dopt.WAIT := DBMS_AQ.no_wait;
10     dopt.navigation := DBMS_AQ.first_message;
11     DBMS_AQ.dequeuhttp://www.oracleplus.nete (queue_name              => 'input_queue',
12                      dequeue_options         => dopt,
13                      message_properties      => mprop,
14                      payload                 => payload,
15                      msgid                   => deq_msgid
16                     );
17     COMMIT;
18  END demo_dequeue;
19  /

PROCEDURE created.

SQL> BEGIN
  2    demo_dequeue('prog1');
  3  END;
  4  /

PL/SQL PROCEDURE successfully completed.

SQL> SET LINES 200

SQL> SELECT queue, TO_CHAR (enq_time, 'dd-mon-yyyy hh:mi:ss') enq_time,
  2         msg_priority, msg_state
  3    FROM aq$input_queue_table;

QUEUE                          ENQ_TIME                MSG_PRIORITY MSG_STATE
------------------------------ ----------------------- ------------ ----------------
INPUT_QUEUE                    03-jan-2012 01:38:38               1 PROCESSED
INPUT_QUEUE                    03-jan-2012 01:38:38               2 READY
INPUT_QUEUE                    03-jan-2012 01:38:38               3 READY
INPUT_QUEUE                    03-jan-2012 01:38:38               4 READY

SQL> SELECT q_name, TO_CHAR (enq_time, 'dd-mon-yyyy hh:mi:ss') enq_time, step_no,
  2         priority
  3    FROM input_queue_table;

Q_NAME                         ENQ_TIME                   STEP_NO   PRIORITY
------------------------------ ----------------------- ---------- ----------
INPUT_QUEUE                    03-jan-2012 09:38:38             0          2
INPUT_QUEUE                    03-jan-2012 09:38:38             0          3
INPUT_QUEUE                    03-jan-2012 09:38:38             0          4

SQL> SELECT queue, TO_CHAR (enq_time, 'dd-mon-yyyy hh:mi:ss') enq_time,
  2         msg_priority, msg_state
  3    FROM aq$input_queue_table;

QUEUE                          ENQ_TIME                MSG_PRIORITY MSG_STATE
------------------------------ ----------------------- ------------ ----------------
INPUT_QUEUE                    03-jan-2012 01:38:38               2 READY
INPUT_QUEUE                    03-jan-2012 01:38:38               3 READY
INPUT_QUEUE                    03-jan-2012 01:38:38               4 READY

SQL> SELECT q_name, TO_CHAR (enq_time, 'dd-mon-yyyy hh:mi:ss') enq_time,
  2         step_no, priority
  3    FROM input_queue_table;

Q_NAME                         ENQ_TIME                   STEP_NO   PRIORITY
------------------------------ ----------------------- ---------- ----------
INPUT_QUEUE                    03-jan-2012 09:38:38             0          2
INPUT_QUEUE                    03-jan-2012 09:38:38             0          3
INPUT_QUEUE                    03-jan-2012 09:38:38             0          4

SQL> DECLARE
  2     payload1   MESSAGE;
  3     payload2   MESSAGE;
  4     payload3   MESSAGE;
  5     payload4   MESSAGE;
  6  BEGIN
  7     payload1 := MESSAGE ('BELMONT');
  8     payload2 := MESSAGE ('REDWOOD SHORES');
  9     payload3 := MESSAGE ('SUNNYVALE');
10     payload4 := MESSAGE ('BURLINGAME');
11     demo_enqueue (payload1);
12     demo_enqueue (payload2);
13     demo_enqueue (payload3);
14     demo_enqueue (payload4);
15  END;
16  /

PL/SQL PROCEDURE successfully completed.

SQL> SELECT queue, TO_CHAR (enq_time, 'dd-mon-yyyy hh:mi:ss') enq_time,
  2         msg_priority, msg_state
  3    FROM aq$input_queue_table;

QUEUE                          ENQ_TIME                MSG_PRIORITY MSG_STATE
------------------------------ ----------------------- ------------ ----------------
INPUT_QUEUE                    03-jan-2012 01:38:38               2 READY
INPUT_QUEUE                    03-jan-2012 01:38:38               3 READY
INPUT_QUEUE                    03-jan-2012 01:38:38               4 READY
INPUT_QUEUE                    03-jan-2012 01:49:28               5 READY
INPUT_QUEUE                    03-jan-2012 01:49:28               6 READY
INPUT_QUEUE                    03-jan-2012 01:49:28               7 READY
INPUT_QUEUE                    03-jan-2012 01:49:28               8 READY

7 ROWS selected.

SQL> SELECT q_name, TO_CHAR (enq_time, 'dd-mon-yyyy hh:mi:ss') enq_time, step_no,
  2         priority
  3    FROM input_queue_table;

Q_NAME                         ENQ_TIME                   STEP_NO   PRIORITY
------------------------------ ----------------------- ---------- ----------
INPUT_QUEUE                    03-jan-2012 09:49:28             0          7
INPUT_QUEUE                    03-jan-2012 09:38:38             0          2
INPUT_QUEUE                    03-jan-2012 09:38:38             0          3
INPUT_QUEUE                    03-jan-2012 09:38:38             0          4
INPUT_QUEUE                    03-jan-2012 09:49:28             0          5
INPUT_QUEUE                    03-jan-2012 09:49:28             0          6
INPUT_QUEUE                    03-jan-2012 09:49:28             0          8

7 ROWS selected.

SQL> CREATE OR REPLACE PROCEDURE demo_dequeue (appname VARCHAR2)
  2  AS
  3     deq_msgid   RAW (16);
  4     dopt        DBMS_AQ.dequeue_options_t;
  5     mprop       DBMS_AQ.message_properties_t;
  6     payload     MESSAGE;
  7  BEGIN
  8     dopt.consumer_name := appname;
  9     dopt.WAIT := DBMS_AQ.no_wait;
10     dopt.navigation := DBMS_AQ.first_message;
11     DBMS_AQ.dequeue (queue_name              => 'input_queue',
12                      dequeue_options         => dopt,
13                      message_properties      => mprop,
14                      payload                 => payload,
15                      msgid                   => deq_msgid
16                     );
17     COMMIT;
18  END demo_dequeue;
19  /

PROCEDURE created.

SQL> SELECT queue, TO_CHAR (enq_time, 'dd-mon-yyyy hh:mi:ss') enq_time,
  2         msg_priority, msg_state
  3    FROM aq$input_queue_table;

QUEUE                          ENQ_TIME                MSG_PRIORITY MSG_STATE
------------------------------ ----------------------- ------------ ----------------
INPUT_QUEUE                    03-jan-2012 01:38:38               3 READY
INPUT_QUEUE                    03-jan-2012 01:38:38               4 READY
INPUT_QUEUE                    03-jan-2012 01:49:28               5 READY
INPUT_QUEUE                    03-jan-2012 01:49:28               6 READY
INPUT_QUEUE                    03-jan-2012 01:49:28               7 READY
INPUT_QUEUE                    03-jan-2012 01:49:28               8 READY

6 ROWS selected.

SQL> SELECT q_name, TO_CHAR (enq_time, 'dd-mon-yyyy hh:mi:ss') enq_time, step_no,
  2         priority
  3    FROM input_queue_table;

Q_NAME                         ENQ_TIME                   STEP_NO   PRIORITY
------------------------------ ----------------------- ---------- ----------
INPUT_QUEUE                    03-jan-2012 09:49:28             0          7
INPUT_QUEUE                    03-jan-2012 09:38:38             0          3
INPUT_QUEUE                    03-jan-2012 09:38:38             0          4
INPUT_QUEUE                    03-jan-2012 09:49:28             0          5
INPUT_QUEUE                    03-jan-2012 09:49:28             0          6
INPUT_QUEUE                    03-jan-2012 09:49:28             0          8

6 ROWS selected.

SQL> SELECT queue, TO_CHAR (enq_time, 'dd-mon-yyyy hh:mi:ss') enq_time,
  2         msg_priority, msg_state
  3    FROM aq$input_queue_table;

QUEUE                          ENQ_TIME                MSG_PRIORITY MSG_STATE
------------------------------ ----------------------- ------------ ----------------
INPUT_QUEUE                    03-jan-2012 01:38:38               4 READY
INPUT_QUEUE                    03-jan-2012 01:49:28               5 READY
INPUT_QUEUE                    03-jan-2012 01:49:28               6 READY
INPUT_QUEUE                    03-jan-2012 01:49:28               7 READY
INPUT_QUEUE                    03-jan-2012 01:49:28               8 READY

SQL> SELECT q_name, TO_CHAR (enq_time, 'dd-mon-yyyy hh:mi:ss') enq_time, step_no,
  2         priority
  3    FROM input_queue_table;

Q_NAME                         ENQ_TIME                   STEP_NO   PRIORITY
------------------------------ ----------------------- ---------- ----------
INPUT_QUEUE                    03-jan-2012 09:49:28             0          7
INPUT_QUEUE                    03-jan-2012 09:38:38             0          4
INPUT_QUEUE                    03-jan-2012 09:49:28             0          5
INPUT_QUEUE                    03-jan-2012 09:49:28             0          6
INPUT_QUEUE                    03-jan-2012 09:49:28             0          8

SQL> DECLARE
  2     payload1   MESSAGE;
  3     payload2   MESSAGE;
  4     payload3   MESSAGE;
  5     payload4   MESSAGE;
  6  BEGIN
  7     payload1 := MESSAGE ('BELMONT');
  8     payload2 := MESSAGE ('REDWOOD SHORES');
  9     payload3 := MESSAGE ('SUNNYVALE');
10     payload4 := MESSAGE ('BURLINGAME');
11     demo_enqueue (payload1);
12     demo_enqueue (payload2);
13     demo_enqueue (payload3);
14     demo_enqueue (payload4);
15  END;
16  /

PL/SQL PROCEDURE successfully completed.

SQL> SELECT queue, TO_CHAR (enq_time, 'dd-mon-yyyy hh:mi:ss') enq_time,
  2         msg_priority, msg_state
  3    FROM aq$input_queue_table;

QUEUE                          ENQ_TIME                MSG_PRIORITY MSG_STATE
------------------------------ ----------------------- ------------ ----------------
INPUT_QUEUE                    03-jan-2012 01:38:38               4 READY
INPUT_QUEUE                    03-jan-2012 01:49:28               5 READY
INPUT_QUEUE                    03-jan-2012 01:49:28               6 READY
INPUT_QUEUE                    03-jan-2012 01:49:28               7 READY
INPUT_QUEUE                    03-jan-2012 01:49:28               8 READY
INPUT_QUEUE                    03-jan-2012 01:57:38               9 READY
INPUT_QUEUE                    03-jan-2012 01:57:38              10 READY
INPUT_QUEUE                    03-jan-2012 01:57:38              11 READY
INPUT_QUEUE                    03-jan-2012 01:57:38              12 READY

9 ROWS selected.

SQL> BEGIN
  2    demo_dequeue('prog1');
  3  END;
  4  /

PL/SQL PROCEDURE successfully completed.

SQL> SELECT queue, TO_CHAR (enq_time, 'dd-mon-yyyy hh:mi:ss') enq_time,
  2         msg_priority, msg_state
  3    FROM aq$input_queue_table;

QUEUE                          ENQ_TIME                MSG_PRIORITY MSG_STATE
------------------------------ ----------------------- ------------ ----------------
INPUT_QUEUE                    03-jan-2012 01:38:38               4 PROCESSED
INPUT_QUEUE                    03-jan-2012 01:49:28               5 PROCESSED
INPUT_QUEUE                    03-jan-2012 01:49:28               6 READY
INPUT_QUEUE                    03-jan-2012 01:49:28               7 READY
INPUT_QUEUE                    03-jan-2012 01:49:28               8 READY
INPUT_QUEUE                    03-jan-2012 01:57:38               9 READY
INPUT_QUEUE                    03-jan-2012 01:57:38              10 READY
INPUT_QUEUE                    03-jan-2012 01:57:38              11 READY
INPUT_QUEUE                    03-jan-2012 01:57:38              12 READY

9 ROWS selected.

SQL> SELECT q_name, TO_CHAR (enq_time, 'dd-mon-yyyy hh:mi:ss') enq_time, step_no,
  2         priority
  3    FROM input_queue_table;

Q_NAME                         ENQ_TIME                   STEP_NO   PRIORITY
------------------------------ ----------------------- ---------- ----------
INPUT_QUEUE                    03-jan-2012 09:49:28             0          7
INPUT_QUEUE                    03-jan-2012 09:57:38             0          9
INPUT_QUEUE                    03-jan-2012 09:57:38             0         11
INPUT_QUEUE                    03-jan-2012 09:38:38             0          4
INPUT_QUEUE                    03-jan-2012 09:49:28             0          5
INPUT_QUEUE                    03-jan-2012 09:49:28             0          6
INPUT_QUEUE                    03-jan-2012 09:49:28             0          8
INPUT_QUEUE                    03-jan-2012 09:57:38             0         10
INPUT_QUEUE                    03-jan-2012 09:57:38             0         12

9 ROWS selected.

SQL> SELECT queue, TO_CHAR (enq_time, 'dd-mon-yyyy hh:mi:ss') enq_time,
  2         msg_priority, msg_state
  3    FROM aq$input_queue_table;

QUEUE                          ENQ_TIME                MSG_PRIORITY MSG_STATE
------------------------------ ----------------------- ------------ ----------------
INPUT_QUEUE                    03-jan-2012 01:49:28               6 READY
INPUT_QUEUE                    03-jan-2012 01:49:28               7 READY
INPUT_QUEUE                    03-jan-2012 01:49:28               8 READY
INPUT_QUEUE                    03-jan-2012 01:57:38               9 READY
INPUT_QUEUE                    03-jan-2012 01:57:38              10 READY
INPUT_QUEUE                    03-jan-2012 01:57:38              11 READY
INPUT_QUEUE                    03-jan-2012 01:57:38              12 READY

7 ROWS selected.

SQL> BEGIN
  2    demo_dequeue('prog1');
  3  END;
  4  /

PL/SQL PROCEDURE successfully completed.

SQL> SELECT queue, TO_CHAR (enq_time, 'dd-mon-yyyy hh:mi:ss') enq_time,
  2         msg_priority, msg_state
  3    FROM aq$input_queue_table;

QUEUE                          ENQ_TIME                MSG_PRIORITY MSG_STATE
------------------------------ ----------------------- ------------ ----------------
INPUT_QUEUE                    03-jan-2012 01:49:28               6 PROCESSED
INPUT_QUEUE                    03-jan-2012 01:49:28               7 READY
INPUT_QUEUE                    03-jan-2012 01:49:28               8 READY
INPUT_QUEUE                    03-jan-2012 01:57:38               9 READY
INPUT_QUEUE                    03-jan-2012 01:57:38              10 READY
INPUT_QUEUE                    03-jan-2012 01:57:38              11 READY
INPUT_QUEUE                    03-jan-2012 01:57:38              12 READY

7 ROWS selected.

SQL> /

QUEUE                          ENQ_TIME                MSG_PRIORITY MSG_STATE
------------------------------ ----------------------- ------------ ----------------
INPUT_QUEUE                    03-jan-2012 01:49:28               6 PROCESSED
INPUT_QUEUE                    03-jan-2012 01:49:28               7 READY
INPUT_QUEUE                    03-jan-2012 01:49:28               8 READY
INPUT_QUEUE                    03-jan-2012 01:57:38               9 READY
INPUT_QUEUE                    03-jan-2012 01:57:38              10 READY
INPUT_QUEUE                    03-jan-2012 01:57:38              11 READY
INPUT_QUEUE                    03-jan-2012 01:57:38              12 READY

7 ROWS selected.

SQL> /

QUEUE                          ENQ_TIME                MSG_PRIORITY MSG_STATE
------------------------------ ----------------------- ------------ ----------------
INPUT_QUEUE                    03-jan-2012 01:49:28               6 PROCESSED
INPUT_QUEUE                    03-jan-2012 01:49:28               7 READY
INPUT_QUEUE                    03-jan-2012 01:49:28               8 READY
INPUT_QUEUE                    03-jan-2012 01:57:38               9 READY
INPUT_QUEUE                    03-jan-2012 01:57:38              10 READY
INPUT_QUEUE                    03-jan-2012 01:57:38              11 READY
INPUT_QUEUE                    03-jan-2012 01:57:38              12 READY

7 ROWS selected.

SQL> /

QUEUE                          ENQ_TIME                MSG_PRIORITY MSG_STATE
------------------------------ ----------------------- ------------ ----------------
INPUT_QUEUE                    03-jan-2012 01:49:28               7 READY
INPUT_QUEUE                    03-jan-2012 01:49:28               8 READY
INPUT_QUEUE                    03-jan-2012 01:57:38               9 READY
INPUT_QUEUE                    03-jan-2012 01:57:38              10 READY
INPUT_QUEUE                    03-jan-2012 01:57:38              11 READY
INPUT_QUEUE                    03-jan-2012 01:57:38              12 READY

6 ROWS selected.
补充:可以用event 10960 去trace AQ,对于group级别的请参考如下mos文档。

Procedure to Dequeue Messages from any Queue not using Message Grouping [ID 243665.1]

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【学习笔记】Oracle advance queue高级队列详细研究笔记

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/1348.html

Oracle研究中心

关键词:

oracle advance queue测试步骤

Oracle数据库高级队列