Oracle-L: RE: Using MVs in a Large OLTP System

From: Mercadante, Thomas F <NDATFM_at_labor.state.ny.us>
Date: Thu, 15 May 2003 04:56:40 -0800
Message-ID: <F001.00599724.20030515045640@fatcity.com>

Todd,

I agree with what Dennis said. Throw a PK and additional indexes as needed on the MV and review the queries you run. You should see a better result.

Good Luck

Tom Mercadante
Oracle Certified Professional

-----Original Message-----
Sent: Wednesday, May 14, 2003 5:17 PM
To: Multiple recipients of list ORACLE-L

Hey Guys,

We are building an OLTP application on HP 11 & 8.1.7.4 that will become fairly large: 250+ tables, several above 10+ million records, 300+ concurrent users, etc... The Developers, no offence, want to search on EVERYTHING (5+ tables), which naturally requires outer joins. No problem with performance they say until the database was loaded with 1 million structures.

Now performance is, of course, bad. The DBA team is looking into using Materialized Views to solve the problem. We have created a small MV, 7 gigs, and query rewrite is working correctly. Performance in sandbox has been improved, but not as much as we hoped.

We are doing a full table scan on the materialized view. Ideally, we would like to do an index scan, but the rewritten SQL can't be tuning? I am RTFMing my heart out, but I wanted to ask the list if: 1) Are we going done the right path or will the overhead of refreshing MVs kill system performance?
2) What are the kinks with this approach? 3) Can the rewritten SQL be tuned to stop the FTS?

Listed below is the MV and related SQL. Thanks!

CREATE SNAPSHOT mv_strbatch
tablespace chemcore_mv
REFRESH FORCE
ENABLE query rewrite
AS
SELECT
S.STRUCTURE_ID S_STRUCTURE_ID, U_MOLECULAR_WEIGHT, U_MOLECULAR_FORMULA, U_ACTIVE_MOLECULAR_WEIGHT, U_ACTIVE_MOLECULAR_FORMULA, MLOGP, N_O_ATOMS, NH_OH_GROUPS, DENSITY, ORIGINATOR, STR_DATA, APPROVE_DATE,

STR_CHIRALITY_CTR_TYPE_ID, STR_CHIRALITY_DESC_TYPE_ID, S.ENTITY_ID
S_ENTITY_ID, STR_BATCH_ID, SB.STRUCTURE_ID SB_STRUCTURE_ID , BATCH_EXT,
PURITY, FACTOR, TOTAL_MOLECULAR_WEIGHT, TOTAL_MOLECULAR_FORMULA,

BATCH_DENSITY, MATERIAL_REFERENCE, SB.ENTITY_ID SB_ENTITY_ID, LIBRARY_ID, SUB_LIBRARY_ID, PROJECT_ID, SUB_PROJECT_ID, MATERIAL_SOURCE_ID, PURITY_METHOD_ID, PURITY_RANGE_ID, PRN.STRUCTURE_ID PRN_STRUCTURE_ID, PRN.ENTITY_ID PRN_ENTITY_ID, PRIMARY_REFERENCE_NUMBER

FROM v_structure_search s,
     v_structure_primary_ref_number prn,
     v_str_batch_search sb

WHERE s.structure_id = sb.structure_id(+) AND s.entity_id = prn.entity_id(+)

SELECT DISTINCT prn.structure_id, sb.str_batch_id

FROM v_structure_primary_ref_number prn, v_structure_search s,

v_str_batch_search sb
WHERE s.entity_id = prn.entity_id(+) AND s.structure_id = sb.structure_id(+)

Todd Carlson
Oracle Database Administrator
Tripos, Inc.
(314) 647-8837 Ext.3246

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Todd Carlson
INET: tcarlson_at_tripos.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Mercadante, Thomas F
INET: NDATFM_at_labor.state.ny.us

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------