Capacity planning and performance issue

Hi guys,

Can you please provide your comments/suggestion/recommendation on the following issue?

We are expecting the size of the daily data 1GB (per day) in one of our table in the database through ftp from another server. Gradually, it would grow upto 30 GB over a period of few months.

This data would be transferred through UNIX script.

The table structure is enclosed here for your reference

Column Name Column Type
col1 Number(15)
col2 Varchar2(255)
col3 Varchar2(255)
col4 Varchar2(255)
col5 Varchar2(255)
col6 Number(4,2)
col7 Number(10)
col8 Date
col9 Number(8,2)
col10 Number(8,2)
col11 Date
col12 Varchar2(3)

What storage parameter can I recommend to accommodate this high growth of data at table or tablespace level?

How can I check the performance of database perspective in advance. i.e. CPU, Memory, I/O etc.( may be using iostat, vmstat, mpstat, sar)?

In another way, what would be the impact on CPU , I/O, Memory?

What would be the impact on the overall database?

What precautionary actions are required to running database efficiently?

What other things should be taken into consideration before downloading or transferring data from another server.

What do you plan to do with this data once they are loaded?
or
What kind of application is using these data?

and how do you plan to load the data

Is the data ever going to be changed/deleted?

30 GB is not a big table today.

Ultimately, what % of rows going to be retrieved by the SQL only matters.

Are you concerned with loading data into another system?

Tamil

It is required to retrieve data for reporting purpose. This is our reporting server.

The script is written to transfer data first from one server to another through ftp.

The data would load thorugh script. It would remain static over a period of time.

Yes, I have concern with loading of data but the script is already written for populating data. It would be populating into one of the table using my database.

How to estimate the size of this object and what actions are required to run this job smoothly over a period of time.

thanks in advance.

and what method does this 'script' use

Quote:

---

Originally Posted by dbajay

It is required to retrieve data for reporting purpose. This is our reporting server.

The script is written to transfer data first from one server to another through ftp.

The data would load thorugh script. It would remain static over a period of time.

Yes, I have concern with loading of data but the script is already written for populating data. It would be populating into one of the table using my database.

How to estimate the size of this object and what actions are required to run this job smoothly over a period of time.

thanks in advance.

---

You need to collect performance statistics over a period of time and analyze the pattern and growth.

Another thought ......

Have you considered transportable tablespace option?


Tamil

If it's going to be readonly then use pctfree 0 and compress the data on insert (use direct path load and set the table to COMPRESS). Do not use ASSM. Consider ordering the data prior to insert by common reporting predicates so as to improve the clustering factor on those columns' indexes and reduce physical i/o on retrieval.

Estimate the size by loading a representative sample and measuring it.

Thanks for your valuable input. We will use SQL loader within script so that transportable tablespace would not help much.

I agrre that PCTFREE should be zero. what is the ideal size of PCTUSED. It is static data.

I belive that table should be created with nologging option to improve performance also.

The partitioning concept on date column would be also useful.

Can you pl. make your comment on it?

Can you pl. give me more idea technically how to perform and achive it for your below suggestion?

Consider ordering the data prior to insert by common reporting predicates so as to improve the clustering factor on those columns' indexes and reduce physical i/o on retrieval.

Estimate the size by loading a representative sample and measuring it.

If you are not deleting data then PCTUSED does not matter, but just set it to 99.

If you set the table to nologging then you should preserve your source data until the database has been backed up.

Partitioning would be useful for dropping old data easily or for enhancing performance if users will commonly use a particular set of columns to filter their result sets.

When you said "sql loader" a bell went off.
Would the option to use oracle's external table feature be an alternative?

But, I think external table is not supported in Oacle 8.1.7.

Some commenst from me aslo
1) Partitioning is a good idea. Sooner or later you'll need to do someting with the data for particular mont and that will be a pain without partitioning.You could even consider placing a partition or several partitions in separate tablespace and making it read only after the load. This would help with the backup lately and also with unpluging/pluging data into the database using the new 10g R2 features
2) Dave, why you do not like ASSM? Some bad experience or..?
3) OracleDoc, I do not see a huge benefit in the external tables compared to the loader. It could operate a bit easyer but since the scripts are created, it should not matter so much

Quote:

---

Originally Posted by Bore

2) Dave, why you do not like ASSM? Some bad experience or..?
3) OracleDoc, I do not see a huge benefit in the external tables compared to the loader. It could operate a bit easyer but since the scripts are created, it should not matter so much

---

ASSM prevents the stored rows from being clustered by value -- they get scattered around. In this case there is a single loading process so there is no contention to avoid on the insert (one of the primary purposes of ASSM) and you just want the data to be packed as tightly as possible. MSSM, PCTFREE 0, compression, and sorted data is the way to go.

Sorry for coming in...

Quote:

---

3) OracleDoc, I do not see a huge benefit in the external tables compared to the loader. It could operate a bit easyer but since the scripts are created, it should not matter so much

---

REDO generation.

Cheers.

Carlos.

Seems I do not understand something. So loading by ext tables does not generates redo and the loader does?
The loader also has direct path loading if that's in question

http://www.dbasupport.com/oracle/ora...Tables9i.shtml

http://www.oracle.com/technology/pro...ly/sept19.html