Oracle OLAP Care and Maintenance

Because Oracle OLAP runs in the Oracle Database kernel, it inherits all the benefits of the Oracle Database. Many things that are normally done to improve performance or troubleshoot the Oracle Database will affect Oracle OLAP as well. Here, we will focus on how to configure, tune, back up, and troubleshoot Oracle OLAP analytic workspaces.

Configuring and Tuning Oracle OLAP

Oracle OLAP's multidimensional objects use the same database memory, processes, and storage as any other type of object in the database. As a result, Oracle OLAP benefits from the proper tuning and configuration of the Oracle Database itself. For the most part, if the Oracle Database is running efficiently, Oracle OLAP should be functioning efficiently as well. As with an Oracle database, if the Oracle OLAP design is off, the performance is off, so make sure you validate the design. If the design is bad, no amount of tuning can solve the big problem.

In this blog, we present recommendations and techniques that we have used over the years to help diagnose problems and optimize the performance of Oracle OLAP analytic workspaces. For general database configuration and tuning recommendations, see the Oracle Database documentation.

Validating the Oracle OLAP Installation

Occasionally, Oracle OLAP is not installed correctly or the installation is invalidated for some reason. For example, installation-related issues can occur if the Oracle database has been migrated from one major release to another, like moving from Oracle Database 10g to 11 g.

To verify that the Oracle OLAP is installed and functioning properly, you should first ensure Oracle OLAP is in the list of installed options, and then run a SELECT statement to ensure that the installation is valid.

Is Oracle OLAP in the List of Installed Options? When you run SQL*Plus, the header indicates which options are installed on the database. You will see something like the following when you log in:

Oracle Database 10g Enterprise Edition Release 11.1.0.7.0 -64bit 
Production With the Partitioning, OLAP and Data Mining options

If you do not see OLAP in the list, you need to install Oracle OLAP:

• On Windows-based systems, run the universal installer. Under the custom installation options, select the OLAP option to install Oracle OLAP.
• On UNIX/Li nux systems, relink with the OLAP_ON setting using the following commands:

cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk olap_on 
make -f ins rdbms.mk ioracle

Is the Oracle OLAP Installation Valid? Now you can verify that Oracle OLAP is installed and valid. From SQL*Plus or Oracle SQL Developer, issue the following statement while logged in as as administrator:

select comp_id, comp_name, version, status from DBA_REGISTRY where
comp_name like '%OLAP%' or comp_name like '%X%' or comp_name like '%J%';

If Oracle OLAP is installed in the instance, you should see something like the following output:

If any of these elements are missing or invalid, you need to reinstall Oracle OLAP. Before reinstalling it, you should ensure that the Java Virtual Machine (JVM), Oracle Database Java packages, Oracle XML Developer Kit (XDK), and Oracle XML DB are installed and valid. Sometimes the installation can be done using the Database Configuration Assistant (DBCA), but this is not usually the case, especially if the database instance is a custom installation. The simplest way to reinstall Oracle OLAP is to run a manual installation.

NOTE

Reinstalling Oracle OLAP does not affect any existing analytic workspaces that are present in an Oracle database.

To reinstall Oracle OLAP manually, follow these steps:

1. Stop the database and restart it in upgrade or restricted mode.
2. Go to command mode and change to the ORACLE_HOME directory.
3. Start SQL*Plus in SYSDBA mode (sqlplus / as SYSDBA).
4. Run the following commands.

To install the Java VM, XDK, or XML:

conn / as SYSDBA 
@?/javavm/install/initjvm.sql;
@?/xdk/admin/initxml.sql;
@?/xdk/admin/xmlja.sql;
@?/rdbms/admin/catjava.sql;
@?/rdbms/admin/catexf.sql;

To install Oracle OLAP:

@?/olap/admin/olap.sql SYSAUX TEMP;

Check the logs to make sure that this procedure properly installed or reinstalled Oracle OLAP. You can verify the installation by rerunning the validation query.

Setting Database Parameters

You can set parameters to improve the performance of Oracle OLAP. Some of these are Oracle Database parameters; others are specific to Oracle OLAP.

Database Parameters That Affect Oracle OLAP Performance Make sure that the database is set up with the minimum settings to run Oracle OLAP. The parameters that affect the performance of Oracle OLAP are listed in Table 1. Adjust the server parameter file or init.ora file to these values, and then restart your database instance.

The recommendations in Table 1 assume that the computer is dedicated to Oracle Database and that your database is used predominantly (if not exclusively) for OLAP purposes. If you want to reserve some resources for other applications, first calculate the percentage of resources that are available to Oracle Database. For example, if your computer has 4GB of physical memory and you want to reserve 25 to 30 percent for other applications, you would calculate MEMORY_TARGET (or SGA_TARGET plus PGA_AGGREGATE_TARGET) based on 75 percent of 4GB, which is 3GB.

TABLE 1. Initial Settings for Database Parameter Files

Oracle OLAP Parameters Oracle OLAP uses a memory area called the OLAP page pool. The related parameter, called OLAP_PAGE_POOL_SlZE, specifies in bytes (or kilobytes or megabytes) the size of the paging cache to be allocated to an Oracle OLAP session for a user performing any operation against analytic workspaces. This memory is allocated from the User Global Area (UGA).

In Oracle Database 10g and 11g, OLAP_PAGE_POOL_SlZE is set to 0, which means the database dynamically allocates memory to users on an as-needed basis.

In some cases, the dynamic allocation can assign too much memory to a single process, leaving little for other users. If this happens, you many need to change the parameter value to reflect the amount of memory required by the users.

If Oracle OLAP is used in a shared server environment, OLAP_PAGE_POOL_ SIZE is not dynamic. If the value for OLAP_PAGE_POOL_SIZE is not set, the pool is automatically set to six times the _olap_page_pool_low setting (default of 256KB). In this case, we recommend setting OLAP_PAGE_POOL_SIZE to a fixed value that can accommodate your number of concurrent users, available memory in the System Global Area (SGA), and other resources—between 4MB and 16MB can be a good starting value.

Tuning Oracle OLAP

Before attempting to tune Oracle OLAP, ensure that the server is set up properly and the database is performing well on the relational side. For help completing these tasks, see the Oracle Database 2-Day + Performance Tuning Guide available in the DBA Essentials section of the Oracle Database Documentation Library.

Assuming that the server and database have been tuned appropriately, you can focus on Oracle OLAP. Generally, Oracle OLAP performance issues manifest themselves by poor load performance and/or front-end query performance. Load performance can be the most difficult to diagnose, but can also be easiest to fix. This section tells you how to detect poor performance and suggests a few areas where you can improve performance.

Assessing Load Performance A good place to start your assessment of load performance is the load logs generated by the maintenance process. In Oracle OLAP 10g, the logs are located in the XML_LOAD_LOG table owned by OLAPSYS. In version 11 g, each schema has a table called CUBE_BUILD_LOG. These tables have statistics on the loading processes for dimensions and cubes. For either version, look for the rows that specify a LOAD process, and assess the number of rows loaded and the time it took to load the data. As a general rule of thumb, you should expect at least 1 million rows per minute to be loaded by OLAP. Anything less would indicate that you are having an I/O problem or there is some inefficient SQL processing.

If you discover inefficient load processes, you can use DBA tools such as explain plan on the SQL being used to load the data. If the explain plan indicates full table scans and other inefficient behavior, consider fixing these issues. The Automatic Database Diagnostic Monitor (ADDM) can be a big benefit here as well.

The Dimensional Model and Performance The dimensional model should also be considered as a possible contributor to poor performance. Many implementations create a dimension when it would be more appropriate to define a level, hierarchy, or attribute of an existing dimension. This generally happens when migrating from an existing relational data warehouse or ROLAP tools. We have seen cases where the migrated dimensional model had 50 dimensions. In one particular case, we redesigned a 50-dimension model into a 15-dimension model, and the performance issues were resolved.

Creating surrogate keys in a dimension can also contribute to poor performance. Oracle OLAP ensures keys are unique by prefixing the incoming key with the level identifier. Adding a prefix results in larger keys being used in the analytic workspace, which means that it can take longer to store the keys, take up more space, and possibly take more time to retrieve into the front-end tool. If you know that your dimension data contains only unique keys, turn off the surrogate keys option for the dimension. When you do need surrogate keys, use the shortest possible level identifier to minimize the effect of the storage of the data.

Preaggregation: A Balance Between Query Times and Load Performance While preaggregation of data can improve query performance, it can also increase load times and take additional space. There is a trade-off between space and load time. The best query performance is generally a fully solved cube, but that takes time and space. If load times are too long and space is a concern, further analysis is required. If load times are fast and space is not a concern, then set it to fully solve and check the query performance.

TIP

With Oracle OLAP 11g, the cost-based aggregation option does not require setting aggregation levels. This is because this version of Oracle OLAP does the analysis for you.

Therefore, if you are using version 11g with cost- based aggregation, you might want to let it do the work for you.

You can perform an analysis of how many members are loaded at each level of a hierarchy. This analysis is important in determining which levels of a dimension should be preaggregated and which ones can be aggregated at query time. Let's look at some examples. Table 2 shows a simple analysis that should be done on each dimension.

table 2. Product Dimension Example 1

The number of members and the average number of children per member represent a fairly normal distribution, so standard skip-level aggregation should work well. In skip-level aggregation, the bottom level is aggregated (in our example, SKU) and every other level above it is aggregated (in this case, Class and All Products). This is the default behavior of the AWM Cube Aggregation wizard.

Table 3 shows an example where the bottom two levels have a 1:1 ratio. You do not want to spend the time to aggregate the 400 members of the Group level on the fly, so you should preaggregate this level, along with the SKU level. The compressed composite algorithm will identify the 1:1 ratio automatically and further minimize space by not storing both member keys.

Finally, Table 4 shows a much wider distribution. This distribution may take some sample runs to determine the sweet spot with respect to query performance. We recommend starting with the default skip-level aggregation, as shown in Table 4.

table 3. Product Dimension Example 2

table 4. Product Dimension Example 3

If this leads to poor performance, then we recommend aggregating the level with the largest number of members, in this case the Sub Group level. If this change still does not improve performance, we would likely use full aggregation instead.

Cube Storage Cube storage is another important consideration. Using only the amount of storage you need for your data is important for both load and aggregation times. Here are some helpful hints:

• If your data supports it, use the decimal or integer data type. Even though number is the default, this data type can take up to 3.5 times more space than decimal. Number takes 22 bytes; decimal or integer takes 8 bytes.If your data can be expressed in 8 bytes, you should consider taking advantage of the smaller data storage offered by the other data types.
• Use compressed composites if possible, because this will always build a smaller more efficient cube. Our experience also indicates that compressed composites load much faster than uncompressed composites. Where possible, consider using compressed composites to improve load performance.
• Do not use global composites, which are bigger composites since they are shared across cubes. Note that global composites are unavailable when using compressed composites.
• Review your sparsity settings. You do not need to have a dense dimension. In fact, extensive testing has shown that defining all dimensions as sparse can result in a dramatic improvement in load and aggregation performance, without a significant degradation of query performance. This is especially true for compressed composites.

Backing Up Oracle OLAP

Because the Oracle OLAP data is contained in the Oracle database, there should already be a backup strategy in place to back up not only the relational data, but also the OLAP data. In addition to the existing Oracle Database backup utilities, Oracle OLAP also contains utilities that can be used to ensure that the data is backed up and secure.

In this section, we touch on the traditional Oracle Database backup processes, and then focus on the Oracle OLAP processes that are available. While manual backups are usually enough for small systems, it is highly recommended that an automatic backup be made on a regular basis.

Full Database Backups

The simplest and most reliable database backup is called a cold backup. This consists of shutting down the database instance and copying the directories containing the database files (including Oracle OLAP data) to an archive device, such as tape or other removable media. The challenge is that the database instance must be shut down to perform this type of backup. This is not always possible.

Software products allow for backing up the Oracle database files while they are still open. Recovery Manager (RMAN) is an example of this type of software that is available from Oracle. For more information, see the Oracle Database documentation.

Oracle Database Export Commands

Oracle Data Pump enables fast bulk data and metadata movement between Oracle databases. Data Pump provides parallel export and import utilities (expdp and impdp), as well as a web-based Oracle Enterprise Manager interface. Because the OLAP option is fully integrated into the Oracle Database, it can make use of this facility for backing up and moving data from one machine to another.

RMAN is commonly used to back up, restore, and migrate databases. Again, because Oracle OLAP is fully integrated into the Oracle Database, it can make use of this facility.

A standby database can also be used, provided it is run in Physical mode.

Excluding Analytic Workspace Data for Exports

Analytic workspaces can take up a lot of space and can take time to export. If the analytic workspace can be reconstituted quickly from relational data, or you perform a separate export of the analytic workspace using Data Pump or the OLAP DML export command (discussed in the next section), you may want to exclude the analytic workspace data.

To exclude analytic workspaces from dpexp and exp exports, use this command:

select * from sys.exppkgact$ where package IN ('DBMS_AW_EXP', 'DBMS_CUBE_EXP');
delete from sys.exppkgact$ where package IN ('DBMS_AW_EXP', 'DBMS_CUBE_EXP');

To restore analytic workspaces exports in dpexp and exp exports, use this command:

insert into sys.exppkgact$ values ('DBMS_AW_EXP', 'SYS', 2, 1000);
 insert into sys.exppkgact$ values ('DBMS_AW_EXP', 'SYS', 4, 1000);
/* For Oracle Database 11.1, add this syntax: */
insert into sys.exppkgact$ values ('DBMS_CUBE_EXP', 'SYS', 2, 1050);

Oracle OLAP Data Export Commands for Analytic Workspaces

The OLAP DML language has a set of utilities that support exporting and importing analytic workspace data to files on the server. These commands copy data and definitions from the analytic workspace to an EIF file contained in the server directory specified by the directory alias. The status of the data's dimensions determines which values are exported from the analytic workspace. These commands are used to copy all or parts of the data contained in the analytic workspace to a file, from which it can be imported into another schema or database instance. These commands perform the same basic functionally as the database export commands, but for analytic workspace objects.

An important feature of the export command is the ability to create subsets of the data, using the LIMIT command, before exporting the data. This can be very important if you want to create a small test set of data. When imported, the analytic workspace will contain all the structures, but only the data needed for the desired functionality. The data can then be imported into a test schema or test server. This is very difficult to do with relational data (although a new feature of Data Pump allows for issuing SELECT statements to do similar limiting of data).

Another handy feature of the Oracle OLAP export command is the ability to exclude aggregated data from the export file. This creates a much smaller export file and still preserves the data. It does require that you aggregate the data once it is loaded again, but this may take less time than the export. To exclude aggregated data, add the noaggr argument to the end of the export command.

The export command has the following syntax:

EXPORT export_item TO EIF FILE filename [LIST] [NOPROP] -
[NOREWRITE|REWRITE] [FILESIZE n [K, M, or G]]                                             -
[NOTEMPDATA] [NLS_CHARSET charset-exp] [AGGREGATE | NOAGGR]

where export item is one of the following:

• name [AS newname]
• exp [SCATTER AS scattername [TYPE scattertype] EXCLUDING (concatbasedim .             .   .)]
• exp AS name [EXCLUDING (concatbasedim . .   .)]
• ALL

The following example exports the SALESTRACK analytic workspace:

CDA olapdir /* change the directory alias to point to olapdir
EXPORT ALL to eif file salestrack.eif

If you wanted to do the same thing from a SQL command prompt, you can use the DBMS_AW stored procedure, as follows:

exec dbms_aw.execute(1cda olapdir;export all to eif file 
salestrack.eif');

The following is the syntax for the import command:

IMPORT import_item FROM EIF FILE filename [INTO workspace] -
[MATCH [STATUS]|APPEND|REPLACE [DELETE]] [LIST [ONLY]] [DATA] - 
[DFNS] [UPDATE] [NOPROP] [NASKIP] [NLS_CHARSET charset-exp]

where import_item is one of the following:

• name [AS newname]
• ALL

For example, to import the salestrack.eif file into a new analytic workspace, use this command:

CDA olapdir /* change the database alias to point to olapdir 
Import ALL from eif file salestrack.eif

Troubleshooting Oracle OLAP

A plethora of information pertaining to troubleshooting the Oracle Database is available. Our goal here is to provide some hints on troubleshooting issues related to Oracle OLAP.

Access to the Analytic Workspace

It is not unusual to have users of such tools as OBIEE or Microsoft Excel complain that they are not able to see the Oracle OLAP data. The problem can be traced to user access privileges. The OLAP data stored in an analytic workspace is actually an Oracle table. To allow users access to this table, a simple grant needs to be executed.

For example, consider an analytic workspace table called AW$OLAPTRAIN. To allow Scott to read the OLAP data in SALESTRACK contained in the OLAPTRAIN schema, you issue this command:

grant select on AW$SALESTRACK to Scott;
 grant OLAP_USER to Scott;

The additional grant, OLAP_USER, is used to extend the ability to see OLAP objects. After these grants are made, the user, Scott, can now see the data contained in SALESTRACK AW. See Chapter 4 for additional security settings.

Oracle OLAP Dynamic Performance Tables

The Oracle Database has a series of tables that record the database activity and store information about processes and operations in the database instance. Data in the tables is updated continuously while the instance is running. These tables are called the V$ tables, or the dynamic performance tables, and they are owned by SYS. Any user with the SELECT CATALOG role can access these tables. Additionally, the system creates views from these tables and creates public synonyms for the views. The views are also owned by SYS, but an administrator can grant access to them to any user requiring access.

One set of tables collects data pertaining to the operation of Oracle OLAP. These tables and related views are prefixed with V$AW, as listed in Table 5.

TABLE 5. Oracle OLAP Views

The most important views are V$AW_CALC and V$AW_LONGOPS. The V$AW_CALC view contains data about session usage of caches as well as the status of aggregation processing. You should watch how large the page pool is, as well as cache hits and misses. The more effective the caches are, the better is the response time experienced by users. An ineffective cache (that is, one with few hits and many misses) suggests that the data is not being stored optimally for the way it is being viewed. To improve run-time performance, you may need to reorder the dimensions in the cube to load the larger dimensions sooner than later. The V$AW_CALC view also shows the OLAP DML command that was executed. Knowing which command was executed will help you determine what was being done at the time.

The V$AW_LONGOPS view shows the OLAP DML command being run, such as SQL fetch, import, or execute. The view provides the current state of the operation—whether it is executing, fetching, or finished. It also shows the number of rows that have been acted upon and the time the command started executing.

For additional information about these views and how they can be used, see the Oracle OLAP User's Guide under "Administering Oracle OLAP."

Diagnostic Commands

Tables 6, 7, and 8 list of some diagnostic commands that can be used to turn on various features of the database to trace and capture information. These can be used for diagnosing build, query, and program performance problems

TABLE 6. OLAP Maintain Debugging

TABLE 7. Query Debugging

NOTE

In Oracle Database 11g Release 2 (11.2.0), use the DBMS_CUBE_LOG package to manage diagnostic logs.

These commands should be used with caution. If possible, try them on a nonproduction environment until you are comfortable with their use.

Regarding the OLAP DML command settings listed in Table 8, if you are running OLAP Worksheet, there is no need for the PL/SQL wrapper.

Table 8. Program Debugging

Helpful DBA Scripts

Analytic workspace access can be summarized as read-many, write-once—only one user at a time can have an analytic workspace open in read-write (RW) mode, but many can have the same analytic workspace open in read-only (RO) mode. You can find out who has RW access by running a SQL select statement such as this one:

select username,sid,serial#,

owner||'.'||a.aw_name||' ('||decode(attach_mode/ 'READ WRITE', 'RW',

'READ ONLY', 'RO','MULTIWRITE', 'MW', 'EXCLUSIVE', 'XW', attach_ mode)||')' aw, generation

from dba_aws a,v$aw_olap b, v$aw_calc c, v$session

where a.aw_number=b.aw_number and sid=b.session_id and c.session_id = sid order by username, sid, a.aw_name;

Notice that this SELECT statement makes use of the previously mentioned V$ tables to obtain the session and analytic workspace information. The results of the SELECT statement look something like this:

These results provide not only a list of all the users that are connected to analytic workspaces, but also their session information. The session is important if you need to kill their session, trace the commands being executed, or just give them a call and ask them to detach from the analytic workspace when they are done.

A more complex query can yield more interesting and useful information. If you want to know about how much of the Program Global Area (PGA) is being used by a session and how well it is using the OLAP page pool, you could use the following script:

set lines 110 pages 500

col usn for a23 hea "USER (SID,SERIAL#,SVR)"
col pga_used for 9,990.9 hea "PGA MB|USED"
col pga_max for 9,990.9 hea "PGA MB|MAX"
col olap_pp for 9,990.9 hea "OLAP MB" 
col olap_hrate for 99.9 hea "OLAP|Hit %"
bre on REPORT;

comp avg lab Average min lab Minimum max lab Maximum of olap_hrate on REPORT;
comp avg lab Average sum lab Total min lab Minimum max lab Maximum of pga_used on REPORT;
comp avg lab Average sum lab Total min lab Minimum max lab Maximum of pga_max on REPORT;
comp avg lab Average sum lab Total min lab Minimum max lab Maximum of olap_pp on REPORT; 

select vs.username||' ('||vs.sid||','||vs.serial#||','|| 
decode(server,'DEDICATED','D','S','SHD','U')||')' usn,
round((pga_used_mem)/1024/1024/1) pga_used, 
round((pga_max_ mem)/1024/1024,1) pga_max, 
round((pool_size)/1024/1024,1) olap_pp,

round(100*((pool_hits)/((pool_hits)+(pool_misses))),1) olap_hrate 
from v$process vp, v$session vs, v$aw_calc va 
where va.session_id = vs.sid and vp.addr = vs.paddr 
order by vs.username, vs.sid, vs.serial#;

This script produces output like the following:

Oracle provides some other handy scripts for the Oracle OLAP administrator on the Oracle wiki site, as shown in Figure 1.

Figure 1. Oracle OLAP DBA scripts on the Oracle wiki

Another interesting site is the Oracle OLAP blog (http://oracleolap.blogspot.com), which includes discussions of basic and advanced OLAP option topics. Another example script demonstrates how to determine the names and size of all the internal AW objects:

set lines110 pages 500
col partname for a50 hea AW_OBJECT
col kb for 999,999,990 
bre on REPORT;
comp sum lab Total of kb on REPORT;
select partname,
round(sum(dbms_lob.getlength(awlob))/1024,0) KB
from olaptrain.aw$salestrack 
where extnum=0
group by partname 
order by kb;

Flashback with Analytic Workspaces

Oracle OLAP is fully compatible with the Flashback option for the Oracle Database. Flashback can quickly return a database to the state it was in before the last update or maintain action. This feature is handy if you are testing designs or debugging OLAP DML programs. You can use Flashback to help avoid mistakes and save valuable time.

The following script uses SQL and OLAP DML to demonstrate how to use Flashback.

/* grant flashback to OLAPTRAIN
 conn system/manager
grant execute on sys.dbms_flashback to olaptrain;

/* now connect to olaptrain user 
conn olaptrain/oracle
 set serveroutput on size 9999 
begin
   dbms_aw.execute('aw attach SALESTRACK rw');
   dbms_aw.execute('DEFINE flash_test VARIABLE DECIMAL');
   dbms_aw.execute('flash_test = 10'); 
   dbms_aw.execute('update ; commit');
   dbms_aw.execute('show flash_test'); 
   dbms_aw.execute('aw detach SALESTRACK'); 
end;

/* Wait 30 Minutes or so */
begin
   dbms_aw.execute('aw attach SALESTRACK rw'); 
   dbms_aw.execute('flash_test = 20'); 
   dbms_aw.execute('update; commit'); 
   dbms_aw.execute('show flash_test'); 
   dbms_aw.execute('aw detach SALESTRACK'); 
end;

/* Now verify the values */ 
begin
   dbms_flashback.enable_at_time(sysdate - 15/1440); -- go back 15 minutes
   dbms_aw.execute('aw attach SALESTRACK ro');
   dbms_aw.execute('show flash_test');
   dbms_aw.execute('aw detach SALESTRACK');
   dbms_flashback.disable;
end;

 These commands can be run as a script (as shown), run interactively from the SQL prompt, or even run from OLAP Worksheet in SQL or OLAP DML mode.

Вас заинтересует / Intresting for you:

Oracle OLAP Examples: Data War... 3780 views Илья Дергунов Sun, 09 Sep 2018, 08:32:52

Oracle BI 12c Architecture 2785 views Светлана Комарова Wed, 02 May 2018, 09:11:59

Oracle Endeca Information Disc... 1375 views Даниил Wed, 04 Jul 2018, 14:41:32

Oracle Endeca Information Disc... 1893 views Даниил Mon, 25 Jun 2018, 08:22:27