All about changing SQL text without tampering with the actual code
Rewriting Reality: How to Change SQL Text on the Fly in Oracle
In modern
Oracle environments, the need to modify application-generated SQL without
touching the source code is a common challenge for DBAs and performance
engineers. This is often necessary to apply a critical hint, enforce a security
predicate, or redirect a slow query to a pre-calculated result set. One of traditional
way is SQL Patch and you can find its concept and application in previous
topics in [Database Box] Group in LinkedIn.in this chapter, I review two
another powerful method to modify SQL text on the fly. Although using this
solution has several security concerns because anybody with preferred access
can modify a UPDATE statement WHERE CLAUSE also, so you should consider this
ability as a high risk.
Oracle
provides two powerful, yet distinct, mechanisms for this "on-the-fly"
SQL text modification: DBMS_ADVANCED_REWRITE and DBMS_SQL_TRANSLATOR.
Understanding the core difference, functional equivalence, is key to choosing
the right tool for the job.
Before we
dive deeper, let's review how Oracle leverages Query Rewrite internally.
About Cost-Based Optimization and Query Rewrite
When a query
is rewritten, Oracle's cost-based optimizer compares the cost of the rewritten
query and original query and chooses the cheaper execution plan.
Query
rewrite is available with cost-based optimization. Oracle Database optimizes
the input query with and without rewrite and selects the least costly
alternative. The optimizer rewrites a query by rewriting one or more query
blocks, one at a time.
If query
rewrite has a choice between several materialized views to rewrite a query
block, it selects the ones which can result in reading in the least amount of
data. After a materialized view has been selected for a rewrite, the optimizer
then tests whether the rewritten query can be rewritten further with other
materialized views. This process continues until no further rewrites are
possible. Then the rewritten query is optimized and the original query is
optimized. The optimizer compares these two optimizations and selects the least
costly alternative.
Because
optimization is based on cost, it is important to collect statistics both on
tables involved in the query and on the tables representing materialized views.
Statistics are fundamental measures, such as the number of rows in a table,
that are used to calculate the cost of a rewritten query. They are created by
using the DBMS_STATS package.
Queries that
contain inline or named views are also candidates for query rewrite. When a
query contains a named view, the view name is used to do the matching between a
materialized view and the query. When a query contains an inline view, the
inline view can be merged into the query before matching between a materialized
view and the query occurs.
About Checks
Made by Query Rewrite
For
query rewrite to occur, there are a number of checks that the data must
pass. These checks are:
- Join Compatibility Check for
Query Rewrite
- Data Sufficiency Check for Query
Rewrite
- Grouping Compatibility Check for
Query Rewrite
- Aggregate Computability Check
for Query Rewrite
How Oracle Rewrites
Queries
The
optimizer uses a number of different methods to rewrite a query. The first step
in determining whether query rewrite is possible is to see if the query
satisfies the following prerequisites:
Ø Joins present
in the materialized view are present in the SQL.
Ø There is
sufficient data in the materialized view(s) to answer the query.
After that,
it must determine how it will rewrite the query. The simplest case occurs when
the result stored in a materialized view exactly matches what is requested by a
query. The optimizer makes this type of determination by comparing the text of
the query with the text of the materialized view definition.
This text
match method is most straightforward but the number of queries eligible for
this type of query rewrite is minimal.
When the
text comparison test fails, the optimizer performs a series of generalized
checks based on the joins, selections, grouping, aggregates, and column data
fetched. This is accomplished by individually comparing various clauses
(SELECT, FROM, WHERE, HAVING, or GROUP BY) of a query with those of a
materialized view.
Syntax:
|
DBMS_ADVANCED_REWRITE.DECLARE_REWRITE_EQUIVALENCE
( name VARCHAR2, source_stmt VARCHAR2, destination_stmt VARCHAR2, validate BOOLEAN := TRUE, rewrite_mode VARCHAR2 := 'TEXT_MATCH'); DBMS_ADVANCED_REWRITE.DECLARE_REWRITE_EQUIVALENCE
( name VARCHAR2, source_stmt CLOB, destination_stmt CLOB, validate BOOLEAN := TRUE, rewrite_mode VARCHAR2 :=
'TEXT_MATCH'); |
A Boolean indicating
whether to validate that the specified source_stmt is functionally equivalent
to the specified destination_stmt.
v If validate is specified as TRUE,
DECLARE_REWRITE_EQUIVALENCE evaluates the two sub-SELECTs and compares their
results.
v If the results are not the same,
DECLARE_REWRITE_EQUIVALENCE does not create the rewrite equivalence and returns
an error condition.
v If FALSE,
DECLARE_REWRITE_EQUIVALENCE does not validate the equivalence.
rewrite_mode:
The following modes are
supported, in increasing order of power:
v disabled: Query rewrite does not use the equivalence
declaration. Use this mode to temporarily disable use of the rewrite
equivalence declaration.
v text_match: Query rewrite uses the equivalence declaration only
in its text match modes. This mode is useful for simple transformations.
v general: Query rewrite uses the equivalence declaration in all
of its transformation modes against the incoming request queries.
However,
query rewrite makes no attempt to rewrite the specified destination_query.
v recursive: Query rewrite uses the equivalence declaration in
all of its transformation modes against the incoming request queries.
Moreover, query rewrite
further attempts to rewrite the specified destination_query for further
performance enhancements whenever it uses the equivalence declaration.
Oracle recommends you
use the least powerful mode that is sufficient to solve your performance
problem.
1. *** DBMS_ADVANCED_REWRITE: The Performance Optimizer***
The
DBMS_ADVANCED_REWRITE package is designed to declare that a source_stmt is
functionally equivalent to a destination_stmt for the purpose of query rewrite.
Its primary use is to enable the optimizer to use a more efficient execution
path, such as a Materialized View (MV).
Application in OLAP
and Performance Enhancement
This package
is crucial in Online Analytical Processing (OLAP) environments. OLAP queries
often involve complex aggregations over large data sets. By using
DBMS_ADVANCED_REWRITE, a DBA can:
1.
Create a Materialized View (MV): Pre-calculate the expensive
aggregations.
2.
Declare Equivalence: Use DECLARE_REWRITE_EQUIVALENCE to
tell the optimizer that the original, slow query is logically the same as a
simple SELECT from the MV.
3.
Result: The application continues to run the original SQL, but
the optimizer transparently rewrites it to use the MV, drastically improving
performance.
Scenario1: One of beneficial usage of this solution is in OLAP env, in
following, I showing an enhancement for SQL Execution time:
Rewrite Using
Equivalence (UNION ALL)
This
technique is often used to partition a large table logically or to redirect
queries to a more efficient data structure.
Goal: Rewrite a query that selects from a
single large table (SALES) to instead query a UNION ALL of two smaller,
partitioned tables (SALES_Q1, SALES_Q2).
|
Statement |
SQL Text |
|
Source Query |
SELECT SUM(amount) FROM sales WHERE year = 2024; |
|
Destination
Query |
SELECT SUM(amount) FROM sales_q1 WHERE year = 2024 UNION ALL SELECT SUM(amount)
FROM sales_q2 WHERE year = 2024; |
-- 1.
Create the equivalence declaration
|
BEGIN
DBMS_ADVANCED_REWRITE.DECLARE_REWRITE_EQUIVALENCE ( name =>
'SALES_UNION_REWRITE', source_stmt => 'SELECT SUM(amount) FROM sales WHERE year = 2024', destination_stmt => 'SELECT SUM(amount) FROM sales_q1 WHERE year = 2024 UNION ALL SELECT SUM(amount) FROM sales_q2
WHERE year = 2024', validate => TRUE,
-- Validation ensures functional equivalence rewrite_mode => 'GENERAL' ); END; / |
(must have QUERY_REWRITE_ENABLED=TRUE and
QUERY_REWRITE_INTEGRITY=TRUSTED or ENFORCED)
EXPLAIN PLAN FOR
SELECT SUM(amount) FROM sales WHERE year = 2024;
-- The execution plan
will show the rewritten query accessing SALES_Q1 and SALES_Q2.
Scenario2: Result
Cache Materialized View
A Result
Cache Materialized View (RCMV) is a powerful feature that stores the result of
a query in the database's result cache, offering extremely fast access.
Goal: Redirect a frequently executed, slow
query to an RCMV.
Step-by-Step
Implementation:
-- 1.
Create the Result Cache Materialized View
|
CREATE MATERIALIZED VIEW mv_top_customers BUILD IMMEDIATE REFRESH FAST ON COMMIT ENABLE QUERY REWRITE RESULT_CACHE (MODE FORCE)
-- Key feature for RCMV AS SELECT customer_id, COUNT(*)
AS total_orders FROM orders GROUP BY customer_id HAVING COUNT(*) > 100; |
-- 2. No
explicit DECLARE_REWRITE_EQUIVALENCE is needed!
--
Oracle's built-in query rewrite engine automatically handles the rewrite
-- when
the query matches the MV's definition and QUERY_REWRITE_ENABLED is set.
-- 3. The
application runs the original query:
SELECT customer_id,
COUNT(*)
FROM orders GROUP BY
customer_id HAVING COUNT(*) > 100;
-- The
optimizer will automatically rewrite this to use the MV, and the MV's
-- result
will be served from the lightning-fast Result Cache.
Scenario3: Modify Whare Clause and SQL
Statement with two methods:
Method1:
|
BEGIN
dbms_advanced_rewrite.declare_rewrite_equivalence( 'UW', 'SELECT
srvr_id FROM servers INTERSECT SELECT srvr_id FROM serv_inst', 'SELECT
srvr_id FROM servers s WHERE EXISTS (SELECT srvr_id FROM serv_inst i WHERE
s.srvr_id = i.srvr_id)' ,
TRUE, 'TEXT_MATCH'); END; / exec
dbms_advanced_rewrite.drop_rewrite_equivalence ('UW'); |
Method2:
|
dbms_advanced_rewrite.declare_rewrite_equivalence
( name VARCHAR2, source_stmt CLOB, destination_stmt
CLOB, validate BOOLEAN := TRUE, mode VARCHAR2 := 'TEXT_MATCH'); DECLARE sclob CLOB; dclob CLOB; BEGIN sclob := CAST('SELECT
srvr_id FROM servers INTERSECT SELECT srvr_id FROM serv_inst' AS CLOB); dclob := CAST('SELECT
srvr_id FROM servers s WHERE EXISTS (SELECT srvr_id FROM serv_inst i WHERE
s.srvr_id = i.srvr_id)'
AS CLOB); dbms_advanced_rewrite.declare_rewrite_equivalence('UW',
sclob, dclob, TRUE, 'TEXT_MATCH'); END; / exec
dbms_advanced_rewrite.validate_rewrite_equivalence ('UW'); exec
dbms_advanced_rewrite.drop_rewrite_equivalence('UW'); exec
dbms_advanced_rewrite.alter_rewrite_equivalence('UW', 'DISABLED'); |
|
Security
Advisory Default
privileges are not granted to anyone for access to DBMS_ADVANCED_REWRITE
Security Model procedures. To gain access to these procedures, you must
connect as SYSDBA and explicitly grant execute access to the
desired database administrators. You
can control security on this package by granting the EXECUTE privilege
to selected database administrators or roles. For example, the user er can
be given access to use this package by the following statement, executed
as SYSDBA: GRANT
EXECUTE ON DBMS_ADVANCED_REWRITE TO USER; You
may want to write a separate cover package on top of this package for
restricting the alert names used. Instead of granting the EXECUTE privilege
on the DBMS_ADVANCED_REWRITE package directly, you can then grant
it to the cover package. The
Oracle built-in PL/SQL package DBMS_ADVANCED_REWRITE can be used to invisibly
circumvent what appears to be security and can be used to make the results of
a pentest nothing short of laughable. This
is why, as you will clearly see in the next demo, penetration testing
has value at the network level but is of little or no value once the
perimeter is penetrated and the only defensive layer left is the database
itself. Note also that products like
Database Firewall, Audit Vault, etc. are unlikely to observe the
vulnerability demonstrated here. Recommended
Security Rules NEVER Grant EXECUTE on this package to
any user unless for a very specific purpose and the privilege should be
immediately revoked after that activity is completed. Immediately,
as used here, means it should be granted for no more than a few minutes which
is all of the time required to use this package for a valid purpose. WITH GREAT CARE Query
the data dictionary after the privilege has been revoked to verify the
equivalence created is the equivalence approved by IT management and your
CISO. CAUTIONS Be
wary of any request for EXECUTE privilege for this package. The request,
except to implement a known solution to a known performance issue is highly
suspicious and should be treated accordingly. |
Critical Sample (Can Consider for Hacking with
scalation privilege)
|
SQL> begin sys.dbms_advanced_rewrite.declare_rewrite_equivalence
( 'new_tax', 'select price + (price * 0.035) as tax from
sales_table',-- Old query run internally by application 'select price + (price * 0.05) as tax from
sales_table', -- New query automatically run by CBO false); end; SQL> select price + (price * 0.035) as tax from sales_table; --
now calculates using new value of 5% instead of 3.5% |
Yes, this exploit requires
the EXECUTE privilege on the DBMS_ADVANCED_REWRITE package but that should give
you no comfort. History has clearly demonstrated that those already possessing
that privilege is not immune from temptation and blackmail. Neither is it
especially difficult using SQL Injection and other techniques to escalate
privileges to accomplish this attack. One example, the GLOGIN attack on our
exploits page could be used to grant the necessary object privilege.
2.***
DBMS_SQL_TRANSLATOR: The Arbitrary Text Changer***
The
DBMS_SQL_TRANSLATOR package, part of the SQL Translation Framework, is the
correct tool for making arbitrary, non-equivalent changes to SQL text 2.
It performs a simple text substitution before the optimizer ever sees the
query.
Scenario: Adding a
Filtering WHERE Clause (Logical Change)
This is the
perfect tool for your original request: adding a filtering WHERE clause to a
query from a fixed application.
Goal: For
the query SELECT * FROM customer, force the addition of the predicate WHERE
customer_name='ALI'.
|
Statement |
SQL Text |
|
Source
Query |
SELECT *
FROM customer |
|
Translated
Query |
SELECT *
FROM customer WHERE customer_name='ALI' |
Step-by-Step
Implementation:
|
-- 1. Create a SQL Translation Profile BEGIN DBMS_SQL_TRANSLATOR.CREATE_PROFILE( profile_name =>
'APP_FIX_PROFILE' ); END; / BEGIN
DBMS_SQL_TRANSLATOR.REGISTER_SQL_TRANSLATION( profile_name => 'APP_FIX_PROFILE', sql_text => 'SELECT * FROM customer', translated_text =>
'SELECT * FROM customer WHERE customer_name=''ALI''' ); END; / |
-- 3.
Activate the profile for the session or application user
-- For a
session:
ALTER
SESSION SET SQL_TRANSLATION_PROFILE = 'APP_FIX_PROFILE';
- 4. When
the application runs the original query:
SELECT *
FROM customer;
-- The
database will execute the translated query:
--
SELECT * FROM customer WHERE customer_name='ALI'
Scenario: Adding a Hint (Performance Change)
While
DBMS_ADVANCED_REWRITE is the intended tool for hints, DBMS_SQL_TRANSLATOR can
also be used, especially if you are already using the translation framework for
other logical changes.
Goal: Add the hint /*+ FULL(c) */ to the
query SELECT * FROM customer c.
Step-by-Step
Implementation:
|
-- 1. Assuming
'APP_FIX_PROFILE' exists, register the hint translation BEGIN
DBMS_SQL_TRANSLATOR.REGISTER_SQL_TRANSLATION( profile_name => 'APP_FIX_PROFILE', sql_text => 'SELECT * FROM customer c', translated_text =>
'SELECT /*+ FULL(c) */ * FROM customer c' ); END; / |
The Fundamental
Difference: Equivalence vs.
Translation
|
Feature |
DBMS_ADVANCED_REWRITE |
DBMS_SQL_TRANSLATOR |
|
Primary
Goal |
Performance
Optimization (Query Rewrite) |
SQL
Migration/Arbitrary Text Translation |
|
Equivalence
Constraint |
Yes (Officially requires functional
equivalence) |
No (Pure text substitution) |
|
Add
Hint |
Yes
(Intended use) |
Yes |
|
Add
Filtering WHERE Clause |
Possible,
but Risky
(Requires bypassing validation) |
Yes (Intended use for arbitrary
changes) |
|
Best
Practice for Logical Change |
Not
Recommended |
Recommended |
Conclusion:
For logical
changes (e.g., adding a filtering WHERE clause), DBMS_SQL_TRANSLATOR
is the correct and supported tool, as it is designed for arbitrary text
substitution.
For performance
changes that maintain functional equivalence (e.g., using an MV or adding a
hint), DBMS_ADVANCED_REWRITE is the intended and safer tool, as it
leverages the optimizer's integrity checks.
The ability
to use these tools to rewrite application SQL on the fly is a powerful
capability that allows DBAs to maintain performance and enforce security
policies without requiring lengthy application development cycles.
The
Meaning of BUILD_SAFE_REWRITE_EQUIVALENCE
The
procedure DBMS_ADVANCED_REWRITE.BUILD_SAFE_REWRITE_EQUIVALENCE is an internal-use
procedure designed to manage the complexity of advanced MV features,
specifically MV Decomposition.
1. MV
Decomposition and Submaterialized Views
In advanced
data warehousing scenarios, a single complex MV might be slow to refresh.
Oracle's MV Advisor (DBMS_ADVISOR.TUNE_MVIEW) can recommend decomposing this
complex MV into a top-level MV and one or more submaterialized views.
- Top-Level MV: The MV that the user's
query is intended to rewrite to.
- Submaterialized Views: Intermediate MVs that
pre-calculate portions of the data, making the top-level MV faster to
refresh (Fast Refresh) and more flexible for query rewrite.
2. The
Role of BUILD_SAFE_REWRITE_EQUIVALENCE
When the MV
Advisor recommends this decomposition, it needs a way to ensure that:
1.
The top-level MV can still be used for query rewrite.
2.
The fast refresh mechanism works correctly across the
hierarchy of MVs.
BUILD_SAFE_REWRITE_EQUIVALENCE
is the procedure that internally creates the necessary rewrite equivalence
declarations between the top-level MV and its underlying submaterialized
views. It essentially manages the complex metadata required for the optimizer
to understand the MV hierarchy and safely perform query rewrite and fast
refresh.
Why
Oracle Does Not Recommend Direct Use
The
documentation explicitly states:
"Oracle
does not recommend you directly use the BUILD_SAFE_REWRITE_EQUIVALENCE
procedure. You should use either the DBMS_ADVISOR.TUNE_MVIEW or the
DBMS_ADVANCED_REWRITE.CREATE_REWRITE_EQUIVALENCE procedure as
appropriate."
This
recommendation is based on the following reasons:
|
Reason |
Explanation |
|
Internal
Complexity |
The
procedure is designed to be called by Oracle's own scripts (specifically
those generated by DBMS_ADVISOR.TUNE_MVIEW). It likely requires a very
specific set of parameters and conditions that are difficult, if not
impossible, for a human user to correctly determine and maintain. |
|
Metadata
Management |
It handles
the intricate metadata that links the top-level MV to its submaterialized
views. Manually calling this procedure could corrupt the MV hierarchy
metadata, leading to incorrect query rewrite or failed fast refreshes. |
|
Supported
Alternatives |
Oracle
provides two high-level, supported alternatives that achieve the same goal
without the risk of manual intervention: DBMS_ADVISOR.TUNE_MVIEW (for
automatic decomposition) and DBMS_ADVANCED_REWRITE.CREATE_REWRITE_EQUIVALENCE
(for simple, manual MV creation). |
Why You
Cannot Find a Sample
You cannot
find a direct sample for BUILD_SAFE_REWRITE_EQUIVALENCE because it is an undocumented
or semi-documented internal API.
1.
It is Script-Generated: The only "sample" of
this procedure being called is within the PL/SQL script
that DBMS_ADVISOR.TUNE_MVIEW generates as its recommendation. A user
would typically run this generated script, not write the call
to BUILD_SAFE_REWRITE_EQUIVALENCE themselves.
2.
No Public Interface: Since Oracle does not support its direct use,
there is no official documentation or public example demonstrating its
parameters or expected behavior. Any attempt to use it directly would be an
unsupported hack, which is why the community avoids documenting it.
In summary,
while the procedure exists and is vital for advanced MV functionality, it is a black
box that should only be executed as part of a script generated by the
Oracle MV Advisor. For all user-driven MV rewrite needs, you should rely on the
supported procedures: DBMS_ADVANCED_REWRITE.DECLARE_REWRITE_EQUIVALENCE
or DBMS_ADVANCED_REWRITE.CREATE_REWRITE_EQUIVALENCE.
USER_TUNE_MVIEW and DBA_TUNE_MVIEW Views
Executing TUNE_MVIEW generates
two sets of output results: one for the implementation, and the other for
undoing the implementation. The output is accessible through USER_TUNE_MVIEW and DBA_TUNE_MVIEW views.
You can also use DBMS_ADVISOR.GET_TASK_SCRIPT and DBMS_ADVISOR.CREATE_FILE to
print the TUNE_MVIEW results into a script file for later execution.
Example:
The
following example shows how to use TUNE_MVIEW to optimize a CREATE
MATERIALIZED VIEW statement:
|
DECLARE v_tname VARCHAR2(30); BEGIN v_tname := 'mview_task'; DBMS_ADVISOR.TUNE_MVIEW( task_name => v_tname , mv_create_stmt => 'CREATE MATERIALIZED
VIEW omv REFRESH WITH ROWID AS SELECT * FROM orders'); END; You can view the results by querying USER_TUNE_MVIEW or DBA_TUNE_MVIEW as
the following example (sample output included): |
SET LINESIZE 120
COL TASK_NAME FORMAT a20
COL STATEMENT FORMAT a40
SELECT * FROM USER_TUNE_MVIEW WHERE TASK_NAME='mview_task'
AND SCRIPT_TYPE='IMPLEMENTATION';
TASK_NAME
ACTION_ID SCRIPT_TYPE STATEMENT
-------------------- ---------- --------------
----------------------------------------
mview_task 1 IMPLEMENTATION CREATE
MATERIALIZED VIEW LOG ON "OE"."OR
DERS"
WITH ROWID
mview_task 2 IMPLEMENTATION ALTER
MATERIALIZED VIEW LOG FORCE ON "OE
"."ORDERS" ADD ROWID
mview_task 3 IMPLEMENTATION CREATE
MATERIALIZED VIEW OE.OMV REFRESH
FAST
WITH ROWID DISABLE QUERY REWRITE
Alternatively,
you can save the output results in an external script file as in the following
example:
|
CREATE DIRECTORY TUNE_RESULTS_DIR AS '/tmp'; GRANT READ, WRITE ON DIRECTORY TUNE_RESULTS_DIR TO PUBLIC; BEGIN DBMS_ADVISOR.CREATE_FILE
( buffer => DBMS_ADVISOR.GET_TASK_SCRIPT (task_name
=> 'mview_task') , location => 'TUNE_RESULTS_DIR' , filename => 'mview_create.sql’); END; |
The
preceding statement will save the results in /tmp/mview_create.sql.
MV Concurrent
Refreshes (26ai)
In Oracle 26ai
materialized view refreshes can be performed concurrently for on-commit fast
refreshes by adding the ENABLE CONCURRENT REFRESH clause.
create
materialized view order_summary_rtmv
refresh fast
on commit
enable concurrent
refresh
as
select
order_id,
sum(line_qty) as sum_line_qty,
sum(total_value) as sum_total_value,
count(*) as row_count
from order_lines group by order_id;
exec
dbms_stats.gather_table_stats(null, 'order_summary_rtmv');
More info:
No comments:
Post a Comment