Home » RDBMS Server » Performance Tuning » Oracle 9i (SQL not using MV.)
| Oracle 9i [message #377738] |
Wed, 24 December 2008 14:05  |
chintu00
Messages: 91
Registered: February 2007
Location: NJ, US
|
Member |
|
|
I had a view Items_Sales_V with the same SQL as in the MV below. I created a MV now when I do a select * from my view above. The explain plan does'nt show that it using the MV. It is doing a full table scan of both the tables in the SQL.
CREATE MATERIALIZED VIEW Items_sales_MV
TABLESPACE DATA
NOCACHE
LOGGING
NOPARALLEL
BUILD IMMEDIATE
REFRESH FORCE ON DEMAND
WITH PRIMARY KEY
ENABLE QUERY REWRITE
AS
SELECT
trim(r.frm) as sub_group
,i.grp as Group
,r.vend as vendor
,r.itm as ITM
,r.FRCST_YR as FSCL_YR
,sum(r.mon1/1000) as JAN
,sum(r.mon2/1000) as FEB
,sum(r.mon3/1000) as MAR
,sum(r.mon4/1000) as APR
,sum(r.mon5/1000) as MAY
,sum(r.mon6/1000) as JUN
,sum(r.mon7/1000) as JUL
,sum(r.mon8/1000) as AUG
,sum(r.mon9/1000) as SEP
,sum(r.mon10/1000) as OCT
,sum(r.mon11/1000) as NOV
,sum(r.mon12/1000) as DEC
From Item_Master i, Receipt_itm r
Where i.active = 'A'
and i.Item = r.Itm
Group By
r.frm
,i.grp
,r.vend
,r.itm
,r.FRCST_YR;
Can somebody help why my view doesn't use MV
|
|
|
|
|
|
| Re: Oracle 9i [message #377742 is a reply to message #377739] |
Wed, 24 December 2008 16:18  |
chintu00
Messages: 91
Registered: February 2007
Location: NJ, US
|
Member |
|
|
My explain plan as below. It is using the tables instead MV I have created.
SELECT STATEMENT CHOOSE
Cost: 19,526 Bytes: 51,994,613 Cardinality: 776,039
4 SORT GROUP BY
Cost: 19,526 Bytes: 51,994,613 Cardinality: 776,039
3 HASH JOIN
Cost: 10,908 Bytes: 51,994,613 Cardinality: 776,039
1 TABLE ACCESS FULL ITEM_MASTER
Cost: 8,355 Bytes: 5,006,066 Cardinality: 385,082
2 TABLE ACCESS FULL RECEIPT_ITM
Cost: 1,338 Bytes: 57,494,556 Cardinality: 1,064,714
The SQL for my view is same as SQL for my MV I have posted above.
Thanks
|
|
|
|
| Re: Oracle 9i [message #377744 is a reply to message #377739] |
Wed, 24 December 2008 16:34 |
chintu00
Messages: 91
Registered: February 2007
Location: NJ, US
|
Member |
|
|
The explain plan shows that the SQL uses the table instead of the MV I have created.
SELECT STATEMENT CHOOSE
Cost: 19,526 Bytes: 51,994,613 Cardinality: 776,039
4 SORT GROUP BY
Cost: 19,526 Bytes: 51,994,613 Cardinality: 776,039
3 HASH JOIN
Cost: 10,908 Bytes: 51,994,613 Cardinality: 776,039
1 TABLE ACCESS FULL ITEM_MASTER
Cost: 8,355 Bytes: 5,006,066 Cardinality: 385,082
2 TABLE ACCESS FULL RECEIPT_ITM
Cost: 1,338 Bytes: 57,494,556 Cardinality: 1,064,714
I am doing a select * from the view and SQL for my view is same as SQL for MV I have created as above.
|
|
|
|
|
|
| Re: Oracle 9i [message #377801 is a reply to message #377777] |
Thu, 25 December 2008 03:49 |
trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam
|
Senior Member |
|
|
I make an example for you.
In this example, I create 2 MVIEW, one is refresh complete, one is query rewrite
C:\>sqlplus metatest/metatest@meta
SQL*Plus: Release 10.1.0.2.0 - Production on Thu Dec 25 16:07:46 2008
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing o
metatest@META> alter user hr account unlock;
User altered.
metatest@META> alter user hr identified by hr;
User altered.
metatest@META> create materialized view log on hr.employees;
Materialized view log created.
metatest@META> create materialized view log on hr.departments
2 /
Materialized view log created.
metatest@META> create materialized view meta_hr_mview
2 refresh complete
3 next sysdate+3/(24*60)
4 as
5 select e.employee_id ID, e.last_name NAME, sum(E.salary) TOTAL_SAL,
6 d.department_name DEPT_NAME
7 from hr.employees E, hr.departments D
8 where E.department_id=D.department_id
9 group by e.employee_id, e.last_name,d.department_name
10 /
Materialized view created.
metatest@META> create materialized view hr_meta_mview_equery
2 enable query rewrite
3 as
4 select e.employee_id ID, e.last_name NAME, sum(E.salary) TOTAL_SAL,
5 d.department_name DEPT_NAME
6 from hr.employees E, hr.departments D
7 where E.department_id=D.department_id
8 group by e.employee_id, e.last_name,d.department_name
9 /
Materialized view created.
Now, I recheck if the query revokes MView or not
metatest@META> set autotrace traceonly
metatest@META> select e.employee_id ID, e.last_name NAME, sum(E.salary) TOTAL_SA
L,
2 d.department_name DEPT_NAME
3 from hr.employees E, hr.departments D
4 where E.department_id=D.department_id
5 group by e.employee_id, e.last_name,d.department_name
6 /
106 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=106 Bytes=6042)
1 0 MAT_VIEW REWRITE ACCESS (FULL) OF 'HR_META_MVIEW_EQUERY' (MAT_VIEW R
EWRITE) (C
ost=3 Card=106 Bytes=6042)
Statistics
----------------------------------------------------------
12 recursive calls
0 db block gets
16 consistent gets
0 physical reads
0 redo size
3791 bytes sent via SQL*Net to client
589 bytes received via SQL*Net from client
9 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
106 rows processed
Said it as MAT_VIEW in the statistics. Now, I do not want retrieve from MVIEW
metatest@META> alter session set query_rewrite_enabled=false;
alter session set query_rewrite_enabled=false
*
ERROR at line 1:
ORA-02248: invalid option for ALTER SESSION
metatest@META> show parameter query_
metatest@META> host sqlplus sys/tuananhtran@meta as sysdba
SQL*Plus: Release 10.1.0.2.0 - Production on Thu Dec 25 16:33:57 2008
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
sys@META> grant query rewrite to metatest;
Grant succeeded.
sys@META> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Pr
oduction
With the Partitioning, OLAP, Data Mining and Real Application Testing options
metatest@META> alter session set query_rewrite_enabled=false;
Session altered.
metatest@META> select e.employee_id ID, e.last_name NAME, sum(E.salary) TOTAL_SAL,
2 d.department_name DEPT_NAME
3 from hr.employees E, hr.departments D
4 where E.department_id=D.department_id
5 group by e.employee_id, e.last_name,d.department_name
6 /
106 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=7 Card=106 Bytes=3710)
1 0 HASH (GROUP BY) (Cost=7 Card=106 Bytes=3710)
2 1 MERGE JOIN (Cost=6 Card=106 Bytes=3710)
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'DEPARTMENTS' (TABLE) (Cost=2 C
ard=27 Byt
es=432)
4 3 INDEX (FULL SCAN) OF 'DEPT_ID_PK' (INDEX (UNIQUE)) (Cost=1 Car
d=27)
5 2 SORT (JOIN) (Cost=4 Card=107 Bytes=2033)
6 5 TABLE ACCESS (FULL) OF 'EMPLOYEES' (TABLE) (Cost=3 Card=107 By
tes=2033)
Statistics
----------------------------------------------------------
53 recursive calls
0 db block gets
15 consistent gets
0 physical reads
0 redo size
3791 bytes sent via SQL*Net to client
589 bytes received via SQL*Net from client
9 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
106 rows processed
metatest@META> alter session set query_rewrite_enabled=true;
Session altered.
metatest@META> select e.employee_id ID, e.last_name NAME, sum(E.salary) TOTAL_SA
L,
2 d.department_name DEPT_NAME
3 from hr.employees E, hr.departments D
4 where E.department_id=D.department_id
5 group by e.employee_id, e.last_name,d.department_name
6 /
106 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=106 Bytes=6042)
1 0 MAT_VIEW REWRITE ACCESS (FULL) OF 'HR_META_MVIEW_EQUERY' (MAT_VIEW R
EWRITE) (C
ost=3 Card=106 Bytes=6042)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
10 consistent gets
0 physical reads
0 redo size
3791 bytes sent via SQL*Net to client
589 bytes received via SQL*Net from client
9 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
106 rows processed
metatest@META>
The above code, you will see, if I disable query_rewrite_enabled, the statement retrieved data from master tables (EMPLOYEES and DEPARTMENTS) instead of revoking MVIEW.
At the end, I will refresh the MVIEW when I update one row in HR.EMPLOYEES
metatest@META> set autotrace off
metatest@META> select employee_id, last_name
2 from hr.employees
3 where employee_id=165
4 /
EMPLOYEE_ID LAST_NAME
----------- -------------------------
165 Lee
metatest@META>
metatest@META> update hr.employees
2 set last_name='LEE'
3 where employee_id=165
4 /
1 row updated.
metatest@META> commit;
Commit complete.
metatest@META> select employee_id,last_name
2 from hr.employees
3 where employee_id=165
4 /
EMPLOYEE_ID LAST_NAME
----------- -------------------------
165 LEE
metatest@META> execute dbms_refresh.refresh('META_HR_MVIEW');
PL/SQL procedure successfully completed.
metatest@META> select * from meta_hr_mview
2 where id=165
3 /
ID NAME TOTAL_SAL DEPT_NAME
---------- ------------------------- ---------- ----------
165 LEE 6800 Sales
metatest@META>
[Updated on: Thu, 25 December 2008 03:54] Report message to a moderator |
|
|
|
| Re: Oracle 9i [message #377816 is a reply to message #377801] |
Thu, 25 December 2008 12:46 |
chintu00
Messages: 91
Registered: February 2007
Location: NJ, US
|
Member |
|
|
trantuananh24hg that was a nice example.
But I noticed you have created 2 materialized. Can you please tell why?
My Materialized view definition is different from yours. But I have also enabled query rewrite in MV definition. I pasting it again.
I also don't have Materialized view logs on the table. I don't think we need MV logs for refresh on demand MVs.
NOCACHE
LOGGING
NOPARALLEL
BUILD IMMEDIATE
REFRESH FORCE ON DEMAND
WITH PRIMARY KEY
ENABLE QUERY REWRITE
|
|
|
|
| Re: Oracle 9i [message #377836 is a reply to message #377816] |
Thu, 25 December 2008 20:37 |
trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam
|
Senior Member |
|
|
I do not often use MView enable query, but I will re-create an example like yours
metatest@META> select * from tab;
no rows selected
metatest@META> CREATE MATERIALIZED VIEW customers_orders_mv
2 TABLESPACE RSMGR
3 NOCACHE
4 LOGGING
5 NOPARALLEL
6 BUILD IMMEDIATE
7 REFRESH FORCE ON DEMAND
8 WITH PRIMARY KEY
9 ENABLE QUERY REWRITE
10 AS
11 SELECT c.customer_id ID, c.cust_first_name NAME,
12 o.order_date ord_date,
13 o.order_status ord_status,SUM(o.order_total) ord_total
14 FROM customers@metadwh_oe c, orders@metadwh_oe o
15 WHERE c.customer_id = o.customer_id
16 GROUP BY c.customer_id,
17 c.cust_first_name,
18 o.order_date,
19 o.order_status
20 order by o.order_date desc
21 /
Materialized view created.
metatest@META> set autotrace traceonly
metatest@META> select * from customers_orders_mv
2 /
105 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=105 Bytes=6720)
1 0 MAT_VIEW ACCESS (FULL) OF 'CUSTOMERS_ORDERS_MV' (MAT_VIEW) (Cost=3 Card=105 Bytes=6720)
Statistics
----------------------------------------------------------
234 recursive calls
0 db block gets
73 consistent gets
0 physical reads
0 redo size
5000 bytes sent via SQL*Net to client
578 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
105 rows processed
|
|
|
|
| Re: Oracle 9i [message #377839 is a reply to message #377836] |
Thu, 25 December 2008 21:10 |
chintu00
Messages: 91
Registered: February 2007
Location: NJ, US
|
Member |
|
|
|
You did select from the MV itself. Please do a select as in the definition of MV. Would you suspect MV logs are required as in my case.
|
|
|
|
| Re: Oracle 9i [message #377842 is a reply to message #377839] |
Thu, 25 December 2008 21:30 |
trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam
|
Senior Member |
|
|
Oh, as my understanding, you wonder that, you created a MView like that
metatest@META> CREATE MATERIALIZED VIEW customers_orders_mv_meta
2 TABLESPACE RSMGR
3 NOCACHE
4 LOGGING
5 NOPARALLEL
6 BUILD IMMEDIATE
7 REFRESH FORCE ON DEMAND
8 WITH PRIMARY KEY
9 ENABLE QUERY REWRITE
10 AS
11 SELECT c.customer_id ID, c.cust_first_name NAME,
12 o.order_date ord_date,
13 o.order_status ord_status,SUM(o.order_total) ord_total
14 FROM oe.customers c, oe.orders o
15 WHERE c.customer_id = o.customer_id
16 GROUP BY c.customer_id,
17 c.cust_first_name,
18 o.order_date,
19 o.order_status
20 order by o.order_date desc
21 /
Materialized view created.
But when you select from OE.Customers & OE.Orders, you'd like to navigate the statement which retrieved data from MView, not from master tables like here
metatest@META> SELECT c.customer_id ID, c.cust_first_name NAME,
2 o.order_date ord_date,
3 o.order_status ord_status,SUM(o.order_total) ord_total
4 FROM oe.customers c, oe.orders o
5 WHERE c.customer_id = o.customer_id
6 GROUP BY c.customer_id,
7 c.cust_first_name,
8 o.order_date,
9 o.order_status
10 order by o.order_date desc
11 /
105 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=9 Card=105 Bytes=3675)
1 0 SORT (GROUP BY) (Cost=9 Card=105 Bytes=3675)
2 1 HASH JOIN (Cost=8 Card=105 Bytes=3675)
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'ORDERS' (TABLE) (Cost=2 Card=105 Bytes=2520)
4 3 INDEX (RANGE SCAN) OF 'ORD_CUSTOMER_IX' (INDEX) (Cost=1 Card=1
05)
5 2 TABLE ACCESS (FULL) OF 'CUSTOMERS' (TABLE) (Cost=5 Card=319 Bytes=3509)
Statistics
----------------------------------------------------------
15 recursive calls
0 db block gets
22 consistent gets
0 physical reads
0 redo size
5000 bytes sent via SQL*Net to client
578 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
105 rows processed
metatest@META>
|
|
|
|
|
|
|
|
|
|
| Re: Oracle 9.2.0.8.0 [message #378267 is a reply to message #377801] |
Mon, 29 December 2008 11:07 |
chintu00
Messages: 91
Registered: February 2007
Location: NJ, US
|
Member |
|
|
Please create a same simple view with the same SQL as in the Materialized. Then when you select from the view. It does not use the Materialized view but the tables.
When I use dbms_mview.Explain_Rewrite
I get following 2 messages in my tables.
QSM-01063: query has a dictionary table or view
QSM-01019: no suitable materialized view found to rewrite this query
Does that mean I can't use a table or view in my SQL where I expect MV to be used.
|
|
|
|
| Re: Oracle 9.2.0.8.0 [message #378301 is a reply to message #378267] |
Mon, 29 December 2008 20:47 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
QSM-01063: query has a dictionary table or view
Sounds like your SQL references a table owned by SYS.
Are you selecting from DUAL?
Ross Leishman
|
|
|
|
| Re: Oracle 9.2.0.8.0 [message #378457 is a reply to message #378301] |
Tue, 30 December 2008 09:03 |
chintu00
Messages: 91
Registered: February 2007
Location: NJ, US
|
Member |
|
|
|
I create a MV then a View with the same SQL. Then I do a select * from view. It does not use MV. I am not using dual or any other sys owned objects.
|
|
|
|
| Re: Oracle 9.2.0.8.0 [message #378519 is a reply to message #378457] |
Tue, 30 December 2008 18:58 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
What happens if you just run the SQL that is in the view, rather than selecting from the view?
Ross Leishman
|
|
|
|
|
|
| Re: Oracle 9.2.0.8.0 [message #378525 is a reply to message #378522] |
Tue, 30 December 2008 20:00 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
I just did a quick test myself:
- Create a table
- Create an aggregate MV
- Select from the table using same SQL as the aggregate MV - did not rewrite
- Repeated SELECT with /*+ REWRITE*/ hint - worked
- Created VIEW with same SQL - no hint
- SELECT * FROM View - didn't rewrite
- SELECT /*+REWRITE*/ * from View - worked.
This tells me that it is possible to get REWRITE working when selecting from a view that has the same SQL as the MV, although I needed to use the REWRITE hint.
Try the REWRITE hint. If that doesn't work, try a much simpler test case and see if that works. My test case was a copy of DBA_TABLES and a view/MV/SQL of SELECT owner, count(*) FROM tbl GROUP BY owner.
Note that I am using 10.2. It may be a limitation on v9
Ross Leishman
|
|
|
|
| Re: Oracle 9.2.0.8.0 [message #379979 is a reply to message #378301] |
Thu, 08 January 2009 16:14 |
chintu00
Messages: 91
Registered: February 2007
Location: NJ, US
|
Member |
|
|
I still couldn't get it working. I guess it probably has got to do something with the type of view and Materialized View I have. Also my 2 tables are in different schemas.
One other question I have related it
********This MV is from Oracle doc******************It is a fast refresh***********************
CREATE MATERIALIZED VIEW cust_dly_sales_mv
BUILD IMMEDIATE REFRESH FAST ON DEMAND
ENABLE QUERY REWRITE AS
SELECT s.time_id, p.prod_id, p.prod_name, COUNT(*),
SUM(s.quantity_sold), SUM(s.amount_sold),
COUNT(s.quantity_sold), COUNT(s.amount_sold)
FROM sales s, products p, times t
WHERE s.time_id = t.time_id AND s.prod_id = p.prod_id
GROUP BY s.time_id, p.prod_id, p.prod_name;
But when I try to create a fast refresh MV I get the
ORA-12015: cannot create a fast refresh materialized view from a complex query.
I am trying to figure what is the difference between the SQLs in the MVs.
Both the SQL have aggregate, group by, where clause and 2 or more tables.
So what makes the second one complex SQL and first not(on the top not a complex SQL)
CREATE MATERIALIZED VIEW Items_sales_MV
TABLESPACE DATA
NOCACHE
LOGGING
NOPARALLEL
BUILD IMMEDIATE
REFRESH FORCE ON DEMAND
WITH PRIMARY KEY
ENABLE QUERY REWRITE
AS
SELECT
trim(r.frm) as sub_group
,i.grp as Group
,r.vend as vendor
,r.itm as ITM
,r.FRCST_YR as FSCL_YR
,sum(r.mon1/1000) as JAN
,sum(r.mon2/1000) as FEB
,sum(r.mon3/1000) as MAR
,sum(r.mon4/1000) as APR
,sum(r.mon5/1000) as MAY
,sum(r.mon6/1000) as JUN
,sum(r.mon7/1000) as JUL
,sum(r.mon8/1000) as AUG
,sum(r.mon9/1000) as SEP
,sum(r.mon10/1000) as OCT
,sum(r.mon11/1000) as NOV
,sum(r.mon12/1000) as DEC
From Item_Master i, Receipt_itm r
Where i.active = 'A'
and i.Item = r.Itm
Group By
r.frm
,i.grp
,r.vend
,r.itm
,r.FRCST_YR;
|
|
|
|
| Re: Oracle 9.2.0.8.0 [message #380009 is a reply to message #379979] |
Thu, 08 January 2009 19:42 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
DBMS_MVIEW.EXPLAIN_MVIEW() should tell you why it won't work.
It may be because the SELECT column trim(r.frm) is different to the GROUP BY column r.frm
Ross Leishman
|
|
|
|
| Re: Oracle 9.2.0.8.0 [message #380238 is a reply to message #380009] |
Fri, 09 January 2009 11:54 |
chintu00
Messages: 91
Registered: February 2007
Location: NJ, US
|
Member |
|
|
rleishman thanks a lot.
I think most of my problem was due to "SELECT column trim(r.frm) is different to the GROUP BY column r.frm"
I appreciate your help and insight.
|
|
|
|
Goto Forum:
Current Time: Tue Nov 26 01:42:09 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
