Materialization or performance Issues with Materialized SQL Views / SQL Data Marts

How to debug Materialized SQL Views / SQL Data Marts

Details:

  A SQL view named "ER_V_FCASTING_0104" has been created with materialized option.

The purpose was to materialize the output of a complex query to improve the performance of Custom Reports.

We are not able to track materialized object in the database, so we doubt SQL view is materialized.
 

How to check this?

 Please notice that "SQL Views" in 4.X and "SQL Data Marts"  in 9.X are the same thing renamed, they correspond to

Oracle sql views in BCO DB (this was done because in "dashboards" in 4.X were renamed to "views" in 9.X,  and this could generate confusion)

How to check a materialized view/datamart:

For TSCO 11.5 and 20.02

Administration > Advanced Reporting >Data marts >SQL data marts



Export Datamart

For BCO 9.X or higher go into:

Check the line associated to your view/datamart and get the value of its physical name and ERID

0) if have a problem creating the view itself, and get and error like:

The inserted view fails due to SQL errors. Please verify it.

from BCO UI, try to create it from a SQL client like SQLdeveloper as:

create view mysqltestview as (insertyourSQLcodehere)

to check the basic syntax is ok.

Things to verify when writing code for a BCO SQL view are:

• if the SQL query has an undefined list of columns to extract,

(select * from XXXXX) and some of them are duplicated /ambiguous

(for example, joined two tables and doing a select * from it, this is correct SQL, but not useful to make a materialzied view in BCO).
Try to explicitly list the columns want as your query output.
So instead of doing a:

select * from sys_def d , sys_object o where o.sysid=d.sysid

you must do:

select d.*, o.sysobjid from sys_def d , sys_object o where o.sysid=d.sysid
 

• If the SQL query contains comments to lines done with -- :

select tableA.columnA,

--tableA.columnB,

tableA.columnD, tableB.ColumnE from tableA, tableB where tableA.columnA = tableB.columnA

replace the -- with /* */ and it will work

select tableA.columnA,

/* tableA.columnB,*/

tableA.columnD, tableB.ColumnE from tableA, tableB where tableA.columnA = tableB.columnA

1) get its DDLs definitions, using the physical name from the UI:

select dbms_metadata.get_ddl('VIEW',ER_V_FCASTING_0104_2018','CPIT_OWN') from dual;

from this output you could see some thing like:

DBMS_METADATA.GET_DDL('VIEW','ER_V_FCASTING_0104_2018','CPIT_OWN')
--------------------------------------------------------------------------------

CREATE OR REPLACE FORCE VIEW "CPIT_OWN"."ER_V_FCASTING_0104_2018" ("SYSID
", "DAYSBACK", "DT", "AVG_80TH_PRCTILE", "MAX_60TH_PRCTILE") AS
SELECT "SYSID","DAYSBACK","DT","AVG_80TH_PRCTILE","MAX_60TH_PRCTILE" FROM ER_V_FCASTING_0104_2018_G


the fact that the materialized view has _G at the end of its definition shows that it hasn't been materialized successfully yet.

2) proof of this is the fact that the _G is still a view:

select dbms_metadata.get_ddl('VIEW','ER_V_FCASTING_0104_2018_G','CPIT_OWN') from dual;


DBMS_METADATA.GET_DDL('VIEW','ER_V_FCASTING_0104_2018_G','CPIT_OWN')
--------------------------------------------------------------------------------

CREATE OR REPLACE FORCE VIEW "CPIT_OWN"."ER_V_FCASTING_0104_2018_G" ("SYS
ID", "DAYSBACK", "DT", "AVG_80TH_PRCTILE", "MAX_60TH_PRCTILE") AS
select sysid,daysback,trunc(ts)as dt,avg(Percentile_Cont_AVG)avg_80th_prctile,
avg(Percentile_Cont_max)max_60th_prctile from(SELECT distinct pm.sysid,sdd.ts AS
ts,daysback,PERCENTILE_CONT(0.80)WITHIN GROUP(ORDER BY sdd.avgvalue ASC)OVER(PA
RTITION BY pm.sysid,trunc(sdd.ts),pm.sysmetricid,daysback)as Percentile_Cont_AVG
,PERCENTILE_CONT(0.60)WITHIN GROUP(ORDER BY sdd.maxvalue ASC)OVER(PARTITION BY p
m.sysid,trunc(sdd.ts),pm.sysmetricid,daysback)as Percentile_Cont_max FROM cpit_o
wn.pv_sys_data_day sdd,cpit_own.pv_sys_metric pm,(select(trunc(sysdate,'MM')-1)-
add_months(trunc(sysdate,'MM'),-3)as daysback from dual)an_period WHERE pm.metri
c='CPU_UTIL' and sdd.sysmetricid=pm.sysmetricid and sdd.ts between add_months(tr
unc(sysdate,'MM'),-3)and trunc(sysdate,'MM')-1)group by sysid,daysback,trunc(ts)


3) When the view it's materialized succesfully the _G disappears and name of the based view changes to something similar to ER_MV2018_20130611105002, containing a materialization date timestamp:
DBMS_METADATA.GET_DDL('VIEW','ER_V_FCASTING_0104_2018','CPIT_OWN') from dual;

from this output you could see some thing like:

DBMS_METADATA.GET_DDL('VIEW','ER_V_FCASTING_0104_2018','CPIT_OWN')
--------------------------------------------------------------------------------

CREATE OR REPLACE FORCE VIEW "CPIT_OWN"."ER_V_FCASTING_0104_2018" ("SYSID
", "DAYSBACK", "DT", "AVG_80TH_PRCTILE", "MAX_60TH_PRCTILE") AS
SELECT "SYSID","DAYSBACK","DT","AVG_80TH_PRCTILE","MAX_60TH_PRCTILE" FROM ER_MV2018_20130611105002

You can double check this with:

dbms_metadata.get_ddl('TABLE','ER_MV2018_20130611105002','CPIT_OWN') from dual;

4)If want to be able to reforce the materialization of a valid view can do a manual delete:

delete from er_props where erid=2018 and name like '%er.materializer.last.date'

5) can also check the view definition:

select * from er_props where erid=2018

6) you can also check the view properties:

select * from er_def where erid=2018

7) indexes on a view column can be created from the UI in advanced mode,

8) to create an index manually on the Ts column for a test: create index x on ER_MV2018_20130611105002(ts)

9) to check existing indexes on materialized views:
select * from user_indexes where table_name='ER_MV2018_20130611105002'