Eliminate Multiple Datafiles within Tablespace 2

[rasprer]

Since my question basically involves an Exp/Imp issue..I will address it here.

I have some instances where I have mulitiple datafiles within a tablespace. I actually want to only have one datafile and eliminate the others since they are basically unused.

My question is this...I have exported a user with the compress=y option. When I initialize the import, will it recreate the additional datafiles or will it only create 1 which is what I want. Also, for instance I do have a tablespace currently with a size of 384mb with mulitple database files..1 being 128Mb with 127MB being used, and second file 256Mb with 67Mb used. When creating the tablespace...should I initially size the tablespace between 384-400MB.

Suggestions and advises please.

J

 

[rasprer]

CARP..Thank you. Got my answer. Perfect.

I just executed the above steps in this order under the SYS user.

1. created swiss_cheese_table.
2. created package and body
3. exec swiss_cheese.assess_schema('MySchema','HI', 5);
4. selected all tables withen a tablespaces and executed..
exec swiss_cheese.assess_table('MySchema', tables, 'HI',5);
5. exec swiss_cheese.assess_tablespace('TblSpace', ‘HI’, 5);
6. exec swiss_cheese.assess_database(HI, 5);
7. ran the assestment query but include a where condition to only include the tablespace used in (step 4)swiss_cheese.assess_table

results below.

Reserved
Table Current Est. Est. Space Chained
Name Blocks Blocks Savings Used Rows
------------------------- ---------- ---------- ---------- ---------- --------
TABLE1 225 238 -13 40 0
TABLE2 409 415 -6 17 0
TABLE3 143 129 14 0 0
TABLE4 685 636 49 0 0


For table1, I would issue:
exec swiss_cheese.REORG('MySchema', 'Table1');
... and
For tables 2 - 4, I would issue:
exec swiss_cheese.REORG('MySchema', 'Table2',409);
exec swiss_cheese.REORG('MySchema', 'Table3',143);
exec swiss_cheese.REORG('MySchema', 'Table4',685);

...Is my interpretation correct? Your advise,please.

 

[engineer2100]

Carp, Mufasa,

Does a positive Est. Savings a good sign against a negative value?

-Engi

 

[rasprer]

Carp/Santa...I have just executed the REORG. Results below


Reserved
Table Current Est. Est. Space Chained
Name Blocks Blocks Savings Used Rows
------------------------- ---------- ---------- ---------- ---------- --------
TABLE1 222 242 -20 65 0
TABLE3 140 129 11 0 0
TABLE4 683 617 66 0 0

... I am assuming that the reason why TABLE2 is no longer printed..this table and its indexes have be reorganized.

For Table1, 3, and 4, although it is still being marked as 'need defraging', it has indeed been defraged. But can you (Carp/Santa) elaborate again the results above in regards to the slight decrease in 'blocks' and an increase/decrease in 'EST Blocks' and 'Estimated Savings'?

 

[rasprer]

Carp/Santa...I have just executed the REORG. Results below


Reserved
Table Current Est. Est. Space Chained
Name Blocks Blocks Savings Used Rows
------------------------- ---------- ---------- ---------- ---------- --------
TABLE1 222 242 -20 65 0
TABLE3 140 129 11 0 0
TABLE4 683 617 66 0 0

... I am assuming that the reason why TABLE2 is no longer printed..this table and its indexes have be reorganized.

For Table1, 3, and 4, although it is still being marked as 'need defraging', it has indeed been defraged. But can you (Carp/Santa) elaborate again the results above in regards to the slight decrease in 'blocks' and an increase/decrease in 'EST Blocks' and 'Estimated Savings', and 'Space Used'? Clarification would be appreciated.

 

[carp]

rasperer -
Table2 disappears because its potential gains from reorganizing have dropped below your threshold.
The changes in the remaining table may be due to relatively old statistics being used the first time through. If the stats have been gathered within the past two weeks, the code uses whatever you have. Consequently, the stats may not reflect reality. During reorg, stats are regathered. The combination of fresh stats and a freshly organized table would be the most likely sources of changes in statistics.

engi -
A negative savings value means it is likely that reorganizing your table will result in more blocks being used. A positive value means reorganizing will probably result in fewer blocks used. Which is "good" depends on what you are trying to accomplish.

 

[engineer2100]

Carp,
I want to know whether it is good for me to reorg the table. The data that rasperer posted for the tables show both -ve and +ve values. Pardon me for asking this trivial question (this is one of those threads that I like but have little or no information about the figures being generated), I want to know reorg'ing which table would benefit me.

-Engi

 

[rasprer]

...Thanks Carp! Regarding Stats... We have some instances where OPTIMIZER = RULE. Thus, no stats have been generated. In this case, would the results be accurate? Will I have to set OPTIMIZER= CHOOSE to have accurate results.

 

[carp]

Nope.
If there are no stats, then the code gathers them. If there were no stats to begin with, then I am at a loss to explain the differences with any degree of confidence. This code takes its best guess. If you reorg the table, Oracle does what Oracle does - which may be quite different from what the code thought it would do. But I would expect the projection and the results to converge!

You might want to take a peek at dba_tables and ascertain that nobody has actually gathered statistics. When faced with the inexplicable, check your assumptions!

 

[SantaMufasa]

(I just arrived back at my office from "Oracle Technology Day" in Salt Lake City, which explains why I have been so quiet on this thread today. Of course some Tek-Tipsters probably have been basking in "The Quiet". For such, I'm sorry to disturb the peace.)

J,
No, you do not need to set "OPTIMIZER=CHOOSE" to get accurate statistics. But this "Swiss Cheese" package depends upon accurate statistics even if your optimizer is RBO.

Engi,
To your questions, (...is it "good for me to reorg the table"...if there are "-ve and +ve values"...?):

1) As I mentioned in the "code posting" earlier today, if values in "Est. Savings" are negative, it typically means that a reorganisation will likely result in consumption of more blocks since the reorg will re-instill into each block whatever the current percent is for intra-block free space. This scenario means that currently (in the "unreorganised" table) the data consume a significant portion of the intra-block free space. Further, the high consumption of intra-block free space is likely causing the table to be more susceptible to "chained rows" (a significant drag on performance). Therefore, tables with a negative value for "Est. Savings", although they will consume more blocks upon reorg, will be "better off" from an expansion and performance perspective.

2) The higher a positive value in "Est. Savings", the more Swiss Cheese (intra-block "air pockets") that exist in a table. Tables with high values in "Est. Savings", then, would certainly make good candidates for reorganisation.

So, in summary, tables with high negative or high positive values in "Est. Savings" would also be high on my list of tables to reorg.

These are all great questions. Any others?

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 21:49 (01Feb05) UTC (aka "GMT" and "Zulu"),
@ 14:49 (01Feb05) Mountain Time

Click here to Donate to Tsunami Relief. 100% of your contributions here go to the victims...0% to administration.
They were "The First-Responder" to the disaster, with relief deliveries arriving before Red Cross and U.S. aid.

 

[rasprer]

Carp....The tables and indexes have all been analyzed.

Also..I just realized. With regards to your 'SwissCheese' package, I don't have to run all these individually do I? I am assuming that it all depends what I would like to do..whether I want to assess an entire schema, table, tablespace or database. Am I Correct?

exec swiss_cheese.assess_schema(p_schema, p_precision, p_threshold)
exec swiss_cheese.assess_table(p_schema, p_table, p_precision, p_threshold)
exec swiss_cheese.assess_tablespace(p_tablespace, p_precision, p_threshold)
exec swiss_cheese.assess_database(p_precision, p_threshold)
Sample: exec swiss_cheese.assess_database(‘HI’, 5)

 

[SantaMufasa]

J,

Your intuition is correct: You certainly do not need to run these assessment procedures individually/independently. They simply give you control of "assessment granularity". If you run "exec swiss_cheese.assess_database(p_precision, p_threshold)", then you certainly do not need to run any of the other assessments (since "assess_database" assesses every non-SYSTEM table in the database).

The reason I listed them all earlier this morning was do offer full disclosure on usage, but certainly not to suggest that one ever runs them all during the same assessment period.

In fact, if you do run multiple assessments that "overlap" (i.e., each assesses the same table), then you are actually "wasting" computer cycles by re-doing such duplicative assessments.

...Good catch...I'm glad you requested clarification.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 21:57 (01Feb05) UTC (aka "GMT" and "Zulu"),
@ 14:57 (01Feb05) Mountain Time

Click here to Donate to Tsunami Relief. 100% of your contributions here go to the victims...0% to administration.
They were "The First-Responder" to the disaster, with relief deliveries arriving before Red Cross and U.S. aid.

 

[rasprer]

Mufasa..I just read your response. I am quite confused now.

BEFORE REORG...

Table Current Est. Est. Space Chained
Name Blocks Blocks Savings Used Rows
------------------------- ---------- ------------------------- ---------- ----
TABLE1 225 238 -13 40 0
TABLE2 409 415 -6 17 0
TABLE3 143 129 14 0 0
TABLE4 685 636 49 0 0

AFTER REORG

Table Current Est. Est. Space Chained
Name Blocks Blocks Savings Used Rows
------------------------- ---------- ---------- ---------- ---------- --------
TABLE1 222 242 -20 65 0
TABLE3 140 129 11 0 0
TABLE4 683 617 66 0 0

...Why is my Est Savings higher after the REORG. Isn't it suppose to be lower?

 

[rasprer]

Mufasa..I just read your response. I am quite confused now. BEFORE REORG...

Table Current Est. Est. Space Chained
Name Blocks Blocks Savings Used Rows
------------------------- ---------- ------------------------- ---------- ----
TABLE1 225 238 -13 40 0
TABLE2 409 415 -6 17 0
TABLE3 143 129 14 0 0
TABLE4 685 636 49 0 0

AFTER REORG

Table Current Est. Est. Space Chained
Name Blocks Blocks Savings Used Rows
------------------------- ---------- ---------- ---------- ---------- --------
TABLE1 222 242 -20 65 0
TABLE3 140 129 11 0 0
TABLE4 683 617 66 0 0

 

[carp]

To reiterate -
It's a best guess!
The way individual rows are loaded within blocks is going to dictate how many blocks are actually required. The code is essentially saying "Your blocks can hold 6000 bytes of row data. You have 5000 rows with an average length of 200 bytes. Therefore, you should need about ((200 * 5000)/6000) + 1 = 168 blocks.
But remember, the stats are a snapshot at the time the table is analyzed. Subsequent changes are not reflected in the stats. So, after the reorg, if the new stats indicate an average row length of 225 bytes and 5100 rows, the code will now say you will need about (225 * 5100)/6000 + 1 = 192 blocks. Which is still just another best guess.
Your results may vary.

 

[SantaMufasa]

And to add to Carp's helpful explanation, I'll redirect your attention to a note-quote from my "code posting" this morning:

Another note: Once your reorganise a table, if you re-run an assessment script and it still shows that the table occupies more blocks than the assessment procedure estimates that the table should ideally occupy, that apparent anomaly results from the calculation differences between Oracle’s statistical AVG_ROW_LEN estimate multiplied by NUM_ROWS versus the actual space consumption. The difference should be rather small and you can write it off to the inherent inaccuracies of estimates…Estimates are just that: Estimates.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 22:18 (01Feb05) UTC (aka "GMT" and "Zulu"),
@ 15:18 (01Feb05) Mountain Time

Click here to Donate to Tsunami Relief. 100% of your contributions here go to the victims...0% to administration.
They were "The First-Responder" to the disaster, with relief deliveries arriving before Red Cross and U.S. aid.

 

[SantaMufasa]

...And to further put your mind at ease as to why the apparent discrepancy between Oracle's table-statistics "estimates" versus "actual": Notice that Carp's regathering of statistics uses a sampling of 10% of the rows (i.e., the statistical gathering looks at only 1 of every 10 rows.) What if the sampled rows just happen to average 100 bytes, but there are a "ton" of rows that were not sampled that are actually 1000 bytes in length? Under such a scenario, is there any wonder why a estimated-versus-actual discrepancy exists.

If one is uncomfortable about these possibilities, then it is feasible that a "COMPUTE" (instead of "ESTIMATE") parameter during statistics gathering might reduce any disparity between statistical figures and actual content sizes.

(I haven't tested this scenario out, however, in an effort to close the numerical gap.)

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 22:28 (01Feb05) UTC (aka "GMT" and "Zulu"),
@ 15:28 (01Feb05) Mountain Time

Click here to Donate to Tsunami Relief. 100% of your contributions here go to the victims...0% to administration.
They were "The First-Responder" to the disaster, with relief deliveries arriving before Red Cross and U.S. aid.

 

[engineer2100]

Mufasa,

You just answered my next question from the script (pertaining to 10% sampling).

-Engi

 

[carp]

I guess you could also include a parameter that would allow you to adjust the sampling percentage. This should be fairly simple to do and is left as an exercise for the reader.

 

[rasprer]

CARP AND MUFASA. Thanks for the clarification. It's making more sense now. I will continue looking into this until it makes 100% sense to me. You both have been great.

 

[rasprer]

Carp/Mufasa...what I am noticing now is this.

After I have executed the "exec swiss_cheese.REORG" displayed from the assestment query(from swiss_tables), and then running the 'exec swiss_cheese.assess_schema' to verify the changes, the output list addtional tables that were not initially output from the query prior to doing the REORG. Why is that? Although, I no longer see some tables, I do see addional tables. Any reason for this?