Re: Long Parse Time for a big Statement
Date: Tue, 18 Jan 2022 19:22:06 -0500
Message-ID: <ff2ae2cf-dbdc-8c27-a46a-6f9a50ccabe9_at_gmail.com>
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-- http://www.freelists.org/webpage/oracle-l Received on Wed Jan 19 2022 - 01:22:06 CET