SHARE
TWEET

Untitled

a guest Jul 17th, 2019 62 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. -- To run on Oracle 18c
  2. -- sqlplus hr/hr@//localhost:1521/XEPDB1 @count_rows.sql
  3.  
  4. SET ECHO ON
  5.  
  6. SPOOL count_rows.out
  7.  
  8. WHENEVER SQLERROR EXIT
  9. DECLARE
  10.    l_username user_users.USERNAME%TYPE;
  11.    l_tablecount NUMBER;
  12. BEGIN
  13.  
  14.    SELECT username
  15.      INTO l_username
  16.      FROM user_users
  17.         ;
  18.  
  19.    IF l_username != 'HR' THEN
  20.       raise_application_error(-20000, 'Can only be used with HR user');
  21.    END IF;
  22.  
  23.    SELECT COUNT(*) -- Avoid "ORA-01403: no data found" by counting
  24.      INTO l_tablecount
  25.      FROM user_tables
  26.     WHERE table_name = 'TEST_STATS_ESTIMATE'
  27.         ;
  28.  
  29.    IF l_tablecount = 1 THEN
  30.       raise_application_error(-20001, 'Table name collision, please rename or drop existing TEST_STATS_ESTIMATE');
  31.    END IF;
  32. END;
  33. /
  34. WHENEVER SQLERROR CONTINUE
  35.  
  36. ALTER SESSION SET NLS_DATE_FORMAT = 'YYYYMMDD';
  37. COLUMN last_analyzed FORMAT 99999999 HEADING 'Last|Analyzed'
  38. COLUMN num_rows      FORMAT 99999    HEADING 'Num|Rows'
  39. COLUMN table_name    FORMAT a19
  40.  
  41. -- Start with no stats
  42. EXECUTE DBMS_STATS.DELETE_SCHEMA_STATS(ownname=>'HR');
  43.  
  44. -- Show num_rows is NULL without stats
  45. SELECT table_name, num_rows, last_analyzed
  46.   FROM user_tables;
  47.  
  48. SET TIMING ON
  49. -- Gather stats on a single table, timed
  50. EXECUTE DBMS_STATS.GATHER_TABLE_STATS(ownname=>'HR', tabname=>'DEPARTMENTS', estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE, cascade=>TRUE, degree=>DBMS_STATS.DEFAULT_DEGREE, method_opt=>'FOR ALL COLUMNS SIZE AUTO');
  51. SET TIMING OFF
  52.  
  53. -- See that one table now has a num_rows populated
  54. SELECT table_name, num_rows, last_analyzed
  55.   FROM user_tables;
  56.  
  57. SET TIMI ON
  58. -- Gather stats for the schema, timed
  59. EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'HR', cascade => TRUE, options => 'GATHER AUTO');
  60. SET TIMI OFF
  61.  
  62. -- All tables have num_rows populated
  63. SELECT table_name, num_rows, last_analyzed
  64.   FROM user_tables;
  65.  
  66. -- Create a new 10K row table to demo how stats are an estimate, not a count
  67. CREATE TABLE test_stats_estimate AS
  68. SELECT LEVEL id, CAST(DBMS_RANDOM.string('a',100) AS VARCHAR2(100)) txt FROM dual
  69. CONNECT BY LEVEL <= 10000;
  70.  
  71. -- For a 10% sample for demo, can't use DBMS_STATS.AUTO_SAMPLE_SIZE here as the table is too small and Oracle will pick 100%
  72. EXECUTE DBMS_STATS.GATHER_TABLE_STATS(ownname=>'HR', tabname=>'TEST_STATS_ESTIMATE', estimate_percent=>10, cascade=>TRUE, degree=>DBMS_STATS.DEFAULT_DEGREE, method_opt=>'FOR ALL COLUMNS SIZE AUTO');
  73.  
  74. -- Note: We know there are 10K rows in this table but due to estimation, a different number is shown
  75. SELECT table_name, num_rows, last_analyzed FROM user_tables WHERE table_name LIKE 'TEST%';
  76.  
  77. -- What happens if we delete half the rows?
  78. DELETE FROM test_stats_estimate WHERE ROWNUM <= 5000;
  79. COMMIT;
  80. -- Verify the rows are gone
  81. SELECT COUNT(*) FROM test_stats_estimate;
  82. -- Note: no change to num_rows
  83. SELECT table_name, num_rows, last_analyzed FROM user_tables WHERE table_name LIKE 'TEST%';
  84.  
  85. -- Direct path insert another 10K rows
  86. INSERT /*+ APPEND */ INTO test_stats_estimate
  87. SELECT LEVEL id, CAST(DBMS_RANDOM.string('a',100) AS VARCHAR2(100)) txt FROM dual
  88. CONNECT BY LEVEL <= 10000;
  89. COMMIT;
  90. -- Note: Again no change to num_rows
  91. SELECT table_name, num_rows, last_analyzed FROM user_tables WHERE table_name LIKE 'TEST%';
  92.                                                        
  93. -- We can even truncate the table and see the num rows is still unchanged
  94. TRUNCATE TABLE test_stats_estimate;
  95. SELECT COUNT(*) FROM test_stats_estimate;
  96. SELECT table_name, num_rows, last_analyzed FROM user_tables WHERE table_name LIKE 'TEST%';
  97.  
  98. -- Modifications are tracked in user_tab_modifications
  99. -- Th ability to turn off monitoring (ALTER TABLE x NOMONITORING) was deprecated in 11g
  100. COLUMN inserts   FORMAT 99999
  101. COLUMN updates   FORMAT 99999
  102. COLUMN deletes   FORMAT 99999
  103. COLUMN truncated FORMAT a9
  104. SELECT table_name, inserts, updates, deletes, truncated FROM user_tab_modifications;
  105.  
  106. -- The user_tab_modifications details are summarized into stale_stats
  107. COLUMN stale_stats FORMAT a11
  108. SELECT table_name, num_rows, last_analyzed, stale_stats FROM user_tab_statistics;
  109.  
  110. -- Ask Oracle to gather stale stats
  111. EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS (ownname => 'HR', cascade => TRUE, options => 'GATHER AUTO');
  112. -- Note: Accurate num_rows estimate
  113. SELECT table_name || NULLIF(' : ' || partition_name,' : ') table_name, num_rows, last_analyzed, stale_stats FROM user_tab_statistics;
  114.  
  115.  
  116. DROP TABLE test_stats_estimate;
  117.  
  118. EXIT
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top