RE: Using MVs in a Large OLTP System

Thanks to everyone who responded!

The trouble with the query was a function based index. We removed it and replaced it with a regular index, no FTS. We have since built several more MVs with good results.

And Mladen, there are actually people here who believe that throwing hardware at it would solve the 'performance' problem, which is a design problem. Maybe I should forward your email to them. ;-) Replication to another database is the last option and is above their latest proposal to use 'dummy' data in the transaction tables so outer joins would be unnecessary. I really tried hard not to scream.

Todd

-----Original Message-----
Gogala
Sent: Wednesday, May 14, 2003 6:08 PM
To: Multiple recipients of list ORACLE-L

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).




-- 
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).