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;
/
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;
/
(ownname=>'XXX',
tabname=>'GA_LANGUAGES',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
cascade => DBMS_STATS.AUTO_CASCADE );
END;
/
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;
/
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;
/
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;
/
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
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