DBMS_WM.CompressWorkspace

DBMS_WM.Compress Workspace

 If   you version enabled the tables with  DBMS_WM.EnableVersioning ('TABLE_NAME', 'VIEW_WO_OVERWRITE')   API  THEN your  Oracle Workspace  database WILL  GET SLOW  WHEN SO MANY DATA COMES ; use DBMS_WM.CompressWorkspace API  TO MAKE IT FASTER; There is a scenario to check  whether your Workspace  is getting slow or Workspace database is to be Compressed or not ,execute this  workspace API
DBMS_WM.SetDiffVersions('LIVE',wksp2) . and  then  pick a version enabled table having highest record count   , issue command select count(*) from tablename_diff ; if it takes more than a minute to GET RESULT THEN YOU CAN MAKE SURE THAT YOU NEED TO COMPRESS YOUR WORKSPACE ;here u want to use the   statement  (1) , using statement (2) will not give better result because your table is version enabled  with DBMS_WM.EnableVersioning ('TABLE_NAME', 'VIEW_WO_OVERWRITE')  .

(1) exec DBMS_WM.CompressWorkspace('LIVE',compress_view_wo_overwrite => true);

(2) exec DBMS_WM.CompressWorkspace('LIVE')



VIEW_WO_OVERWRITE: The without overwrite (WO_OVERWRITE) option. A view named _HIST  is created to contain history information, and it will show all modifications to the same version of the table. A history of modifications to the version is maintained; that is, subsequent changes to a row in the same version do not overwrite earlier changes.

compress_view_wo_overwrite        A Boolean value (TRUE or FALSE).

TRUE causes history information between the affected savepoints to be deleted even if VIEW_WO_OVERWRITE was specified when versioning was enabled.

FALSE causes history information (between the affected savepoints) for a table not to be deleted if VIEW_WO_OVERWRITE was specified when versioning was enabled. (If VIEW_WO_OVERWRITE was not specified for a table, history information for the table is deleted regardless of the parameter value.) FALSE is assumed if the procedure format without this parameter is used.


Advantage
------------
1  Run-time performance for Workspace Manager operations is improved.
2  Less disk storage is used for Workspace Manager structures

Use of Combined Index

The Below example shows the difference in execution plan when a composite index is created  

SELECT d.ITEM_ID,d.ITEM_DESC,D.UNIT,d.ITEM_REF_ID,d.ITEM_LEVEL,N_QTYMILE,S_WORKCODE,
SYS_CONNECT_BY_PATH (ITEM_DESC,'--->') scbdesc,LPAD(' ',level*3,' ')||ITEM_DESC LNAME ,ACCESSORIES,SYS_CONNECT_BY_PATH (ITEM_ID,'--->') scbp,level,CONNECT_BY_ROOT ITEM_ID rootn , CONNECT_BY_ISLEAF leaf FROM ITEM_MASTER d START WITH ITEM_REF_ID is null CONNECT BY PRIOR ITEM_ID =ITEM_REF_ID ORDER SIBLINGS BY ITEM_LEVEL

create index idx_ITEM_MASTER_refid  on ITEM_MASTER (ITEM_ID,ITEM_REF_ID)

After creating combined index check the execution plan




SELECT  d.ITEM_ID,d.ITEM_DESC,D.UNIT,d.ITEM_REF_ID,d.ITEM_LEVEL,N_QTYMILE,S_WORKCODE,
SYS_CONNECT_BY_PATH (ITEM_DESC,'--->') scbdesc,LPAD(' ',level*3,' ')||ITEM_DESC LNAME ,ACCESSORIES,SYS_CONNECT_BY_PATH (ITEM_ID,'--->') scbp,level,CONNECT_BY_ROOT ITEM_ID rootn , CONNECT_BY_ISLEAF leaf FROM ITEM_MASTER d
START WITH ITEM_REF_ID is  null CONNECT BY PRIOR ITEM_ID =ITEM_REF_ID ORDER SIBLINGS BY ITEM_LEVEL

ADD FOREIGN KEY TO VERSIONED TABLE

   ORACLE WORKSPACE MANAGER  TOPIC

    If there is  a version enabled table 'BILL' and
  
 /*  CREATE TABLE BILL (N_BILLID NUMBER(10), CONSTRAINT PK_BILL PRIMARY KEY (N_BILLID));
   EXEC DBMS_WM.ENABLEVERSIONING('BILL') ; */
   
 now i created a new table BILL_DETAILS which must be a child table of BILL
 
 /* CREATE TABLE BILL_DETAILS ( N_BILLDTID NUMBER(10),CONSTRAINT PK_BILLDETAILS PRIMARY KEY (N_BILLDTID) ,   N_BILLID NUMBER(10) , S_DESC VARCHAR2 (100), D_SYSDATE  DATE   DEFAULT SYSDATE ) ; */
  
 here you are not able to add a foreign key between one versioned and one non-versioned table.if it is done  oracle error will be the result


 ORA-20200: UNSUPPORTED CONSTRAINT
   
for that you would need to execute dbms_wm.enableversioning on the non-versioned table (you can add a foreign key between 2 version enabled tables) if you don't  want the detail to be kept as version enabled table then you can disable versioning of the detail table.

    EXEC DBMS_WM.ENABLEVERSIONING('BILL_DETAILS') 
  
    EXEC DBMS_WM.BEGINDDL('BILL');
  
    EXEC DBMS_WM.BEGINDDL('BILL_DETAILS');
  
   ALTER TABLE    BILL_DETAILS_LTS ADD CONSTRAINT FK_BILLDET_BILL FOREIGN KEY (N_BILLID)  REFERENCES BILL_LTS(N_BILLID);
  
   EXEC DBMS_WM.COMMITDDL('BILL');
  
   EXEC DBMS_WM.COMMITDDL('BILL_DETAILS');

   EXEC DBMS_WM.DISABLEVERSIONING('BILL_DETAILS')   ;

Oracle Spatial hints

/*+NO_QUERY_TRANSFORMATION*/

The NO_QUERY_TRANSFORMATION hint causes the optimizer to skip all query transformations including but not limited to OR expansion,view merging, subquery unnesting, star transformation and materialized view rewrite.use the "NO_QUERY_TRANSFORMATION" hint to disable most of the transformations , although some transformations still seem to take place, e.g. IN seems to be always transformed into EXISTS .

This hint which improve performance to a great extent for my Spatial query.

SELECT /*+ NO_QUERY_TRANSFORMATION*/ * FROM CABLE WHERE MDSYS.sdo_filter
(shape,MDSYS.SDO_GEOMETRY (2003,3785,NULL,MDSYS.sdo_elem_info_array (1,1003,3),MDSYS.sdo_ordinate_array (-9003954.980329761, 4271239.694744173, -9003673.417374918, 4271428.398654524)),'querytype=WINDOW') = 'TRUE' and (sdo_RELATE(shape, (SELECT geom FROM user_boundary WHERE userid = 103),'MASK=INSIDE') = 'TRUE') ORDER BY OID 

Execution Time : 47msec

SELECT * FROM CABLE WHERE MDSYS.sdo_filter
(shape,MDSYS.SDO_GEOMETRY (2003,3785,NULL,MDSYS.sdo_elem_info_array (1,1003,3),MDSYS.sdo_ordinate_array (-9003954.980329761, 4271239.694744173, -9003673.417374918, 4271428.398654524)),'querytype=WINDOW'
) = 'TRUE' and (sdo_RELATE(shape, (SELECT geom FROM user_boundary WHERE userid = 103),'MASK=INSIDE') = 'TRUE') ORDER BY OID

Execution Time : 1:09 min

....

if you execute this spatial query without this hint it take around 3 minutes to execute ! amazed

If you want dumps you can mail us :oracleplsql10g@gmail.com


The cost of a query is not proportional to the time execution of a query for example If cost of a query (A) is greater than cost of another query (B) then its not always query (A) takes more execution time than query (B) .

............

Try this simple query too

SELECT /*+ NO_QUERY_TRANSFORMATION */
shape FROM cable WHERE (sdo_relate (shape,(SELECT geom FROM user_boundary WHERE userid =103),'MASK=INSIDE')='TRUE')


SELECT shape FROM cable WHERE (sdo_relate (shape,(SELECT geom FROM user_boundary WHERE userid =103),'MASK=INSIDE')='TRUE')

----------------------
These queries may get executed when no query transformation hint is not given for in some case

SELECT diminfo   FROM all_sdo_geom_metadata  WHERE owner = :own AND table_name = :tab AND column_name = :col

SELECT sdo_relation   FROM sdo_relatemask_table  WHERE sdo_mask = :b1

So evaluate your query and the data in the tables and check execution plan in both scenario and its performance and decide .

Advantage of analytic Function

This example shows you that  Usage of  Analytic function will make query good in performance
 
select mxid,obj#,dataobj#,owner#,name,j.namespace,type#,ctime,mtime,stime,status,flags,spare1,spare2 from sys.obj$ j,(select max(dataobj#) mxid,namespace from sys.obj$ where owner#=61 group by namespace)w where owner#=61 and j.dataobj#=w.mxid and obj# in (51606,52492,52602) ;
 
explain plan set statement_id ='r' into plan_table for select mxid,obj#,dataobj#,owner#,name,j.namespace,type#,ctime,mtime,stime,status,flags,spare1,spare2 from sys.obj$ j,(select max(dataobj#) mxid,namespace from sys.obj$ where owner#=61 group by namespace)w where owner#=61 and j.dataobj#=w.mxid  and obj# in (51606,52492,52602) ;
 
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE',NULL));
 

 
Total Cost=718
 
select max(dataobj#) over(partition by owner#,namespace order by owner#,namespace) mx,obj#,dataobj#,owner#,name,namespace,type#,ctime,mtime,stime,status,flags,spare1,spare2 from sys.obj$ where owner#=61 and obj# in (51606,52492,52602);
 

 

 
explain plan set statement_id ='r' into plan_table for select max(dataobj#) over(partition by owner#,namespace order by owner#,namespace) mx, obj#,dataobj#,owner#,name,namespace,type#,ctime,mtime,stime,status,flags,spare1,spare2 from sys.obj$ where owner#=61 and obj# in (51606,52492,52602);
 
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE',NULL));
 

 
Total Cost is 29
 
.`. Usage of second sql script is advisable ; where oracle optimizer works default on cost based (ie:CBO)
-----------------------------