473,738 Members | 3,636 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Performance Related Clarification.

1 New Member
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
Jul 6 '06 #1
1 2044
sharmanic2002
6 New Member
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
2352
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 has been in prior months - so I'm really not sure what could cause this. Could anyone suggest some potentiol avenues to explore for reasons that could cause PHP sessions to fail? Thanks for any suggestions!
5
4036
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 database. The servlet is deployed on two seperate win2k servers (Access to the tomcat servers is via DNS round robin load balancing). The database is on a another win2k server.
25
3486
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 within a try statement myself, or is there some clever implementation enhancement which makes this a bad idea? i.e. should I prefer: if hasattr(self,"datum"): datum=getattr("datum") else: datum=None
59
4436
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 presented that says "Use conventional error-handling techniques rather than exception handling for straightforward local error processing in which a program is easily able to deal with its own errors." By "conventional error-handling," I believe...
0
1197
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 resolution. If you attempt to connect to a local database with a connect string using server=. rather than
115
7625
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 transform function. When compiling under gcc on my big-endian PowerPC (Mac OS X), declaring this array as "static" DECREASES the transform throughput by around 5%. However, declaring it as "static" on gcc/Linux/Intel INCREASES the throughput by...
2
2424
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 SQL Server 2000. I encounted various performance issues with the production server with a particular query. It would take approximately 22 seconds to return 100 rows, thats about 0.22 seconds per row. Note: I ran the query in single user mode. So...
2
1565
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 SQL Server 2000. I encounted various performance issues with the production server with a particular query. It would take approximately 22 seconds to return 100 rows, thats about 0.22 seconds per row. Note: I ran the query in single user mode. So...
2
2921
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 review upto some level so that the reviewer and developer's time can be reduced during the code review. In my script, I am using pattern matching and grep in many places. So, my clarification is : Is it fine to make use of grep to find a pattern in a...
0
8788
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9476
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9335
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
9263
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
1
6751
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6053
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4570
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4825
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
2745
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.