1 / 26

Help! I have far too many extents (What is smon doing?)

Help! I have far too many extents (What is smon doing?). David Kurtz Go-Faster Consultancy Ltd. david.kurtz@go-faster.co.uk www.go-faster.co.uk. Who am I?. DBA Independent Consultant Performance Tuning Oracle/PeopleSoft. What is this all about?. A couple of months I made a mistake

bracha
Download Presentation

Help! I have far too many extents (What is smon doing?)

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Help! I have far too many extents (What is smon doing?) David Kurtz Go-Faster Consultancy Ltd. david.kurtz@go-faster.co.uk www.go-faster.co.uk Go-Faster Consultancy Ltd.

  2. Who am I? • DBA • Independent Consultant • Performance Tuning • Oracle/PeopleSoft Go-Faster Consultancy Ltd.

  3. What is this all about? • A couple of months I made a mistake • created a table without a storage clause. • due to a late night coding error • instead of 100’s of rows • actually had 10,000,000’s of rows • 182,000 extents • so we dropped the table • 182,000 free extents Go-Faster Consultancy Ltd.

  4. First time left smon to get on with it. three days Second time we couldn’t wait we came up with a more imaginative solution. which I will tell you about later What happened next? Go-Faster Consultancy Ltd.

  5. Why couldn’t we wait for smon? • This occurred on a migration database • Significant amounts of DDL • COALESCE took out, and held, a lock on the space transaction enqueue • SMON took out ST lock while it was busy. • Certain create statements cause a coalesce. • Only one ST lock allowed. Go-Faster Consultancy Ltd.

  6. Why not just drop tablespace? • It appears that dropping and empty tablespace also coalesces the tablespace. • Trace reveals much of same SQL • Don’t know why • uet$ already empty • All that is necessary to delete fet$ Go-Faster Consultancy Ltd.

  7. What did Oracle suggest? • TRUNCATE TABLE REUSE STORAGE • ALTER TABLE … DEALLOCATE UNUSED KEEP nnn M; • ALTER TABLESPACE … COALESCE; • DROP TABLE …; Go-Faster Consultancy Ltd.

  8. Construct a test • Create a table with 512 8k extents. • Try deallocate and coalesce in n parts. • 1,2,4,8 etc parts • measure time, physical I/O, CPU Go-Faster Consultancy Ltd.

  9. Performance of Deallocate Go-Faster Consultancy Ltd.

  10. What happens when you… • Drop Table? • Coalesce Tablespace? • Truncate Table? • Truncate Table Reuse Storage? • and what does smon do? Go-Faster Consultancy Ltd.

  11. How do you find out what is happening? • SQL_TRACE • Issue the command • TKPROF • sys = yes • recursive SQL Go-Faster Consultancy Ltd.

  12. DROP TABLE • delete • all catalogue information about table and its indexes • including 9 tables in C_OBJ# cluster • UET$ used extent table • insert • FET$ - free extent table • update TSQ$ - tablespace quota Go-Faster Consultancy Ltd.

  13. COALESCE TABLESPACE • Only 4 SQL statements • Select from FET$ • select file#, block#, ts#, length from fet$ where ts# = :1 • select length from fet$ where file#=:1 and block#=:2 and ts#=:3 Go-Faster Consultancy Ltd.

  14. COALESCE TABLESPACE • Delete from FET$, or • delete from fet$ where file#=:1 and block#=:2 and ts#=:3 • Update FET$ set LENGTH# • update fet$ set length=:4 where file#=:1 and block#=:2 and ts# = :3 Go-Faster Consultancy Ltd.

  15. Is this a problem? • Is having lots of extents a problem? • Oracle say: • Not a significant issue during DML operations. • It can be a problem during space management • TRUNCATE TABLE • DROP TABLE • ALTER TABLESPACE … COALESCE • DROP TABLESPACE Go-Faster Consultancy Ltd.

  16. Construct a test • Create a large tablespace CREATE TABLESPACE silly DATAFILE 'e:\ps\db\ora816d\data\silly.dbf' size 800m; Go-Faster Consultancy Ltd.

  17. Disable SMON • Most of the time smon looks for work select f.file#, f.block#, f.ts#, f.length from fet$ f, ts$ t where t.ts#=f.ts# and t.dflextpct!=0 and t.bitmapped=0 • Disable SMON ALTER TABLESPACE silly DEFAULT STORAGE(PCTINCREASE 0); Go-Faster Consultancy Ltd.

  18. Create a badly sized table CREATE TABLE silly_tab ( value VARCHAR2(4000) , value2 VARCHAR2(4000) ) TABLESPACE silly PCTFREE 98 PCTUSED 1 STORAGE (INITIAL 8k NEXT 8k PCTINCREASE 0 MAXEXTENTS UNLIMITED); Go-Faster Consultancy Ltd.

  19. Populate the table INSERT INTO silly_tab SELECT RPAD(TO_CHAR(TO_DATE(rownum,'j'),'jsp'),4000,'.') , RPAD(TO_CHAR(TO_DATE(rownum,'j'),'jsp'),3000,'.') FROM all_objects WHERE rownum <= 10000 • or create extents with MINEXTENTS Go-Faster Consultancy Ltd.

  20. Capture Recursive SQL • Enable SQL_TRACE • Drop Table • Coalesce Tablespace • Measure timings from TKPROF Go-Faster Consultancy Ltd.

  21. DDL Time -v- Number of Extents Go-Faster Consultancy Ltd.

  22. DDL Time -v- Number of Extents Go-Faster Consultancy Ltd.

  23. Time taken to drop and coalesce goes up more than linearly with the number of extents. Keep the number of extents manageable mainly for administrative reasons but also because coalesce could cause a looking problem in some scenarios. Conclusion Go-Faster Consultancy Ltd.

  24. Do not try this at home • This is COMPLETELY UNSUPPORTED • This is how we coalesced our 182,000 extents. • drop object • tablespace now empty • DELETE FROM FET$ WHERE TS# = ... • drop tablespace (quickly) • recreate tablespace Go-Faster Consultancy Ltd.

  25. Questions? Go-Faster Consultancy Ltd.

  26. Help! I have far too many extents (What is smon doing?) David Kurtz Go-Faster Consultancy Ltd. david.kurtz@go-faster.co.uk www.go-faster.co.uk Go-Faster Consultancy Ltd.

More Related