Pages

Monday, June 10, 2024

SQLs for Tuning Instance

------------------------------
-- See Memory Allocation
------------------------------

SET PAGESIZE 1000
SET LINESIZE 120

SELECT snap_id, name, ROUND(value/1024/1024) AS MB
  FROM STATS$PGASTAT
 WHERE snap_id = (SELECT MAX(snap_id)  FROM STATS$PGASTAT)
 ORDER BY snap_id;

   SNAP_ID NAME                                             MB
---------- ---------------------------------------- ----------
     44400 PGA memory freed back to OS                  816563
     44400 aggregate PGA auto target                      4712
     44400 aggregate PGA target parameter                 5728
     44400 bytes processed                             1615482
     44400 cache hit percentage                              0
     44400 global memory bound                             573
     44400 max processes count                               0
     44400 maximum PGA allocated                          1397
     44400 maximum PGA used for auto workareas             577
     44400 maximum PGA used for manual workareas             1
     44400 process count                                     0
     44400 recompute count (total)                           1
     44400 total PGA allocated                             824
     44400 total PGA inuse                                 493
     44400 total freeable PGA memory                       225

------------------------------
-- See Oracle ORA Errors
------------------------------

A. See errors from last 3 days
SELECT originating_timestamp, module_id, message_text
  FROM X$DBGALERTEXT
 WHERE originating_timestamp > SYSDATE-3
   AND message_text like '%ORA-%';

21-JAN-21 06.50.06.639 AM +08:00
JDBC Thin Client
ORA-00060: Deadlock detected. More info in file /software/oracle/diag/rdbms/igt/igt/trace/igt_ora_29669.trc.
29-JAN-21 06.50.02.484 AM +08:00
JDBC Thin Client
ORA-00060: Deadlock detected. More info in file /software/oracle/diag/rdbms/igt/igt/trace/igt_ora_28443.trc.
02-FEB-21 06.50.07.884 AM +08:00
JDBC Thin Client
ORA-00060: Deadlock detected. More info in file /software/oracle/diag/rdbms/igt/igt/trace/igt_ora_29669.trc.
09-FEB-21 06.50.06.381 AM +08:00
JDBC Thin Client
ORA-00060: Deadlock detected. More info in file /software/oracle/diag/rdbms/igt/igt/trace/igt_ora_28443.trc.
10-FEB-21 06.50.03.655 AM +08:00
JDBC Thin Client
ORA-00060: Deadlock detected. More info in file /software/oracle/diag/rdbms/igt/igt/trace/igt_ora_28443.trc.

B.
SELECT * FROM SYS.WRI$_ALERT_HISTORY

------------------------------
-- Tune SGA Size
------------------------------
SELECT sga_size,
       sga_size_factor * 100 f,
       estd_db_time t,
       estd_physical_reads p
FROM   V$SGA_TARGET_ADVICE 
ORDER BY sga_size;
sga_size   sga_size_factor estd_db_time estd_physical_reads
---------- --------------- ------------ -------------------
      2664              25       455644           157059680
      3996            37.5        80093           157059680
      5328              50        73654           100220168
      6660            62.5        70855            88140809
      7992              75        69378            78183215
      9324            87.5        68435            74716964
     10656             100        67778            70025271
     11988           112.5        67114            66180884
     13320             125        66741            63519923
     14652           137.5        66178            56965558
     15984             150        65548            49829983
     17316           162.5        65379            49829983
     18648             175        65379            42981511
     19980           187.5        65379            42981511
     21312             200        65379            42981511
15 rows selected.
------------------------------
-- Tune Cache Hit Ratio
------------------------------
SET PAGESIZE 1000
SET LINESIZE 120
SELECT snap_id, 
       pga_target_factor,
       ROUND(pga_target_for_estimate/1024/1024) target_mb,
       estd_pga_cache_hit_percentage cache_hit_perc
 FROM STATS$PGA_TARGET_ADVICE
 WHERE snap_id > (SELECT MAX(snap_id) - 30 FROM STATS$PGA_TARGET_ADVICE)
 ORDER BY snap_id
 
    SNAP_ID PGA_TARGET_FACTOR  TARGET_MB CACHE_HIT_PERC 
 ---------- ----------------- ---------- --------------
     44371              .125        716             97
     44371               .25       1432             98
     44371                .5       2864             99
     44371               .75       4296             99
     44371                 1       5728            100
     44371               1.2       6874            100
     44371               1.4       8019            100
     44371               1.6       9165            100
     44371               1.8      10310            100
     44371                 2      11456            100
     44371                 3      17184            100
     44371                 4      22912            100
     44371                 6      34368            100
     44371                 8      45824            100
     44400              .125        716             97
     44400               .25       1432             98
     44400                .5       2864             99
     44400               .75       4296             99
     44400                 1       5728            100
     44400               1.2       6874            100
     44400               1.4       8019            100
     44400               1.6       9165            100
     44400               1.8      10310            100
     44400                 2      11456            100
     44400                 3      17184            100
     44400                 4      22912            100
     44400                 6      34368            100
     44400                 8      45824            100
  
-----------------------------
 Explain Plan for SQL
-----------------------------
explain plan for
SELECT ROWID FROM MY_USER.GA_W_COUNTERS_HISTORY
WHERE TS_LAST_MODIFIED <sysdate-1 AND ROWNUM < (10 +1); 

SET LINESIZE 130
SET PAGESIZE 0
SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY);

SQL> SET LINESIZE 130
SET PAGESIZE 0
SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY);SQL> SQL>   2
Plan hash value: 2655254229

-------------------------------------------------------------------------------------
| Id  | Operation         | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                           |    10 |   200 |     3   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY    |                           |       |       |            |          |
|*  2 |   INDEX RANGE SCAN| GWCH_TS_LAST_MODIFIED_IDX |    10 |   200 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<11)
   2 - access("TS_LAST_MODIFIED"<SYSDATE@!-1)

15 rows selected.