|
Resources
Home
Datasheet
FAQ
Train the Toad
Propriatary Apps
Tuning Robot
Data Warehouse
Business Apps
ROI Example
QuickTour ppt
Services
Search
|
Critical Business
Issue
I
have a performance problem and can't do anything about it!
Quite
often, when application performance is suffering, the answer is
"Rewrite the SQL". But this is not as easy as it
sounds...especially when you don't have access to the source code.
Most enterprises world-wide
already have in place an Oracle monitoring tool – like Precise, Quest,
Compuware, Oracle’s Grid Control, etc. These solutions can identify
poorly-performing SQL; typically providing SQL optimization suggestions
but offer no capability of implementing the change.
Proprietary and Oracle Legacy applications do not allow
changing the source code, whilst the most effective performance
improvement to long running requests, batches and cycles is achieved by
rewriting the SQL source code.
This is not feasible when the customer has no control
over the application source code (in packaged OLTP applications), and in
large DW and BI environments - where reports and ad-hoc queries are
created and changed daily.
Challenges
Customers of 3rd-party ERP/CRM applications
such as PeopleSoft, SAP, Oracle e-Business, Siebel, Amdocs, Clarify
and others, are frequently faced with the dilemma of knowing what is
wrong, and knowing what the solution is, but find themselves at the
mercy of the vendor to provide the fix on a timely basis, and
something that won't break the budget.
Other culprits include BI/DWH environments
including Business Objects, Cognos, Crystal Reports and other
end-user query and reporting tools. Sometimes, no matter how you
specify the request in the tool, the SQL that gets generated is
sub-optimal.
And what about those Development Tools like
Toad, DBArtisan, PL/SQL, SQL*Plus and others? Quite often the
developers and DBAs that use these tools do not understand the
implications of running tasks that cause massive performance
problems, both in Production and in Test.
A study done by a large Oracle customer, managing more
than 500 databases, concluded that Toad misuse by DBAs and developers in
production environments causes two downtime occurrences per database
every year!
Solution
Implemented at
the SQL*Net Protocol Layer, operating as a database listener,
ActiveBase Performance is able to see in-bound
SQL from any application before it reaches the database.
A flexible Rules
Engine inspects the SQL and when there is a match based on your
criteria, the specified action will be performed.
| ActiveBase
Rules Engine |
| ??? Match
SQL ??? |
!!! Take Action !!! |
UserID
Program
Machine IP Address
Time of Day
Text String Matching
Contextual SQL Syntax
Execution Plan Attribute
Oracle Cost
PL/SQL function
User-defined (Java) |
Rewrite
Hint
Block
Redirect
Search and Replace
Define Symbol
Stop Applying Rules or Continue
Message
Audit (add record to AB log)
User-defined (Java) |
The result is
performance improvements can be applied without having to change the underlying database
or the application source code.
Here are some examples of rules. You are
only limited by your imagination. If you can write it down, you
can make it an ActiveBase Rule.
| ActiveBase
Rules Examples |
| ??? Match
SQL ??? |
!!! Take Action !!! |
|
When transaction ‘Where’ clause
condition exceeds 10 days |
Add
Hint for an Oracle statement to apply full scan instead of index
range scan |
|
Unneeded decode and format functions
on ‘Where’ clause conditions that massively degrade Oracle
performance |
Rewrite
statement by deleting unneeded code |
|
SQL requests that will never
complete based on partial explain plan identification |
Block
the request and issue an ORA-900 error code with a custom error
text message returned to the application defined in the rule |
|
Developers using Toad and PL/SQL who are
requesting Parallelism between 9:00am and 5:00pm |
Add Hint to disable Parallelism, allow request
to continue, send a custom ORA-900 message to the user
explaining the change |
|
.BI requests are generating SQL that will run
FULL TABLE SCANS on more the 100 Daily Partitions |
Redirect these
requests to QA so that Production will not be impacted |
|
New performance
features just became available in the latest release of Oracle,
but can't take advantage until applications goes through
development cycle |
Rewrite the SQL to
inject the new performance features available until the
development cycle catches up. |
Customer Example
A major telecom company used
Precise i3 to analyze a serious performance problem in their primary
billing application, a 3rd-party application from a vendor.
The customer provided the
vendor the Precise diagnostics including what the problem was, why it
was the problem and the recommended solution. The vendor agreed, but the
response was not what the customer wanted to hear; it would be at least
12 months before they could get to it, and there would be a special-fix
fee well into the upper 6-digits.
The customer installed
ActiveBase in the morning, and by 2:00pm, jobs that had been running for
14 hours, were now completing in 30 minutes, without touching the source
code or the database.
Rule Examples
|
Solution Overview
SQL*Net Proxy Dynamically Rewrites
SQL
click for larger image
Control
Toad Users
even with SYSBDA Access
click for larger image
Rule Example
click for larger image
|
