WIXLIB

Top Performance Tuning Tips for OBIEEPart IIBharath TeralaSravan Daggupati Copyright 2012. Apps Associates LLC.1

Value Delivery – Core to Our Mission E-Business SuiteImplementation &Managed Services *OBIEE, Pre-Built BIAnalytics Hyperion EPM Middleware,Integration Infrastructure Services Subject MatterExperts Best PracticeMethodology High Value ROI Local / GlobalService Delivery* Selected by Oracle as BI Pillar PartnerBostonNew YorkChicagoAtlantaGermanyNetherlandsIndia Copyright 2012. Apps Associates LLC.Middle East2

AgendaOBIEE ArchitectureSingle HostMultiple HostRecommended PatchesPerformance Tuning ComponentsOracle Weblogic ServerOracle BI ServerOracle BI Presentation ServerData warehouseSummary Copyright 2012. Apps Associates LLC.3

Architecture Review Copyright 2012. Apps Associates LLC.4

OBIEE ArchitectureLogical Architecture of Enterprise Install on SingleHost Copyright 2012. Apps Associates LLC.5

OBIEE Architecture IILogical Architecture of Enterprise Install on MultipleHosts Copyright 2012. Apps Associates LLC.6

OBIEE with BIAPPSClientWeb ServerPresentationServicesRepository andWarehouse DatabaseETL ServerReturnAnalyticsResultsCatalogDAC ClientManage / ConfigureDACRepositoryRead/WriteDAC ServerOracle abase / EBSSource DBWarehouseDataMonitor ETL/Design ETLWrite Copyright 2012. Apps Associates LLC.InformaticaClient7

PatchesOBIEE 11.1.1.5Patch 13611078: TRACKING BUG FOR 11.1.1.5.0 BP2PATCHSET (BP1 BUG 13562882 NEW BUG FIXES)12821662:SEVERE PERFORMANCE DEGRADATION WITH PIVOT TABLEPROMPT OVER OLAP SOURCE12800814:PSR:PERF:BI THERE ARE SOME MEMORY LEAKS IN SAS(OBIS)12739309:PSR:PERF:BI OBIS RESPONSE TIME GOES UP TO 500SECONDS WHEN RUNNING SCOTIA RPD12717149:PERFORMANCE ISSUE IN VALUE HIERARCHY DRILL ISSUE - WITHESSBASE ALIAS COLUMNS12701483:BAD PERFORMANCE IN A PIVOT TABLE WITH ESSBASE ANDUNCHECKUSE UNQUALIFIED MEMBER12399899:PERFORMANCE IMPROVEMENT - REMOVE CENTER QUERIES FORPAGE SLICES NOT DISPLAYED11924932:PERFORMANCE ISSUES IN 11G11823765:SIGNIFCANT PERFORMANCE DIFFERENCE BETWEEN PIVOT ANDTABULAR VIEWS Copyright 2012. Apps Associates LLC.8

Patches Contd.OBIEE 11.1.1.6Patch 13932572: Patch 11.1.1.6.2 Oracle BusinessIntelligence InstallerNo direct performance fixesAbout 20 high priority bugs are fixedThis patch is highly recommended for all the customers(except Exalytics customers) who are using OracleBusiness Intelligence Enterprise Edition 11.1.1.6.0 and11.1.1.6.1 Copyright 2012. Apps Associates LLC.9

Performance Tuning Copyright 2012. Apps Associates LLC.10

Performance TuningComponents InvolvedWeblogic ServerBI ServerPresentation ServerData warehouseMonitoring ToolsEnterprise Manager Metric PalettePerformance Monitor - http:// server:port /analytics/saw.dll?PerfmonServermpstat - Report processors related statisticsvmstat - vmstat reports virtual memory statistics of process virtualmemory, disk, trap, and CPU activityiostat: Reports terminal and disk I/O activity and CPU utilization Copyright 2012. Apps Associates LLC.11

Oracle Weblogic ServerTune connection backlog bufferingConnections are dropped or refused at the client, and no othererror messages are on the server, the Accept Backlog valuemight be set too low.Increase value by 25% of default value each time to evaluateTune Statement CacheWhen using Oracle Database “Oracle JDBC Driver” Copyright 2012. Apps Associates LLC.12

Oracle BI ServerJVM - BI ServerTune Heap Size Copyright 2012. Apps Associates LLC.13

Oracle BI ServerDisallow RPD UpdatesImprove Oracle BI Server performance, because in this mode,the Oracle BI Server does not need to handle lock controlSet the User Session Log-Off PeriodYou can override the time to elapse, in minutes, before a user isautomatically logged off by setting the User Session ExpiryConfiguration Options for Data in Tables andPivot TablesMaximum Number of Rows to Download to Excel optionMaximum Number of Rows Per Page to Include in Email optionMaximum Number of Rows Processed to Render A Table View Copyright 2012. Apps Associates LLC.14

BI Server ComponentsSet the Maximum Number of RowsProcessed to Render a TableOverride the maximum number of rows that can be fetchedand processed from the Oracle BI Server for rendering atable.Reducing the number of rows in a table can significantlyimprove performance by reducing the system resources thatcan be consumed by a given user session.Query CacheSignificant Performance benefit improving the query responsetime.Important consideration to purge the cache Copyright 2012. Apps Associates LLC.15

Presentation Catalog ManagementCatalog resides on a NFS Share in aclusterProper guidelines necessary for file systemRead-to-write ratio is typically at least 100 to 1.Use care when considering storing arbitrary"Properties" in “.atr” files.Presentation Services additionally caches all “.atr” filesinternally.Tune “MaxAgeMinutes” element in theinstanceconfig.xml – default for cluster 5 minutes Copyright 2012. Apps Associates LLC.16

Presentation Catalog Management.Handling Catalog FileUNIX Platforms:UNIX kernels must commonly be configured to allowmore than 4000 subdirectories per directoryWindows Platforms:FAT is not supported, and NTFS is required.Performance on Windows platforms degradesnoticeably when more than 8000 files exist in asingle directoryStrongly recommended that you not store morethan 4000 catalog objects in a single directorySet HashUserHomeDirectories element to 2 fromits default value of 0 Copyright 2012. Apps Associates LLC.17

Presentation Server ComponentsUnion requestsGenerates more temp files, more memoryFaster disks for temp files along with modelchanges#Columns in Criteria vs. Pivot ViewAdditional grouping based on dimensions incriteriaGuided NavigationsAdditional load on BI Resources Copyright 2012. Apps Associates LLC.18

Presentation Server ComponentsRANK / FiltersPerformed on Temp files, network trafficbetween OBIPS, OBIS, Data warehouse, Top NanalysisObject Permissions By Groups/RolesDesigning better federated requests.More temp files, better synchronizing confirmeddimensions in each data source to reduce datafederation. Copyright 2012. Apps Associates LLC.19

Presentation Server ComponentsSlow or delayedLog inNavigation – my dashboard / dropdown listCatalog Search timeSlow or delayedClean Invalid Permissions in the Catalogruncat -cmd forgetAccounts -username xxxxx -cleanup -offline“Catalog PATH“Zero Bytes, corresponding .atr files;7-zip, refresh GUIDs, same security store in each envSchedule Cleanup as repetitive job. Copyright 2012. Apps Associates LLC.20

BI Server ComponentsQuery Result CacheDATA STORAGE PATHSMultiple directories on different drives with I/O ControlMultiple paths for value great than 4 GBMAX ROWS PER CACHE ENTRY,MAX CACHE ENTRY SIZEMAX CACHE ENTRIES,POPULATE AGGREGATE ROLLUPSUSE ADVANCED HIT DETECTIONDISABLE SUBREQUEST CACHINGCACHE POLL SECONDS Copyright 2012. Apps Associates LLC.21

BI Server ComponentsOther NQSConfig.INI ParametersCASE SENSITIVE CHARACTER COMPARISONORDER BY, GROUP BY, DISTINCT, JOIN,COMPARISIONS (USER GROUP,USER, user group,user)WORK DIRECTORY PATHSVIRTUAL TABLE SIZE 128KB – 256 KB ( Windows 64KB)MAX SESSION LIMIT - #sessions to BI ServerSERVER THREAD RANGE - #queries active in BI ServerCONNECTION POOL - #threads to process physical SQL Copyright 2012. Apps Associates LLC.22

BI Server ComponentsMinimize Session VariablesUsage Tracking (S NQ ACCT)Row Count vs. Cumulative Database RowsCache Entries TrendPeak Usage TrendAggregation StrategyAggregation Persistence WizardAggregate Facts / SnapshotsSummary Advisor (For Exalytics) Copyright 2012. Apps Associates LLC.23

Summary Advisor- ExalyticsExalytics:OBIEE 11.1.1.6Times TenEssbaseMemory1 TB RAM, 1033 MHzCompute4 Intel Xeon E7-4870, 40 corestotalNetworking40 Gbps InfiniBand – 2 ports10 Gbps Ethernet – 2 ports1 Gbps Ethernet – 4 portsStorage3.6 TB HDD CapacitySummary Advisor:-Slow data sources, facts,grainsWorkload distribution withoptimal data martAny size DWStats Collector based onUsage Tracking Copyright 2012. Apps Associates LLC.24

Data WarehouseGather StatisticsYou should consider switching to ‘FOR ALL COLUMNSSIZE AUTO’ syntax inDBMS STATS.GATHER TABLE STATS call in DAC:Navigate to your DAC HOME /CustomSQLs andopen customsql.xml file for editing.Replace ‘FOR INDEXED COLUMNS’ with ‘FOR ALLCOLUMNS SIZE AUTO’ inDBMS STATS.GATHER TABLE STATS call in SqlQuery name "ORACLE ANALYZE TABLE“STORED PROCEDURE "TRUE“ section.Save the changes. Copyright 2012. Apps Associates LLC.25

Data WarehouseTune Init.ora ParametersMEMORY TARGETPGA AGGREGATE TARGETPARALLEL MAX SERVERSRefer and Set BIAPPS Initialization ParametersPartitioningPartitioning allows a table, index or index-organized table to besubdivided into smaller pieces.Partitioning for manageabilityPartitioning for easier data accessPartitioning for join performanceReduce the Initial and Incremental ETL TimeOptimizer Partition Elimination Logic Copyright 2012. Apps Associates LLC.26

Data WarehouseMaterialized ViewsQUERY REWRITEPre-Aggregate summary views improves significantly enduser query performance Copyright 2012. Apps Associates LLC.27

Questions & 9:00 AMImplementing Oracle BI Apps for Multiple ERP systemsASatish Rapolu09:00 AMPublishing Financial Reports Using R12 Report ManagerEBen Berlangieri10:00 AMTop Performance Tuning Tips for OBIEE Part IIABharath Terala11:00 AMLessons Learned During Oracle Business Intelligence11g UpgradeASanthosh Chetla

10:00 AM Top Performance Tuning Tips for OBIEE Part II A Bharath Terala 11:00 AM Lessons Learned During Oracle Business Intelligence 11g Upgrade A Santhosh Chetla Questions & Suggestions Bharath.Terala@appsassociates.com Sravan.Daggupati@appsassociates.com