CODEWITHSUNDEEP
performanceoracle-databaseoracle11gindexingdatabase-migration
Fernando De Melo Braga
Fernando De Melo Braga

Reputation: 65

How to use currently available memory to minimize CREATE INDEX time?

I'll need to migrate our main database to a new server and storage subsystem in a couple of weeks. Oracle 11 is currently running on Windows, and we will install a brand new SuSE for it. There will be no other major changes. Memory will be the same, and the server is just a bit newer.

My main concern is with the time it will take to create indexes. Our last experience recreating some indexes took very long, and since then I'm researching how to optimize it.

The current server has 128GB of memory and we're using Oracle ASSM (51GB for SGA and 44GB for PGA), and Spotlight On Oracle reports no physical read activity on datafiles. Everything is cached on memory, and the performance is great. Spotlight also informs that PGA consumption is only 500 MB.

I know my biggest table has 100 million rows, and occupy 15GB. Its indexes, however, occupy 53GB. When I recreate one of these, I can see a lot of write activity in the TEMP tablespace.

So the question is: how can I use all available memory in order to minimize TEMP activity ?

I'm not really sure if this is relevant, but we see an average of 300-350 users connections, and I raised initialization parameters to 700 max sessions.

Best regards,

Upvotes: 2

Views: 243

Answers (1)

Mark J. Bobak
Mark J. Bobak

Reputation: 14403

You should consider setting WORKAREA_SIZE_POLICY to MANUAL for the session that will be doing the index rebuilds, and then setting SORT_AREA_SIZE to a sufficiently large number. (Max is O/S dependent, but 2GB would be a good starting point.)

Also, though you didn't make any mention of it, you should also consider using NOLOGGING to improve performance.

Hope that helps.

Upvotes: 1

Related Questions