Solved

database tables data in compress mode

Posted on 2013-01-29
7
369 Views
Last Modified: 2013-02-17
hi,

we have a web based application which is developed in asp dotnet and database as oracle 11g.
We are currently facing performance issues.Loading of screens is very slow at internet level but while browsing at intranet application performance is good.As per our observation the designer of the application has designed in a such away that what ever master need to be populated will be populated ate time page load only.we cannot change anything at application design level as we dont have ownership.Can we do performance tunning at database level..

Regards,
GSK
0
Comment
Question by:gotetioracle
7 Comments
 
LVL 37

Expert Comment

by:Geert Gruwez
ID: 38834143
the classic way is install statspack
or if you pay for the diagnostic pack use the awr reports

these will report the top queries for cpu, io and time

http://www.orafaq.com/wiki/Statspack
http://jonathanlewis.wordpress.com/statspack-examples/

basically the top queries in v$sql sorted by buffer_gets or disk_reads

this is how to find the top queries
tuning each query requires more work, check the tuning guide
http://docs.oracle.com/cd/E11882_01/server.112/e16638/toc.htm
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 38834170
was everything alright until last few days or last few weeks ? Is the performance issue is still
bad as the same from day one it was developed ?

if it has gone bad recently, they probably you may need to gather/collect stats for the tables/indexes which might also help. also have a look at the volumes of data in the tables and whether housekeeping/archiving etc is happening or not.

Thanks,
0
 

Author Comment

by:gotetioracle
ID: 38834357
hi nav_kum_v,

The problem is at application design level and it is increasing day by day ,as the volume of data in the master tables is increasing.And to tell you ,we have no authorization to change the application design we ar handicapped.Only i can do is at database level.My query is ,is there any method to  read write data from application to database or database to application  in compress mode.

To your information .Application is retrieving all the masters  at the time of page load only.

Regards,
Gsk
0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 38834395
I do not think yes for your question ---> "Only i can do is at database level.My query is ,is there any method to  read write data from application to database or database to application  in compress mode. "

But please note that you can enable compression for the oracle tables at the database level if not already enabled. if you are interested, then this link can give more information :

http://www.oracle-base.com/articles/11g/table-compression-enhancements-11gr1.php
http://itcareershift.com/blog1/2011/02/04/oracle-table-and-index-compression-everything-an-oracle-dba-needs-to-know/
0
 
LVL 37

Expert Comment

by:Geert Gruwez
ID: 38834465
if the design is flawed then tweaks in the design are needed
i've never met a flawless designer yet. you could be the first ...
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38835080
I agree with the above comment that I don't think there is a native way to compress Oracle data across the wire.

There is an IIS compression that might help (It is version specific so find the proper doc for your IIS version):
https://www.microsoft.com/technet/prodtechnol/WindowsServer2003/Library/IIS/d52ff289-94d3-4085-bc4e-24eb4f312e0e.mspx?mfr=true

I should point out there is a know security hack for compression over SSL.  There are many articles out there on this:
http://arstechnica.com/security/2012/09/many-ways-to-break-ssl-with-crime-attacks-experts-warn/



Are you sure the issue is with just the amount of data crossing the wire?

How have you determined this?

What is the difference in configurations between the Intranet server and the Internet server?
What network pieces are in place between the two setups?
Do you have something monitoring External traffic that might be getting in the way?  Something like an intrusion detection device that is scanning your packets that is slowing it down?

I agree that a bad design is a bad design.  Over time as data increases even the Intranet app will likely suffer from performance issues.


I also want to point out that it is a VERY BAD design to have an Internet app connect directly to a database.  All I need to do is compromise your web server and I likely own your entire database.

Most of the time data to the Internet is funneled through an app server or handled through web service calls.
0
 
LVL 34

Accepted Solution

by:
johnsone earned 500 total points
ID: 38835342
What about restricting the data coming over the wire with a view?  Not sure if that would be possible.  You can rename the underlying table(s) involved and replace them with view(s) that have the same names.  The change would be seamless to the application and would allow you to reduce the number of records being returned.  That may not work with the way the application is designed but it is the only way I can think of to reduce amount of data going across the wire.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now