Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: DBMS_SQL Tuning Problem

Re: DBMS_SQL Tuning Problem

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 30 Jul 1998 00:27:00 GMT
Message-ID: <35c0bcae.3433206@192.86.155.100>


A copy of this was sent to rcfowler_at_my-dejanews.com (if that email address didn't require changing) On Wed, 29 Jul 1998 14:02:01 GMT, you wrote:

>It appears that DBMS_SQL is not using an optimizer hint provided in a SQL
>Statement...The syntax I'm using is as follows:SELECT /*+ INDEX(A MyIndex)
>*/A.FIELD1, B.FIELD2FROM Table1 A, Table2 BWHERE A.Field1 = B.Field1I
>can run this statement in PL/SQL and get a 1-2 second response time. The
>same statement, when Parsed and executed in DBMS_SQL returns a 30-40 second
>response time. I have tried several things, such as usingFIRST_ROWS along
>with the Index, the CHOOSE hint, as well as FIRST_ROWS with the index hint
>specified but the response time in DBMS_SQL remains unchanged - still 30-40
>seconds with PL/SQL at 1 to 2 seconds. Has anyone had similar experiences
>with DBMS_SQL? We are running Oracle7 Server Release 7.3.2.3.1. Our
>statistics are up to date, and I believeour default mode is Cost-Based
>optimization.Thanks for your help!Rick Fowler
>
>-----== Posted via Deja News, The Leader in Internet Discussion ==-----
>http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum

I suggest you add a call to:

dbms_session.set_sql_trace(TRUE);

in your pl/sql code before you run the dbms_sql call and also before you run it in pl/sql straightaway. (to be able to execute dbms_session.set_sql_trace() in a stored procedure, you will need to grant ALTER SESSION directly to the owner of the procedure)

You can then use TKPROF on the generated trace files (they will go into the directory specified by user_dump_destination in your init.ora file) to see exactly what sql is being executed and what the query plans are.

That will help track down what exactly is happening (and if your query to dbms_sql is in a string, not a constant -- you'll be able to see EXACTLY what sql statement you generated -- it might be a little different from what you think and that could be making the hint unusable or you might be using constants in the query where pl/sql is using bind variables and the queries you are comparing are different....  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA  

http://govt.us.oracle.com/ -- downloadable utilities  



Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Wed Jul 29 1998 - 19:27:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US