CSA: Memory and Performance Tracking (On-Premise Only)

ccppmop1581
Monitor system performance, manage physical memory allocations on each server in a cluster, and analyze the database servers for optimal database performance.
2
Monitor System Performance
You can track system performance using CSA. You can collect and store data on
Classic PPM
 servers until it is analyzed and moved to
Classic PPM
.
How Performance Tracking Sessions Work
  • When a session is started, calls to a
    Classic PPM
     server or servers from user workstations are tracked and recorded. Analysis is done on the data after the session is complete. The analysis results can be accessed from the performance tracking
    page that lists the individual sessions.
  • For each call, the analysis provides the response times in milliseconds and data volumes in bytes.
  • You can start a new session immediately, start it later on demand, or schedule it to start at a designated time.
  • You can define the duration of the session.
  • Only one session at a time can be started and in
    Collecting Data
    status.
  • All services in a cluster are included in the performance tracking session. During the session, you can abort the tracking on individual services.
  • Once a session starts, you cannot restart it, even if the session is stopped or has completed the analysis. You can use the Save As button on the session property page to save its settings to a new session name, then start the new session.
  • You can record and track data for individual users. Tracking a high number of users can affect performance in the system itself.
  • You can track data from levels 1 through 10. Each successive level provides information at a deeper level in the system. If you select to track performance at the most detailed level (10), performance for the system itself can be affected.
Create Performance Tracking Sessions
Follow these steps:
  1. Log in to CSA.
  2. Under Performance, click Performance Tracking.
  3. Click New.
  4. Complete the following fields:
    • Session Name
      Defines the name of the session.
    • Description
      Defines the description of the session. This information appears in the list of sessions on the performance tracking
      page.
    • Start Options
      Specifies when the session starts. If you select Scheduled, use the Date Picker to select the start date. Select the start time in hours and minutes.
      Values:
      Immediately, On Demand, or Scheduled.
    • Duration
      Specifies the length of time the session is to last. You can use the fields in combination to indicate the exact duration.
      Fields:
      Days, Hours, and Minutes
    • Tracking Level
      Select a number from 1 to 10 to indicate the level of detail you want to track. If you select the lowest level (1), only the name of the call is listed in the analysis data for a session. The data that is presented for the call listing (response times and data volume) are not affected by the level selected. If you want to view the call listing's data at a more detailed level, select a higher tracking level to collect and display more detail. For each additional tracking level selected, another level is added to the treeview available for each call. Tracking level 10 is equal to all available levels. The information that appears in the treeview for any specific call can vary according to the complexity of the call. A higher tracking level collects more performance detail but consumes more resources.
    • Track Individual Users
      Indicates if information is recorded about individual users and the actions they take in
      Classic PPM
      .
    • Analyze Data Immediately After Completion
      Indicates that the start the analysis of the data immediately after the session ends.
    • Response Time Percentile
      Defines the percentile for which the slowest response times are displayed. For example, if you indicate 90 percent, only the response times that fall below the 90th percentile are displayed. The 10 percent of responses that are faster than the 90th percentile are not processed.
  5. Save the changes.
Stop Performance Tracking Sessions
An administrator with the appropriate rights can stop a session manually at any time. You do not have to be the one who created or started a session to stop it.
Follow these steps:
  1. Log in to CSA.
  2. Open Performance, and click Performance Tracking.
  3. In the row that contains the session, click Stop.
Stop a Performance Tracking Session for a Service
An administrator with the appropriate rights can stop a performance tracking session manually at any time for a service without stopping the session.
Follow these steps:
  1. Log in to CSA.
  2. Open Performance, and click Performance Tracking.
  3. Click the name of the session.
  4. In the Services section, locate the service whose session you want to terminate, and click the
    Abort link located in the row.
View Session Results
You can view the results for a performance tracking session after the session is analyzed. Data response times and data volumes for each call to the server are listed. The amount of information that appears in the listing depends on the tracking level that you select. You can expand a call in the list to view additional information.
Follow these steps:
  1. Log in to CSA.
  2. Open Performance, and click Performance Tracking.
  3. In the row that contains the session, click View Results.
    The results page appears. The Overview tab shows the properties set for the session initially and the statistics for a selected service.
  4. If you have multiple services running, select the service whose results you want to view in the
    Service field.
  5. Click the Analysis tab.
Compare Sessions
You can compare the results of any two sessions in the list. Comparisons can be made for different sessions on the same service or on different services. The analysis of the first session is shown on the Analysis tab in the standard format. The comparison of the second session is shown as a percentage of increase or decrease immediately following each data statistic that is shown for the first session.
Follow these steps:
  1. Log in to CSA.
  2. Open Performance, and click Performance Tracking.
  3. In the list of sessions, select the two sessions you want to compare, and click Compare.
    The comparison page appears.
    The Sessions section of the page indicates which session is the base and which is the comparison. To select a service other than the default for a session, click the drop-down list in the Service
    column.
  4. Click Reverse Comparison to change the order of the base and comparison sessions.
  5. Use the list filter in the Performance Analysis Filter section to limit the data that displays in the list.
Manage Memory
It is important to allocate sufficient physical memory to each server in a
Classic PPM
 cluster. The amount of physical memory that is required depends on how the cluster is configured, what services are running, and how many users the cluster supports.
There must be more physical memory installed in the server than is actually used by
Classic PPM
. Do not rely on virtual memory because performance degrades due to the operating system needing to swap memory with disk, causing a significant slowdown of the system.
Monitor Memory Consumption and Performance
You can monitor the memory consumption on Unix for each process, or the server overall, with commands such as top or prstat.
In Microsoft Windows environments, use the Windows Task Manager. Navigate to the Processes
tab to see the memory consumption for each process or use the Performance
tab to see the memory configuration and consumption for the entire server.
For more information about using the Task Manager, see the Microsoft Windows Task Manager Help.
Maintain Sufficient Memory
As with any other application, allocate sufficient physical memory to each server in a
Classic PPM
 cluster. The amount of physical memory that is required depends on how the cluster is configured. For example, consider what services are running on which server and how large of an implementation must be supported. Follow this rule: There must be more physical memory installed in the server than is actually used by
Classic PPM
. Do not rely on virtual memory because performance degrades due to the operating system needing to swap memory with disk, causing a significant slowdown of the application.
  • Memory consumption on Unix for each process, or the server overall, can be checked with commands such as top or prstat.
  • In Microsoft Windows environments, use the Windows Task Manager. Navigate to the Process tab to see the memory consumption for each process or use the Performance tab to see the memory configuration and consumption for the entire server.
To modify the memory allocation for other services such as Oracle and Microsoft SQL Server, refer to the administration documentation for each product.
Before you change the memory settings, shut down the service and restart it after you modify the memory settings.
Follow these steps:
  1. Log in to CSA.
  2. Select the respective server using Cluster - Servers.
  3. Do one of the following:
    • To adjust the memory on the application server, select the Application tab.
    • To adjust the memory on the background server, select the Background tab.
  4. Enter the new memory allocation in the Java VM Parameters field, and save.
  5. Select the Services tab.
  6. Restart the app and bg services.
Restrict the Microsoft SQL Server Memory Consumption
Over time, Microsoft SQL Server consumes the entire available physical system memory, and more. As a result, operating system-level paging significantly slows down the database operations. As a best practice, you should restrict the amount of memory that is required for Microsoft SQL Server to allocate.
Depending on the version, you might be able to reserve about 200 MB of memory for the OS. Assume that Microsoft SQL Server is the only application running. Allocate 90 percent of the remaining system memory to Microsoft SQL Server. For example, your system has 2 GB of memory. After you allocate the OS memory, you have about 1.8 GB available. Allocate about 1.6 GB (90 percent of 1.8) to Microsoft SQL Server.
Follow these steps:
  1. Open the Microsoft SQL Server Enterprise Manager application, right-click the server, and select Properties.
  2. Select the Memory tab. Adjust the slider bar in the Dynamically Configure SQL Server Memory section to the computed maximum setting.
  3. Click OK to confirm the changes.
For more information about the Microsoft SQL Server system configuration, search the Microsoft TechNet website.
Adjust Memory Allocation
Before adjusting the memory that is allocated to
Classic PPM
 or a background service, first stop the service. After adjusting the memory, restart the service. To modify the memory allocation for other services such as Oracle and Microsoft SQL Server, see the respective administration documentation.
Follow these steps:
  1. Log in to CSA.
  2. Open Home, and click Servers.
  3. Click the name of the server for which you want to adjust memory.
  4. Click the Services tab.
  5. Select the check box next to any service you want to change, then click Stop.
  6. To change the memory settings for
    Classic PPM
    , follow these steps:
    1. Click the Properties tab.
    2. Click the Application sub tab.
    3. In the Java VM Parameters field, change the memory allocation, and save.
  7. To change memory settings for the background service, follow these steps:
    1. Click the Properties tab.
    2. Click the Background sub tab.
    3. In the Java VM Parameters field, change the memory allocation, and save.
  8. Click the Services tab.
  9. Select the check box next to any service you changed, and click Start.
Ensure Adequate Disk I/O Throughput
Classic PPM
 is a mixed environment with the following wide range of activities:
  • Online Transactional Processing (OLTP)
    Entering time sheets, entering financial transactions, collaborating on documents, and capacity planning.
  • Batch processing
    Post Transactions to Financials, Data Mart Extraction, and Import Financial Actuals.
  • Data analysis
    Reports, Queries, Graphs, and Grids
Most of these activities present a fair amount of read/write load on the database server. As a best practice, allow for maximum throughput of these operations. Configure your database server with a RAID 0+1 disk configuration. This configuration provides striping of disks and the appropriate failover mechanism for disk failures.
Optimize Database Performance
Use these techniques to analyze the database servers and ensure optimal database performance.
Analyze the Oracle Database Schema
You can use one of the following methods to analyze the database schema:
  • Use the Oracle Table Analyze job. To schedule this job, you must have the following permissions:
    • You must have access rights to run this job, or be part of the Report and Job Administrator group.
    • You must be part of the Job Users group to get access to the reports and jobs page.
      As a best practice, run this job at least once a week during a period of lower user activity, such as on a weekend night.
  • Use the admin db analyze command-line utility. Typically, there is no need to use this method over the job. This method performs the same analyze command that the scheduled job performs. However, during a
    Classic PPM
     hot fix or patch release application, it might be necessary to analyze the database without having
    Classic PPM
     up and running.
  • Direct and Asynchronous I/O
    I/O operations in UNIX and Linux systems typically go through the file system cache. Although this does not represent a problem in itself, this extra processing requires resources. Bypassing the file system cache reduces CPU requirements, and frees up the file system cache for other non-database file operations. Operations against raw devices automatically bypass the file system cache.
    When a synchronous I/O request is submitted to the operating system, the writing process blocks until the write is complete before continuing processing. With asynchronous I/O, processing continues while the I/O request is submitted and processed. This allows asynchronous I/O to bypass some of the performance bottlenecks that are associated with I/O operations.
    Oracle can take advantage of direct I/O and asynchronous I/O on supported platforms using the FILESYSTEMIO_OPTIONS parameter. Possible values are the following:
    • ASYNCH - Enabled asynchronous I/O where possible.
    • DIRECTIO- Enabled direct I/O where possible.
    • SETALL- Enabled both direct I/O and asynchronous I/O where possible.
    • NONE - Disabled both direct I/O and asynchronous I/O.
    Best Practice
    : SETALL
  • Analyze AWR report
    Generate an AWR or Statspack report from Oracle and analyze the report. Look for how PGA and SGA are sized and adjust their sizes as appropriate.
    Look at the number of redo log switches and the best practice is to have 2 to 3 redo log switches per hour.
Set CPU Speed to Improve Oracle 11g Performance
The optimizer in Oracle 11g uses both CPU and reads to determine the cost of a query. In addition, it uses system load characteristics to attempt to reduce the overall load. If the CPU speed setting is not set, the optimizer performs poorly. To set the CPU speed, run the gather system stats as follows:
execute dbms_stats.gather_system_stats('Start'); -- <some time delay while the database is under a typical workload> execute dbms_stats.gather_system_stats('Stop');
Set Additional Oracle 11g Parameters to Improve Performance
The following parameter has been shown to have a positive performance gain when running
Classic PPM
 on Oracle 11g. These parameters are meant to be optional
tuning
parameters and should be used to achieve adequate database performance.
CURSOR_SHARING=FORCE Oracle init parameter
By setting this parameter to FORCE, Oracle is essentially rewriting the executed queries to and replacing any literals with bind values. If the queries executed are the same (though values are different), only one shared query is created and is shareable and used for all sessions. This turns a hard parse into a soft parse. A soft parse will lock (or latch) the shared pool less than a hard parse and, thus, provide a performance gain. Watch the Oracle AWR for excessive query parsing to determine if this is a setting you should attempt to use.
Analyze the Microsoft SQL Server Database Schema
Similar to Oracle, Microsoft SQL Server needs table and index statistics to efficiently execute SQL statements. As a SQL Server DBA, you must create a SQL Server job for updating statistics and reindexing and run it on a regular basis.
Enable the Datamart Parallel Option
The datamart extraction is I/O intensive as it needs to detect incremental changes in the transactional tables and then write them to the Datamart reporting tables. In a typical customer environment, there is sufficient CPU power available to launch multiple parallel processes that access the disks in parallel, thereby minimizing the overall Datamart runtimes.
Ensure Optimal Database Server File Layout
Both Oracle and Microsoft SQL Server perform significantly better when their table files are separated from the index files. Separate the log files from the other two. Use these steps to ensure optimal database server file layout:
  1. Place the Oracle table tablespace data files on separate disks from the index tablespace data files.
  2. Create separate file groups for tables and indexes and place them on different disks.
  3. Migrate the indexes to a new index file group.
Oracle Tablespace Datafiles
We recommend the use of Automatic Segment Space Management (ASSM) with autoextend on to improve and automate the space management within segments. ASSM offers manageability and performance advantages over the Freelist-based space management. For more information about ASSM, see your Oracle documentation.
SQL Server Tablespace Datafiles
Create separate file groups for tables and indexes and place them on different disks. Place the transaction logs on another set of disks.
Migrate
Classic PPM
 Indexes to New Index File Group
Follow these steps:
  1. Open Microsoft SQL Server Enterprise Manager.
  2. Back up the
    Classic PPM
     database.
  3. Right-click on the
    Classic PPM
     database, and click Properties.
  4. Click the Data Files tab.
  5. Click in to the new row below the
    Classic PPM
    _Data file name and enter the following information:
    • Classic PPM_Idx as the file name.
    • The correct second disk location for the file named Classic PPM_Idx_Data.NDF.
    • A value for Space Allocated (the value should be at least 50 percent of the primary data file allocation).
    • IDX next to Filegroup Name.
    The name that you use must not be a Microsoft SQL Server keyword such as INDEX.
    Do not change any of the other default settings.
  6. To create the new data file and the new filegroup, when all values are entered, click OK.
  7. To verify that the data file and filegroup are created, right-click the
    Classic PPM
     database.
  8. Click the Data Files tab and verify that the new data file is created. Verify that all the values you entered are present and correct.
  9. Click the Filegroups tab and verify that the new filegroup entered exists. The default filegroup should be PRIMARY.
  10. Connect to the
    Classic PPM
     database using SQL Query Analyzer as the niku db user.
  11. Execute the stored procedure CMN_MIGRATE_MSSQL_INDEXES_SP with the new filegroup as a parameter:
    EXECUTE CMN_MIGRATE_MSSQL_INDEXES_SP 'IDX'
    When this stored procedure is run, all the indexes from the PRIMARY filegroup are moved to the IDX filegroup on the second disk.
    Executing this stored procedure might take a significant amount of time depending on the size of the database.