To see how much RAM is allocated by the database go to the Parameters > Parameters Overview tab. In the Param name field, type SGA and search for SGA_MAX_SIZE on the list. The parameter value specifies the memory assigned to the database.

Then go to Memory > SGA tab. The screen shows how much RAM is used, how much is free (SGA TOTAL). You also see the size of the individual SGA memory components.

To check how much RAM the server has, go to the Performance > OS Stat tab. In the table below, select the Physical Memory column. The graph shows the amount of RAM on the server.

To check the size and increment of the database, go to the Space monitor > Database size tab. The screen shows the total, free, and occupied space in the database for a given period. In addition, the DATABASE GROWTH section shows the database size increase in the last day, week, and month.

Information about the size of the database, tablespace, data files are in the Space monitortab. Functionality Last Snap Tablespace Size shows graphically and in the table the most recent data on the size of each tablespace in the database.

The same information can be obtained from the Dashboard screen. You should select the database, then Database space.

To see the increase in the tables on selected dates, check the Space Monitor > Tablespace history tab. It is possible to set the date range and to select a given tablespace.

To check the settings of individual database parameters, go to the Parameters tab. The screen displays all parameters in the database along with the value set. When you select the parameter below, you will see the change history.

Parameters > Parameters History screen shows a list of all parameter changes for a specified period. It is possible to select a parameter by entering its name or value.

To find queries that generate the largest I/O load, go to the Performance > SQL 3D tab

When looking for reads – set the Draw bar on Disk Reads to see the most burdensome queries for reads from disk devices.

When looking for writes – expand the Show additional filters bar and select Log generating statements to display the most burdensome queries for writes.

To find out which session triggers UNDO GLOBAL problem, go to the Sessions > Undo usage sessions tab. Then sort the Used records column by descending order. A session with the highest number of records being processed is causing the problem.

To analyze the problem within a given period, go to the Session > Undo history tab and set the date range. When you select a point in time, select the Undo tab below and sort the used records column in descending order. A session with the highest number of records being processed is causing problems.

To find out which session makes the most changes to the database, go to the Sessions > Undo usage sessions tab. Then sort the Used records column by descending order. The session with the most processed records makes the most changes to the database.

To see what generates shared_pool latches go to Performance > Latches > Latch library cache tab. In the SHARED POOL STATEMENTS section, you can see a list of queries with literals and the amount of shared pool memory used. The number of query versions is also included. Literals that are most utilizing the memory are the cause of shared_pool latches.

To analyze the database load over the past 2 years, go to the Performance > Load trendstab. Then set the date range for the last 24 months and set the grouping by month. By default, you see the Elapsed time graph, which shows the duration of all database queries for the selected period in the selected time grouping (15 minutes, day, month).

To check who is triggering the query, go to the Sessions > Session / Undo history tab. When you type the query ID in the Using Query Hash field below, you will see a list of all sessions that run the query within the selected period.

To check the performance statistics of a given query, go to the Performance > SQL Detailstab. When you enter the query ID or select it from the list, the SQL STATISTICS section displays the performance statistics for the selected period. It is possible to group results by month, day, hour and snap (15 minutes).

To check the execution plan, go to the Performance > SQL Details tab. When you enter a query ID or select it from the list at the bottom, you will see an execution plan on the Explain plan tab.

Sometimes, the query uses more than one execution plan. Checking the Compare Plansbox shows the second execution plan. This way you can list the differences and find out why the execution is slower on given execution plan.

You can also check the execution plan for an active session. To do so, go to the Sessions tab. When you select the session below, the query text and execution plan will appear.

To find out how many queries are using this execution plan, select the command in the SQL Details tab, and then click Add to SQL plan.

Then go to the SQL Plan tab and select the previously added execution plan from the list on the left. The overall performance statistics for the execution plan appear, and when you select the Statements using plan tab, you can see all the queries that use given execution plan.

The type of memory configuration can be found in the Memory > SGA tab, in the SGA Usage field.

You can find individual components of memory buffers in the Memory > SGA tab in the CURRENT MEMORY UTILIZATION section. When you hover over a segment on the graph, it will show its value.

To check the history of SGA settings, go to the Memory > SGA History tab. You see the occupancy of the individual SGA elements for the selected period.

To check which session most utilizes the sorting tablespace, go to the Sessions > Sort usage sessions tab. You can see the tablespace for sorting and the list of sessions that use this space. Then, to display the searched sessions, you should sort Space Used column in the descending order.

To check whether there are literals in the database, go to Performance > Latches > Latch library cache tab. In the SHARED POOL STATEMENTS section, you can see a list of queries with literals and the amount of shared pool memory used.

Literals are a problem when in the database appear SHARED POOL type latches. To verify the occurrence of the latches, go to the Waits > Analyze tab, then set the date range and grouping. In the WAIT STATISTICS section, look for the wait latch: shared pool. If the waiting time is at a negligible level compared to the duration of all processes – there is no problem with literals.

There are several performance issues that do not have a cause in the database:

• Network problems
• Slow processing on the application side
• Slow operation of the disk array

To verify that there is a problem in the database with the long wait for a network response, go to the Waits > Overviewtab, then click on the selected point on the graph. If there is a high level of TCP Socket (KGAS) wait in the database – there is a problem on the network. The most often occurring waits are shown below the chart.

The problem may also occur on the disk array side. To verify this, go to the I/O Stats > I/O Analyze tab, then select Writes/Reads and Write time/Read time respectively depending on the operation being performed. If you do not see the increase in reads/writes, and the time of these operations increases considerably, then the problem lies on the hardware side or other processes on the server from outside the database put the load on the disk array.

To verify the most burdensome queries, go to the Performance > SQL 3D tab. Then in the Draw bar field, select the category in which the query is putting the load on the database, set the date and group the result by day, month or snap (15 minutes). Below on the 3D graph, you will see the most burdensome queries. To analyze one of them, click on the query, then View SQL detail, which will open the SQL details tab.

On the Performance > SQL details tab we will see the query text, performance statistics for the given period, and query execution plan. It is possible to group performance statistics after a snap (15 minutes), hour, day, and month.

In the Performance > SQL Details tab, selecting a query on the bottom screen shows the execution plan. Click on Show plan objects:

All objects used in the query are displayed on the screen. To check which indexes are on the table, click on the table name and a list of indexes will appear on the right. Clicking on the index at the bottom will show the columns used in that index.

To find queries based on the used objects, go to the Performance > SQL Details tab, and then click the Find SQL button located to the right:

Then go to the Statements by text tab and enter the object name. A list of the queries that use the indicated object will be displayed below, along with the basic performance statistics for the period.

To find which queries have changed the execution plan, go to the Performance > SQL Detail tab, and then click the Find SQL button to the right:

Then select the Flip-Flop Statements tab. After the refresh, a list of commands that changed the execution plan for the period will appear.

To compare the performance of a few days, go to the Performance > Compare > Compare Days tab. Then choose the days to compare and add them to the list. The chart contains performance data from each added day grouped after a snap or an hour. The comparison can be made for the duration of all queries, CPU time, executions, rows processed, fetches, disk reads, buffer gets.

To compare the time periods, go to the Performance > Compare > Compare Periods tab. Then select two interesting periods. The chart contains performance data for each period. The comparison can be made for the duration of all queries, CPU time, executions, rows processed, fetches, disk reads, buffer gets.

To find the query generating the latch: cache buffer chains, go to the Performance > Latches > Buffer Latches tab. When you select a point on the graph, below you will see a list of queries that read the most buffers. Please check which query has the largest Buffer Utilization. It will be responsible for the appearance of latch: cache buffer chains.

To find out who caused the performance problem at 2 am, go to the Performance > Database load tab. Then select the 2 am time point at the graph. If you see an increase in database server utilization, please check on the list below which query was responsible for this.

Then, to verify who run this command, go to the Sessions > Session / Undo history tab. Then enter the query ID and search for who has run it around 2:00.

First, go to the Performance > Waits tab. When you click on a point on the graph, look in the wait section for the wait with “enq” in the name. If there is one, go to Locks > Locks history.

When you go to the Locks > Locks History tab, the graph shows the session timeout and the number of sessions in the lock for a given period. Clicking on a point will show you a list of blocking and blocked sessions.

In the blocking session there is an arrow with the possibility to collapse the remaining sessions below. Locked sessions can be collapsed into a lock session.

First, you need to check if there are waits connected with write operations, i.e. log file sync, db file parallel write, parallel write log, free buffer busy. To do so, go to the Performance > Waits > Analyze tab. Then find and select indicated waits.

Then go to the I/O Stats > I/O Analyze screen. If you set the same date range as before for disk waits, you should select the Writes, Write time column. If write time increases at the wait growth point, and the number of records is not increasing, the problem lies with the disk array. If the number of writes grows, you need to find the command that writes the most data to the array in that period.

Of course, if the write time of a single 8K block of data is greater than 0.004 seconds then this indicates slow I/O support and the problem should be looked beyond the database. For SSD disk array the write time of a single 8K block is 0.003 seconds.

To find the query that writes the most data to the disk array, go to the Performance > SQL 3D tab, expand the Show additional filters bar, and select Log generating statements. The queries displayed are the most burdensome regarding writing to a disk array.

To see if there are problems with reads, go to the I/O Stats > I/O Analyze tab. Then set the date range, granularity, and select from the table below the Reads and Read time columns. If the reading time increases and the number of readings is not increasing, there is a problem with reading on the disk array. However, if the number of readings is also increasing, you should find the command that reads the most data during this period and therefore causes problems with reading.

Of course, if the read time of a single 8K block of data is greater than 0.003 seconds then this indicates slow I/O support and the problem should be looked beyond the database. For SSD disk array the read time of a single 8K block is 0.0002 seconds.

To find out which query reads the most data from the disk array go to the Performance > SQL 3D tab, set the Draw bar to Disk Reads. The queries displayed are the most burdensome regarding reading from the disk array.

To find out what percentage of the database load is caused by given query, select the Performance > SQL Details tab. After entering the query ID, we will see its content, the execution statistics for the given period, and the execution plan. On the lower tab, select Graph, and select Database load for Elapsed Time. The graph shows the duration of the command and its percentage of all queries in the database.

To see how much load to the database is caused by several selected queries, go to the Performance > SQL Analyze tab. The graph shows the duration of all the commands selected below against all queries in the database.

To substitute literals for variables, first, go to the Performance > SQL Details tab. If you select a query, check the Online value field. Then, at the very bottom of the execution plan, values will be substituted for variables along with their data type. Then match the values to the variables in the text of the query.

For information about the amount of RAM allocated, go to the Memory> Memory usage tab. You can see how much RAM is used by the instances, and how much free memory the server has (TOTAL SERVER MEMORY) and in what proportion of memory is divided (Memory Utilization).

To check the size of the instance, go to the Space monitor> Current space tab. The screen shows the total, free, and occupied space in the instance.

To check the size of individual databases, you should group the result by the database:

To check the increment of an instance, go to the Space monitor > History tab. The screen shows the total, free and occupied space in the instance for the selected period. To check the increment for each database, you should group the result by the database.

To check the settings of individual parameters, go to Parameters > Instance Parameters > Server Configuration Parameters Overview. The screen displays all parameters in the database along with the set value. When you select the parameter below, you will see the change history.

Parameters > Parameters History screen shows a list of all parameter changes for a specified period. It is possible to select a parameter by entering its name or value.

To check the settings of individual database parameters, go to the Parameters > Database Parameters > Database Parameters Overview tab. The screen displays all parameters in the database along with the set value. When you select the parameter below, you will see the changes history.

Parameters> Database Parameters > Database Parameters History screen shows a list of all parameter changes for a specified period. It is possible to select a parameter by entering its name or value.

To find queries that generate the largest I/O load, go to the Performance > SQL 3D tab. Set the Draw bar on Disk Readsto see the most burdensome queries in regards to reads from the disk array.

To find out which session makes the most changes to the instance, go to the Sessions > Log usage sessions tab. Then sort the Log record count column in descending order. The session with the highest number makes the most changes.

To find the commands responsible for the shared_pool latch, go to the Performance > SQL 3D tab. Then set the Draw barto Buffer Writes to see the queries that write the most data to the memory.

To analyze the instance load over the past 2 years, go to the Performance > Load trends tab. Then set the date range for the last 24 months and set the grouping by month. By default, you see the Elapsed time graph, which shows the duration of all queries on the instance for the selected period.

To see in which database queries generate the greatest load, go to the Performance > SQL Analyze tab. By clicking on the Database Load tab and sorting the table in the descending order by the Elapsed Time column, you will see a list of the most heavily loaded databases.

The second way to determine in which database queries generate the greatest load is to go to the Performance > Instance load tab. Then click All databases in the upper right corner. You will see a table showing the share of each database load for the selected period.

To check who is triggering the query, go to the Sessions > Active Sessions / Log usage sessions history tab. When you type the query ID in the Using Query Hash field below, you will see a list of all sessions that run the query within the selected period.

To check the performance statistics of a given query, go to the Performance> SQL Details tab. When you enter the query id or select it from the list, the SQL STATISTICS section displays the performance statistics for the selected period. It is possible to group results by month, day, hour and snap (15 minutes).

To check the execution plan, go to the Performance > SQL Details tab. When you enter a query ID or select it from the list at the bottom, you will see an execution plan on the Explain plan tab.

Sometimes, the query uses more than one execution plan. Checking the Compare Plans box shows the second execution plan. This way you can list the differences and find out why the execution is slower on given execution plan.

You can also check the execution plan for an active session. To do so, go to the Sessions tab. When you select the session below, the query text and execution plan will appear.

To see how much memory is used by the instances, go to the Memory> Memory usage tab. On the right side of the screen there is a chart on which you can see:

• the amount of available RAM and its usage by all processes running on the server (bar on the graph below in blue box)
• amount of memory used by SQL instances (bar on the graph below in green box)

To verify that the SQL instance uses 100% of the allocated memory, go to the Memory > Memory usage tab. Then compare Max Server memory with the amount of memory used by the instance. In addition, you should verify that the memory areas (Memory Utilization for last snapshot section) sum to the total memory size – then we know that SQL uses allocated memory at 100%.

You can find individual memory buffers in the Memory > Memory usage section of the MEMORY UTILIZATION section. When you hover over a segment on the graph, it will show its value.

To check the memory settings history, go to the Memory > Memory usage history tab. You can see the memory utilization for the selected period.

To check which session to the greatest extent uses temporary space tempdb go to the Sessions > Temp usage sessions tab. You see the occupied tempdb space and the list of sessions that use this space. Then, to display the searched sessions, the Total Space Used column should be sorted in descending order.

There 2 main performance issues that do not have their cause in the SQL instance:

• Processing on the side of the user’s application
• Slow operation of the disk array

The cause of the problem often lies in the long waiting time on the application side. In the effect, locks occur in the database. Session blocking on the instance side is inactive and doing nothing, waiting for the commit or response from the application. To analyze this problem, go to the Locks > Locks history tab. If the graph shows long-term locking and the lock session is always SLEEPING, then this is an issue with a long wait for the response from the application.

The problem may also be on the disk array side. To do this, go to the I/O Stats > I/O Analyze tab, then select Writes/Reads and Write time/Read time respectively, depending on the operation being analyzed. If you do not see the increase in reads/writes, and the time of these operations is much longer, then the problem lies on the hardware side or other processes on the server from outside the database put too much load on the disk array.

To verify the most burdensome queries, go to the Performance > SQL 3D tab. Then in the Draw bar field, select the category in which the query is putting the load on the instance, set the date and group the result by day, month or snap (15 minutes). Below on the 3D graph you will see the most burdensome queries. To analyze one of them, click on the query, then View SQL detail, which will open the SQL details tab.

On the Performance > SQL details tab we will see the query text, performance statistics for the given period, and query execution plan. It is possible to group performance statistics after a snap (15 minutes), hour, day, and month.

On the Performance > SQL Details tab, selecting a query on the bottom screen shows the execution plan. Click on Show plan objects:

All objects used in the query are displayed on the screen. To check which indexes are on the table, click on the table name and a list of indexes will appear on the right. The Index columns are the columns on which the index was created. Clicking on it at the bottom will give you detailed information about the columns.

To find queries based on the used objects, go to the Performance > SQL Details tab, and then click the Find SQL button located to the right:

Then go to the Statements by text tab and enter the table name. A list of the queries that use the indicated table will be displayed below, along with the basic performance statistics for the period.

To find which queries have changed the execution plan, go to the Performance > SQL Detail tab, and then click the Find SQL button on the right:

Then select the Flip-Flop Statements tab. After the refresh, a list of commands that changed the execution plan for the period will appear.

To compare the performance of a few days, go to the Performance > Compare > Compare Days tab. Then choose the days to compare and add them to the list. The chart contains performance data from each added day grouped after a snap or an hour.

To compare the time periods, go to the Performance > Compare> Compare Periods tab. Then select two interesting periods. The chart contains performance data for each period.

To find out who caused the performance problem at 2am, go to the Performance > Instance load tab. Then select the 2 am time point at the graph. If you see an increase in instance server utilization, please check on the list below which query was responsible for this.

Then, to verify who run this command, go to the Sessions > Active sessions / Log usage sessions history tab. Then enter the query ID and search for who has run it around 2:00.

First, go to the Performance> Waits tab. When you click on a point on the graph, look in the wait section for the wait with “LCK” in the name. If there is one, follow further instructions.

When you go to the Locks > Locks History tab, the graph shows the session timeout and the number of sessions in the lock for a given period. Clicking on a point will show you a list of blocking and blocked sessions.

Next to the blocking session, there is an arrow with the possibility to collapse and expand the locked sessions below. The problem lies with the instance when the lock session is active and executes the command in the database. Conversely, when the blocking session is sleeping (sleeping status), the problem lies with the application.

First, you need to check of there are waits connected with write operations, i.e. writelog. To do so, go to the Performance > Waits > Analyze tab. Then find and select indicated wait.

Then go to the I/O Stats > I/O Analyze screen. When you set the same date range as before for disk waits, you should select the Writes & Write time columns. If write time increases at the wait growth point, and the number of records is not increasing, the problem lies with the disk array. If the number of writes grows, you need to find the command that writes the most data to the array in that period.

To find the query that writes the most data to the array, go to the Performance > SQL 3D tab. Then set the Draw bar to Buffer Writes. The queries displayed are the most burdensome when it comes to writing to a disk array.

First, you should check whether there are waits connected with disk reads i.e. pageiolatch_sh. To do so, go to the Performance > Waits > Analyze tab. Look for wait pageiolatch_sh

Then go to the I/O Stats > I/O Analyze screen. We set the same date range as before for the pageiolatch_sh wait. If read time is also increasing at the growth point of the wait, at the same time the number of readings does not increase – the problem lies with the disk array.

To find out what percentage of the instance load is caused by given query, select the Performance > SQL Details tab. After entering the query ID, we will see its content, the execution statistics for the given period, and the execution plan. On the lower tab, select Graph, and select Instance load for Elapsed Time. The graph shows the duration of the command and its percentage of all queries in the instance.

To see how much load to the instance is caused by several selected queries, go to the Performance > SQL Analyze tab. The graph shows the duration of all the commands selected below against all queries in the instance.