Pages

Sunday, October 29, 2023

DBMS_STATS Starhome Stuff

BEGIN
 DBMS_STATS.GATHER_SCHEMA_STATS ('SYS');
END;
/

BEGIN
 DBMS_STATS.GATHER_DICTIONARY_STATS;
END;
/

BEGIN
 DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
END;
/


BEGIN
  DBMS_STATS.gather_table_stats
  (ownname=>'XXX', 
   tabname=>'GSM_IMSI_PREFIXES', 
   estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
   cascade => DBMS_STATS.AUTO_CASCADE );
END;
/

BEGIN
  DBMS_STATS.gather_table_stats
  (ownname=>'XXX', 
   tabname=>'GSM_COUNTRIES', 
   estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
   cascade => DBMS_STATS.AUTO_CASCADE );
END;
/

BEGIN
  DBMS_STATS.gather_table_stats
  (ownname=>'XXX', 
   tabname=>'GSM_NETWORKS_T', 
   estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
   cascade => DBMS_STATS.AUTO_CASCADE );
END;
/

BEGIN
  DBMS_STATS.gather_table_stats
  (ownname=>'XXX', 
   tabname=>'GSM_MERGE_NETWORKS', 
   estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
   cascade => DBMS_STATS.AUTO_CASCADE );
END;
/

BEGIN
  DBMS_STATS.gather_table_stats
  (ownname=>'XXX', 
   tabname=>'GSM_MERGE_NW_MAPPING', 
   estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
   cascade => DBMS_STATS.AUTO_CASCADE );
END;
/

BEGIN
  DBMS_STATS.gather_table_stats
  (ownname=>'XXX', 
   tabname=>'GSM_NETWORKS_SEG_T', 
   estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
   cascade => DBMS_STATS.AUTO_CASCADE );
END;
/

BEGIN
  DBMS_STATS.gather_table_stats
  (ownname=>'XXX', 
   tabname=>'GA_LANGUAGES', 
   estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
   cascade => DBMS_STATS.AUTO_CASCADE );
END;
/

BEGIN
  DBMS_STATS.gather_table_stats
  (ownname=>'XXX', 
   tabname=>'GSM_SERVICE_MERGED_NETWORKS', 
   estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
   cascade => DBMS_STATS.AUTO_CASCADE );
END;
/

BEGIN
  DBMS_STATS.gather_table_stats
  (ownname=>'XXX', 
   tabname=>'GSM_NETWORKS_EXCEPTIONS', 
   estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
   cascade => DBMS_STATS.AUTO_CASCADE );
END;
/

BEGIN
  DBMS_STATS.gather_table_stats
  (ownname=>'XXX', 
   tabname=>'GA_ACCOUNTS', 
   estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
   cascade => DBMS_STATS.AUTO_CASCADE );
END;
/

=========================
See execution plan of an SQL
=========================
ALTER SYSTEM FLUSH SHARED_POOL;

SELECT sql_id, child_number 
  FROM V$SQL 
WHERE sql_id = 'fw4tmpkt79r4r';

SELECT sql_id, child_number 
  FROM V$SQL 
WHERE sql_text LIKE 'MERGE into SGA_W_IPN_SUBSCRIBER o using%';


SET LINESIZE 120
SET PAGESIZE 0
SELECT * FROM TABLE(DBMS_XPLAN.display_cursor('fw4tmpkt79r4r',3,'OUTLINE'));


=========================
How to Flush a single SQL
=========================
--DBA_HIST_ACTIVE_SESS_HISTORY 
SELECT * FROM DBA_HIST_ACTIVE_SESS_HISTORY 
 WHERE sql_id IN ('ccgg2ap67ptbp','0gn14c20ay0qs') 
   AND SNAP_ID = (SELECT MAX(snap_id) from DBA_HIST_SNAPSHOT);

--V$SESSION
SELECT * FROM V$SESSION 
 WHERE sql_id IN ('ccgg2ap67ptbp','0gn14c20ay0qs');

--V$SQLAREA 
SELECT sql_text, sql_id, last_active_time, executions, 
       disk_reads, buffer_gets, user_io_wait_time
FROM V$SQLAREA 
WHERE sql_id IN ('ccgg2ap67ptbp','0gn14c20ay0qs');

--Flush Shared Pool for SQLs in question
SELECT 'EXEC DBMS_SHARED_POOL.PURGE ('''||ADDRESS||','||HASH_VALUE||''',''C'');' FROM  V$SQLAREA where SQL_Id='ccgg2ap67ptbp';
SELECT 'EXEC DBMS_SHARED_POOL.PURGE ('''||ADDRESS||','||HASH_VALUE||''',''C'');' FROM  V$SQLAREA where SQL_Id='0gn14c20ay0qs';

SQL> EXEC DBMS_SHARED_POOL.PURGE ('00000000FD2BC510,1283122549','C');

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_SHARED_POOL.PURGE ('000000009A0252B8,2158953176','C');

PL/SQL procedure successfully completed.

No comments:

Post a Comment