Solved

database tables data in compress mode

Posted on 2013-01-29
7
400 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Why You Need a DevOps Toolchain

IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.

 
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 77

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 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

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
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 with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

738 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