| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: A transactionally consistent view on real-time data with MicroStrategy
<erik.ykema_at_gmail.com> wrote in message
news:1149284215.556809.269580_at_i40g2000cwc.googlegroups.com...
> Hi,
> This is about getting a transactionally consistent view the data while
> the reader is issueing commits as part of the read activity. I can't
> figure out how to get it right in an elegant way, after having studied
> the Concepts Guide.
> Following I will draw the situation and the options I have considered.
> We are on Oracle DBMS 9.2.0.7.
>
> I am working on a near real time OLTP environment, we are running
> MicroStrategy ('MSTR') reports against a set of tables and materialized
> views.
> During report runs users are active in the system (i.e. committing
> transactions through their client applications) and every fifteen
> minutes the MVs are refreshed by a scheduled process (which takes 7
> minutes, to be improved later).
> Several MSTR reports can be running concurrently.
>
> The challenge:
> run the report against a transactionally consistent set of data.
> "Isolation_level = serialized" or "transaction read only" would
> conceptually point in the right direction.
> However, Microstrategy, AFAIK, creates so called multi pass SQL for
> fetching it's data, and _commits_ (by issuing DDL) in between reading
> from different fact tables.
> DDL is not allowed in a read only transaction, and my consistent view
> in serializable mode ends with the implicit commit.
>
> A typical MSTR generated SQL scenario could look like this:
> alter session set isolation_level = serialized; -- my addition, trying
> to get it right, using a custom pre-report SQL statement
> create table A1 as select from A;
> create table B1 as select from B;
> create table C1 as select from C;
> create table Z as select from A, B, C fully outer joined;
> drop table A1, B1, C1;
> -- read Z into the report engine and present it to the user
> drop Z;
>
> The CTAS has an implicit commit, AFAIK not disablable in Oracle by any
> means. So I am unfortunately ending my transaction after the first CTAS
> and loosing my transactionally consistent view on the data.
>
> I have considered several options:
> - copy all OLTP tables also to 1:1 MVs (fast refreshable) and use
> dbms_lock to serialize the read and update action between readers
> (MSTR) and writers (the client app).
> This could work, though the moment of refresh becomes unpredictable,
> since if many reports are fired up between to subsequent refresh jobs,
> the refresh process might have to wait untill it's turn.
> - insert an 'AS OF SCN xxx' in all CTAS statements. Unfortunately MSTR
> doesn't faciliate this desire.
> - Have MSTR create all intermediate helper tables in the first part of
> the multi pass scenario as an create table A1 as select from A where
> 0=1 (etc.); setting isolation level serializable, and inserting into
> these tables. (Hence I don't commit over reads and do not end my
> serialized transaction too soon.) However MSTR doesn't facilitate this.
> - find a way in Oracle for having serializable mode for the session in
> stead of the transaction. Unfortunately I have not come across such a
> feature.
> - Copy the report's to-be-read data to Global Temporary Tables (GTTs)
> in a pre-report custom SQL statement, so the report will create and
> read from it's own fixed data set. This is not elegant and not feasible
> if the amount of data is considerable.
> - Maintain several different copies of the data in partitioned MVs,
> pass the value of the partition to a view from which MSTR reads using a
> helper table, and maintain the data untill all users of that
> transactionally consistent set have finished reading. (Pre- and
> post-sql MSTR statements calling a stored procedure) could facilitate
> this. Again the data volume issue spoils the fun, and I do not
> lookforward to the complexity of the mechanism.
> - Dbms_Flashback.Enable_At_System_Change_Number(xxx); as a pre-report
> sql statement. Does not allow DML or DDL... hence MSTR cannot fill it's
> helper tables.
>
> So I feel like not being able to solve this elegantly, and perhaps
> needing to file enhancement requests with both Oracle (serializable
> session) and MSTR (AS OF option and/or CTAS to be changed in Create All
> Tables First and Later Insert Into Them.)
>
> Any comments and or suggestions are highly appreciated.
> Best regards,
> Erik Ykema
>
No guarantees that this would work, but
Create a context:, and procedure to set it.
create or replace context scn_ctx using scn_proc ;
create or replace procedure scn_proc as
begin
dbms_session.set_context(
namespace => 'scn_CTX',
attribute => 'scn',
value => sys.dbms_flashback.get_system_change_number
);
Then you specify all data to Microstrategy through views that had a one-off declaration like:
create view v_a
as
select * from a as of scn(sys_context('scn_ctx','scn'))
/
If you can get Microstrategy to run a pre-query SQL that can call your scn_proc then all your scratch tables should be populated with read-consistent data.
-- Regards Jonathan Lewis http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html Cost Based Oracle: Fundamentals http://www.jlcomp.demon.co.uk/cbo_book/ind_book.htmlReceived on Mon Jun 05 2006 - 12:45:16 CDT
 |
 |