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_DESCRIPTIO N
-------------------------------------------------------------------------------------
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_CON FIGS
14 HASH JOIN
15 TABLE ACCESS FULL RMGT_T_ROOM_CON FIGS
16 HASH JOIN
17 TABLE ACCESS FULL RMGT_T_FLOOR_SE CTIONS
18 HASH JOIN
19 TABLE ACCESS FULL RMGT_T_FLOOR_SE CTIONS
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_COMMU NITY146
28 INDEX UNIQUE SCAN PK_RMGT_T_COMMU NITY146
29 TABLE ACCESS FULL RMGT_T_BUILDING S
30 TABLE ACCESS FULL RMGT_T_BUILDING S
31 INDEX UNIQUE SCAN PK_RMGT_T_ROOMS 46
32 INDEX UNIQUE SCAN PK_RMGT_T_ROOMS 46
33 TABLE ACCESS FULL RMGT_T_ROOM_PER SON
34 INDEX UNIQUE SCAN PK_PPLE_T_PERSO N116
35 rows selected
Kindly advise.
Thanks in Advance
Luxman
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_DESCRIPTIO N
-------------------------------------------------------------------------------------
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_CON FIGS
14 HASH JOIN
15 TABLE ACCESS FULL RMGT_T_ROOM_CON FIGS
16 HASH JOIN
17 TABLE ACCESS FULL RMGT_T_FLOOR_SE CTIONS
18 HASH JOIN
19 TABLE ACCESS FULL RMGT_T_FLOOR_SE CTIONS
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_COMMU NITY146
28 INDEX UNIQUE SCAN PK_RMGT_T_COMMU NITY146
29 TABLE ACCESS FULL RMGT_T_BUILDING S
30 TABLE ACCESS FULL RMGT_T_BUILDING S
31 INDEX UNIQUE SCAN PK_RMGT_T_ROOMS 46
32 INDEX UNIQUE SCAN PK_RMGT_T_ROOMS 46
33 TABLE ACCESS FULL RMGT_T_ROOM_PER SON
34 INDEX UNIQUE SCAN PK_PPLE_T_PERSO N116
35 rows selected
Kindly advise.
Thanks in Advance
Luxman
Comment