migration from sql server to oracle
Hari, June 08, 2011 - 1:02 am UTC
Hi Tom,
I need to migrate my sql serverdata to oracle 11g. Kindly help us to how would i do...
June 08, 2011 - 10:35 am UTC
Rowcount & blockcount relation to estimate in production environment
AK, June 20, 2011 - 9:01 am UTC
If I know the rowcount in my devlopment environment & I also know the rowcount in the production environment will I be able to estimate the number of blocks for production enviroment.
Factor = Prodution Rowcount/ Dev Rowcount
Estmated Production Block count = Dev Blockcount * Factor
Thanks
AK
June 20, 2011 - 1:41 pm UTC
it depends.
It depends on what you do to the data (delete/update wise) and how the data initially gets into the table.
simple case: What if in development you loaded in 1,000,000 records and ended up deleting 500,000 of them. And that was all. And further, suppose that does not reflect what you would be doing in production (development rarely does anything on the scale of production - ever). If you tried to extrapolate up - you would get some funny numbers wouldn't you.
simple case: what if in development you just mostly insert in your testing, but in real life you do lots of updating (making rows bigger)... Again...
So, it depends. If the table in development was just loaded in bulk and you are planning on loading production in bulk - you can probably extrapolate up. Otherwise - it will be a bit sketchy.
shailesh Bhagwat, June 21, 2011 - 6:59 am UTC
Hi Tom,
Thanks for previous reply.
I have two identical schemas : one with data and other without data.
I exported schema stats from schema with data to stattable and I imported stattable in schema without data.
Now, although all statistics are setting correctly,
1. ENDPOINT_NUMBER and ENDPOINT_VALUE from "user_histograms" does not set to imported value.
2. HISTOGRAM from "user_tab_col_statistics" does not set to imported values.
I found that, it is happening even if column n10 and column n11 from stattable refers to ENDPOINT_NUMBER and ENDPOINT_VALUE when type ='C'.
Will you please explain why it is happening ?
And Is there any way to manually set up histogram statistics like "dbms_stats.set_table_stats" for table ?
June 21, 2011 - 8:00 am UTC
give full example, use just one table - populate it with data, gather stats, display RELEVANT stats, do your move of the data, demonstrate the difference.
give a full, complete, yet AS CONCISE AS POSSIBLE example - just like I do for you guys. From start to finish. Demonstrate every step you did.
shailesh bhagwat, June 24, 2011 - 6:46 am UTC
Hi Tom,
Here consider following example..
selectCOLUMN_NAME , TBLE_NAME, NM_DISTINCT, NM_NULLS, HSTOGRAM from user_tab_col_statistics;
COLUMN_NAME NUM_DISTINCT NUM_NULLS HISTOGRAM
-------------- --------------- --------- ---------------
COMPID 68 0 FREQUENCY
VERSION 649 0 HEIGHT BALANCED
OTYPENO 38 0 FREQUENCY
OREFNO 28979 0 HEIGHT BALANCED
PTYPENO 82 0 FREQUENCY
PVALLEN 298 0 NONE
PVALUE 1393 150339 HEIGHT BALANCED
____________________________________________________________
I exported above User with grants but no data to schema.dmp file.
------------------------------------------------------------
exec dbms_stats.export_schema_stats(ownname=>'ownername',stattab =>'stattable');
------------------------------------------------------------
I exported stattable using 'exp' command to stats.dmp file with data.
-----------------------------------------------------------
Now, I create another user and import schema.dmp for this user using 'imp' command.
Then I imported stats.dmp to this user using 'imp' utility with data.
-----------------------------------------------------------
Now I again run,
selectCOLUMN_NAME , TBLE_NAME, NM_DISTINCT, NM_NULLS, HSTOGRAM from user_tab_col_statistics;
and I get...
COLUMN_NAME NUM_DISTINCT NUM_NULLS HISTOGRAM
-------------- --------------- --------- ---------------
COMPID 68 0 NONE
VERSION 649 0 NONE
OTYPENO 38 0 NONE
OREFNO 28979 0 NONE
PTYPENO 82 0 NONE
PVALLEN 298 0 NONE
PVALUE 1393 150339 NONE
------------------------------------------------------------
So, my concern is,
1) Why histogram does not reflected in new schema ?
2) I execute,
select n10,n11 from stattable where type ='C';
and I get
C4 N10 11
------------------------------ ---------- ----------
OTYPENO 4980 11
OTYPENO 4982 11
OTYPENO 4984 11
OTYPENO 4985 11
.....
.....
.....
(n10,n11 are ENDPOINT NUMBER,ENDPOINT VALUE from user_histograms view.)
But,
When I execute following query in new schema,
select column_name,ENDPOINT_NUMBER,ENDPOINT_VALUE from user_histograms;
I get,
COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE
----------- ----------------- ---------------
OTYPENO 1 135
OTYPENO 1 185
OTYPENO 1 222
OTYPENO 1 225
So,my question is, why ENDPOINT_NUMBER, ENDPOINT_VALUE are not reflecting in my new schema even though they are present in stattable ?
Hopefully, you get my concern.
June 24, 2011 - 9:34 am UTC
I'll say it again:
give full example, use just one table - populate it with data, gather stats, display RELEVANT stats, do your move of the data, demonstrate the difference.
give a full, complete, yet AS CONCISE AS POSSIBLE example - just like I do for you guys. From start to finish. Demonstrate every step you did.
do it like I do on so many questions.
Start with a create table.
Populate the data in that table.
Do the dbms_stats
etc etc etc
give me something to reproduce with on my system. I won't really look at the question till I have that. You are missing MANY steps in the above. For example, you say "i imported stats.dmp to this user...", but I don't see any dbms_stats calls after that.
Exporting Histogram statistics...
Shailesh Bhagwat, July 01, 2011 - 3:22 am UTC
Hi,
First I want to thanks you. I re-scan my scripts to give full and complete example as you said...
"give a full, complete, yet AS CONCISE AS POSSIBLE example - just like I do for you guys. From start to finish. Demonstrate every step you did. "
and I found mistake from my script.
Tom,
We can export table statistics/column statistics/index statistics uing "dbms_stats.export".
e.g. exec dbms_stats.export_table_stats(HR, 'EMPLOYEES', NULL, 'STAT_TAB', NULL, TRUE);
Which will result in exporting Employees table statistics (ENDPOINT_NUMBER,ENDPOINT_VALUE etc)to stat_tab table.
In the similar manner, Can we export histogram statistics to stat_table ?
I searched it but I did not get any clue. Will you please help in this query?
July 01, 2011 - 9:21 am UTC