Hi,

We converted our Power builder application from Oracle 10g to Oracle 11g.

We are getting very slow to retrieveing data from the server.
Basically we have noticed the following system internal sqls taking more time during our sql calls.

My schema Name : CMATISPROD
Oracle Version : 11.2.0.2.0

Indexes are moved separte table space.

Kindly help me out how to resolve this problem.


SELECT index_name, uniqueness
FROM SYS.all_indexes
WHERE table_owner = 'CMATISPROD' AND table_name = 'EXPRESSIONS';

* Formatted on 2011/12/15 14:19 (Formatter Plus v4.8.0) */
SELECT table_owner, table_name
FROM SYS.all_synonyms
WHERE owner = 'CMATISPROD'
AND synonym_name = 'EXPRESSIONS'
AND (db_link IS NULL OR db_link = '')

/* Formatted on 2011/12/15 14:19 (Formatter Plus v4.8.0) */
SELECT SYS.all_cons_columns.column_name,
SYS.all_constraints.constraint_name
FROM SYS.all_constraints, SYS.all_cons_columns
WHERE SYS.all_constraints.constraint_type = 'P'
AND SYS.all_constraints.table_name = 'CURRENCY'
AND SYS.all_constraints.owner = 'CMATISPROD'
AND SYS.all_constraints.constraint_name =
SYS.all_cons_columns.constraint_name
AND SYS.all_constraints.table_name = SYS.all_cons_columns.table_name
AND SYS.all_constraints.owner = SYS.all_cons_columns.owner
ORDER BY SYS.all_constraints.constraint_name, SYS.all_cons_columns.POSITION

SELECT column_name
FROM SYS.all_ind_columns
WHERE index_name = 'PK_CURRENCY'
AND index_owner = 'CMATISPROD'
AND table_owner = 'CMATISPROD'
AND table_name = 'CURRENCY'

SELECT table_owner, table_name
FROM SYS.all_synonyms
WHERE owner = 'PUBLIC'
AND synonym_name = 'EXPRESSIONS'
AND (db_link IS NULL OR db_link = '')

Regards,
Vijai.B

Hi Vijai,

>> We are getting very slow to retrieveing data from the server. Basically we have noticed the following system internal sqls taking more time during our sql calls.

That's a good clue.

**********************************
>> Indexes are moved separte table space.

That's only for easier management, not performance!

***********************************
>> Kindly help

Start by revealing the internal execution plans and compare the 10g one with the 11g one!



These are dictionary queries against complex views!

Try analyzing the dictionary tables/indexes with dbms_stats:



For a complete method, see my book "Otale Tuining: The Definitive Reference":

http://rampant-books.com/book_1002_oracle_...ence_2nd_ed.htm

Let me know of this helps . . .

Dear Mr.Burleson,

Thank you very much for your great soluation. I am analysiing the same. We will revert to you if any further help required.

Regards,
Vijai.B