AboutElliot Mak Expertise I can answer questions with regarding to Oracle DB (8i, 9i, 10g) installation, configuration, administration, Data Guard, SQL, and PL/SQL (Procedures, triggers, functions).
Experience I am an Oracle DBA, Senior PA, Project Manager, and Data Architect with 10 years of experience.
I was using ORACLE 8.1.6. on OS Windows 2000.
Somehow, my PC got corrupted & stopped working. I reformatted the PC (P III with 256 MB RAM) & installed Windows 2000, ORACLE 8.1.6. In ORACLE, when i tried to import the backup of my user, it was taking lot of time. After 5-6 hours, i stopped the process. Then, i imported the tables with selecting option of not inserting the table data. Then, the system was working fine.
But, now as i am entering data in tables one by one, the speed of system is going slower & slower. Even running a simple 'select count(*) from table' query is taking 5 to 6 minutes. The configuration of PC is same as that of before breakdown. Further, there are no bad sectors in hdd.
I don;t know what is the pboblem. Somebody told me that it may be due to tablespace. But i don't know how to alter tablespace & what is the command. Further, whether altering the tablespace will solve my problem or not.
Please help me in the matter.
Answer Good day,
There are couple things I will suggest you to take a look at, and they are as follows:
1. Tablespace. It is quite important that your data located outside the SYSTEM tablespace. SYSTEM tablespace contains all the metadata of the database. This tablespace queries or changes by the database frequently.
To check if you have non system objects inside your SYSTEM tablespace, you can use the following query
select owner,
tablespace_name,
table_name
from sys.dba_tables
where owner not in ('SYS','SYSTEM')
order by
1,3,2
/
If your user table is located in the SYSTEM tablespace, you may need to recreate table with the non-system tablespace and insert the data again. Couple ways you can do
1a. Method #1
Export the data from your user table
Drop the table
Recreate the table with the non-system tablespace
Import the data
1b. Method #2
Create a table (give it a temporary name) for your user table with the non-system tablespace
Insert the data from the old table to the newly created table
Drop the old table
Rename the newly create table
Usually you can see an improvement as soon as the table is moved outside of the SYSTEM tablespace especially when there is a limited memory resource.
2. SGA sizing of your instance. The slow down of the instance may be caused by lack of memory configure for your SGA due to limited memory available on your workstation(256MB). Here are the command you can use to check your SGA sizing
show parameter large_pool_size
show parameter shared_pool_size
show parameter sort_area_size
show parameter java_pool_size
show parameter log_buffer
show parameter db_block_buffer
I hope this helps. If you have any question, please don't hesitate to let me know.
