Guest User

Untitled

a guest
Jan 17th, 2018
72
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 3.66 KB | None | 0 0
  1. <<rows per block>> =
  2. floor( <<database block size>> *
  3. (1 - <<pctfree of table>>/100) /
  4. <<size of row>> ) + 1
  5.  
  6. <<size of table>> =
  7. ceil( <<number of rows in table>> / <<rows per block>> ) *
  8. <<database block size>>
  9.  
  10. SQL> create table foo(
  11. 2 foo_id number,
  12. 3 foo_str varchar2(100)
  13. 4 );
  14.  
  15. Table created.
  16.  
  17. <<rows per block>> =
  18. floor( 8192 *
  19. (1 - 10/100) /
  20. 56 ) + 1
  21.  
  22. <<size of table>> =
  23. ceil( 1000000 / 132 ) *
  24. 8192
  25.  
  26. SQL> ed
  27. Wrote file afiedt.buf
  28.  
  29. 1 insert into foo
  30. 2 select level, dbms_random.string( 'p', dbms_random.value(1,100))
  31. 3 from dual
  32. 4* connect by level <= 1000000
  33. SQL> /
  34.  
  35. 1000000 rows created.
  36.  
  37. SQL> exec dbms_stats.gather_table_stats( 'SCOTT', 'FOO' );
  38.  
  39. PL/SQL procedure successfully completed.
  40. SQL> ed
  41. Wrote file afiedt.buf
  42.  
  43. 1 select avg_row_len, num_rows
  44. 2 from user_tables
  45. 3* where table_name = 'FOO'
  46. SQL> /
  47.  
  48. AVG_ROW_LEN NUM_ROWS
  49. ----------- ----------
  50. 56 1000000
  51.  
  52. SQL> select sum(bytes)/1024/1024 mb
  53. 2 from user_segments
  54. 3 where segment_name = 'FOO';
  55.  
  56. MB
  57. ----------
  58. 72
  59.  
  60. SQL> ed
  61. Wrote file afiedt.buf
  62.  
  63. 1 select bytes, count(*)
  64. 2 from user_extents
  65. 3 where segment_name = 'FOO'
  66. 4 group by bytes
  67. 5* order by bytes
  68. SQL> /
  69.  
  70. BYTES COUNT(*)
  71. ---------- ----------
  72. 65536 16
  73. 1048576 63
  74. 8388608 1
  75.  
  76. SQL> ed
  77. Wrote file afiedt.buf
  78.  
  79. 1 DECLARE
  80. 2 l_space_used NUMBER;
  81. 3 l_space_allocated NUMBER;
  82. 4 l_chained_pct NUMBER;
  83. 5 BEGIN
  84. 6 dbms_space.object_space_usage(
  85. 7 'SCOTT',
  86. 8 'FOO',
  87. 9 'TABLE',
  88. 10 NULL,
  89. 11 l_space_used,
  90. 12 l_space_allocated,
  91. 13 l_chained_pct);
  92. 14 dbms_output.put_line('Space Used: ' || TO_CHAR(round(l_space_used/1024/1024,2)) || ' MB');
  93. 15 dbms_output.put_line('Space Allocated: ' || TO_CHAR(l_space_allocated/1024/1024) || ' MB');
  94. 16 dbms_output.put_line('Chained Percentage: ' || TO_CHAR(l_chained_pct));
  95. 17* END;
  96. SQL> /
  97. Space Used: 66.22 MB
  98. Space Allocated: 72 MB
  99. Chained Percentage: 0
  100.  
  101. PL/SQL procedure successfully completed.
  102.  
  103. SQL> ed
  104. Wrote file afiedt.buf
  105.  
  106. 1 DECLARE
  107. 2 l_used_bytes NUMBER;
  108. 3 l_allocated_bytes NUMBER;
  109. 4 BEGIN
  110. 5 dbms_space.create_table_cost('USERS',
  111. 6 56,
  112. 7 1000000,
  113. 8 10,
  114. 9 l_used_bytes,
  115. 10 l_allocated_bytes);
  116. 11 dbms_output.put_line('Used Bytes: ' || TO_CHAR(round(l_used_bytes/1024/1024,2)) || ' MB');
  117. 12 dbms_output.put_line('Alloc Bytes: ' || TO_CHAR(l_allocated_bytes/1024/1024) || ' MB');
  118. 13* END;
  119. SQL> /
  120. Used Bytes: 63.52 MB
  121. Alloc Bytes: 64 MB
  122.  
  123. PL/SQL procedure successfully completed.
  124.  
  125. SQL> ed
  126. Wrote file afiedt.buf
  127.  
  128. 1 DECLARE
  129. 2 l_used_bytes NUMBER;
  130. 3 l_allocated_bytes NUMBER;
  131. 4 l_cols sys.create_table_cost_columns;
  132. 5 BEGIN
  133. 6 l_cols := sys.create_table_cost_columns(
  134. 7 sys.create_table_cost_colinfo('NUMBER',10),
  135. 8 sys.create_table_cost_colinfo('VARCHAR2',100));
  136. 9 dbms_space.create_table_cost('USERS',
  137. 10 l_cols,
  138. 11 1000000,
  139. 12 10,
  140. 13 l_used_bytes,
  141. 14 l_allocated_bytes);
  142. 15 dbms_output.put_line('Used Bytes: ' || TO_CHAR(round(l_used_bytes/1024/1024,2)) || ' MB');
  143. 16 dbms_output.put_line('Alloc Bytes: ' || TO_CHAR(l_allocated_bytes/1024/1024) || ' MB');
  144. 17* END;
  145. SQL> /
  146. Used Bytes: 67.94 MB
  147. Alloc Bytes: 72 MB
  148.  
  149. PL/SQL procedure successfully completed.
Add Comment
Please, Sign In to add comment