Help - Search - Members - Calendar
Full Version: After conversion 11G very slow my Power builder application
Oracle DBA Forums > Oracle > Oracle Forum
bvkumar


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
burleson
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!

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


These are dictionary queries against complex views!

Try analyzing the dictionary tables/indexes with dbms_stats:


CODE
exec dbms_stats.gather_schema_stats('SYS');


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 . . .
bvkumar
QUOTE (burleson @ Dec 19 2011, 05:35 AM) *
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!

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


These are dictionary queries against complex views!

Try analyzing the dictionary tables/indexes with dbms_stats:


CODE
exec dbms_stats.gather_schema_stats('SYS');


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 . . .

bvkumar

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
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.
Invision Power Board © 2001-2014 Invision Power Services, Inc.