Hi Mladen,
thanks, but that is obvious. 😁
Regards
Lothar
Am 19.01.2022 um 01:22 schrieb Mladen Gogala:
On 1/18/22 10:26, Jonathan Lewis wrote:
f Lothar can query v$sql the way you've suggested then looking at typecheck memory is worth doing; though it does need to be after an attempt to execute the query since EXPLAIN PLAN behaves differently from the parsing for execution so a positive check for TCHK won't necessarily prove anything. The other detail to watch out for is whether it will be necessary to query x$glob for the correct columns as the statement might not be revealed in v$sql if it fails parsing.
Lothar's client obviously has problems with the data model. 130,000 lines in a query, which includes a view querying 55 tables means only one thing: whoever has designed the data model needs to suffer an unusual and cruel punishment. The obvious solution is to simplify the data model. I would say that if the data model supports such monstrosities as views over 55 tables, the data model has reached its end of life and the application needs to be redesigned from scratch. This is obviously a data warehouse type application and maybe the designers should read the book by Kimball & Ross called "The Data Warehouse Toolkit". Lothar asked if there are any quick fixes. No, there are no quick fixes here. The application and the data model will have to be redesigned.
--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com
-- //www.freelists.org/webpage/oracle-l
