------------------------------
-- 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
------------------------------
WHERE originating_timestamp > SYSDATE-3
AND message_text like '%ORA-%';
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.
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
------------------------------
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
-----------------------------
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.
