Solved

database tables data in compress mode

Posted on 2013-01-29
7
405 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
The Orion Papers

Are you interested in becoming an AWS Certified Solutions Architect?

Discover a new interactive way of training for the exam.

 
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

Containers & Docker to Create a Powerful Team

Containers are an incredibly powerful technology that can provide you and/or your engineering team with huge productivity gains. Using containers, you can deploy, back up, replicate, and move apps and their dependencies quickly and easily.

Question has a verified solution.

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

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
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.

691 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