Posts

    [ + | - ]

Archives

    [ + | - ]

Resource Pages

    [ + | - ]

Welcome to Oracle Masters. The portal, as the tagline says, is for anything Oracle. FAQs, Certifications, Resources! In addtion, there will be pages on real life scenarios, Oracle internals, approaching the DBA job at various stages in your career and many other useful resources.Further, if you have a question, you can ask it and we will answer it at the earliest!
So join in. Learn, share and prosper!
Register with us!





Ramesh Menon
Oracle Certified Master - 10g
Oracle Certified RAC Expert - 10g
Oracle Certified Professional - 11g

Are you interested in Weekend/Online Training in Oracle DBA? Do you have business/consulting requirements? Click here Enquiry Form to leave your requirements or mail me at Ramesh Menon

  • Page 1 of 2
  • 1
  • 2
  • >
Architecture andPerformance Tuning
Tuesday, 8 Jun 2010 5:35 PM

Query tuning

By niket Niket Vora

Optimizer is not using index when i am giving hint to the query which contains bind variables

It is generating its own plan

The query is

?Download text
1
2
3
4
5
6
7
8
9
10

SELECT b.amount, b.invoice_charge_price, b.charge_id, b.internal_agreement_no,
       b.item_code, b.entity_id, b.agreement_id, b.start_date, b.end_date,
       b.charge_code, b.charge_type, price_inclusive_of_taxes,
       b.pricing_entity_id, b.party_slaccount
  FROM bt_billing_charges b
 WHERE b.entity_id = :b3
   AND NVL (b.invoice_no, 0) = 0
   AND b.paid_status = 'N'
   AND b.cp_code = NVL (:b2, b.cp_code)
   AND b.paymode_trans_no = NVL (:b1, b.paymode_trans_no);

Now execution plan is
============

?Download text
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

Plan Table
============
----------------------------------------------------------------+--------------------------------
---+
| Id  | Operation                      | Name                   | Rows  | Bytes | Cost  | Time
   |
----------------------------------------------------------------+--------------------------------
---+
| 0   | SELECT STATEMENT               |                        |       |       |   539 |
   |
| 1   |  CONCATENATION                 |                        |       |       |       |
   |
| 2   |   FILTER                       |                        |       |       |       |
   |
| 3   |    TABLE ACCESS BY INDEX ROWID | BT_BILLING_CHARGES     |     1 |    88 |   269 |  00:00:
04 |
| 4   |     INDEX RANGE SCAN           | IDX_BT_BILLING_ENTITYID|   807 |       |     8 |  00:00:
01 |
| 5   |   FILTER                       |                        |       |       |       |
   |
| 6   |    TABLE ACCESS BY INDEX ROWID | BT_BILLING_CHARGES     |     1 |    88 |   269 |  00:00:
04 |
| 7   |     INDEX RANGE SCAN           | IDX_BT_BILLING_ENTITYID|   807 |       |     8 |  00:00:
01 |
----------------------------------------------------------------+--------------------------------
---+
Predicate Information:
----------------------
2 - filter(:B1 IS NULL)
3 - filter((NVL("B"."INVOICE_NO",0)=0 AND "B"."PAID_STATUS"='N' AND "B"."PAYMODE_TRANS_NO" IS NOT
 NULL AND "B"."CP_CODE"=NVL(:B2,"B"."CP_CODE")))
4 - access("B"."ENTITY_ID"=TO_NUMBER(:B3))
5 - filter(:B1 IS NOT NULL)
6 - filter((NVL("B"."INVOICE_NO",0)=0 AND "B"."PAID_STATUS"='N' AND "B"."CP_CODE"=NVL(:B2,"B"."CP
_CODE") AND "B"."PAYMODE_TRANS_NO"=:B1))
7 - access("B"."ENTITY_ID"=TO_NUMBER(:B3))

Now i am passing hint to the query

?Download text
1
2
3
4
5
6
7
8
9
10

SELECT /*+ INDEX (b BT_BILLING_CHARGES_I4) */ b.amount, b.invoice_charge_price, b.charge_id, b.internal_agreement_no,
       b.item_code, b.entity_id, b.agreement_id, b.start_date, b.end_date,
       b.charge_code, b.charge_type, price_inclusive_of_taxes,
       b.pricing_entity_id, b.party_slaccount
  FROM bt_billing_charges b
 WHERE b.entity_id = :b3
   AND NVL (b.invoice_no, 0) = 0
   AND b.paid_status = 'N'
   AND b.cp_code = NVL (:b2, b.cp_code)
   AND b.paymode_trans_no = NVL (:b1, b.paymode_trans_no)

But still it is using same plan.

Now when i removed bind variables with values then it is using index

?Download text
1
2
3
4
5

SELECT /*+ INDEX (b BT_BILLING_CHARGES_I4) */ B.AMOUNT,B.INVOICE_CHARGE_PRICE,B.CHARGE_ID,B.INTER
NAL_AGREEMENT_NO, B.ITEM_CODE,B.ENTITY_ID,B.AGREEMENT_ID,B.START_DATE,B.END_DATE, B.CHARGE_CODE,B
.CHARGE_TYPE,PRICE_INCLUSIVE_OF_TAXES,B.PRICING_ENTITY_ID,B.PARTY_SLACCOUNT FROM IMCL420.BT_BILLI
NG_CHARGES B WHERE B.ENTITY_ID=12345263 AND NVL(B.INVOICE_NO,0)=0 AND B.PAID_STATUS='N' AND B.CP_
CODE=NVL(0,B.CP_CODE) AND B.PAYMODE_TRANS_NO= NVL(0,B.PAYMODE_TRANS_NO)

and exec plan is

?Download text
1
2
3
4
5
6
7
8
9
10
11
12
13

Plan Table
============
-------------------------------------------------+-----------------------------------+
| Id  | Operation         | Name                 | Rows  | Bytes | Cost  | Time      |
-------------------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT  |                      |       |       |     5 |           |
| 1   |  INDEX RANGE SCAN | BT_BILLING_CHARGES_I4|     1 |    88 |     5 |  00:00:01 |
-------------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
1 - access("B"."ENTITY_ID"=12345263 AND NVL("INVOICE_NO",0)=0 AND "B"."PAID_STATUS"='N')
1 - filter((NVL("INVOICE_NO",0)=0 AND "B"."PAID_STATUS"='N' AND TO_NUMBER("B"."CP_CODE")=NVL(0,TO
_NUMBER("B"."CP_CODE")) AND "B"."PAYMODE_TRANS_NO"=NVL(0,"B"."PAYMODE_TRANS_NO")))

so my question is how to force query containing bind variables to use index

Kindly guide me

Thanks

Regards

Niket

Popularity: 16% [?]

Performance Tuning andUncategorized
Sunday, 9 Aug 2009 2:30 AM

Generating Execution Plan for a SQL Query

By Ramesh Menon Ramesh Menon

Generating Execution Plan for a SQL Query

To find the execution path of a query, first create the PLAN_TABLE by running the $ORACLE_HOME/rdbms/admin/utlxplan.sql script.

Once created, you can use any of the following methods to see a query’s execution path.
Read the rest of this entry »

Popularity: 15% [?]

Architecture andPerformance Tuning
Sunday, 9 Aug 2009 12:06 AM

Explain Plan

By tdomf_2a078

Hi Ramesh,

Wat are the different ways for checking the explain plan for a particular query ??

Regards,
Vinita

Popularity: 8% [?]

Architecture andPerformance Tuning andPerformance Tuning FAQs
Monday, 13 Jul 2009 1:03 AM

Diff:Latches and Locks

By Ruxina Ruxina Lopes

Dear Ramesh,

Please help to understand the difference between latches and locks.

Thanks,
Ruxina

Popularity: 19% [?]

Performance Tuning andUncategorized
Saturday, 11 Jul 2009 9:38 PM

Enqueues – Demonstration

By Ramesh Menon Ramesh Menon
This entry is part 2 of 2 in the series Enqueues

In this section, we will go through a demo of generating and detecting locks with scripts

Read the rest of this entry »

Popularity: 53% [?]

Architecture andPerformance Tuning
Saturday, 20 Jun 2009 1:15 AM

Histogram

By sandeepchavan25 sandeep chavan

what is histogram?wht are the types of histogram in details

Popularity: 93% [?]

Architecture andPerformance Tuning
Friday, 15 May 2009 9:19 AM

Table Fragmenetation.

By kiranrane kiran rane

How to find out table fragementation?
and row chaning and migration?

any example and any solution on the above issue.

Popularity: 87% [?]

Architecture andPerformance Tuning
Thursday, 14 May 2009 7:26 AM

Materialised view and Query rewrite.

By niket Niket Vora

1)What is Materialised View and Query Rewrite?

2) How it is done? – Any EWxample.

3) What are the advantages and disadvantages of Materialised View and Query Rewrite ?

Popularity: 87% [?]

Performance Tuning
Saturday, 28 Mar 2009 10:13 AM

Oracle Tracing – Oradebug

By Ramesh Menon Ramesh Menon
This entry is part 5 of 6 in the series Oracle Tracing

Oradebug is a powerful utility provided by Oracle. This tool is normally used on directions from Oracle support as its contents are mostly undocumented. However, many documents by third parties are readily available on the internet.

This tool should be used with utmost care and understanding. We will restrict our discussion about this tool to tracing, and will cover other features some other day!
Read the rest of this entry »

Popularity: 63% [?]

Performance Tuning
Saturday, 28 Mar 2009 8:14 AM

Oracle Tracing – DBMS_SYSTEM

By Ramesh Menon Ramesh Menon
This entry is part 2 of 6 in the series Oracle Tracing

Oracle’s dbms_system package can be used to manipulate other user sessions, trace sessions and other purposes. The DBMS_SYSTEM package is well known, but it’s an undocumented package. You won’t find references to it in the manuals. Oracle doesn’t support the use of this package, and you use it at your own risk.
Read the rest of this entry »

Popularity: 8% [?]

  • Page 1 of 2
  • 1
  • 2
  • >

Control Panel

Login



My Links

Blogroll

Project Management

Recent Posts

Recent Comments
Internet Advertisingundelete