漢のDB講座 番外編
今回は、先日のDB講座でOracleのクエリーリライトに軽く説明してみましたが、
ログにはどのように出力されるのか？と言った質問を受けたので、
調査してみました。
ログと一言で言ってもOracleには様々なログがあります。
REDOログ、監査ログ、アーカイブログ…。
でも、今回の質問の意図がクエリーリライト機能が使われた事を
どのように知れるかってことなので、身近なAUTOTRACEとSQLトレースでどのような
出力になるかの観点で調査しております。
環境は以下の通り。
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production Windows 2012 R2 (on Windows Azure)
なお、構成については、以下を参照させていただきました。
クエリー・リライト - オラクル・Oracleをマスターするための基本と仕組み
今回利用するテーブル
DESC SALES; Name Null? Type --------------------- -------- -------------- USER_NAME VARCHAR2(5) SALES NUMBER SALES_DATE DATE
サンプルデータとして４０万ちょっとの件数を用意しました。
SELECT * FROM SALES;
... BOB 16300 24-FEB-11 SAM 19300 24-FEB-11 USER_ SALES SALESE_DA ----- ---------- --------- DAVE 19400 24-FEB-11 CAS 22900 24-FEB-11 TOM 28700 24-FEB-11 BOB 14400 24-FEB-11 SAM 12100 24-FEB-11 DAVE 17600 24-FEB-11 CAS 16300 24-FEB-11 TOM 13600 24-FEB-11 BOB 20000 24-FEB-11 SAM 26400 24-FEB-11 DAVE 1200 08-DEC-10 412280 rows selected.
さて、簡単に現在の状態でAUTOTRACEを確認してみます。
set autotrace on SELECT USER_NAME, SUM(SALES) SUM_SALES FROM SALES WHERE USER_NAME = 'TOM' GROUP BY USER_NAME ;
USER_ SUM_SALES
----- ----------
TOM 1233431500
Execution Plan
----------------------------------------------------------
Plan hash value: 1729043503
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 17 | 3 (0)| 00:00:01 |
| 1 | SORT GROUP BY NOSORT| | 1 | 17 | 3 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL | SALES | 1 | 17 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("USER_NAME"='TOM')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1213 consistent gets
0 physical reads
0 redo size
621 bytes sent via SQL*Net to client
543 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processedまあ、普通にテーブルアクセスフルが出てきます。
続いて、現在の状態でSQLトレースを確認してみます。
ALTER SESSION SET SQL_TRACE = TRUE; SELECT USER_NAME, TRUNC(SALES_DATE, 'MONTH') SALES_DATE, SUM(SALES) SUM_SALES FROM SALES GROUP BY USER_NAME, TRUNC(SALES_DATE, 'MONTH') ; ALTER SESSION SET SQL_TRACE = FALSE;
以下、tkprofの結果です。
該当する箇所だけ抜き取ってみます。
SELECT
USER_NAME, TRUNC(SALES_DATE, 'MONTH') SALES_DATE, SUM(SALES) SUM_SALES
FROM
SALES
GROUP BY
USER_NAME, TRUNC(SALES_DATE, 'MONTH')
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.02 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 39 0.29 0.31 0 1213 0 565
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 41 0.29 0.33 0 1213 0 565
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 104
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
565 565 565 HASH GROUP BY (cr=1213 pr=0 pw=0 time=310196 us cost=3 size=26 card=1)
412280 412280 412280 TABLE ACCESS FULL SALES (cr=1213 pr=0 pw=0 time=195467 us cost=3 size=26 card=1)
********************************************************************************
ふむふむ、こちらもテーブルフルスキャンになっていますね。
さて、いよいよ書き換え用のマテリアライズドビューを作成します。
CREATE MATERIALIZED VIEW SALES_SUM_PRODUCT_MONTH BUILD IMMEDIATE REFRESH COMPLETE ENABLE QUERY REWRITE AS SELECT USER_NAME, TRUNC(SALES_DATE, 'MONTH') SALES_DATE, SUM(SALES) SUM_SALES FROM SALES GROUP BY USER_NAME, TRUNC(SALES_DATE, 'MONTH') ;
ENABLE QUERY REWRITE
ってところがクエリーリライトのための記述っすね。
では、実行してみましょう。
セッション内でクエリーリライトの使用を可能にします。
ALTER SESSION SET QUERY_REWRITE_ENABLED=TRUE;
トレースの出力を確認するため、強制的にクエリーリライトを使うよう
ヒント句を付与してSQLを実行します。
SELECT /*+ REWRITE(SALES_SUM_PRODUCT_MONTH) */ USER_NAME, SUM(SALES) SUM_SALES FROM SALES WHERE USER_NAME = 'TOM' AND TRUNC(SALES_DATE, 'MONTH') = TO_DATE('2005/10','YYYY/MM') GROUP BY USER_NAME, TRUNC(SALES_DATE, 'MONTH');
AUTOTRACEの実行結果です。
Execution Plan
----------------------------------------------------------
Plan hash value: 2900729471
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 17 | 3 (0)| 00:00:01 |
|* 1 | MAT_VIEW REWRITE ACCESS FULL| SALES_SUM_PRODUCT_MONTH | 1 | 17 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("SALES_SUM_PRODUCT_MONTH"."SALES_DATE"=TO_DATE(' 2005-10-01 00:00:00',
'syyyy-mm-dd hh24:mi:ss') AND "SALES_SUM_PRODUCT_MONTH"."USER_NAME"='TOM')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
620 bytes sent via SQL*Net to client
543 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQLトレースの結果です。
********************************************************************************
SELECT /*+ REWRITE(SALES_SUM_PRODUCT_MONTH) */
USER_NAME, SUM(SALES) SUM_SALES
FROM
SALES
WHERE
USER_NAME = 'TOM'
AND TRUNC(SALES_DATE, 'MONTH') = TO_DATE('2005/10','YYYY/MM')
GROUP BY
USER_NAME, TRUNC(SALES_DATE, 'MONTH')
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 5 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 5 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 104
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 MAT_VIEW REWRITE ACCESS FULL SALES_SUM_PRODUCT_MONTH (cr=5 pr=0 pw=0 time=53 us cost=3 size=17 card=1)
********************************************************************************おぉぉ、どちらの結果も「MAT_VIEW REWRITE」「SALES_SUM_PRODUCT_MONTH」
となっていて、クエリーが書き換わったのが出力されている事が分かります。
クエリーリライトはかなり効果的な機能ではありますが、
あくまでも暫定処置、緊急の応急処置と捉えるべきでしょう。
実行されるはずのSQLをOracleが書き換えているため、環境が換わったら遅くなったって
こともあり得ます。
修正すべき箇所が分かっていれば、誰が見てもわかるコードにしないと、保守性も低下しますし。
さて、上にも書きましたが、今回、サンプルデータを作成するのに苦労しました。
JDBCの簡単なプログラムでサンプルデータを作ろうと思いましたが、
ローカルからOracleへの接続がうまくできず、結果、ローカルのMySQLにデータを作成し、
強引にINSERT文を作り、Azure上で実行という力技でした。
この辺りも、ちょっと研究をしなければと思う年の瀬でありんす。
では、みなさん Merry X'mas!!
