Home » Other » General » OLTP & OLAP Desgin question (Oracle 11gR2/12c)
OLTP & OLAP Desgin question [message #626254] Thu, 23 October 2014 13:57 Go to next message
ravikanth_b
Messages: 42
Registered: November 2007
Location: Bay Area, CA
Member
Hello Experts,

Not sure in which section to post this question but I will try here.
Could you please help me with an architecture question?
I have worked for many years in "traditional" OLTP/OLAP shops with various architecture models such as Kimbal/Inmon models.
Currently I am in a product company which has a OLTP/OLAP solutions. The product is installed on customers site.
I have a push from product management to combine OLAP/OLTP into one schema as opposed to different schemas, one for OLTP and other for OLAP.
What are the pro's and con's for each approach?
This application could be installed either Oracle or SQL Server as backend.

Thanks for your time.
-Ravi

[Updated on: Thu, 23 October 2014 14:01] by Moderator

Report message to a moderator

Re: OLTP & OLAP Desgin question [message #626258 is a reply to message #626254] Thu, 23 October 2014 17:18 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>I have a push from product management to combine OLAP/OLTP into one schema as opposed to different schemas,

They know their product better than you.
It obviously works in a single schema.
Since you have no specific reason to not do as they direct, you should do what they want.
Re: OLTP & OLAP Desgin question [message #626259 is a reply to message #626258] Thu, 23 October 2014 17:52 Go to previous messageGo to next message
ravikanth_b
Messages: 42
Registered: November 2007
Location: Bay Area, CA
Member
So you suggest that we take functional spec as-is written by people who doesn't have any technical architectural experience and convert as-is into technical spec? I have been in the industry for over 15yrs, never have i seen a technical architectural design dictated by non-technical person. May be i am old school and it is quite common these days. Suggestion well taken. Thanks!
Re: OLTP & OLAP Desgin question [message #626260 is a reply to message #626259] Thu, 23 October 2014 18:34 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
If you make two schemas, as you desire, then you introduce the issue of naming & permission issues which do not exist in a single schema.
Specify the identifiable advantages & reasons to have two separate schemas.
Re: OLTP & OLAP Desgin question [message #626263 is a reply to message #626260] Thu, 23 October 2014 23:07 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
For OLAP, use the typical star schema. OLTP, use the relational model and it needs to be at least normalized to 3NF. Coming to your question, having all this in the same schema or two different schemas? Which one do YOU think would be easy to maintain?
Re: OLTP & OLAP Desgin question [message #626266 is a reply to message #626263] Fri, 24 October 2014 01:08 Go to previous messageGo to next message
ravikanth_b
Messages: 42
Registered: November 2007
Location: Bay Area, CA
Member
Two issues:

1. Combine everything into once database/schema
2. Running OLAP/reporting on OLTP system

Apart from fundamental principles of software engineering best practices like
functional, logical and physical separation which is advocated by many experts like
Kimbal and Inmon, following are few considerations we could debate on.

Plan cache issues:

When you submit a query for execution the database engine will check to see if a plan already exists in memory. If it does, great! This saves time as the query optimizer will not need to create a new plan. The area of memory that contains the query plans is named the plan cache.

In OLAP, ad-hoc queries tends to occupy plan cache affecting OLTP queries
based on LRU algorithm.

==============================================================================

Quote from a SQL Server DBA:

"Here's the problem: OLTP is not the same as OLAP. When end users try to use an
system designed for OLTP as an OLAP system it leads to performance issues.
For SQL Server this is usually where locking and blocking rear their heads.
It was not uncommon for me to see contention between users that wanted to
insert data and users that wanted to generate reports. I had an alert built
just to notify me when a session was blocked for more than five minutes.
Can you imagine waiting that long and consider it to be part of your "normal"

processing? I can. I see it frequently when OLTP systems are being used for OLAP purposes.

What you want to do here would be to build a reporting solution for those OLAP users"

===================================================================================

Tuning an OLTP database is quite different than tuning an OLAP database.
For example, OLAP databases can benefit from many indexes, but too many indexes
in an OLTP database can hurt data modification activity.

If we are using OLTP to run OLAP queries, i am sure we would want to create more indexes
to support ad-hoc queries. This is where we get into endless spiral trying to do a balancing act between the two.
===================================================================================

OLAP type queries tend to return large amounts of data, which often lock tables, which decreases concurrency, which hurts performance. So if a user wants to return
a million rows in an OLTP database, there may be many very unhappy users.

"Ideally" they should be logically/physically separate.
===================================================================================

OLTP is desingned to get small amount of data, quickly which has large CPU requirements.
OLAP is desingned to get process and get large amount of data (throughput) has large memeory requirements.

OLAP queries tends to kick out OLTP queries from cached memory/SGA based on LRU algorithm.

Storage requirements are different for each of these systems.

===================================================================================

Parallel executions are not as good for OLTP, where as good for OLAP.
We do not have luxury of setting these parameters globally. We have set parallelism query by query.
===================================================================================
Re: OLTP & OLAP Desgin question [message #626268 is a reply to message #626266] Fri, 24 October 2014 01:46 Go to previous messageGo to next message
John Watson
Messages: 8640
Registered: January 2010
Location: Global Village
Senior Member
All those statements above apply equally well to one schema or two. I think you are confusing "schema" with "database". People who work with SQL Server often do that.
They are also typical SQL Server problems, not Oracle problems.
Re: OLTP & OLAP Desgin question [message #626284 is a reply to message #626266] Fri, 24 October 2014 03:49 Go to previous messageGo to next message
Littlefoot
Messages: 21690
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
It depends on a final answer to Ravi

This application could be installed either Oracle or SQL Server as backend.
Re: OLTP & OLAP Desgin question [message #626313 is a reply to message #626284] Fri, 24 October 2014 12:55 Go to previous messageGo to next message
ravikanth_b
Messages: 42
Registered: November 2007
Location: Bay Area, CA
Member
Our product could be installed both on Oracle OR SQL Server. But some of these concerns apply to both. I am trying to compile both Pro's and Con's.
Re: OLTP & OLAP Desgin question [message #626314 is a reply to message #626313] Fri, 24 October 2014 13:01 Go to previous messageGo to next message
John Watson
Messages: 8640
Registered: January 2010
Location: Global Village
Senior Member
No, none of your "concerns" apply to Oracle. They are all SQL Server issues.
Re: OLTP & OLAP Desgin question [message #626315 is a reply to message #626314] Fri, 24 October 2014 13:05 Go to previous messageGo to next message
ravikanth_b
Messages: 42
Registered: November 2007
Location: Bay Area, CA
Member
So do you have any opinion either way as apposed to traditional DW architecture?
Re: OLTP & OLAP Desgin question [message #628708 is a reply to message #626315] Thu, 27 November 2014 04:36 Go to previous messageGo to next message
Flyby
Messages: 188
Registered: March 2011
Location: Belgium
Senior Member
I would keep them as separate schema's for mobility. So you can move the OLAP out of the way from OLTP if necessary.
Any clue why management wants it in a single schema? (ownership chaining?)
Re: OLTP & OLAP Desgin question [message #628731 is a reply to message #628708] Thu, 27 November 2014 08:46 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
OP has not visited forum this month
Re: OLTP & OLAP Desgin question [message #634858 is a reply to message #626315] Mon, 16 March 2015 19:43 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>All in single schema. Facing hell lot of problems as I warned. concurrency issues, locking issues, data inconsistencies, security issues and much more.

Any concurrency issues, locking issues, data inconsistency issues or security issues are NOT the direct result of having a single schema.

Concurrency & locking issues result when more than 1 session is competing for same object at the same time;
regardless of the number of existing schemas.
Previous Topic: oracle upgradation
Next Topic: Oracle Database
Goto Forum:
  


Current Time: Thu Dec 02 09:50:30 CST 2021