Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Using MVs in a Large OLTP System

Re: Using MVs in a Large OLTP System

From: Mladen Gogala <mgogala_at_adelphia.net>
Date: Wed, 14 May 2003 15:08:22 -0800
Message-ID: <F001.00598FB8.20030514150822@fatcity.com>


Todd, why do you say that you're building an OLTP system? It looks more like a reporting system to me. Anyway, if you want to read 10 million rows with a sub-second response time, the answer is very simple: HP Superdome, with memory-mapped solid state disks, 300+ CPUs and a very high degree of parallelism (the machine is a NUMA box which can have up to 512 CPUs). Material views also do not sound like an OLTP system. I was tempted to suggest bitmap indexes, hash clusters and denormalization, which would make the picture of an OLTP system complete but one of my New Year resolutions was to restrain my sense of humor, so I cannot do so. Have you considered DB-replication to a reporting database which could make use of all those lovely DSS techniques or SRDF-ing the database weekly to an ODS database?

On 2003.05.14 17:16 Todd Carlson wrote:
> 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).
>

-- 
Mladen Gogala
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Mladen Gogala
  INET: mgogala_at_adelphia.net

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).
Received on Wed May 14 2003 - 18:08:22 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US