### 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; ``` -------------------------------- ### 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. ``` -------------------------------- ### 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; ``` -------------------------------- ### 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 ``` -------------------------------- ### Analyze Subset of Instances with DBMS_ADDM.ANALYZE_PARTIAL Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/tgdba/automatic-performance-diagnostics.html Defines the procedure signature for partial analysis and provides an example of executing it for a comma-delimited list of instances. ```sql BEGIN DBMS_ADDM.ANALYZE_PARTIAL ( task_name IN OUT VARCHAR2, instance_numbers IN VARCHAR2, begin_snapshot IN NUMBER, end_snapshot IN NUMBER, db_id IN NUMBER := NULL); END; / ``` ```sql VAR tname VARCHAR2(30); BEGIN :tname := 'my ADDM for 7PM to 9PM'; DBMS_ADDM.ANALYZE_PARTIAL(:tname, '1,2,4', 137, 145); END; / ``` -------------------------------- ### 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; ``` -------------------------------- ### 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 . . . . . . . . . . ``` -------------------------------- ### 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 ``` -------------------------------- ### DBMS_AUTO_MV.CONFIGURE Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/tgdba/auto_material_views.html The CONFIGURE procedure allows database administrators to start, stop, and configure automatic materialized views by setting specific parameters. ```APIDOC ## DBMS_AUTO_MV.CONFIGURE ### Description Configures the behavior of automatic materialized views, including operational modes and maintenance task status. ### Parameters - **AUTO_MV_MODE** (string) - Optional - Sets the operational mode: 'IMPLEMENT' (enable), 'OFF' (disable), or 'REPORT ONLY'. - **AUTO_MV_MAINT_TASK** (string) - Optional - Sets the maintenance task status: 'ENABLE', 'DISABLE', or 'CLEANUP_AND_DISABLE'. ### Request Example ```sql -- Enable automatic materialized views exec dbms_auto_mv.configure('AUTO_MV_MODE', 'IMPLEMENT'); -- Enable maintenance tasks exec dbms_auto_mv.configure('AUTO_MV_MAINT_TASK', 'ENABLE'); ``` ``` -------------------------------- ### Top 5 Timed Events Report Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/tgdba/instance-tuning-using-performance-views.html Example output from the Automatic Workload Repository showing the top 5 timed events ranked by call time percentage. ```text Top 5 Timed Events ~~~~~~~~~~~~~~~~~~ % Total Event Waits Time (s) Call Time -------------------------------------- ------------ ----------- --------- CPU time 559 88.80 log file parallel write 2,181 28 4.42 SQL*Net more data from client 516,611 27 4.24 db file parallel write 13,383 13 2.04 db file sequential read 563 2 .27 ``` -------------------------------- ### 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 ``` -------------------------------- ### 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 ``` -------------------------------- ### 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; ``` -------------------------------- ### 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 ``` -------------------------------- ### 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 ``` -------------------------------- ### 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 (, , 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; ``` -------------------------------- ### Execute OLTP Workload Test Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/tgdba/IO-configuration-and-design.html Run an OLTP-like workload using the default orion.lun input file. ```bash $ ./orion -run oltp ``` -------------------------------- ### SQL Statement Literal Difference Examples Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/tgdba/tuning-shared-pool-and-large-pool.html Statements differing only by literal values do not share a SQL area unless CURSOR_SHARING is set to FORCE. ```sql SELECT count(1) FROM employees WHERE manager_id = 121; SELECT count(1) FROM employees WHERE manager_id = 247; ``` -------------------------------- ### Execute Performance Hub Report Script Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/tgdba/generating-performance-hub-active-report.html Run the perfhubrpt.sql script from the SQL prompt to initiate report generation. ```sql @$ORACLE_HOME/rdbms/admin/perfhubrpt.sql ``` -------------------------------- ### Display Instances for Second Period Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/tgdba/comparing-database-performance-time.html View available instances again before configuring the second time period. ```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 INSTNUM1 ----------------------------------------------------- 1,2 ``` -------------------------------- ### Enable Memoptimize Pool Workflow Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/tgdba/tuning-system-global-area.html Complete workflow for checking, setting, and restarting the database to enable the memoptimize pool. ```sql SQL> SHOW PARAMETER MEMOPTIMIZE_POOL_SIZE NAME TYPE VALUE --------------------- ----------- ----- memoptimize_pool_size big integer 0 SQL> ALTER SYSTEM SET MEMOPTIMIZE_POOL_SIZE=10G SCOPE=SPFILE; System altered. SQL> SHUTDOWN IMMEDIATE Database closed. Database dismounted. ORACLE instance shut down. SQL> STARTUP ORACLE instance started. Total System Global Area 1.1832E+10 bytes Fixed Size 9010864 bytes Variable Size 1.1799E+10 bytes Database Buffers 16777216 bytes Redo Buffers 7766016 bytes Database mounted. Database opened. SQL> SHOW PARAMETER MEMOPTIMIZE_POOL_SIZE NAME TYPE VALUE --------------------- ----------- ----- memoptimize_pool_size big integer 10G ``` -------------------------------- ### Create a Snapshot using PL/SQL Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/tgdba/managing-snapshots.html Executes the CREATE_SNAPSHOT procedure to capture AWR statistics immediately on the local database instance. ```sql BEGIN DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT (); END; / ``` -------------------------------- ### Orion Performance Analysis Commands Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/tgdba/IO-configuration-and-design.html Commands for analyzing storage performance with specific I/O patterns. ```bash $ orion -run simple ``` ```bash $ orion -run normal ``` ```bash $ orion -run advanced -size_small 32 \ -size_large 1024 -type rand -matrix detailed ``` ```bash $ orion -run advanced -simulate raid0 \ -stripe 1024 -write 100 -type seq -matrix col -num_small 0 ``` ```bash -run advanced -size_small 32 -size_large 1024 -type rand -matrix detailed ``` ```bash -run advanced -simulate raid0 -write 100 -type seq -matrix col -num_small 0 ``` -------------------------------- ### Check Current Free List Settings Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/tgdba/instance-tuning-using-performance-views.html Retrieve the current FREELISTS configuration for a specific segment to diagnose potential contention. ```sql SELECT SEGMENT_NAME, FREELISTS FROM DBA_SEGMENTS WHERE SEGMENT_NAME = segment name AND SEGMENT_TYPE = segment type; ``` -------------------------------- ### Input First Period Database Details Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/tgdba/comparing-database-performance-time.html Provide the database identifier and instance number 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 Using 1 for Instance Number for the first pair of snapshots ``` -------------------------------- ### Create a Single Baseline Template Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/tgdba/managing-baseline-templates.html Use this procedure to schedule a one-time baseline capture for a specific fixed time interval. ```PL/SQL BEGIN DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE_TEMPLATE (start_time => '2012-04-02 17:00:00 PST', end_time => '2012-04-02 20:00:00 PST', baseline_name => 'baseline_120402', template_name => 'template_120402', expiration => 30, dbid => 3310949047); END; / ``` -------------------------------- ### Execute AWR Load Script Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/tgdba/transporting-automatic-workload-repository-data.html Run the awrload.sql script from the SQL prompt while connected as the SYS user. ```sql @$ORACLE_HOME/rdbms/admin/awrload.sql ``` -------------------------------- ### Configure Workload Analysis Window Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/tgdba/auto_material_views.html Sets the maximum number of hours to investigate queries for recommendations. ```sql exec dbms_auto_mv.configure('AUTO_MV_ANALYZE_WORKLOAD_WINDOW', 48); ``` -------------------------------- ### Enable Log Buffer Space Redo Prioritization Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/tgdba/configuring-database-for-performance.html Use this SQL command to dynamically enable redo prioritization, which allows the database to throttle batch sessions during periods of high redo buffer pressure. ```sql ALTER SYSTEM SET log_redo_prioritization=TRUE; ``` -------------------------------- ### Querying the V$LIBRARYCACHE View Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/tgdba/tuning-shared-pool-and-large-pool.html Retrieve library cache statistics for each namespace to monitor activity and identify potential cache misses. ```sql SELECT namespace, pins, pinhits, reloads, invalidations FROM V$LIBRARYCACHE ORDER BY namespace; ``` -------------------------------- ### Enable Force Full Database Caching Mode Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/tgdba/tuning-database-buffer-cache.html Use this command to enable the force full database caching mode for the database. ```sql ALTER DATABASE FORCE FULL DATABASE CACHING; ``` -------------------------------- ### Insert Data Using Fast Ingest Hint Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/tgdba/tuning-system-global-area.html Apply the MEMOPTIMIZE_WRITE hint to INSERT statements for tables already configured for fast ingest. ```sql INSERT /*+ MEMOPTIMIZE_WRITE */ INTO test_fast_ingest VALUES (1,'test'); ``` -------------------------------- ### Specify Database Identifier and Instance Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/tgdba/generating-automatic-workload-repository-reports.html Input the required database ID and instance number for the specific report. ```text Enter value for dbid: 3309173529 Using 3309173529 for database Id Enter value for inst_num: 1 ``` -------------------------------- ### Specify Database Instance Number Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/tgdba/generating-performance-hub-active-report.html Select the instance number or specify all instances for the report. ```text Please enter instance number: all instances ``` -------------------------------- ### Generate and Report Recommendations Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/tgdba/auto_material_views.html Executes the recommendation process and queries the results from the DBA_AUTO_MV_ANALYSIS_RECOMMENDATIONS view. ```sql var exec_name varchar2(200); begin :exec_name := dbms_auto_mv.recommend(); end; SELECT * FROM DBA_AUTO_MV_ANALYSIS_RECOMMENDATIONS WHERE exec_name = :exec_name; ``` -------------------------------- ### Querying with RESULT_CACHE Hint Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/tgdba/tuning-result-cache.html Use the RESULT_CACHE hint to instruct the database to store and retrieve query results from the server result cache. ```sql SELECT /*+ RESULT_CACHE */ department_id, AVG(salary) FROM hr.employees GROUP BY department_id; ``` -------------------------------- ### Set Preliminary FAST_START_MTTR_TARGET Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/tgdba/instance-tuning-using-performance-views.html Applies a chosen compromise value for the MTTR target after determining practical bounds. ```sql SQL> ALTER SYSTEM SET FAST_START_MTTR_TARGET=30; ``` -------------------------------- ### Configure AWR SQL Report Parameters Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/tgdba/generating-automatic-workload-repository-reports.html Interactive prompts for configuring report type, snapshot range, SQL ID, and output filename for a SQL statement report. ```text Enter value for report_type: html ``` ```text Enter value for num_days: 1 ``` ```text Enter value for begin_snap: 146 Enter value for end_snap: 147 ``` ```text Enter value for sql_id: 2b064ybzkwf1y ``` ```text Enter value for report_name: Using the report name awrrpt_1_146_147.html ``` -------------------------------- ### Calculate Library Cache Hit Ratio Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/tgdba/tuning-shared-pool-and-large-pool.html Formula to determine the library cache hit ratio using pinhits and pins. ```sql Library Cache Hit Ratio = sum(pinhits) / sum(pins) ``` -------------------------------- ### Configure AWR Report Parameters Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/tgdba/generating-automatic-workload-repository-reports.html Interactive prompts for defining report type, time range, snapshot IDs, and output filenames. ```text Enter value for report_type: text ``` ```text Enter value for num_days: 2 ``` ```text Enter value for begin_snap: 150 Enter value for end_snap: 160 ``` ```text Enter value for report_name: Using the report name awrrpt_1_150_160 ``` -------------------------------- ### Create a Baseline with DBMS_WORKLOAD_REPOSITORY Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/tgdba/managing-baselines.html Use the CREATE_BASELINE procedure to define a fixed baseline between two snapshot IDs with a specified expiration period. ```plsql BEGIN DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE (start_snap_id => 270, end_snap_id => 280, baseline_name => 'peak baseline', dbid => 3310949047, expiration => 30); END; / ``` -------------------------------- ### Select Snapshots for First Period Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/tgdba/comparing-database-performance-time.html Choose the beginning and ending snapshot IDs for the first time period. ```text Enter value for begin_snap: 102 Enter value for end_snap: 103 ``` -------------------------------- ### Analyze Database with DBMS_ADDM.ANALYZE_DB Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/tgdba/automatic-performance-diagnostics.html Procedure signature for running ADDM in database mode to analyze all instances of a database. ```PL/SQL BEGIN DBMS_ADDM.ANALYZE_DB ( task_name IN OUT VARCHAR2, begin_snapshot IN NUMBER, end_snapshot IN NUMBER, db_id IN NUMBER := NULL); END; / ``` -------------------------------- ### Identify Sessions Waiting for I/O Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/tgdba/instance-tuning-using-performance-views.html Use this query to find the SQL address and hash value for sessions currently experiencing I/O-related wait events. ```sql SELECT SQL_ADDRESS, SQL_HASH_VALUE FROM V$SESSION WHERE EVENT LIKE 'db file%read'; ``` -------------------------------- ### Execute Local AWR Report Script Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/tgdba/generating-automatic-workload-repository-reports.html Run the awrrpt.sql script from the SQL prompt to begin the local report generation process. ```sql @$ORACLE_HOME/rdbms/admin/awrrpt.sql ``` -------------------------------- ### Orion Workload Evaluation Commands Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/tgdba/IO-configuration-and-design.html Basic Orion commands for evaluating different storage workload types. ```bash -run oltp ``` ```bash -run dss ``` ```bash -run normal ``` -------------------------------- ### Querying V$DB_CACHE_ADVICE for Buffer Cache Sizing Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/tgdba/tuning-database-buffer-cache.html Retrieves predicted physical read requirements for the default buffer pool to assist in cache sizing decisions. ```sql COLUMN size_for_estimate FORMAT 999,999,999,999 heading 'Cache Size (MB)' COLUMN buffers_for_estimate FORMAT 999,999,999 heading 'Buffers' COLUMN estd_physical_read_factor FORMAT 999.90 heading 'Estd Phys|Read Factor' COLUMN estd_physical_reads FORMAT 999,999,999 heading 'Estd Phys| Reads' SELECT size_for_estimate, buffers_for_estimate, estd_physical_read_factor, estd_physical_reads FROM V$DB_CACHE_ADVICE WHERE name = 'DEFAULT' AND block_size = (SELECT value FROM V$PARAMETER WHERE name = 'db_block_size') AND advice_status = 'ON'; ``` -------------------------------- ### Find Total Buffers in Instance Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/tgdba/tuning-database-buffer-cache.html Aggregates the total number of buffers across all buffer pools in the database instance. ```sql SELECT name, block_size, SUM(buffers) FROM V$BUFFER_POOL GROUP BY name, block_size HAVING SUM(buffers) > 0; ``` -------------------------------- ### Configure AWR RAC Report Parameters Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/tgdba/generating-automatic-workload-repository-reports.html Interactive prompts for configuring report type, database ID, instance numbers, snapshot range, and output filename. ```text Enter value for report_type: html ``` ```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 ``` ```text Enter value for dbid: 3309173529 Using 3309173529 for database Id ``` ```text Enter value for instance_numbers_or_all: 1,2 ``` ```text Enter value for num_days: 2 ``` ```text Enter value for begin_snap: 150 Enter value for end_snap: 160 ``` ```text Enter value for report_name: Using the report name awrrpt_rac_150_160.html ``` -------------------------------- ### Calculate CPU Resource Requirement Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/tgdba/managing-operating-system-resources.html Equation to determine the percentage of available CPU resources required based on transaction rates. ```text 20 tpm / 120 tpm * 90% = 15% of available CPU resource ``` -------------------------------- ### Create a Repeating Baseline Template Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/tgdba/managing-baseline-templates.html Use this procedure to automate the creation of recurring baselines over a specified date range. ```PL/SQL BEGIN DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE_TEMPLATE (day_of_week => 'monday', hour_in_day => 17, duration => 3, expiration => 30, start_time => '2012-04-02 17:00:00 PST', end_time => '2012-12-31 20:00:00 PST', baseline_name_prefix => 'baseline_2012_mondays_', template_name => 'template_2012_mondays', dbid => 3310949047); END; / ``` -------------------------------- ### Set Default ADDM Parameter Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/tgdba/automatic-performance-diagnostics.html Configures the DBIO_EXPECTED parameter for ADDM, which represents the average time in microseconds to read a single database block. ```PL/SQL EXECUTE DBMS_ADVISOR.SET_DEFAULT_TASK_PARAMETER( 'ADDM', 'DBIO_EXPECTED', 8000); ``` -------------------------------- ### Enable Table for Fast Ingest Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/tgdba/tuning-system-global-area.html Use the MEMOPTIMIZE FOR WRITE clause during table creation to enable fast ingest capabilities. ```sql SQL> create table test_fast_ingest ( id number primary key, test_col varchar2(15)) segment creation immediate memoptimize for write; Table created. ``` -------------------------------- ### DBMS_ADDM.ANALYZE_INST Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/tgdba/automatic-performance-diagnostics.html Analyzes a specific instance of the database by creating and executing an ADDM task. ```APIDOC ## DBMS_ADDM.ANALYZE_INST ### Description Runs ADDM in Instance mode to analyze a particular instance of the database. ### Parameters - **task_name** (VARCHAR2) - Required - Name of the analysis task to be created. - **begin_snapshot** (NUMBER) - Required - Snapshot number of the beginning of the analysis period. - **end_snapshot** (NUMBER) - Required - Snapshot number of the end of the analysis period. - **instance_number** (NUMBER) - Optional - The instance number to analyze. Defaults to the current instance. - **db_id** (NUMBER) - Optional - The database identifier. Defaults to the current database. ``` -------------------------------- ### Configure Minimum Query Time Threshold Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/tgdba/auto_material_views.html Sets the minimum execution time in seconds for a query to be considered for recommendation. ```sql exec dbms_auto_mv.configure('AUTO_MV_ANALYZE_WORKLOAD_MIN_TIME', 1800); ``` -------------------------------- ### Configure Oracle Precompiler Cursor Clauses Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/tgdba/tuning-shared-pool-and-large-pool.html Set these clauses on the command line or within the program to control cursor lifecycle behavior. ```text HOLD_CURSOR = YES RELEASE_CURSOR = NO MAXOPENCURSORS = desired_value ``` -------------------------------- ### Identify Database Instances for AWR Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/tgdba/generating-automatic-workload-repository-reports.html Displays available database identifiers and instance numbers for selection. ```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 ``` -------------------------------- ### Querying Session Memory Statistics Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/tgdba/tuning-shared-pool-and-large-pool.html Use these SQL queries to calculate the total current and maximum memory allocated across all user sessions. ```sql SELECT SUM(value) || ' bytes' "total memory for all sessions" FROM V$SESSTAT, V$STATNAME WHERE name = 'session uga memory' AND V$SESSTAT.STATISTIC# = V$STATNAME.STATISTIC#; ``` ```sql SELECT SUM(value) || ' bytes' "total max mem for all sessions" FROM V$SESSTAT, V$STATNAME WHERE name = 'session uga memory max' AND V$SESSTAT.STATISTIC# = V$STATNAME.STATISTIC#; ``` -------------------------------- ### Specify Export File Name Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/tgdba/transporting-automatic-workload-repository-data.html Provide the base name for the export dump file; the .dmp extension is appended automatically. ```text Enter value for file_name: awrdata_30_40 ``` -------------------------------- ### Set FAST_START_MTTR_TARGET Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/tgdba/instance-tuning-using-performance-views.html Configures the target mean time to recover for the database instance. ```sql ALTER SYSTEM SET FAST_START_MTTR_TARGET=30; ```