?
Solved

database tables data in compress mode

Posted on 2013-01-29
7
Medium Priority
?
421 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 38

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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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 38

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 78

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 35

Accepted Solution

by:
johnsone earned 1500 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses
Course of the Month16 days, 21 hours left to enroll

862 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