Re: need help designing a heavily concurrent reporting engine
- From: Tim Gorman <tim@xxxxxxxxx>
- To: "Pakhutkin, Maxim (Max)" <maxim.pakhutkin@xxxxxxxxxxxxxx>
- Date: Tue, 04 Sep 2012 22:06:40 -0600
Maxim,
I've not considered *removing* data using EXCHANGE PARTITION, as
DROP/TRUNCATE is much easier, but anything is possible. A two-step
removal while first doing EXCHANGE then DROP/TRUNCATE wouldn't be
exactly similar as a simple DROP/TRUNCATE, as the elapsed time between
the EXCHANGE and the TRUNCATE/DROP would allow in-flight queries to
complete while preventing new queries against the soon-to-be-removed
data from starting, so I guess there could be a reason to add more
complexity. Usually when I'm DROP/TRUNCATEing, nobody is looking for
the data because it is in the realm of archival already, and access is
so infrequent.
Not sure why you would want five (5) sessions performing EXCHANGE
PARTITION on the same table; that's just begging for a enqueuing on a
TM (a.k.a. DML) lock. One partition at a time on the same table; they
complete quickly enough as long as you don't have global indexes. If
you have global indexes, get rid of them; they're not worth it. If you
believe you need them to enforce a unique constraint, then there is a
design flaw in the dimensional data model that should be addressed.
It sounds like the application has a problem with parsing. Is it
written in C/C++ using OCI or PRO*C, or is it using the DBMS_SQL package
in PL/SQL? The only time I've ever seen such latch contention is where
the application developer believed that each and every EXEC first
required a PARSE, and that causes big problems during high concurrency
whether or not partitioning is involved.
Please take a SQL trace of a session of the application, TKPROF it, and
determine if the numbers of "parses" are equal to the number of
"executions" for the cursors experiencing library cache locks, etc?
Hope this helps...
-Tim
On 9/4/2012 7:05 PM, Pakhutkin, Maxim (Max) wrote:
>
> Tim,
>
> Thanks for the information.
>
> I forgot to mention that we've tried removing data via the exchange
> partition method. I probably didn't mention it because the result was
> similar to the TRUNCATE partition approach.
>
> The problem is that we observed heavy library cache lock/latching
> waits during exchange partition operation. For example, 35 processes
> may be querying the partitioned table, while 5 may be doing exchange
> partition operation. To soft-parse the query, oracle needs a library
> cache latch on the object, I believe. If the object is being modified
> (via exchange partition), the queries have to wait (because they'd
> need to hard-parse the query if the object gets modified while they
> are waiting). When another exchange needs to happen, it will stack up
> in the queue waiting for the queries that are waiting for the first
> exchange operation, and so on.
>
> We are hoping not to have to stop queries while we reload data in the
> segments, but because of the library cache latching, only a handful of
> processes are able to continue while dozens more wait for latches to
> be released/acquired.
>
> --
>
> *Max Pakhutkin*
>
> *From:*Tim Gorman [mailto:tim@xxxxxxxxx]
> *Sent:* Tuesday, September 04, 2012 5:55 PM
> *To:* maxim.pakhutkin@xxxxxxxxxxxxxx; ORACLE-L
> *Subject:* Re: need help designing a heavily concurrent reporting engine
>
> Maxim,
>
> For ad-hoc activity, your best bet for a data model is dimensional
> <http://en.wikipedia.org/wiki/Dimensional_modeling> (a.k.a. star
> schema) with facts and dimesions.
>
> Range-partition the fact table(s) by date. Use the EXCHANGE-PARTITION
> load technique described in this white paper
> <http://www.evdbt.com/TGorman%20TD2009%20DWScale.doc> and presentation
> <http://www.evdbt.com/OOW09%20DWScaling%20TGorman%2020091013.ppt>,
> allowing you to use INSERT+APPEND, nologging if necessary, TRUNCATE to
> remove data. Mostly direct-path loads for table data and index
> creation, very little "conventional" DML -- everything you're wishing
> for. :-)
>
> If you have any slowly-changing dimensions, first load the "type 2"
> (time-variant) dimension table(s) using the EXCHANGE-PARTITION load
> technique, then re-load the "type 1" (point-in-time) dimension
> table(s) using the exchange-partition technique illustrated on slides
> 25-29 of the presentation
> <http://www.evdbt.com/OOW09%20DWScaling%20TGorman%2020091013.ppt>.
> Again, no need for expensive "conventional" operations; large volume
> data manipulation is faster with INSERT+APPEND in parallel. No need
> for all this is the dimension is not slowly-changing, by the way.
>
> That's it in a nutshell. I've been using this stuff for ~14 years and
> talking to the world about it for over 8 years now. It's not easy to
> do, but it rocks.
>
> ------------------------------------------------------------------------
>
>
> By the way, I'll be doing my 2-day seminar on this topic (a.k.a.
> "/Scaling To Infinity: Partitioning Data Warehouses on Oracle
> Database/") for Oracle University in Munich Germany on 22-23 October
> <http://education.oracle.com/webreg/ShoppingCart?arg=1&doccode=D70363_1657076&av_id=&org_id=34&territory_code=DE&lang=D&fee=&event_id=3253864&p_return_url=http://education.oracle.com/pls/web_prod-plq-dad/searchResult.searchEngineParser?p_search_keyword=Gorman&p_org_id=1001&p_lang=US&p_country=34,41&p_search_location=&p_search_category_id=&p_search_format=&p_search_start_month=OCT&p_search_start_year=2012&p_search_end_month=NOV&p_search_end_year=2012&p_page_number=1&p_search_from_date=&p_search_to_date=&p_forceExpandedSearch=N>
>
> and in Utrecht Netherlands on 25-26 October
> <http://education.oracle.com/webreg/ShoppingCart?arg=1&doccode=D70363_1657076&av_id=&org_id=41&territory_code=NL&lang=NL&fee=&event_id=3253866&p_return_url=http://education.oracle.com/pls/web_prod-plq-dad/searchResult.searchEngineParser?p_search_keyword=Gorman&p_org_id=1001&p_lang=US&p_country=34,41&p_search_location=&p_search_category_id=&p_search_format=&p_search_start_month=OCT&p_search_start_year=2012&p_search_end_month=NOV&p_search_end_year=2012&p_page_number=1&p_search_from_date=&p_search_to_date=&p_forceExpandedSearch=N>.
>
> I'll be doing my 60-90 minute version of the 2-day seminar at the
> Slovenian Oracle Users Group in Llubjana Slovenia 15-16 October
> <http://www.sioug.si/index.php?option=com_content&view=article&id=201&Itemid=180&lang=en>.
>
> I'm glad to do one or more of the 60-90 minute presentations over a
> GoToMeeting webinar as well, if anyone would be interested?
>
> Please let me know if you have any questions.
>
> Hope this helps...
>
> --
> Tim Gorman
> consultant -> Evergreen Database Technologies, Inc.
> postal => PO Box 352151, Westminster CO 80035
> website =>http://www.EvDBT.com/
> email =>Tim@xxxxxxxxx <mailto:Tim@xxxxxxxxx>
> mobile => +1-303-885-4526
> fax => +1-303-484-3608
> Lost Data? =>http://www.ora600.be/ for info about DUDE...
>
>
>
>
> On 9/4/2012 3:10 PM, Pakhutkin, Maxim (Max) wrote:
>
> Hello,
>
>
>
> I'm looking for some advice for designing a process for generating
> reports. The reports are ad-hoc, i.e. it's safe to assume that the reports
> will be generated continuously and concurrently, no start and finish point or
> quiet time. This basically data-warehouse-type process needs to live in a
> heavily-OLTP type system, so we hope to reduce datafile and redo I/O overhead
> as much as possible.
>
>
>
> The direction we've been going so far:
>
> 1) We are hoping to run multiple database sessions each working on its
> own set of data at a time (this corresponds to one report). This way we can
> add more processes if we need to speed things up (plenty of CPU and storage
> on the system). These sessions are called renderers (they render reports) and
> each will use its own render_key value to access its own data.
>
> 2) The processes would copy some detail data for each report into their
> own tables, do various joins of this detail data with other data to generate
> data for various sections of the report.
>
> 3) The detail data does not need to be restorable, so we are hoping to
> use INSERT+APPEND to put it in, TRUNCATE to get rid of it, and NOLOGGING on
> the tables/tablespaces that store it, to avoid the I/O overhead of using
> plain INSERT/DELETE.
>
> 4) We don't want to use GLOBAL temporary tables, if possible, because in
> addition to the render process accessing the data for its report, the
> "orchestrator" process will need to access the same data. Also, we want to be
> able to easily collect optimizer stats on the data and don't have to set the
> stats, which may be error prone and is a support overhead.
>
> 5) after each set of data is inserted, stats would be collected on that
> data.
>
>
>
> The goal is to get maximum speed out of it, but also hopefully to keep
> code fairly reasonably maintainable.
>
>
>
> At first I thought of partitioned tables, with each process working on it
> own partition. But I found out that INSERT+APPEND and TRUNCATE PARTITION
> don't play well with concurrent access to the same table (even though
> different partitions of the same table are being accessed). Collecting stats
> also would take out latches on the whole table in library cache, despite us
> collecting stats only on partition level. For all of the above we get library
> cache waits of various kinds, slowing things down significantly.
>
>
>
> Then we thought of using separate tables, but joining them into a
> union_all view (for ease of code maintenance), that way simulating a
> partitioned table. Each separate table would still have one column filled
> with the same value, dedicated to identifying which render process would work
> on it.
>
>
>
> CREATE TABLE X1 (
>
> Render_key number, <-- this column contains the same value for all rows
> in the table, and the appropriate bill rendering process would supply this
> value in queries to get only its own data from the union-all view.
>
> ...
>
> );
>
>
>
> CREATE TABLE X2(
>
> Render_key number,
>
> ...
>
> );
>
> Etc.
>
>
>
> Now we can INSERT+APPEND, but TRUNCATE doesn't work too well, as some
> processes accessing the view get "object no longer exists" error due to other
> processes truncating their tables that are part of the same view. (This may
> be partially because oracle tries to use a very selective index that exists
> on each of these tables first and then filter it by render_key identifier,
> instead of reading the render_key column histograms first, realizing that
> there is only one table where that column will have the right value and not
> touching the other tables, as I hoped it would. But I may be wrong and it
> would do that even without the index).
>
>
>
> I can't think of any other approaches that would get us what we want
> (speed + ease of code maintenance) and am ready to recommend to the
> developers that each process work in its own schema with its own tables (so
> code and tables would be duplicated everywhere, making maintaining it a
> harder).
>
>
>
> Can anyone think of other architectural alternatives that could give us
> what we want?
>
>
>
> Thanks in advance.
>
>
>
> --
>
> Max Pakhutkin
>
>
>
> --
>
> //www.freelists.org/webpage/oracle-l
>
>
>
>
>
>
>
>
>
>
>
--
Tim Gorman
consultant -> Evergreen Database Technologies, Inc.
postal => PO Box 352151, Westminster CO 80035
website => http://www.EvDBT.com/
email => Tim@xxxxxxxxx
mobile => +1-303-885-4526
fax => +1-303-484-3608
Lost Data? => http://www.ora600.be/ for info about DUDE...
--
//www.freelists.org/webpage/oracle-l
Other related posts:
