ORA-907 Error From SQL Tuning Advisor When Running It Against STS SQL Created by SPA
(Doc ID 2331851.1)
Last updated on APRIL 01, 2024
Applies to:
Oracle Database - Enterprise Edition - Version 12.1.0.2 and laterOracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Information in this document applies to any platform.
Symptoms
- LARGE NUMBER OF ORA-907 ERROR DURING SQL Tuning Advisor Analyzing SQL
- Error SQL is from SQL Tuning Set (STS) which created by SQL Performance Analyzer(SPA)
- Error SQL is like dynamic dynamic sampling statements with the undocumented SAMPLE BLOCK / SEED construct
SELECT
/* OPT_DYN_SAMP */
/*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false') NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */
NVL(SUM(C1),:"SYS_B_0"),
NVL(SUM(C2),:"SYS_B_1")
FROM
(
SELECT
....
END AS C2
FROM <USER.TABL_NAME> SAMPLE BLOCK (:"SYS_B_6" , :"SYS_B_7") SEED (:"SYS_B_8") "A1"
)
SAMPLESUB
Changes
Upgrade 12c using SPA testing.
Cause
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
Symptoms |
Changes |
Cause |
Solution |
References |