Oracle 23c New Features for DBA'sThe issue is there’s a query which executes quickly through 12c client in 12c database where as it is taking longer time when connecting as with 11g Clients to 12c database.
The table has a lob column and what observed is,
- 12c Client to 12c database:- During Execution of the query, direct path reads and PGA has grown upto 1.5gb, v$temporary_lobs shows the lobs created
- 11g Client to 12 c database :- During Execution of the query, db file sequential reads and PGA has consumed only 61mb, where v$temporary_lobs show nothing.
This is due to 12c clients create a server side temporary lobs (abstract lobs) and used PGA much and completed quickly.
Where in the 11g client does not have the enhancement to create abstract lobs and still fetches to the client side and caused query to run for 2 days.
This is listed as bug, 22729430 and after applying this patch the query running from 11g client also completed quickly.
Bottom Line, many things can happen especially you use many things in Oracle database. ;).
Hope this helps.
GeekDBA
Follow Me!!!