• 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: Wiktor Moskwa <email@hidden>
  • Date: Tue, 21 Aug 2007 21:15:41 +0200
  • Organization: PowerMedia

Ken Anderson wrote:
> 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?

Install SQL Developer - it's free and easy to use:
http://www.oracle.com/technology/software/products/sql/index.html
All necessary information about tables, indexes and statistics will be
available to you. Explain plan will be very easy to generate.

For now issue following queries to learn about indexes and availability
of statistics:
=================================================
select table_name, status, num_rows, avg_row_len, sample_size, last_analyzed
from user_tables
where table_name='SETTLEMENT_PRICES';

select index_name, index_type, table_name, uniqueness, status, num_rows,
sample_size, last_analyzed
from user_indexes
where table_name='SETTLEMENT_PRICES';

select index_name, table_name, column_name, column_position
from user_ind_columns
where table_name='SETTLEMENT_PRICES';
=================================================

In the mean time I'll try to get something out of this query.

> Also, is there a way to run a query with bind variables from
> isqlplus?

Al queries run in sqlplus are using bind variables - values
are substitued by placeholders before sending to database.

--
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

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