9 Replies Latest reply on Dec 13, 2012 1:08 PM by EdStevens

    How to Check Database  OLAP or OLTP

    Venkat
      Hi Team,

      I am working for Various databases. How to check Connected database is OLAP or OLTP. I am only the DBA in this Project. No senior one with me.

      I am doing basic performance tuning and query optimization for code developed by programmers.

      Please suggest.

      Thanks and Regards,
      Venkat.
        • 1. Re: How to Check Database  OLAP or OLTP
          fjfranken
          Hi,

          I assume your question should be:

          How to check Database for DWH or OLTP


          You have to see for yourself. Look at the processes or naming convention.
          Some guidelines here: https://blogs.oracle.com/datawarehousing/entry/oltp_and_data_warehouse_how_do


          Cheers
          FJFranken
          • 2. Re: How to Check Database  OLAP or OLTP
            Mark D Powell
            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:

            Connected to:
            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 --
            • 3. Re: How to Check Database  OLAP or OLTP
              Venkat
              Yes! You are right. Generally. Data warehousing is OLAP. therefore. I have questioned.
              Sorry for this.

              Regards,
              Venkat.
              • 4. Re: How to Check Database  OLAP or OLTP
                Venkat
                Thank you Mark.

                If i dine the performance tuning in OLTP database, Can i use same tuned query on OLAP database. Becuase OLAP contains "*fact tables and Dimension tables*" etc.

                Venkat.
                • 5. Re: How to Check Database  OLAP or OLTP
                  Venkat
                  Thanks for your kindly Help.
                  • 6. Re: How to Check Database  OLAP or OLTP
                    Mark Malakanov (user11181920)
                    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.
                    • 7. Re: How to Check Database  OLAP or OLTP
                      Mark D Powell
                      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 --
                      • 8. Re: How to Check Database  OLAP or OLTP
                        979662
                        use below query to find your oracle DB OLAP or not.

                        select * from v$option where parameter='OLAP';

                        Thank you
                        • 9. Re: How to Check Database  OLAP or OLTP
                          EdStevens
                          976659 wrote:
                          use below query to find your oracle DB OLAP or not.

                          select * from v$option where parameter='OLAP';

                          Thank you
                          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.