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
