Tom, where can I look to find out about UPI? The net80 book?

Dear Tom,
Thank you for your response. It is helpfull.

Thanks Lubos

I have a well tuned application runs on Oracle 8i and 9i with VB as front end connects the DB through RDO connections. ie uses Oracle ODBC drivers. Well in my recent tuning of some expensive SQL statements that I captured in v$Sql view hold my breath. After a thorough investigations I found those are the SQL always being generated when that application connects the DB and believe me those SQL statements are consuming a lot amount of

Sharable Memory = 303180
persistemt_memory = 1548
runtime_memory=78172
sorts=2
buffer_gets = 37059

But when I check the entire SQL statements the top 50 or 100 statements of such (below ) consuming a lot resources. How can I avoid such time taking statements run in the Shared-pool and is there any way to by pass this kind of SQL generation or any suggestions to my situations. I am presenting below few of such statements that showing more resoures consumable but the final rows process are only 17 or 2. I have also planned at one time either I should create on any additional index on sys base tables accrodig to these SQL statements ..but couldn't gain anything.

SQL statements found in v$SQL:-
================================

SELECT '', a.owner, decode (b.object_type, 'PACKAGE', CONCAT( CONCAT (b.object_name, '.'), a.object_name), b.object_name),decode(a.position, 0, 'RETURN_VALUE', a.argument_name), decode(a.position, 0, 5, decode(a.in_out, 'IN', 1, 'IN/OUT', 2, 'OUT', 4)), 0, a.data_type, a.data_precision, a.data_length, a.data_scale, a.radix, 2, '' FROM ALL_ARGUMENTS a, ALL_OBJECTS b WHERE ( b.object_type = 'PROCEDURE' OR b.object_type = 'FUNCTION' ) AND b.object_id = a.object_id AND a.data_level = 0 AND a.OBJECT_NAME LIKE 'EP_UPDATE_SERVICE_DELIVERY' ORDER BY a.position



SELECT '', a.owner, decode (b.object_type, 'PACKAGE', CONCAT( CONCAT (b.object_name, '.'), a.object_name), b.object_name),decode(a.position, 0, 'RETURN_VALUE', a.argument_name), decode(a.position, 0, 5, decode(a.in_out, 'IN', 1, 'IN/OUT', 2, 'OUT', 4)), 0, a.data_type, a.data_precision, a.data_length, a.data_scale, a.radix, 2, '' FROM ALL_ARGUMENTS a, ALL_OBJECTS b WHERE ( b.object_type = 'PROCEDURE' OR b.object_type = 'FUNCTION' ) AND b.object_id = a.object_id AND a.data_level = 0 AND a.OBJECT_NAME LIKE 'EF_PHONE' ORDER BY a.position


Appreciate any suggestions or any clues "How to avoid to generate such expesive SQL for the current situation of my application which uses Oracle ODBC" Due to this expensive SQL my initial connections take a bit longer and when I ran this query as separate then I figured it out these statements are the main culprits to run at least sometimes 25 seconds run time to produce result sets.

Thanks

Tom,

My product runs on three different versions of Oracle successfully as of today and those are:-

Oracle DB Version Oracle ODBC client
=================== ===================
8.1.5.0 No patch 8.1.5.5 ODBC release

8.1.6.0 Release 2 8.1.6.3.4 ODBC Release

9.i Relese 2 with patch 9.02.00.00 ODBC Release.

I presented those above SQL statements were generating in all the versions of databases commonly and they are cosuming a lot memory too. I am thankful for your reply and watching these postings
9.2.0.2.1

Tom,

How did you find out from the SQL statements in v$SQL that the version of the ODBC drivers used was 9203?

Hi Tom,

I spent some time trying to search for my answer on your site, metalink, otn, and google but couldn't find anything.

We are bringing a new 3rd party app that uses oracle as a backend database. The vendor says that they would build their application schema and would only need an odbc connection to the target db.

However, one of the project managers in our team claims that this is not possible -- ie. one cannot issue ddl/dcl on an odbc connection (from the Windows server) to oracle.

I never heard of this before and tried to find evidence to support/debunk this statement.

Can you please help?

thanks!