I would like to know how to deal with active indexes.
And pando, thanks for your input, I will increase my shared pool size.
And as I mentioned, we are using a poduct which will reads the web server logs ,transalate them as required and import them to our database. this is done by an executable(perl), which I have no control over. I know it is using sql loader because that's how the files are being loaded. I did search for scripts where I could change the loader parameters ,but did'nt find any and finally reached this conclusion.
The following suggestions apply only for DW where bulk load is done.
If the machine has multiple CPUs and Oracle is installed with Parallel Option, You can use PARALLEL LOAD (SQL LOADER) after creating the table with PARALLEL and PARTITION options.
Ask the ETL vendor how to write the control file for the SQL loader. They can provide more info. Sort the flat file before loading. Drop the indexes on the table. Enable NOLOGGING on the table. After load is completed , recreate the indexes on that table.
If Indexes are not dropped, Consider creating a temporary tablespace of size 1GB with initial 24M NEXT 24M PCTINCREASE 0. Do not coalesce the tablespace often, because the temporary segments already created will be reused by new sessions. Never use any value to the PCTINCREASE parameter (other than zero) with temporary tablespace.
My database is not installed with parallel option. Do you think I need to enable this ? If yes, please let me know how.
Then, now we are having issues with performance. The marketing team who are running the reports seem to complain that their queries are taking a long time. still havent reached the point to make out which tables are being accessed . I've just joined newly and the rest of the people who was managing all this seems to be on leave and I have nowhere to turn to.
For this system, we do a daily dataload during the early morning hours and then throughout the day, the other staff query this database to generate reports.
Could you please advice me on which paameters I need to pay attention.