This chapter discusses these topics:
Section 5.2, "Table Pinning in Flash Memory on the Oracle Database Machine"
Section 5.3, "Configuring the Oracle Exadata Database Server"
Specific in-memory applications for JD Edwards EnterpriseOne were evaluated to determine if placing critical indexes in memory on the Oracle database machine yielded performance benefits. This section documents specific indexes to consider manually placing in memory. However, other impacts to the overall Oracle Exadata database performance may be introduced and the overall performance of the database should be considered and evaluated. Additionally, the customer DBA must determine whether it is feasible to place the recommended indexes in memory given constraints such as the size of database tables and available memory.
This section discusses the recommendations for creating primary indexes in memory for the following JD Edwards EnterpriseOne In-Memory applications:
For the Project Profile Management application, performance can be improved by creating the primary indexes in memory for the following tables:
F4812H
F0911
F4812
F0902
When specifying indexes to be created in memory, the schema, table name, and the index name must be specified. For example, in a DV910 environment, the JD Edwards default schema is testdta. Therefore, assuming DV910 the following create index segment specifies these values in the correct order and syntax:
create index testdta.f4812h_exa
|
Caution: For supportability purposes, it is recommended that the index name specified in this document is used. |
The second segment of the SQL statement to create the index in memory must include the schema, table name, and each table column name. For example, the following segment completes the create index in memory SQL statement:
on testdta.f4812h(wdmcu,wdobj,wdsub,wdlssq,wdvoid,wdelgc,wdidgj);
The following are the SQL statements that are recommended to create the primary indexes in memory (each create index should be specified as one contiguous line with no line returns) for the DV910 pathcode and testdta datasource:
create index testdta.f4812h_exa on testdta.f4812h(wdmcu,wdobj,wdsub,wdlssq,wdvoid,wdelgc, wdidgj);
create index testdta.f0911_exa on testdta.f0911(glmcu,glaid,gllt,globj,glsub,gldgj,glpost,glaa,glu);
create index testdta.f4812_exa on testdta.f4812(wdaid5,wdmcu,wdobj,wdsub,wddgj,wdelgc,wdcrcd,wdaid,wdccd,wdbtol,wdt cls,wdjrst,dicuj);
create index testdta.f0902_exa on testdta.f0902 ("gbctry","gblt","gbfq","gbfy","gbmcu","gbaid");
Unlike the In-Memory application of Project Portfolio Management, the indexes for the Sales Advisor application are pre-built and included in the deliverable of the code base. Therefore no further index creation requirements are needed.
The Oracle database machine allows tables to be pinned to fast access memory areas. This feature is unique in how it is managed and handled by the Oracle software. The Oracle database allows the database administrators to specify certain tables to be forced into cache apart from the automatic caching algorithm. By using this feature when faster memory can be used in lieu of storage, the end user response time in lab environments was decreased up to 33% for 2000 and 3000 user loads.
This chapter discusses these topics:
Section 5.2.1, "Discovering Suitable Candidates for Table Pinning"
Section 5.2.2, "Manually Pinning Table into Flash Cache Memory"
The method to determine which tables to pin into flash memory included querying the Oracle metric 'buffer gets' and 'full table scans'. A full table scan is one of the more costly disk I/O database processes. Table scans involve many requests for data stored on the disk and requires a substantial number of 'buffer gets'. The performance of table scans is significantly improved when the targeted table is in solid state flash memory.
The following Oracle SQL Query was used to determine high 'buffer gets' and 'full table scans'. As a result, for EnterpriseOne the serialized object tables, DV900.F989999 and DV900.F989998, were identified as good candidates for pinning into the solid state flash memory.
%> cat get FullScan.sql set pages 1000 set feedback off set lines 150 spool fts.lst column sql_text Format A50 Heading 'Full Table Scans' wrap break on sql_text skip 1 Select Executions, Sorts, Disk_Reads, Buffer_Gets, CPU_Time, Elapsed_Time, sql_text from v$sqlarea where (address, hash_value) in (Select address, hash_value from v$sql_plan where options like '%FULL%' and operation like '%TABLE%') Order by Elapsed_Time spool off set feedback on
The resulting output file was inspected and the following line was identified:
EXECUTIONS SORTS DISK READS BUFFER GETS CPU TIME ELAPSED TIME Full Table Scans 32284 0 2271 266744503 447974714 452738447 SQL_TEXT SELECT T0.WBJPO,T0.WBUID,T0.WBOID,T0.WBLNGPREF,T0.WBJVER FROM DV900.F989999 T0, DV900.F989998 T1 WHERE ((T1.WBJOBID = :1 AND T0.WBUID = :2 )) AND (T0.WBOID = T1.WBOID)
Relative to the results identified above, the large number of disk reads, executions and buffer gets indicated these tables are excellent candidates for caching.
To manually pin tables into flash memory the Oracle databases uses a simple SQL query. For example, to pin the two EnterpriseOne tables that were identified as likely candidates for improved performance in the previous section of this chapter. In this example, the cell_flash_cache is the name of the faster storage. The following example shows the SQL commands to pin specific JD Edwards EnterpriseOne tables in a RAM solid state disk cache on the Exadata Database Machine:
alter table dv910.f989999 storage (cell_flash_cache keep); alter table dv910.f989998 storage (cell_flash_cache keep);
This section provides some recommended configuration parameters for JD Edwards EnterpriseOne Database Server. These settings are representative of the changes you should consider to configure your own system, depending on such factors as available machine resources and expected user load. These settings are based on a load of 2,000 users.
|
Note: These recommendations assume user competency with applicable tools on the referenced systems. |
The example 'alter' commands listed in this chapter are representative of those that your Oracle DBA might use to configure the database on your Exadata machine. These commands assume a load of 2,000 users on a two-node database on the Exadata machine.
This section discusses these topics:
The following are an example of the 'alter' commands that an Oracle DBA might consider to configure the Oracle Database:
alter system set "_enable_NUMA_support"=FALSE scope=spfile; alter system set "_file_size_increase_increment"=2044M scope=spfile; alter system set compatible=11.2.0.3.0 scope=spfile; alter system set db_lost_write_protect=TYPICAL scope=spfile; alter system set filesystemio_options=setall scope=spfile; alter system set global_names=TRUE scope=spfile; alter system set open_cursors=1000 scope=spfile; alter system set parallel_adaptive_multi_user=FALSE scope=spfile; alter system set processes=10000 scope=spfile; alter system set sql92_security=TRUE scope=spfile; alter system set dispatchers = '' scope=spfile; alter system set pga_aggregate_target=0 scope=spfile; alter system set shared_servers=0 scope=spfile;
The following shows an example of the database settings after the alter commands in the preceding section have been executed to configure the Oracle database used with Oracle VM Templates on Exalogic. This example assumes the Exadata Oracle IDs are ovsorcl1 and ovsorcl2 for each of the two nodes on the Exadata Database Machine.
An ASM Disk Group of DA_SLCM61 is used in this example. The Exadata target hostname is 'slcm61-scan1' on the default port of 1521 for Oracle connection configuration. The Exadata is configured to use dedicated connections and automated memory management.
ovsorcl2.__db_cache_size=25232932864 ovsorcl1.__db_cache_size=20937965568 ovsorcl2.__java_pool_size=134217728 ovsorcl1.__java_pool_size=134217728 ovsorcl2.__large_pool_size=13421772 8 ovsorcl1.__large_pool_size=13421772 8 ovsorcl2.__oracle_base='/u01/app/oracle' ovsorcl2.__pga_aggregate_target=21743271936 ovsorcl1.__pga_aggregate_target=26172456960 ovsorcl2.__sga_target=32480690176 ovsorcl1.__sga_target=28051505152 ovsorcl2.__shared_io_pool_size=0 ovsorcl1.__shared_io_pool_size=0 ovsorcl2.__shared_pool_size=6710886400 ovsorcl1.__shared_pool_size=6308233216 ovsorcl2.__streams_pool_size=0 ovsorcl1.__streams_pool_size=268435456 *._enable_NUMA_support=FALSE *._file_size_increase_increment=2143289344 *.audit_file_dest='/u01/app/oracle/admin/ovsorcl/adump' *.audit_trail='db' *.cluster_database=true *.cluster_interconnects='' *.compatible='11.2.0.2.0' *.control_files='+DA_SLCM61/ovsorcl/controlfile/current.286.807801199','+DBFS_DG/ovsorcl/controlfile/current.2 56.807801199' *.db_block_size=8192 *.db_create_file_dest='+DA_SLCM61' *.db_domain='' *.db_lost_write_protect='TYPICAL' *.db_name='ovsorcl' *.db_recovery_file_dest='+DBFS_DG' *.db_recovery_file_dest_size=4558159872 *.diagnostic_dest='/u01/app/oracle' *.dispatchers=' ' * . filesystemio_options='SETALL' *. global_names=TRUE ovsorcl2.instance_number=2 ovsorcl1.instance_number=1 *.memory_target=54129590272 *.open_cursors=1000 *.parallel_adaptive_multi_user=FALSE *.pga_aggregate_target=0 *.processes=10000 *.remote_listener='slcm61-scan1:1521' *.remote_login_passwordfile='exclusive' *.sec_case_sensitive_logon=FALSE *.sessions=2755 *.shared_servers=0 *.sql92_security=TRUE ovsorcl2.thread=2 ovsorcl1.thread=1 ovsorcl1.undo_tablespace='UNDOTBS1' ovsorcl2.undo_tablespace='UNDOTBS2' *.use_large_pages='TRUE'
The Oracle database can be configured in either a shared or dedicated connection mode, where:
Dedicated connection mode is the default for the Oracle database and works best on systems that have ample memory and require persistent connections.
Shared connection mode is ideal when processor and memory resources need to be constrained.
For the JD Edwards EnterpriseOne in-memory applications, the number of connections to the database is greatly increased relative to applications that are not in-memory. In some instances the number of connections could be as high as 10 times greater. As a result of this increase in connections, the Oracle database requires a larger footprint in processor and memory resources. Therefore, while shared connections can be used to place a limit on processor and memory consumption, it is likely that this connection mode will result in decreased performance.
There are a number of direct Oracle database tuning settings at the startup of the database that are not dynamic and which must be considered with the JD Edwards EnterpriseOne in-memory applications. As described below, these settings are the memory sizing of the PGA/SGA, processes, open cursors and the configuration of the redo and temp areas of the database.
PGA/SGA and Processes
Enabling the feature of Automatic Memory Management (AMM) requires the SGA and PGA together. It should be configured initially depending on the options of the Oracle installation and the amount of memory on the database server. Additionally, shared connections will add to the requirements necessary for the SGA, so following the recommendations in this section for Oracle database tuning in a shared connection environment in this regard is important.
Open_Cursors
The following script can be initiated to determine the amount of open cursor consumption by the JD Edwards EnterpriseOne in-memory applications:
-- Script to calculate open_cursor utilization column highest_open_cur format 999; column max_open_cur format a15; select max(a.value) as highest_open_cur, p.value as max_open_cur from v$sesstat a, v$statname b, v$parameter p where a.statistic# = b.statistic# and b.name = 'opened cursors current' and p.name= 'open_cursors' group by p.value;
In lab environments, relative to the normal JD Edwards EnterpriseOne applications, by design the in-memory applications consumed a much larger number of open cursors. It would not be uncommon for this number to be in the range of 10 to 20K.
Oracle Redo Logs/Temp
The JD Edwards EnterpriseOne in-memory applications demonstrate extensive use of insert, update, and delete activity since it relies heavily on work tables in the JD Edwards EnterpriseOne schema. As a result these large numbers of data manipulation language (DML) actions require larger and greater numbers of Redo logs. The redo logs store changes to the database for instance recovery and integrity of the database in case of failure.
The default number and size of the redo logs is small. Coupled with applications that require a greater size for these logs, the use of the defaults will likely cause the database server to checkpoint continuously. This puts a high load on the buffer cache and disk I/O subsystem. If there are too few redo logs, then the archive cannot keep up, and the database will must wait, degrading the overall performance of the JD Edwards EnterpriseOne application.
Running the EnterpriseOne in-memory applications may require a larger Redo logs. Also consider that the JD Edwards EnterpriseOne in-memory applications require larger temporary tablespace sizes to help improve sort performance.
Table and index partitioning was evaluated in lab environment for the Project Portfolio Management work tables as a way to increase the performance. The range partitioning of both the table and index was performed for the Project Portfolio Management work tables.
Range partitioning is useful when you have distinct ranges of data you want to store together, which is the case with the Project Portfolio Management application. Although implementing this practice would appear to be of great benefit, in practice the additional cost of this feature likely does not warrant the small benefit observed when table and index partitioning was enabled.