sql - Low Impact End-to-End Database Connection & Query Test -

the database oracle. goal perform status check in code is:

• robust
• end-to-end (healthy connection not mean healthy objects, i.e. views offline tables)
• creates minimal overhead calling application

to suffice these various requirements, i've come following query:

select null view_name null not null 

so let's break down:

• select null attempt "leverage" sql result caching explicitly specifying value.
• from view_name fail if view_name not present in database (i.e. end-to-end).
• where null not null attempt avert table scans, return 0 records, etc.

any thoughts, improvements, suggestions, etc. appreciated. know if there conceivable issues query or approach.

you better off checking valid objects under user connecting as.

select count(*) user_objects status != 'valid'; 

or if looking valid objects in db, then:

select count(*) obj$ status != 'valid'; 

just make sure query not run - 50 times minute.

an better approach optimistic , attempt operation - select/update/insert/delete want against db.

invalid objects cause errors(which need handle in code anyway), focus on having robust error handling in application , don't worry making connection see if ok in db.

your test check db object status may pass, next instant, db object can go invalid.