Team:
This below testcase was run on Oracle 19.9 database.
q1) why the online stats gathering feature doesn't kick in post the direct path load on "twitter_data" table?
q2) could you please help us to understand though we have Mview inplace, why the optimizer is not performing query rewrites?
demo@QES1> select banner_full from v$version;
BANNER_FULL
-------------------------------------------------------------------------
---
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.9.0.0.0
demo@QES1> create table twitter_data( doc_id number,
2 twit clob,
3 constraint twitter_data_pk primary key(doc_id),
4 constraint twitter_data_chk
5 check( twit is json) );
Table created.
demo@QES1> declare
2 l_sql long;
3 begin
4 l_sql := q'# {
5 "text": "RT @PostGradProblem: In preparation for the NFL lockout, I will be spending twice as much time analyzing my fantasy baseball team during ...",
6 "truncated": true,
7 "in_reply_to_user_id": null,
8 "in_reply_to_status_id": null,
9 "favorited": false,
10 "source": "<a href=\"http://twitter.com/\" rel=\"nofollow\">Twitter for iPhone</a>",
11 "in_reply_to_screen_name": null,
12 "in_reply_to_status_id_str": null,
13 "id_str": "54691802283900928",
14 "entities": {
15 "user_mentions": [
16 {
17 "indices": [
18 3,
19 19
20 ],
21 "screen_name": "PostGradProblem",
22 "id_str": "271572434",
23 "name": "PostGradProblems",
24 "id": 271572434
25 }
26 ],
27 "urls": [ ],
28 "hashtags": [ ]
29 },
30 "contributors": null,
31 "retweeted": false,
32 "in_reply_to_user_id_str": null,
33 "place": null,
34 "retweet_count": 4,
35 "created_at": "Sun Apr 03 23:48:36 +0000 2011",
36 "user": {
37 "notifications": null,
38 "profile_use_background_image": true,
39 "statuses_count": 351,
40 "profile_background_color": "C0DEED",
41 "followers_count": 48,
42 "profile_image_url": "http://a1.twimg.com/profile_images/455128973/gCsVUnofNqqyd6tdOGevROvko1_500_normal.jpg",
43 "listed_count": 0,
44 "profile_background_image_url": "http://a3.twimg.com/a/1300479984/images/themes/theme1/bg.png",
45 "description": "watcha doin in my waters?",
46 "screen_name": "OldGREG85",
47 "default_profile": true,
48 "verified": false,
49 "time_zone": "Hawaii",
50 "profile_text_color": "333333",
51 "is_translator": false,
52 "profile_sidebar_fill_color": "DDEEF6",
53 "location": "Texas",
54 "id_str": "##x1##",
55 "default_profile_image": false,
56 "profile_background_tile": false,
57 "lang": "en",
58 "friends_count": 81,
59 "protected": false,
60 "favourites_count": 0,
61 "created_at": "Tue Oct 06 01:13:17 +0000 2009",
62 "profile_link_color": "0084B4",
63 "name": "GG",
64 "show_all_inline_media": false,
65 "follow_request_sent": null,
66 "geo_enabled": false,
67 "profile_sidebar_border_color": "C0DEED",
68 "url": "##x2##",
69 "id": 80177619,
70 "contributors_enabled": false,
71 "following": null,
72 "utc_offset": -36000
73 },
74 "id": 54691802283900930,
75 "coordinates": null,
76 "geo": null
77 } #';
78 insert /*+ append */ into twitter_data(doc_id, twit )
79 select rownum,replace( replace( l_sql, '"##x1##"', object_id ), '"##x2##"', '"'||substr( object_name,1,20)||'"' )
80 from all_objects , all_users
81 where rownum <=1000000;
82 commit;
83 end;
84 /
PL/SQL procedure successfully completed.
demo@QES1> select num_rows,blocks,empty_blocks,last_analyzed
2 from user_tab_statistics
3 where table_name ='TWITTER_DATA';
NUM_ROWS BLOCKS EMPTY_BLOCKS LAST_ANALYZ
---------- ---------- ------------ -----------
demo@QES1> exec dbms_stats.gather_table_stats(user,'TWITTER_DATA');
PL/SQL procedure successfully completed.
demo@QES1> select num_rows,blocks,empty_blocks,last_analyzed
2 from user_tab_statistics
3 where table_name ='TWITTER_DATA';
NUM_ROWS BLOCKS EMPTY_BLOCKS LAST_ANALYZ
---------- ---------- ------------ -----------
1000000 6796 0 21-DEC-2020
demo@QES1> create materialized view mv_twit
2 build immediate
3 refresh fast on statement
4 enable query rewrite
5 as
6 select doc_id,Jt.*
7 from twitter_data, json_table( twit, '$.user'
8 columns(
9 id_str number path '$.id_str' error on error null on empty,
10 url varchar2(20) path '$.url' error on error null on empty,
11 got_geo varchar2(10) path '$.geo_enabled' error on error null on empty,
12 friends_cnt number path '$.friends_count' error on error null on empty
13 )) jt
14 /
Materialized view created.
demo@QES1> select num_rows,blocks,empty_blocks,last_analyzed
2 from user_tab_statistics
3 where table_name ='MV_TWIT';
NUM_ROWS BLOCKS EMPTY_BLOCKS LAST_ANALYZ
---------- ---------- ------------ -----------
1000000 5937 0 21-DEC-2020
demo@QES1> set autotrace traceonly exp
demo@QES1> select t.twit."user".id_str id_str,
2 t.twit."user".url url,
3 t.twit."user".geo_enabled got_geo,
4 t.twit."user".friends_count cnt
5 from twitter_data t
6 where t.twit."user".id_str ='360';
Execution Plan
----------------------------------------------------------
Plan hash value: 2996362279
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 81M| 10G| 27M (1)| 00:17:45 |
| 1 | NESTED LOOPS | | 81M| 10G| 27M (1)| 00:17:45 |
| 2 | TABLE ACCESS FULL | TWITTER_DATA | 1000K| 126M| 1849 (1)| 00:00:01 |
|* 3 | JSONTABLE EVALUATION | | | | | |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("P"."C_04$"='360')
demo@QES1> select doc_id,Jt.*
2 from twitter_data, json_table( twit, '$.user'
3 columns(
4 id_str number path '$.id_str' error on error null on empty,
5 url varchar2(20) path '$.url' error on error null on empty,
6 got_geo varchar2(10) path '$.geo_enabled' error on error null on empty,
7 friends_cnt number path '$.friends_count' error on error null on empty
8 )) jt
9 /
Execution Plan
----------------------------------------------------------
Plan hash value: 2996362279
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8168M| 1110G| 27M (1)| 00:17:42 |
| 1 | NESTED LOOPS | | 8168M| 1110G| 27M (1)| 00:17:42 |
| 2 | TABLE ACCESS FULL | TWITTER_DATA | 1000K| 131M| 1849 (1)| 00:00:01 |
| 3 | JSONTABLE EVALUATION | | | | | |
---------------------------------------------------------------------------------------
demo@QES1> set autotrace off
demo@QES1>
demo@QES1>
demo@QES1> begin
2 dbms_mview.Explain_Rewrite(query=>q'# select t.twit."user".id_str id_str,
3 t.twit."user".url url,
4 t.twit."user".geo_enabled got_geo,
5 t.twit."user".friends_count cnt
6 from twitter_data t
7 where t.twit."user".id_str ='360' #');
8 end;
9 /
PL/SQL procedure successfully completed.
demo@QES1> select sequence,message,pass from rewrite_table;
SEQUENCE MESSAGE PASS
---------- ------------------------------ ----------
1 QSM-01150: query did not rewri NO
te
2 QSM-01263: query rewrite not p YES
ossible when query references
a dictionary table or view
demo@QES1> show parameter query_rewrite_enab
NAME TYPE VALUE
------------------------------------ ----------- ------
query_rewrite_enabled string TRUE
demo@QES1>
1. The insert is in conventional mode, not direct-path!
The presence of the JSON check constraint is causing this. You can validate this by trying to query the table after the insert. If it used direct-path, you'll get an error; with conventional it works:
create table twitter_data (
doc_id number,
twit clob,
constraint twitter_data_pk primary key(doc_id)
);
insert /*+ append_values */into twitter_data
values ( 1, '{ test : "test" }' );
select * from twitter_data;
ORA-12838: cannot read/modify an object after modifying it in parallel
commit;
alter table twitter_data add
constraint twitter_data_chk
check ( twit is json );
insert /*+ append_values */into twitter_data
values ( 2, '{ test : "test" }' );
select * from twitter_data;
DOC_ID TWIT
1 { test : "test" }
2 { test : "test" }
commit;2. The dot-notation query has this where clause
t.twit."user".id_str ='360'
The JSON_table expression declares id_str as a number. So there's a character <> numeric comparison => implicit conversion!
This is causing the rewrite to fail somewhere. Use the correct data types it does use the MV:
set serveroutput off
select t.twit."user".id_str id_str,
t.twit."user".url url,
t.twit."user".geo_enabled got_geo,
t.twit."user".friends_count cnt
from twitter_data t
where t.twit."user".id_str = '360'; -- implicit conversion
select *
from table(dbms_xplan.display_cursor(null, null, 'BASIC LAST'));
-----------------------------------------------
| Id | Operation | Name |
-----------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | NESTED LOOPS | |
| 2 | TABLE ACCESS FULL | TWITTER_DATA |
| 3 | JSONTABLE EVALUATION | |
-----------------------------------------------
select t.twit."user".id_str id_str,
t.twit."user".url url,
t.twit."user".geo_enabled got_geo,
t.twit."user".friends_count cnt
from twitter_data t
where t.twit."user".id_str = 360;
select *
from table(dbms_xplan.display_cursor(null, null, 'BASIC LAST'));
------------------------------------------------------------------
| Id | Operation | Name |
------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | NESTED LOOPS | |
| 2 | NESTED LOOPS SEMI | |
| 3 | TABLE ACCESS FULL | TWITTER_DATA |
| 4 | MAT_VIEW ACCESS BY INDEX ROWID BATCHED| MV_TWIT |
| 5 | INDEX RANGE SCAN | I_OS$_MV_TWIT |
| 6 | JSONTABLE EVALUATION | |
------------------------------------------------------------------