Using SAP NetWeaver with Oracle Database In-Memory

Please download to get full document.

View again

of 20
All materials on our website are shared by users. If you have any questions about copyright issues, please report us to resolve them. We are always happy to assist you.
Information Report
Category:

Ancient Egypt

Published:

Views: 3 | Pages: 20

Extension: PDF | Download: 0

Share
Description
Using SAP NetWeaver with Oracle Database In-Memory Key Guidelines O R A C L E W H I T E P A P E R A U G U S T 2015 Table of Contents Preface 3 Prerequisites 4 Restrictions 5 Index Design 5 Database Buffer
Transcript
Using SAP NetWeaver with Oracle Database In-Memory Key Guidelines O R A C L E W H I T E P A P E R A U G U S T 2015 Table of Contents Preface 3 Prerequisites 4 Restrictions 5 Index Design 5 Database Buffer Cache 5 SAP Dictionary Support 5 SAP BW Aggregates 5 Approach 1: Oracle Database In-Memory Advisor 6 Prerequisites 6 Getting the IM Advisor Package and Documentation 7 Installation 7 Using IM Advisor 7 AWR Statistics and SQL Tuning Set 7 Running IM Advisor 9 Transport of AWR Statistics, SQL Tuning Set and IM Advisor Augment Data from your Live System to another System for further Analysis 9 IM Advisor Parameters with SAP 10 Fetching and refining IM Advisor results 10 Load identified SAP tables into the IM Column Store 12 Check Status of SAP tables in the IM Column Store 12 Approach 2: Knowing which SAP table(s) to be placed into the IM Column Store 14 1 USING SAP NETWEAVER WITH ORACLE DATABASE IN-MEMORY Installation of SAP_IM_ADV package 14 How to use SAP_IM_ADV package 14 Recommended Database In-Memory related init.ora Parameters 17 References 18 SAP Notes 18 MOS Notes 18 White Papers 18 Documentation 18 2 USING SAP NETWEAVER WITH ORACLE DATABASE IN-MEMORY Preface This document describes mandatory steps for using Oracle Database In-Memory with SAP Applications and SAP Solutions based on SAP NetWeaver Technology. Therefore this white paper is valid for SAP Products like SAP BW, SAP ERP, SAP CRM or SAP XI or SAP solutions like SAP IS-U, SAP IS-Banking or SAP IS-Retail. Oracle Database In-Memory is certified and supported for both Single Instance and Real Applications Clusters (RAC) on all UNIX/Linux, Windows and Oracle Engineered platforms certified by SAP using SAP Products based on SAP NetWeaver Technology. Two approaches are described on how to organize SAP tables and table partitions in the In-Memory Column Store (IM column store). The first approach provides a systematic way to identify SAP tables and table partitions which are useful to be placed in the IM column store. It provides you with information whether the SAP application can benefit from using Database In-Memory. The second approach provides you with information on how much additional memory you need on your system for placing a specific SAP table/partition or a set of SAP tables/partitions into the IM column store. Guidelines and recommendations given in this document are SAP specific and may differ from guidelines and recommendations given in other Non-SAP specific or general purpose papers. 3 USING SAP NETWEAVER WITH ORACLE DATABASE IN-MEMORY Prerequisites To use Oracle Database In-Memory with SAP NetWeaver the following technical and business prerequisites must be met: Oracle Database 12c Release 1 Patch Set 1 ( ) plus o UNIX/Linux: Oracle Database SAP Bundle Patch June 2015 (SAP1202P_1506) or newer Strongly Recommended Oracle Database SAP Bundle Patch August 2015 (SAP1202P_1508) o Windows: Windows DB Bundle Patch or newer Strongly Recommended Windows DB Bundle Patch SAP NetWeaver 7.x Version with minimum SAP Kernel 7.21_EXT Proper Licensing of Oracle Database In-Memory Option o o For Oracle Full Use Customers the license for the Database In-Memory Option is required For ASFU Customers (Customers who have licensed Oracle Software through SAP) the license from SAP for the Oracle In-Memory License Package is required Oracle Database In-Memory (IM) Advisor o Requires Licenses of Oracle Tuning and Diagnostics Pack for Oracle Full Use Customers if Database In-Memory option has been deployed. Free of charge for ASFU Customers. SAP Note will be updated on a regular base with future changes. Always check SAP Note before using Database In-Memory with SAP NetWeaver based products. 4 USING SAP NETWEAVER WITH ORACLE DATABASE IN-MEMORY Restrictions Index Design It is not allowed to make any changes to the standard index design of the SAP installations. SAP Applications highly depend on indexes to deliver good throughput and response times. Therefore it is not supported to drop any index of the standard SAP index design or set any standard SAP index invisible or unusable. Therefore it is forbidden to make any changes to indexes which are not part of the Y or Z index namespaces (these namespaces are reserved for customer specific changes). Violation of this rule will create an unsupported SAP installation. SAP customers are allowed to make changes to their customer specific index design i.e. all indexes which belong to the Y or Z namespaces can be changed. Examples: Index BSIS~0 (or any primary key index) may NOT be dropped or set invisible/unusable as it is a standard index. Index GLFUNCA~2 may NOT be dropped or set invisible/unusable as it is a standard index. Index VBAP~Z03 may be dropped or set invisible/unusable as it is a custom index (namespace Z (~Z)) and not a standard index. Individual Columns It is not supported to load individual columns of an SAP table or partition into the IM column store. It is also not supported to exclude individual columns from an SAP table or partition from the IM column store. An SAP table is a database table used by an SAP application. All columns of an SAP table or partition must be loaded into the IM column store. Database Buffer Cache It is not allowed to reduce the size of the database buffer cache and assign the memory to the IM column store. When using Database In-Memory additional memory is needed on the system to accommodate both the database buffer cache and the IM column store. SAP Dictionary Support Full SAP Dictionary (DDIC) Support of in-memory attributes at the table level starts with the support package SAP_BASIS 7.40 SP12. When using older SAP kernels in-memory attributes will be lost during system copies, upgrades, table reorganization or other SAP maintenance operations. SAP BW Aggregates It is not recommended to drop all aggregates from an existing BW system as aggregates are designed for providing good throughput in concurrent user environments. 5 USING SAP NETWEAVER WITH ORACLE DATABASE IN-MEMORY Approach 1: Oracle Database In-Memory Advisor The Oracle Database In-Memory Advisor (IM Advisor) is a tool that tries to provide you with recommendations which database tables could be placed into the IM column store and tries to estimate the performance benefits of using the IM column store. The IM Advisor relies on data stored in the automatic workload repository (AWR) as well as on some heuristics. For better results it also allows the use of captured SQL tuning sets. For SAP applications it is strongly recommended to use a reasonable time window of collected AWR data. So at least 2-3 days of AWR data should be used for the IM Advisor. It absolutely makes no sense to use data from a 1-2 hour time window. For SAP applications the more AWR data is used for the IM Advisor the better the recommendations of the IM Advisor will be as in general the SAP workload changes during different times of the day. For instance during the night batch jobs are running which do not run during the day generating different database workloads. Also AWR data during month-end closing, year-end closing or fiscal quarter/year closing should be considered as well. Note that the IM Advisor has no knowledge about SAP specific tables where in-memory placement is not useful. Therefore additional steps are required which are described later in this paper to define the in-memory table set when running SAP applications. Prerequisites The database where you want to run IM Advisor must have XDB component installed as IM Advisor relies on functions provided by XDB. In Oracle Database 12c XDB is installed by default. In Oracle Database 11g Release 2 Version or XDB may have to be installed if not already available before installing IM Advisor. Run the following query to determine if XDB is available in your database: select comp_name, version, status from dba_registry where comp_id = 'XDB'; In order to install XDB with the following parameters: XDB user password - welcome1 (customer specific) XDB user default tablespace - SYSAUX XDB user temporary tablespace - PSAPTEMP YES or NO (SecureFile or LOBs) - YES Example: sqlplus / as sysdba spool xdb_install.log set echo on; connect / as sysdba shutdown immediate; welcome1 SYSAUX PSAPTEMP spool off For further details refer to My Oracle Support (MOS) note USING SAP NETWEAVER WITH ORACLE DATABASE IN-MEMORY Getting the IM Advisor Package and Documentation The IM Advisor is contained in the SAP Bundle Patch as patch The SAP Bundle Patch places the IM Advisor in the directory ORACLE_HOME /sap/ora_imadvisor. The IM Advisor can also be downloaded from Oracle as described in My Oracle Support (MOS) note Furthermore the IM Advisor white paper can be downloaded from SAP Support Note or from My Oracle Support (MOS) Note The IM Advisor uses AWR statistics and optionally a SQL Tuning Set to generate its recommendations. Because of this it can run either on the database where the AWR statistics (and SQL Tuning Set) have been collected or on another database where the AWR statistics (and SQL Tuning Set) have been loaded into using AWR and STS export/import methods as well as IM Advisor s augment export and import scripts (check scripts imadvisor_awr_augment_export.sql and imadvisor_awr_augment_import.sql). Installation Follow the IM Advisor documentation and start the installation of IM Advisor either by unzipping the package if downloaded from the MOS note or by running the imadvisor_install.sql installation script if IM Advisor was installed with the SAP Bundle Patch (SBP). When installed with SBP execute: cd $ORACLE_HOME/sap/ora_imadvisor sqlplus / as sysdba It is recommended to create the IM Advisor schema objects in the SYSAUX tablespace instead of the SYSTEM tablespace. Using IM Advisor AWR Statistics and SQL Tuning Set IM Advisor can work with AWR statistics only. AWR statistics are collected automatically on a regularly basis and kept until they expire. It is not recommended to rely on AWR statistics only because relevant SQL statements may have already been aged out from the SQL area during snapshot intervals. Therefore adding a SQL Tuning Set that contains all the statements run over a representative period of time can help to improve the results of IM Advisor significantly. AWR statistics and SQL Tuning Set can be either collected on the live system during normal workload phases or if you are using the Oracle Real Application Testing Option (RAT) on a copy of the live system during workload replay. The following script gives an example how to collect proper data for the IM Advisor in a live system. At the beginning it creates an AWR snapshot then it starts collection of SQL Tuning Set data every minute for the given amount of time and at the end it creates a second AWR snapshot. The AWR snapshot range and the captured SQL Tuning Set will then be used in the IM Advisor run. Note: If you do not wish to use a SQL Tuning Set then you do not need to execute the following script. ACCEPT sts_name PROMPT Name of SQL Tuning Set: ; ACCEPT sts_owner PROMPT Owner of SQL Tuning Set: ; ACCEPT sts_schema PROMPT SAP schema for capture of sql area: ; ACCEPT sts_seconds PROMPT How many seconds should be captured: ; 7 USING SAP NETWEAVER WITH ORACLE DATABASE IN-MEMORY SET SERVEROUTPUT ON; DECLARE snapid_start number; snapid_stop number; BEGIN snapid_start:=dbms_workload_repository.create_snapshot(); DBMS_OUTPUT.PUT_LINE('start snapshot id = ' snapid_start); DBMS_SQLTUNE.CREATE_SQLSET( sqlset_name = '&sts_name', sqlset_owner = '&sts_owner'); DBMS_SCHEDULER.CREATE_JOB( job_name = 'STSSAPWORKLOAD', job_type = 'PLSQL_BLOCK', job_action = 'BEGIN dbms_sqltune.capture_cursor_cache_sqlset( sqlset_name = ''&sts_name'', time_limit = &sts_seconds, repeat_interval = 60, sqlset_owner = ''&sts_owner'', basic_filter = ''parsing_schema_name = ''''&sts_schema''''''); END;'); DBMS_SCHEDULER.RUN_JOB('STSSAPWORKLOAD'); snapid_stop:=dbms_workload_repository.create_snapshot(); DBMS_OUTPUT.PUT_LINE('stop snapshot id = ' snapid_stop); END; / We recommend to create the SQL Tuning Set in the same schema you are planning to run IM Advisor later i.e. SAPWORK as your SQL Tuning Set name, SAPSR3 or SYS as your SQL Tuning Set owner, SAPR3 or SAPSR3 as your SAP schema. The script will print the AWR snapshot id at the start and the end of SQL Tuning Set collection. Please remember and write down those two snapshot ids as they will be needed later. If you are planning to collect AWR statistics and SQL Tuning Set during workload replay on a test system you do not need to create the SQL Tuning Set with the script above. Instead just specify to collect SQL Tuning Set when calling DBMS_WORKLOAD_REPLAY.PREPARE_REPLAY. Example: begin dbms_workload_replay.prepare_replay ( capture_sts = true, sts_cap_interval = 60 ); end; To determine the system generated name of the SQL Tuning Set check the view DBA_WORKLOAD_REPLAYS after the workload replay has completed. 8 USING SAP NETWEAVER WITH ORACLE DATABASE IN-MEMORY Running IM Advisor IM Advisor can run on any Oracle 12c database that contains the collected AWR statistics and SQL Tuning Set data. In addition it can run on any Oracle 11g database version or higher that has XDB installed which is mandatory for the IM Advisor. This means that you could collect AWR statistics and SQL Tuning Set in a live system and run the IM Advisor either in the same system or transport it from the live system to another system where you want to run the IM Advisor. Transport of AWR Statistics, SQL Tuning Set and IM Advisor Augment Data from your Live System to another System for further Analysis Export/Import of AWR statistics and SQL Tuning Sets is described in in the Oracle 12c Database Documentation. Export/Import of AWR augment data for the IM Advisor is described in the IM Advisor documentation. Here an overview of the steps for exporting AWR statistics, SQL Tuning Set and IM Advisor augment data: Create an export directory on local filesystem Example: mkdir /oracle/c11/sapdata1/imadv_data Create a directory object for that export directory Example: create or replace directory IMADV_DATA as /oracle/c11/sapdata1/imadv_data Export AWR statistics within the relevant AWR snapshot range Example: sqlplus / as Export IM Advisor augment data Example: sqlplus / as If you have created an SQL Tuning Set, create a staging table, load the SQL Tuning Set data into the staging table and export the staging table using expdp (datapump). Example: exec DBMS_SQLTUNE.CREATE_STGTAB_SQLSET(table_name= 'STSSAPWORKLOAD'); exec DBMS_SQLTUNE.PACK_STGTAB_SQLSET(sqlset_name= 'STSSAPWORKLOAD', sqlset_owner= '%', staging_table_name= 'stssapworkload'); expdp / DIRECTORY=IMADV_DATA DUMPFILE=stssapworkload.dmp TABLES=STSSAPWORKLOAD Drop the SQL Tuning Set Example: exec DBMS_SQLTUNE.DROP_SQLSET( sqlset_name = 'STSSAPWORKLOAD' ); Drop the staging table Example: drop table STSSAPWORKLOAD; Now copy the files in the export directory to your import directory on the system where you want to run the IM Advisor and import them. Create a directory object for that import directory Example: create or replace directory IMADV_DATA as /oracle/c11/sapdata1/imadv_data Import AWR statistics Example: sqlplus / as Check the dbid of the AWR import Example: select distinct dbid from dba_hist_snapshot; Import IM Advisor augment data Example: sqlplus / as If you have created and exported an SQL Tuning Set, import the staging table using impdp (datapump), copy the SQL Tuning Set from the staging table into the SQL Tuning Sets schema. Example: impdp / DIRECTORY=IMADV_DATA=stssapworkload.dmp TABLES=STSSAPWORKLOAD exec DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET(sqlset_name = STSSAPWORKLOAD, replace = TRUE, staging_table_name = STSSAPWORKLOAD ); Drop the staging table Example: drop table STSSAPWORKLOAD; 9 USING SAP NETWEAVER WITH ORACLE DATABASE IN-MEMORY IM Advisor Parameters with SAP Beside AWR statistics and SQL Tuning Set the IM Advisor provides a number of parameters that take influence on the heuristics used to calculate and build the list of recommendations. The following IM Advisor parameters have to be used for SAP applications. These parameters may slightly change over time or additional parameters may be added in the future. IM Advisor Parameter Name and Value: WRITE_DISADVANTAGE_FACTOR = 0.7 LOB_BENEFIT_REDUCTION = 1.2 MIN_INMEMORY_OBJECT_SIZE = READ_BENEFIT_FACTOR = 2 IM Advisor can optimize the recommendations based on a specified amount of memory (RAM). This parameter should be used with SAP applications when generating the recommendations. The script below is an example how to run the IM Advisor and fetch the results. It assumes that AWR statistics and an SQL Tuning Set have been collected as described in the above chapter AWR Statistics and SQL Tuning Set. It creates an IM Advisor task, sets the necessary parameters for proper heuristics, adds the SQL Tuning Set and the AWR statistics by specifying a start- and end- snapshot id and finally executes the task and generates the recommendations based on the available memory target value (inmemory_size) of 64GB. set serveroutput on /* drop eventually pre-existing task */ exec dbms_inmemory_advisor.drop_task('sapwork',true); exec dbms_inmemory_advisor.create_task('sapwork'); exec dbms_inmemory_advisor.set_parameter('write_disadvantage_factor', 0.7, 'SAPWORK'); exec dbms_inmemory_advisor.set_parameter('lob_benefit_reduction', 1.2, 'SAPWORK'); exec dbms_inmemory_advisor.set_parameter('min_inmemory_object_size', , 'SAPWORK'); exec dbms_inmemory_advisor.set_parameter('read_benefit_factor', 2, 'SAPWORK'); /* Only needed when using a SQL Tuning Set */ exec dbms_inmemory_advisor.add_sqlset('sapwork', 'STSSAPWORKLOAD'); exec dbms_inmemory_advisor.add_hist_statistics('sapwork', 1306, 1590); exec dbms_inmemory_advisor.execute_task('sapwork'); /* Memory Target Value 64GB = Bytes */ exec dbms_inmemory_advisor.generate_recommendations('sapwork', NULL, ); Fetching and refining IM Advisor results The generated recommendations are stored in the database and need to be fetched and written into local files. Enter value for im_task_name: SAPWORK Fetching recommendation files for task SAPWORK Enter value for recommendation_directory: /data/awr_2_ _im_12864/imadv Placing recommendation files in directory /data/awr_2_ _im_12864/imadv 10 USING SAP NETWEAVER WITH ORACLE DATABASE IN-MEMORY This will generate the files below: imadv]$ ls -la *SAPWORK* -rw-r--r--. 1 oracle oinstall Jun 11 11:09 imadvisor_auxiliary_sapwork.html -rw-r--r--. 1 oracle oinstall Jun 11 11:09 imadvisor_sapwork.html -rw-r--r--. 1 oracle oinstall 6151 Jun 11 11:09 imadvisor_sapwork.sql -rw-r--r--. 1 oracle oinstall Jun 11 11:09 imadvisor_object_sapwork.html -rw-r--r--. 1 oracle oinstall Jun 11 11:09 imadvisor_sql_sapwork.html NOTE: Please check the value of the Analytics Processing Percentage from the Workload Database Usage section in the file imadvisor_sapwork.html. If the value of Analytics Processing Percentage is less than 40% then using Database In-Memory will not improve the overall throughput of the SAP system. In such case Database In-Memory cannot help as there are not enough analytical queries produced by the SAP application. In above example imadvisor_sapwork.sql contains the SQL statements to place the tables recommended by the IM Advisor into the IM column store. As the IM Advisor is not SAP aware it may have recommended tables which should never be loaded into the IM column store when running an SAP Application. Because of this two scripts are provided which correct the IM Advisor output. These scripts are included In the SAP Bundle Patch (and not available from MOS). The first script called sapgenexclusions.sql produces a text file (exclusions.txt) with all SAP tables that should not be placed into the IM column store. The second script called sapmodimadvscript.pl will generate the final list of recommendations in the file finalimtabs.sql based on the output o
We Need Your Support
Thank you for visiting our website and your interest in our free products and services. We are nonprofit website to share and download documents. To the running of this website, we need your help to support us.

Thanks to everyone for your continued support.

No, Thanks
SAVE OUR EARTH

We need your sign to support Project to invent "SMART AND CONTROLLABLE REFLECTIVE BALLOONS" to cover the Sun and Save Our Earth.

More details...

Sign Now!

We are very appreciated for your Prompt Action!

x