Oracle Masters

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 7
1
2
3
4
5
6
7
>

Uncategorized

Tuesday, 13 Jul 2010 3:23 AM

Duplicate active database over network using RMAN – 11g

By Ramesh Menon

No Replies »

This post is a demonstration of duplicating an Oracle database to another server over the network. The article is for the scenario where ALL directory structures are the same on both the source and the destination database.

Read the rest of this entry »

Popularity: 12% [?]

Architecture andPerformance Tuning

Tuesday, 8 Jun 2010 5:35 PM

Query tuning

By niket

9 Replies »

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

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

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

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

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

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

Thursday, 3 Jun 2010 12:27 AM

10g rac

By tdomf_2a078

1 Reply »

hi ramesh

Can u explain about master and slave nodes. I have 4 node rac, if master is down , which node will take over as master out of 3 alive nodes.

2. can I mannually switch master and slave manually.

Popularity: 5% [?]

Architecture andDBA Posts

Thursday, 27 May 2010 7:52 AM

Oracle 9i binary installation.

By srini

No Replies »

We faced this error during the oracle 9I binary installation during (silent installation) on solaris 5.10 (While applying the patch set (9.2.0.6.0))

erver: /tmp/dumps/ship.patch.drop7/Disk1 $ ./runInstaller -silent -responseFile /tmp/dumps/ship.patch.drop7/Disk1/response/patchset.rsp
Starting Oracle Universal Installer…

Checking installer requirements…

Checking operating system version: must be 5.8 or 5.9. Actual 5.10
Failed <<<<

Exiting Oracle Universal Installer, log for this session can be found at /ora9i/oracle/oraInventory/logs/installActions2010-05-27_03-09-17PM.log

This error we rectified by editing the file
oraparamsilent.ini
oraparam.ini
By default these files were present on Disk1/install folder of the patch set dumps.

We added solaris 5.10 entry in to the above mentioned files under column

[Certified Versions]
Solaris=5.8,5.9,5.10

We gone through the installation.

Regards

Srinivas

Popularity: 2% [?]

Backup/Recovery andHA - RAC & Dataguard FAQs

Wednesday, 19 May 2010 8:14 AM

Recover physical standby database after loss of archive log – roll forward

By Ramesh Menon

No Replies »

This tutorial simulates a scenario, where in you are unable to recover your standby database because of a corrupted archive log file. The premise is that the archive log is corrupted on both production and standby, and we do not have a backup of this archive log.

The tutorial shows an example of how we can use RMAN incremental backup to recover the standby in such a scenario.

Read the rest of this entry »

Popularity: 7% [?]

HA - RAC, Dataguard

Friday, 7 May 2010 11:08 PM

Part 4 – Oracle 10gR2 RAC Solaris 10 – Installation and configuration of RAC database

By Ramesh Menon

2 Replies »

This entry is part 4 of 4 in the series 10gR2 RAC on x86 64bit Solaris 10 x86 - 64

With CRS successfully installed and running, we can proceed with installation of Oracle RAC binaries and configure a RAC database. In this section we will see the screenshots for Oracle RAC database creation.

Read the rest of this entry »

Popularity: 9% [?]

HA - RAC, Dataguard

Friday, 7 May 2010 10:34 PM

Part 3 – Oracle 10gR2 RAC Solaris 10 – CRS Installation

By Ramesh Menon

No Replies »

This entry is part 3 of 4 in the series 10gR2 RAC on x86 64bit Solaris 10 x86 - 64

On successful completion of cluster verification utility, you can proceed with installation of Oracle Clusterware. This section has the screenshots of clusterware installation including running the scripts as root from both nodes. DO NOT RUN these scripts parallely, run them on one node and then the other on completion of the script on the first node.

Read the rest of this entry »

Popularity: 2% [?]

Uncategorized

Friday, 7 May 2010 10:05 PM

Part 2 – Oracle 10gR2 RAC Solaris 10 – Prepare for CRS installation

By Ramesh Menon

No Replies »

This entry is part 2 of 4 in the series 10gR2 RAC on x86 64bit Solaris 10 x86 - 64

In this phase we will …..

Upload binaries to stage folder
Setup user equivalance
Run Cluvfy for CRS Setup

Read the rest of this entry »

Popularity: 3% [?]

HA - RAC, Dataguard

Friday, 7 May 2010 9:18 PM

Part 1 – Oracle 10gR2 RAC Solaris 10 – Introduction

By Ramesh Menon

1 Reply »

This entry is part 1 of 4 in the series 10gR2 RAC on x86 64bit Solaris 10 x86 - 64

Since Oracle 11g R2 requires quite a lot of resources, you can use the following article to setup Oracle 10g RAC on Solaris 10. The article has sections common with the Oracle 11g R2 tutorial. Hence, I have just put in links where ever possible, so as to not repeat common content.
NOTE: If you are missing some configuration information in the links, come back here to gather that information. The idea is to just follow the links with the appropriate configuration information from this section.

Read the rest of this entry »

Popularity: 3% [?]

Unix for DBAs

Friday, 7 May 2010 9:10 PM