New tablespace performs slowly

[lavieille]

I recently learned that new tables shouldn't be created in the SYSTEM tablespace, so I copied them to a new tablespace called CAT_DAT. Also, I transfered the corresponding indexes (some generated by Oracle and others custom made) to the tablespace CAT_IDX. Since this time, the performance of the DB is very poor.

The user specified to draw from CAT_DAT and CAT_IDX is configured the same as the old user who drew from the SYSTEM tablespace. Is there something I can do to get the performance back up?

[jdorlon]

try analyzing the tables.

with each table in the tablespace, do this :

analyze table x compute statistics;
(it will do the indexes automatically when you do the tables)

[sln81]

Check the status of the indexes. They might have become invalid and hence your applications might be running slow. If that is the case, rebuild the indexes.

[lavieille]

No results with your responses. Anyone else?

[JDoyle]

What exactly is the performance problem you're having? Are we talking about the entire database being slower or just queries against the tables you moved?
In the absence of this information try the following:
run explain plan for problem queries. Look for full table scans where index searches should be done.
run utlbstat/utlestat and analyze the results (report.txt)

[deggzy]

Have you looked at the i/o against this new tablespace?
What device have you put these tablespaces on? is it the same as the system tablespace?

Have you put the files on the same device as the redo logs.

have a look at utlbstat/estat and see what the hit ratios are like.

Have a look at the extents of the tables you have moved


Hope this helps

[lavieille]

The response time in querying the moved tables is where the performance problem lies. The rest of the DB is fine. Everything is on the same disk (redo logs, indexes, Oracle binaries, etc.).

[sln81]

Why don't you try this. Reorganize the new tablespace contents once by EXPORT and IMPORT, and then check the Execution plan for your queries whether the indexes are properly used or not.

[LND]

why do not look into optimizer_mode parameter.

If it is CHOOSE, FIRST_ROWS, ALL_ROWS then most probably CBO is employed (unless querie's hint overrides it)

If CBO is used, then statistics are important and must be checked if still available after moving table to new tablespace -> best of all if they are exactly the same as were before reorganization.
Creating new/modifying existing statistics can even make things worth.

Just for fun, if indexes are available and non CBO specific features are used(like partition pruning), try to set optimizer_mode=RULE

[yls177]

[QUOTE][i]Originally posted by jdorlon [/i]
[B]

with each table in the tablespace, do this :

analyze table x compute statistics;
(it will do the indexes automatically when you do the tables) [/B][/QUOTE]

is this the same as running
1) -checkopt
2) -analyze