The BEST way to FETCH from the database

[@ilpaolotopleggend6663]

Complex for me but happy to learn by the best

[@DatabaseDude]

hopefully it didnt dive too deep

[@ilpaolotopleggend6663]

I get the sense of it but not sure about latch chain buffer cache how works internally ecc

[@JimmyBrock]

Love these deep dives

[@marcinbadtke]

Thank you Connor. Inspiring.

[@berndeckenfels]

Hm isn’t the Jdbc driver also using array fetches when you iterate a ResultSet

[@DatabaseDude]

You choose the size. Older drivers used 2 (the minimum) and I think we now default to 10.

[@virajmokadam4719]

Thanks. If we use bulk collect in plsql with fetch size >100, will it be more beneficial?

[@DatabaseDude]

"Depends" .... for example, a slow network might benefit from larger sizes. Typically 100-200 is the sweet spot with diminishing benefits after that

[@virajmokadam4719]

@@DatabaseDude ok

[@MrEnrique2k]

Love it

[@ahmedinomazic7032]

Great info but can this be used in function, as i see in v$sql most of rows p./fetches are from functions which some basic select count(*) ?

[@DatabaseDude]

sorry I don't quite understand what you are asking

[@ahmedinomazic7032]

@@DatabaseDude In my database in v$sql most of fetches is from functions if I set up for i in ()... would it benefit , would the latches reduce ?

[@DatabaseDude]

@@ahmedinomazic7032 without seeing the context, its hard to say, but probably yes

[@akhilchintu6938]

Hello Sir,
My database is not coming up and not able to open due to ORA-01157:datafile corruption.
errors:
ORA-01157: cannot identify/lock data file 22 - see DBWR trace file
ORA-01110: data file 22: 'datafile/location'
And Assumptions are,
1) DB is non-archive log mode
2) DB is in mount stage
3) no RMAN backup
4) its a nonsytem datafile
How to recover the datafile by following above assumptions and open the database?
Kindly help me out of this.

[@DatabaseDude]

asktom.oracle.com/ords/asktom.search?tag=unable-to-open-the-database-ora-01157-cannot-identifylock-data-file