• Open Menu Close Menu
  • Apple
  • Shopping Bag
  • Apple
  • Mac
  • iPad
  • iPhone
  • Watch
  • TV
  • Music
  • Support
  • Search apple.com
  • Shopping Bag

Lists

Open Menu Close Menu
  • Terms and Conditions
  • Lists hosted on this site
  • Email the Postmaster
  • Tips for posting to public mailing lists
Re: Poor Oracle performance? getting rid of bind variables
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Re: Poor Oracle performance? getting rid of bind variables


  • Subject: Re: Poor Oracle performance? getting rid of bind variables
  • From: Pascal Robert <email@hidden>
  • Date: Tue, 21 Aug 2007 15:13:48 -0400

I suggest that you use Oracle SQL Developer :

http://www.oracle.com/technology/products/database/sql_developer/ index.html

It's ugly, but it works quite well. You will be able to see if statistics are gathered and the indexes and constraints for each table.

Wiktor,

Here's the query:

SELECT t0.Hub_ID, t0.Is_option_contract, t0.Market_id, t0.Product_ID, t0.Settlement_change, t0.Settlement_price_date, t0.Settlement_price, t0.Settlement_price_id, t0.Strip, t0.Strip_begin_date, t0.Strip_end_date, t0.Strip_type FROM Settlement_prices t0 WHERE (t0.Product_ID = 451 AND t0.Hub_ID = 620 AND t0.Settlement_price_date = '20-Aug-07' AND t0.Is_option_contract = 'N')

When generated from EOF, all 4 where clause are bind variables.

Unfortunately, I don't have access to much database wise... all I can do is complain to the DBAs.

How can I find out what indexes are on the table, and/or what a plan would be? Also, is there a way to run a query with bind variables from isqlplus?

Thanks much,
Ken

On Aug 21, 2007, at 2:47 PM, Wiktor Moskwa wrote:

Ken Anderson wrote:
I'm killing my database now. I'm querying against a table that has
millions of rows, but I only need 20-30. With bind variables, it takes
over 20 seconds - without them, a few hundred milliseconds.



That means for 99% that statistics are not available. Gather them and check the query plan again. The other 1% percent is reserved for certain cases when optimizer needs to be hinted or special indexes created.

If you post the statement and table/index DDL I'll be glad to help.

By not using bind variables every SQL statement will have to be hard
parsed every time it is send to the database. Hard parsing basically
means serializing access to some parts of database's shared memory.

--
Wiktor Moskwa

_______________________________________________ Do not post admin requests to the list. They will be ignored. Webobjects-dev mailing list (email@hidden) Help/Unsubscribe/Update your Subscription: This email sent to email@hidden

_______________________________________________ Do not post admin requests to the list. They will be ignored. Webobjects-dev mailing list (email@hidden) Help/Unsubscribe/Update your Subscription: This email sent to email@hidden
References: 
 >Re: Poor Oracle performance? getting rid of bind variables (From: Ken Anderson <email@hidden>)
 >Re: Poor Oracle performance? getting rid of bind variables (From: Wiktor Moskwa <email@hidden>)
 >Re: Poor Oracle performance? getting rid of bind variables (From: Ken Anderson <email@hidden>)
 >Re: Poor Oracle performance? getting rid of bind variables (From: Wiktor Moskwa <email@hidden>)
 >Re: Poor Oracle performance? getting rid of bind variables (From: Ken Anderson <email@hidden>)

  • Prev by Date: Re: Poor Oracle performance? getting rid of bind variables
  • Next by Date: Re: Poor Oracle performance? getting rid of bind variables
  • Previous by thread: Re: Poor Oracle performance? getting rid of bind variables
  • Next by thread: Re: Poor Oracle performance? getting rid of bind variables
  • Index(es):
    • Date
    • Thread