This content has been marked as final. Show 9 replies
Since both OLTP, On-Line Transaction Processing, and OLAP, On-Line Analytical Processing, have to do with application design and have nothing to do with the actual Oracle software other than the fact Oracle does offer an OLAP feature I would not worry about this as I approached tuning. As you look at the SQL the nature of the database environment should become obvious.
You can see if the OLAP feature is installed by opening an SQLPlus connection and reading the banner. Example:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Just because OLAP is installed does not make the environment OLAP. This was taken from an OLTP. If the great majority of the transactions are queries and DML that target small rows sets then you are likely lookly at an OLTP environment. OLAP environments generally use cubes and I forget the name of the Oracle feature/language you use to navigate cubes but for the most part if it is OLAP it would be built in Hyperion probably running on Essbase.
It is not that unusual for a database to be a bit of a hybrid based on usage. The analytic functions and model clause add a lot of OLAP type analysis ability to what is otherwise an OLTP.
HTH -- Mark D Powell --
OLTP and DWH "tuning" usually done by:
- specific design of tables (star schema, snowflake schema, some denormalization for DWH, versus 3-rd form normalization for OLTP)
- database built with larger block size
- some Oracle parameters set differently, depending on version of Oracle.
What you can do for "tuning", it is just adjusting some of params from the latter area. Unlikely, but may be you will be allowed to rebuild DB with larger block size.
Just an advise. Do not start your tuning without evaluation of current performance, finding the gaps in it, and without setting firm and measurable expectations on the required performance, as well as amount of your efforts you are going to spend on it.
Then prioritize databases and applications running on it in regards of required performance related changes.
Fact tables diminision tables are associated with warehouse and data mart design and while OLAP can be done with these object structures in a real OLAP system the data will most likely be loaded into cubes. Special cube construction and retrieval instructions rather than normal SQL are normally used to process OLAP data.
The following Oracle support master note on OLAP leads to the second note which in turn leads to the third note.
Note: 1107593.1 Master Note for the Oracle OLAP Option
Note: 1107603.1 OLAP Option FAQ's, How-To's, Advice...
Note: 983565.1 OLAP Cube Build Techniques
Also Note: 352598.1 How To Verify Whether The Olap Option Is Installed Properly And Working ?
If you are only dealing with queries against fact and diminision tables and not cubes then you are not really dealing with OLAP so much as data warehouse which means normal queries plus some warehouse specific situations such as star trransformations. Star transformations are discussed in the Warehouse guide. Dealing with large quantities of data and working on SQL that may runs hours is the big change from tuning normal OLTP.
HTH -- Mark D Powell --
976659 wrote:Well, that will indicate wheather the OLAP option (actually just a bunch of additional packages and possibly some addition data dicitionary objects) has been installed or not. As others have indicated, there is nothing about the database itself that "makes" it an OLAP, or a DW, or an OLTP. That is all a matter of how it is used. Kind of like trying to tell if the Honda Accord parked in my driveway is a family sedan or a pizza delivery vehicle. In fact very few databases are used purely as one or the other.
use below query to find your oracle DB OLAP or not.
select * from v$option where parameter='OLAP';