Oracle AI Database Performance Tuning Guide

https://docs.oracle.com/en/database/oracle/oracle-database/26/tgdba/
Admin
Oracle AI Database Performance Tuning Guide provides instructions and best practices for database...
Tokens:85,421
Snippets:1,370
Trust Score:9.1
Update:2 months ago
Show doc for...
Tokens:
Raw
### Example Query Output

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/tgdba/tuning-shared-pool-and-large-pool.html

Sample output format for the session memory queries.

```text
TOTAL MEMORY FOR ALL SESSIONS
-----------------------------
157125 BYTES
```

```text
TOTAL MAX MEM FOR ALL SESSIONS
------------------------------
417381 BYTES
```

--------------------------------

### Example Output for Cursor Execution Plan Query

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/tgdba/tuning-program-global-area.html

Sample output format for the cursor execution plan query.

```text
OPERATION    OPTIONS  NAME     input(MB) LAST_MEM OPTIMAL_ME ONEPASS_ME O/1/M 
------------ -------- -------- --------- -------- ---------- ---------- ------
SELECT STATE                                                                  
HASH         GROUP BY               4582        8         16         16 16/0/0
HASH JOIN    SEMI                   4582     5976       5194       2187 16/0/0
TABLE ACCESS FULL     ORDERS          51                                      
TABLE ACCESS FUL      LINEITEM      1000
```

--------------------------------

### Example Output for V$INSTANCE_RECOVERY

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/tgdba/instance-tuning-using-performance-views.html

Sample output showing the relationship between target and estimated MTTR values.

```text
TARGET_MTTR ESTIMATED_MTTR 
18          15             
```

```text
TARGET_MTTR ESTIMATED_MTTR 
18          30             
```

```text
TARGET_MTTR ESTIMATED_MTTR 
18          25             
```

--------------------------------

### Example Output of V$LIBRARYCACHE Query

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/tgdba/tuning-shared-pool-and-large-pool.html

Sample output showing library cache activity metrics for various namespaces.

```text
NAMESPACE             PINS    PINHITS    RELOADS INVALIDATIONS
---------------
---------- ---------- ---------- -------------
BODY                  8870       8819          0             0
CLUSTER                393        380          0             0
INDEX                   29          0          0             0
OBJECT                   0          0          0             0
PIPE                 55265      55263          0             0
SQL AREA          21536413   21520516      11204             2
TABLE/PROCEDURE   10775684   10774401          0             0
TRIGGER               1852       1844          0             0
```

--------------------------------

### Example Output for Cache Count Query

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/tgdba/tuning-shared-pool-and-large-pool.html

Sample output showing the current and maximum cached cursors for a session.

```text
CURR_CACHED MAX_CACHED USERNAME   SID    SERIAL#
----------- ---------- -------- ----- ----------
         49 50         APP         35        263
```

--------------------------------

### Example Output for Cache Hit Percentage Query

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/tgdba/tuning-shared-pool-and-large-pool.html

Sample output showing cache hits versus total parses.

```text
CACHE_HITS ALL_PARSES % found in cache
---------- ---------- ----------------
        34        700             4.57
```

--------------------------------

### Example Client Result Cache Statistics Output

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/tgdba/tuning-result-cache.html

Sample output showing the structure of client result cache statistics.

```text
STAT_ID    NAME OF STATISTICS      VALUE   CACHE_ID
=======    ==================      =====   ========
    1      Block Size               256         124
    2      Block Count Max          256         124
    3      Block Count Current      128         124
    4      Hash Bucket Count       1024         124
    5      Create Count Success      10         124
    6      Create Count Failure       0         124
    7      Find Count                12         124
    8      Invalidation Count         8         124
    9      Delete Count Invalid       0         124
   10      Delete Count Valid         0         124
```

--------------------------------

### V$ROWCACHE Query Output Example

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/tgdba/tuning-shared-pool-and-large-pool.html

Sample output demonstrating the statistics returned by the V$ROWCACHE query.

```text
PARAMETER              SUM(GETS) SUM(GETMISSES) PCT_SUCC_GETS      UPDATES
--------------------- ---------- -------------- ------------- ------------
dc_database_links             81              1          98.8            0
dc_free_extents            44876          20301          54.8       40,453
dc_global_oids                42              9          78.6            0
dc_histogram_defs           9419            651          93.1            0
dc_object_ids              29854            239          99.2           52
dc_objects                 33600            590          98.2           53
dc_profiles                19001              1         100.0            0
dc_rollback_segments       47244             16         100.0           19
dc_segments               100467          19042          81.0       40,272
dc_sequence_grants           119             16          86.6            0
dc_sequences               26973             16          99.9       26,811
dc_synonyms                 6617            168          97.5            0
dc_tablespace_quotas         120              7          94.2           51
dc_tablespaces            581248             10         100.0            0
dc_used_extents            51418          20249          60.6       42,811
dc_user_grants             76082             18         100.0            0
dc_usernames              216860             12         100.0            0
dc_users                  376895             22         100.0            0
```

--------------------------------

### Example Query Output

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/tgdba/tuning-database-buffer-cache.html

Sample output format for the segment block count query.

```text
OBJECT_NAME                              NUMBER_OF_BLOCKS
---------------------------------------- ----------------
OA_PREF_UNIQ_KEY                                        1
SYS_C002651                                             1
..
DS_PERSON                                              78
OM_EXT_HEADER                                         701
OM_SHELL                                            1,765
OM_HEADER                                           5,826
OM_INSTANCE                                        12,644
```

--------------------------------

### Sample Output for V$PGA_TARGET_ADVICE

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/tgdba/tuning-program-global-area.html

Example output showing the relationship between target memory in MB, cache hit percentage, and over-allocation count.

```text
 TARGET_MB  CACHE_HIT_PERC  ESTD_OVERALLOC_COUNT
----------  --------------  --------------------
        63              23                   367
       125              24                    30
       250              30                     3
       375              39                     0
       500              58                     0
       600              59                     0
       700              59                     0
       800              60                     0
       900              60                     0
      1000              61                     0
      1500              67                     0
      2000              76                     0
      3000              83                     0
      4000              85                     0
```

--------------------------------

### Example Server Result Cache Statistics Output

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/tgdba/tuning-result-cache.html

Sample output showing the structure of server result cache statistics.

```text
NAME                          VALUE
--------------------     ----------
Block Size (Bytes)             1024
Block Count Maximum            3136
Block Count Current              32
Result Size Maximum (Blocks)    156
Create Count Success              2
Create Count Failure              0
Find Count                        0
Invalidation Count                0
Delete Count Invalid              0
Delete Count Valid                0
```

--------------------------------

### Display Buffer Cache Advice Output

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/tgdba/tuning-database-buffer-cache.html

Example output showing estimated physical reads based on varying buffer cache sizes.

```text
 Cache Size (MB)      Buffers Read Factor        Reads
---------------- ------------ ----------- ------------
              30        3,802       18.70  192,317,943      10% of Current Size 
              60        7,604       12.83  131,949,536
              91       11,406        7.38   75,865,861
             121       15,208        4.97   51,111,658
             152       19,010        3.64   37,460,786
             182       22,812        2.50   25,668,196
             212       26,614        1.74   17,850,847
             243       30,416        1.33   13,720,149
             273       34,218        1.13   11,583,180
             304       38,020        1.00   10,282,475      Current Size 
             334       41,822         .93    9,515,878
             364       45,624         .87    8,909,026
             395       49,426         .83    8,495,039
             424       53,228         .79    8,116,496
             456       57,030         .76    7,824,764
             486       60,832         .74    7,563,180
             517       64,634         .71    7,311,729
             547       68,436         .69    7,104,280
             577       72,238         .67    6,895,122
             608       76,040         .66    6,739,731      200% of Current Size
```

--------------------------------

### Analyze Database Instance with DBMS_ADDM.ANALYZE_INST

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/tgdba/automatic-performance-diagnostics.html

Defines the procedure signature for instance-level analysis and provides an example of executing it for a specific instance.

```sql
BEGIN
DBMS_ADDM.ANALYZE_INST (
   task_name           IN OUT VARCHAR2,
   begin_snapshot      IN     NUMBER,
   end_snapshot        IN     NUMBER,
   instance_number     IN     NUMBER := NULL,
   db_id               IN     NUMBER := NULL);
END;
/
```

```sql
VAR tname VARCHAR2(30);
BEGIN
  :tname := 'my ADDM for 7PM to 9PM';
  DBMS_ADDM.ANALYZE_INST(:tname, 137, 145, 1);
END;
/
```

--------------------------------

### AWR Import Process Output

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/tgdba/transporting-automatic-workload-repository-data.html

Example output showing the successful completion of the AWR data import process.

```text
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Completed 113 CONSTRAINT objects in 11 seconds
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Completed 1 REF_CONSTRAINT objects in 1 seconds
Job "SYS"."SYS_IMPORT_FULL_03" successfully completed at 09:29:30
... Dropping AWR_STAGE user
End of AWR Load
```

--------------------------------

### Viewing Cache Statistics Output

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/tgdba/tuning-result-cache.html

Example output from querying the V$RESULT_CACHE_OBJECTS view.

```text
        ID TYPE       CREATION_ BLOCK_COUNT COLUMN_COUNT  PIN_COUNT  ROW_COUNT
---------- ---------- --------- ----------- ------------ ---------- ----------
         2 Result     06-NOV-11           1            2          0         12
```

--------------------------------

### Library Cache Hit Ratio Result

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/tgdba/tuning-shared-pool-and-large-pool.html

Example output showing the result of the library cache hit ratio calculation.

```text
SUM(PINHITS)/SUM(PINS)
----------------------
            .999466248
```

--------------------------------

### Define Orion Input File Content

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/tgdba/IO-configuration-and-design.html

Example of a .lun file containing a list of raw volumes to be tested by Orion.

```text
/dev/raw/raw1
/dev/raw/raw2
/dev/raw/raw3
/dev/raw/raw4
/dev/raw/raw5
/dev/raw/raw6
/dev/raw/raw7
/dev/raw/raw8
```

--------------------------------

### Example ADDM Report Output

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/tgdba/automatic-performance-diagnostics.html

A sample output from an ADDM report identifying performance issues caused by SQL literals and providing actionable recommendations.

```text
FINDING 1: 31% impact (7798 seconds) 
------------------------------------ 
SQL statements were not shared due to the usage of literals. This resulted in 
additional hard parses which were consuming significant database time.

RECOMMENDATION 1: Application Analysis, 31% benefit (7798 seconds)
  ACTION: Investigate application logic for possible use of bind variables
    instead of literals. Alternatively, you may set the parameter 
    "cursor_sharing" to "force".
  RATIONALE: SQL statements with PLAN_HASH_VALUE 3106087033 were found to be
    using literals. Look in V$SQL for examples of such SQL statements.
```

--------------------------------

### Example Output of V$PGASTAT Query

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/tgdba/tuning-program-global-area.html

Sample output showing various PGA memory metrics including target parameters, allocation, and cache hit percentages.

```text
NAME                                                          VALUE UNIT
-------------------------------------------------------- ---------- ------------
aggregate PGA target parameter                             41156608 bytes
aggregate PGA auto target                                  21823488 bytes
global memory bound                                         2057216 bytes
total PGA inuse                                            16899072 bytes
total PGA allocated                                        35014656 bytes
maximum PGA allocated                                     136795136 bytes
total freeable PGA memory                                    524288 bytes
PGA memory freed back to OS                              1713242112 bytes
total PGA used for auto workareas                                 0 bytes
maximum PGA used for auto workareas                         2383872 bytes
total PGA used for manual workareas                               0 bytes
maximum PGA used for manual workareas                       8470528 bytes
over allocation count                                           291
bytes processed                                          2124600320 bytes
extra bytes read/written                                   39949312 bytes
cache hit percentage                                          98.15 percent
```

--------------------------------

### Example SQL Statement for Cursor Sharing

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/tgdba/tuning-shared-pool-and-large-pool.html

A sample SQL query used to demonstrate how the database optimizes statements when CURSOR_SHARING is set to FORCE.

```sql
SELECT *
  FROM hr.employees
 WHERE employee_id = 101;
```

--------------------------------

### Execute ADDM Task for Specific Snapshots

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/tgdba/automatic-performance-diagnostics.html

Example of creating and executing an ADDM task to analyze database performance between two specific snapshot IDs.

```PL/SQL
VAR tname VARCHAR2(30);
BEGIN
  :tname := 'ADDM for 7PM to 9PM';
  DBMS_ADDM.ANALYZE_DB(:tname, 137, 145);
END;
/
```

--------------------------------

### Configure RMF Topology

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/tgdba/configuring-remote-management-framework-rmf.html

Configures nodes, creates a topology, registers nodes, and enables AWR services for a multi-node setup.

```sql
/* Configure the nodes T, S0, and S1 by executing these procedures */

/* Execute this procedure on node T */
SQL> exec DBMS_UMF.configure_node ('T');

/* Execute this procedure on node S0 */
SQL> exec DBMS_UMF.configure_node ('S0');

/* Execute this procedure on node S1 */
SQL> exec DBMS_UMF.configure_node ('S1');

/* Execute all the following procedures on the destination node T */

/* Create the topology 'Topology_1' */
SQL> exec DBMS_UMF.create_topology ('Topology_1');

/* Register the node S0 with the topology 'Topology_1' */
SQL> exec DBMS_UMF.register_node ('Topology_1',
                                  'S0',
                                  'DBLINK_T_to_S0',
                                  'DBLINK_S0_to_T');

/* Register the node S1 with the topology 'Topology_1' */
SQL> exec DBMS_UMF.register_node ('Topology_1',
                                  'S1',
                                  'DBLINK_T_to_S1',
                                  'DBLINK_S1_to_T');

/* Register the database links between the nodes S0 and S1 in the topology 'Topology_1'. 
 * When destination T is unavailable at the time of failover, the source S0 can connect
 * to the candidate destination S1 using this database link.
 */
SQL> exec DBMS_UMF.create_link ('Topology_1',
                                'S0',
                                'S1',
                                'DBLINK_S0_to_S1',
                                'DBLINK_S1_to_S0');

/* Enable the AWR service on the node S0 in the topology 'Topology_1' */
SQL> exec DBMS_WORKLOAD_REPOSITORY.register_remote_database(node_name=>'S0');

/* Enable the AWR service on the node S1 in the topology 'Topology_1' */
SQL> exec DBMS_WORKLOAD_REPOSITORY.register_remote_database(node_name=>'S1');
```

--------------------------------

### Wait Event Statistics Detail

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/tgdba/instance-tuning-using-performance-views.html

Example data from the AWR report showing wait counts, timeouts, and average wait times for specific events.

```text
                                                             Avg
                                                Total Wait   wait     Waits
Event                           Waits  Timeouts   Time (s)   (ms)      /txn
--------------------------- --------- --------- ---------- ------ ---------
log file parallel write         2,181         0         28     13      41.2
SQL*Net more data from clie   516,611         0         27      0   9,747.4
db file parallel write         13,383         0         13      1     252.5
```

--------------------------------

### Configure Database and Instance Parameters

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/tgdba/analyzing-sampled-data.html

Provide the database identifier and select the instances to include in the report.

```text
Enter value for dbid: 3309173529
Using database id: 3309173529
Enter instance numbers. Enter 'ALL' for all instances in an Oracle
RAC cluster or explicitly specify list of instances (e.g., 1,2,3).
Defaults to current instance.
Enter value for inst_num: ALL
Using instance number(s): ALL
```

--------------------------------

### Orion Latency CSV Output Format

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/tgdba/IO-configuration-and-design.html

Example of the latency results for a small random workload.

```text
Large/Small, 1, 2, 3, 6, 9, 12 . . . .

0, 14.22, 14.69, 15.09, 16.98, 18.91, 21.25 . . . .
```

--------------------------------

### Configure First Time Period Parameters

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/tgdba/comparing-database-performance-time.html

Input the database identifier and instance numbers for the first time period.

```text
Enter value for dbid: 3309173529
Using 3309173529 for Database Id for the first pair of snapshots
Enter value for inst_num: 1,2
Using instances 1 for the first pair of snapshots
```

--------------------------------

### Schema Object Reference Example

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/tgdba/tuning-shared-pool-and-large-pool.html

Statements referencing the same table name in different schemas are not considered identical.

```sql
SELECT * FROM employees;
```

--------------------------------

### Specify Report Time Period

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/tgdba/generating-performance-hub-active-report.html

Define the start and end times for the report data in the specified format.

```text
Please enter end time in format of dd:mm:yyyy hh24:mi:ss: 03:04:2014 17:00:00
Please enter start time in format of dd:mm:yyyy hh24:mi:ss: 03:04:2014 16:00:00
```

--------------------------------

### Verify Cell Initialization

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/tgdba/IO-configuration-and-design.html

Check the cellinit.ora file for IP address configuration.

```bash
cat $OSSCONF/cellinit.ora | grep ip
ipaddress2=192.168.0.32/20
ipaddress1=192.168.0.31/20
```

--------------------------------

### Sample Server Result Cache Memory Report Output

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/tgdba/tuning-result-cache.html

Example output generated after executing the DBMS_RESULT_CACHE.MEMORY_REPORT procedure.

```text
R e s u l t  C a c h e  M e m o r y  R e p o r t
[Parameters]
Block Size = 1024 bytes
Maximum Cache Size = 950272 bytes (928 blocks)
Maximum Result Size = 47104 bytes (46 blocks)
[Memory]
Total Memory = 46340 bytes [0.048% of the Shared Pool]
... Fixed Memory = 10696 bytes [0.011% of the Shared Pool]
... State Object Pool = 2852 bytes [0.003% of the Shared Pool]
... Cache Memory = 32792 bytes (32 blocks) [0.034% of the Shared Pool]
....... Unused Memory = 30 blocks
....... Used Memory = 2 blocks
........... Dependencies = 1 blocks
........... Results = 1 blocks
............... SQL = 1 blocks

PL/SQL procedure successfully completed.
```

--------------------------------

### View Available Instances

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/tgdba/analyzing-sampled-data.html

Review the list of database IDs and instance numbers displayed by the script.

```text
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
   DB Id    Inst Num DB Name      Instance     Host
----------- -------- ------------ ------------ ------------
 3309173529        1 MAIN         main         examp1690
 3309173529        1 TINT251      tint251      samp251
 3309173529        2 TINT251      tint252      samp252
 3309173529        3 TINT251      tint253      samp253
 3309173529        4 TINT251      tint254      samp254
```

--------------------------------

### Orion IOPS CSV Output Format

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/tgdba/IO-configuration-and-design.html

Example of the I/O throughput (IOPS) results for a small random workload.

```text
Large/Small,     1,     2,     3,     6,     9,    12 . . . .

0,             105,   208,   309,   569,   782,   951 . . . .
```

--------------------------------

### Define ASH Report Duration

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/tgdba/analyzing-sampled-data.html

Specify the duration in minutes for capturing ASH information starting from the begin time.

```text
Enter value for duration:
```

--------------------------------

### Set ASH Report Time Parameters

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/tgdba/analyzing-sampled-data.html

Defines the start time and duration for the ASH report data collection.

```text
Enter value for begin_time: -10
```

```text
Enter value for duration:
```

--------------------------------

### Configure Orion for Exadata Storage Cells

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/tgdba/IO-configuration-and-design.html

Steps to configure cell IP addresses and create the orion.lun file for Exadata environments.

```bash
cellcli -e list cell attributes ipaddress1,ipaddress2
         192.168.0.31/20         192.168.0.32/20

echo "cell=\"192.168.0.31;192.168.0.32\"" > $OSSCONF/cellip.ora
```

```bash
cellcli -e list griddisk
         orion_FD_00_scaqaj04celadm08    active
         orion_FD_01_scaqaj04celadm08    active
         orion_FD_02_scaqaj04celadm08    active
         orion_FD_03_scaqaj04celadm08    active
         orion_FD_04_scaqaj04celadm08    active
         orion_FD_05_scaqaj04celadm08    active
         orion_FD_06_scaqaj04celadm08    active
         orion_FD_07_scaqaj04celadm08    active

cat orion.lun
o/192.168.0.31;192.168.0.32/orion_FD_00_scaqaj04celadm08
o/192.168.0.31;192.168.0.32/orion_FD_01_scaqaj04celadm08
o/192.168.0.31;192.168.0.32/orion_FD_02_scaqaj04celadm08
o/192.168.0.31;192.168.0.32/orion_FD_03_scaqaj04celadm08
o/192.168.0.31;192.168.0.32/orion_FD_04_scaqaj04celadm08
o/192.168.0.31;192.168.0.32/orion_FD_05_scaqaj04celadm08
o/192.168.0.31;192.168.0.32/orion_FD_06_scaqaj04celadm08
o/192.168.0.31;192.168.0.32/orion_FD_07_scaqaj04celadm08
```

--------------------------------

### SQL Statement Character Matching Examples

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/tgdba/tuning-shared-pool-and-large-pool.html

These statements fail to share a SQL area due to differences in case and spacing.

```sql
SELECT * FROM employees;
SELECT * FROM Employees;
SELECT *  FROM employees;
```

--------------------------------

### Orion MBPS CSV Output Format

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/tgdba/IO-configuration-and-design.html

Example of the data transfer rate (MBPS) results for a large random/sequential workload.

```text
Large/Small,       0

1,   19.18
2,   37.59
4,   65.53
6,   87.03
8,  103.06
10,  109.67
. .   . . .
. .   . . .
```

--------------------------------

### Create ADG Role Transition Link

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/tgdba/configuring-remote-management-framework-rmf.html

Establishes database links between sources and a candidate destination to prepare for role transitions.

```sql
SQL> EXEC DBMS_UMF.CREATE_LINK (topology name, 
                                source name, 
                                candidate destination name,
                                source to candidate destination database link,
                                candidate destination to source database link);
```

--------------------------------

### Execute Orion on Exadata

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/tgdba/IO-configuration-and-design.html

Run the Orion tool with specific parameters for Exadata storage testing.

```bash
which orion` -type rand -write 0 -run advanced -duration 30 -matrix point
-num_small 20 -num_large 0 -testname orion
```

--------------------------------

### Retrieve ADDM Report with DBMS_ADDM.GET_REPORT

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/tgdba/automatic-performance-diagnostics.html

Defines the function signature for retrieving reports and provides an example of displaying the report output.

```sql
DBMS_ADDM.GET_REPORT (
   task_name           IN VARCHAR2
  RETURN CLOB);
```

```sql
SET LONG 1000000 PAGESIZE 0;
SELECT DBMS_ADDM.GET_REPORT(:tname) FROM DUAL;
```

--------------------------------

### List Available Database Instances

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/tgdba/analyzing-sampled-data.html

Displays the available database IDs and instance numbers for report generation.

```text
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
   DB Id    Inst Num DB Name      Instance     Host
----------- -------- ------------ ------------ ------------
 3309173529        1 MAIN         main         examp1690
 3309173529        1 TINT251      tint251      samp251
```

--------------------------------

### Set Report Begin Time

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/tgdba/analyzing-sampled-data.html

Define the start time for the ASH data collection relative to the current system date.

```text
Enter value for begin_time: -1:10
```

--------------------------------

### Configure Second Time Period Parameters

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/tgdba/comparing-database-performance-time.html

Input the database identifier and instance numbers for the second time period.

```text
Enter value for dbid2: 3309173529
Using 3309173529 for Database Id for the second pair of snapshots
Enter value for instance_numbers_or_all2: 3,4
```

--------------------------------

### Define ASH Report Begin Time

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/tgdba/analyzing-sampled-data.html

Specify the start time for the report in minutes relative to the current system date.

```text
Enter value for begin_time: -10
```

--------------------------------

### Run I/O Calibration Procedure

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/tgdba/IO-configuration-and-design.html

Executes the I/O calibration procedure to measure storage performance. Ensure the database is idle or in an off-peak period before running.

```sql
SET SERVEROUTPUT ON
DECLARE
  lat  NUMBER;
  iops INTEGER;
  mbps INTEGER;
BEGIN
-- DBMS_RESOURCE_MANAGER.CALIBRATE_IO (<DISKS>, <MAX_LATENCY>, iops, mbps, lat);
   DBMS_RESOURCE_MANAGER.CALIBRATE_IO (2, 10, iops, mbps, lat);
 
end;
/
```

--------------------------------

### Bind Variable Name Mismatch Example

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/tgdba/tuning-shared-pool-and-large-pool.html

Statements must use identical bind variable names to share a SQL area.

```sql
SELECT * FROM employees WHERE department_id = :department_id;
SELECT * FROM employees WHERE department_id = :dept_id;
```

--------------------------------

### Configure Result Cache with FORCE Annotation and Hints

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/tgdba/tuning-result-cache.html

Demonstrates forcing result caching for a table and using a hint to exclude a specific query from the cache.

```sql
ALTER TABLE sales RESULT_CACHE (MODE FORCE);

SELECT prod_id, SUM(amount_sold)
  FROM sales 
 GROUP BY prod_id 
HAVING prod_id=136;

SELECT /*+ NO_RESULT_CACHE */ * 
  FROM sales
 ORDER BY time_id DESC;
```
Tokens:
Raw