DISPLAY_CURSOR

DISPLAY_CURSOR関数は、実行済みのクエリ計画の詳細を表示するために使用されます。

制限事項

この機能は、V4.3.5バージョンのV4.3.5 BP2以降でサポートされています。

構文

DBMS_XPLAN.DISPLAY_CURSOR(plan_id      DECIMAL          DEFAULT    0,         -- default value: last plan
                          format       VARCHAR(32)      DEFAULT    'TYPICAL',
                          svr_ip       VARCHAR(64)      DEFAULT    null,   -- default value: server connected by client
                          svr_port     DECIMAL          DEFAULT    0,     -- default value: server connected by client
                          tenant_id    DECIMAL          DEFAULT    0,     -- default value: current tenant
                          sql_handle   VARCHAR(32)      DEFAULT    NULL,
                          plan_name    VARCHAR(32)      DEFAULT    NULL
                        )
RETURN DBMS_XPLAN_TYPE_TABLE;

パラメータの説明

例

MySQLテナントはFunction Table機能を持たないため、SELECT ステートメント内で直接 DISPLAY_CURSOR 関数を使用して計画情報を出力できます。

1. テーブルを作成します。

   obclient [test]> CREATE TABLE t1(c1 INT);
   

2. クエリを実行します。

   obclient [test]> SELECT * FROM t1;
   

3. DBMS_XPLAN パッケージを使用して履歴計画を表示します。

   • パラメータなしのクエリ。

     obclient [test]> SELECT DBMS_XPLAN.DISPLAY_CURSOR() ;
     

     クエリ結果は次のとおりです：

     +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
     | DBMS_XPLAN.DISPLAY_CURSOR()                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
     +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
     | ================================================================================================
     |ID|OPERATOR       |NAME|EST.ROWS|EST.TIME(us)|REAL.ROWS|REAL.TIME(us)|IO TIME(us)|CPU TIME(us)|
     ------------------------------------------------------------------------------------------------
     |0 |TABLE FULL SCAN|t1  |1       |4           |0        |0            |0          |155         |
     ================================================================================================
     
     Outputs & filters:
     -------------------------------------
       0 - output([t1.c1]), filter(nil), rowset=16
           access([t1.c1]), partitions(p0)
           is_index_back=false, is_global_index=false,
           range_key([t1.__pk_increment]), range(MIN ; MAX)always true
     
     |
     +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
     1 row in set (0.173 sec)
     

   • パラメータ指定のクエリ。

     obclient [test]> SELECT DBMS_XPLAN.DISPLAY_CURSOR(294, 'typical', 'xx.xx.xx.xx',  2828, 1002, '07E5B378A3CD3778A58E18DB9AD2A430', '7420493073239164301') FROM DUAL;
     

     クエリ結果は次のとおりです：

     +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
     | DBMS_XPLAN.DISPLAY_CURSOR(         294,         'typical',         'xx.xx.xx.xx',         2828,         1002,         '07E5B378A3CD3778A58E18DB9AD2A430',         '7420493073239164301'       ) |
     +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
     |                                                                                                                                                                                                    |
     +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
     1 row in set (0.005 sec)