473,320 Members | 1,699 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Performance Related Clarification.

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
Jul 6 '06 #1
1 1995
Increase the number of check points and check the PMON.
Please take a statspack report in regular intervals which you find will be approriate and comapre the reports.You will find the solution urself.

Sharma J A
Oct 6 '06 #2

Sign in to post your reply or Sign up for a free account.

Similar topics

13
by: jamie howard | last post by:
Hello there - we have a fairly busy server and we just started to have problems with PHP sessions failing. We've never had this problem before and to be honist, out server traffic is lower than it...
5
by: nmac | last post by:
Hi all, hopefully someone can offer some sagely advice regarding Production use of Jakarta's Tomcat. First, some brief background. My company have a servlet application that connects to a MySQL...
25
by: Brian Patterson | last post by:
I have noticed in the book of words that hasattr works by calling getattr and raising an exception if no such attribute exists. If I need the value in any case, am I better off using getattr...
59
by: kk_oop | last post by:
Hi. I wanted to use exceptions to handle error conditions in my code. I think doing that is useful, as it helps to separate "go" paths from error paths. However, a coding guideline has been...
0
by: Andrew Mayo | last post by:
This problem was discovered with MSDE2000 SP2 and under WinXP SP2. We are unsure whether it is more widespread as it has only been seen on one machine to date. The problem is related to name...
115
by: Mark Shelor | last post by:
I've encountered a troublesome inconsistency in the C-language Perl extension I've written for CPAN (Digest::SHA). The problem involves the use of a static array within a performance-critical...
2
by: Brian Tabios | last post by:
Hello Everyone, I have a very complex performance issue with our production database. Here's the scenario. We have a production webserver server and a development web server. Both are running...
2
by: BTabios | last post by:
Hello Everyone, I have a very complex performance issue with our production database. Here's the scenario. We have a production webserver server and a development web server. Both are running...
2
by: ravir | last post by:
Hi, I am new to this group. I am working in Perl and shellscripts. I have a clarification regarding perl grep and pattern matching. I am writing a perl script to automate the process of code...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.