Transcript Single Table Clusters, an alternative to partitioning?

Help! I have far too many extents
(What is smon doing?)
David Kurtz
Go-Faster Consultancy Ltd.
[email protected]
www.go-faster.co.uk
Go-Faster Consultancy Ltd.
1
Who am I?
• DBA
– Independent Consultant
– Performance Tuning
• Oracle/PeopleSoft
Go-Faster Consultancy Ltd.
2
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.
3
What happened next?
• First time
• Second time
– left smon to get on
with it.
– three days
– we couldn’t wait
– we came up with a
more imaginative
solution.
– which I will tell you
about later
Go-Faster Consultancy Ltd.
4
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.
5
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.
6
What did Oracle suggest?
• TRUNCATE TABLE REUSE STORAGE
• ALTER TABLE … DEALLOCATE
UNUSED KEEP nnn M;
• ALTER TABLESPACE … COALESCE;
• DROP TABLE …;
Go-Faster Consultancy Ltd.
7
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.
8
Performance of Deallocate
Deallocate time -v- Number of Deallocates
30
25
Time (seconds)
20
Parse
15
Execute
Fetch
10
5
0
0
10
20
30
40
50
60
70
Number of Deallocates
Go-Faster Consultancy Ltd.
9
What happens when you…
•
•
•
•
Drop Table?
Coalesce Tablespace?
Truncate Table?
Truncate Table Reuse Storage?
• and what does smon do?
Go-Faster Consultancy Ltd.
10
How do you find out what is
happening?
• SQL_TRACE
• Issue the command
• TKPROF
– sys = yes
– recursive SQL
Go-Faster Consultancy Ltd.
11
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.
12
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.
13
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.
14
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.
15
Construct a test
• Create a large tablespace
CREATE TABLESPACE silly
DATAFILE 'e:\ps\db\ora816d\data\silly.dbf' size 800m;
Go-Faster Consultancy Ltd.
16
Disable SMON
• Most of the time smon looks for work
select
from
where
and
and
f.file#, f.block#, f.ts#, f.length
fet$ f,
ts$ t
t.ts#=f.ts#
t.dflextpct!=0
t.bitmapped=0
• Disable SMON
ALTER TABLESPACE silly DEFAULT STORAGE(PCTINCREASE
0);
Go-Faster Consultancy Ltd.
17
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.
18
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.
19
Capture Recursive SQL
• Enable SQL_TRACE
– Drop Table
– Coalesce Tablespace
– Measure timings from TKPROF
Go-Faster Consultancy Ltd.
20
DDL Time -v- Number of Extents
Go-Faster Consultancy Ltd.
21
DDL Time -v- Number of Extents
Go-Faster Consultancy Ltd.
22
Conclusion
• 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.
Go-Faster Consultancy Ltd.
23
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.
24
Questions?
Go-Faster Consultancy Ltd.
25
Help! I have far too many extents
(What is smon doing?)
David Kurtz
Go-Faster Consultancy Ltd.
[email protected]
www.go-faster.co.uk
Go-Faster Consultancy Ltd.
26