Performance Related Clarification.

[luxmanpai]

Hello All,

I have an performance issue, where a query which is executed at the client place which is retrieving 370 records is taking around 10 minutes, Where as the same query which is executed here in our development servers is retrieving 1300 odd records in less than 4 minutes.

I checked the client place for Indexes and rebuilt all the indexes as the tables that were used were major tables. I also had a look into the tablespaces which was 97% full. I further increased the size of tablespace, so that around 20% was free. There after, I executed the same query but this time it timed to 9.5 minutes which is like no major difference.

As i am not a DBA person I am finding difficulty to resolve this issue.
I have created the explain plan for the query. the output is as follows:

ID STEP_DESCRIPTION
-------------------------------------------------------------------------------------
0 SELECT STATEMENT COST= 7283
1 SORT GROUP BY
2 MERGE JOIN CARTESIAN
3 TABLE ACCESS FULL TEMP_T_DAY
4 BUFFER SORT
5 VIEW
6 SORT UNIQUE
7 SORT GROUP BY
8 NESTED LOOPS
9 HASH JOIN
10 NESTED LOOPS
11 NESTED LOOPS
12 HASH JOIN
13 TABLE ACCESS FULL RMGT_T_ROOM_CONFIGS
14 HASH JOIN
15 TABLE ACCESS FULL RMGT_T_ROOM_CONFIGS
16 HASH JOIN
17 TABLE ACCESS FULL RMGT_T_FLOOR_SECTIONS
18 HASH JOIN
19 TABLE ACCESS FULL RMGT_T_FLOOR_SECTIONS
20 HASH JOIN
21 TABLE ACCESS FULL RMGT_T_FLOORS
22 HASH JOIN
23 TABLE ACCESS FULL RMGT_T_FLOORS
24 HASH JOIN
25 HASH JOIN
26 NESTED LOOPS
27 INDEX FULL SCAN PK_RMGT_T_COMMUNITY146
28 INDEX UNIQUE SCAN PK_RMGT_T_COMMUNITY146
29 TABLE ACCESS FULL RMGT_T_BUILDINGS
30 TABLE ACCESS FULL RMGT_T_BUILDINGS
31 INDEX UNIQUE SCAN PK_RMGT_T_ROOMS46
32 INDEX UNIQUE SCAN PK_RMGT_T_ROOMS46
33 TABLE ACCESS FULL RMGT_T_ROOM_PERSON
34 INDEX UNIQUE SCAN PK_PPLE_T_PERSON116

35 rows selected
Kindly advise.
Thanks in Advance
Luxman

[davey23uk]

compare the explain plans

[tabreaz]

Did you gather the stats both on production and client side?

[tamilselvan]

Too many full table scans to fetch 370 rows.
May be you are having design issues.

Gather extended trace data and analyze the output.

Tamil

[tabreaz]

Gather extended trace data and analyze the output. 

Tamil can you explain what did you mean by gather extended data and what to analyze the output?

[davey23uk]

do a 10046 trace, analyse the output of that

[tabreaz]

I got it, Thanks

[marist89]

http://marist89.blogspot.com/2005/07/trace-it_04.html