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

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% [?]

Architecture
Friday, 5 Jun 2009 9:48 PM

Global Temporary Table

By niket Niket Vora

1) Global temporary table use temp segments when inserted but when deleted we still get information about the table in dba_tables view.
Where does it store information?

2) Does performace increases while using global temporary table while generating report.

3) As it uses temporary tablespace there will be less space available for other sort transaction – right
So How much data is advisable to store in global temporary table ?

Kindly guide us.

Regards
Niket

Popularity: 93% [?]

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% [?]

Architecture andReal Life Scenarios
Thursday, 14 May 2009 7:26 AM

Cause of Block Corruption in Testing Environment

By niket Niket Vora

We are facing block corruption in testing environment.

Immediate solution is restore and recovery from production and testing environment.

But Want to know cause behind block corruption.

Popularity: 94% [?]

Control Panel

Login



My Links

Blogroll

Project Management

Recent Posts

Recent Comments
Internet Advertisingundelete