Top 60 Oracle Blogs

Recent comments


Sir, Can you please highlight the usage of No_CPU_Costing hint. My system is a large Retail database and in many places we have huge queries. So optimization of queries is the topmost priority in my job. Many times I wonder in my queries if I would have used the hint No_CPU_Costing hint, my plan cost got lowered drastically and in certain cases, even the execution time is very less compared with the original. But till now I resisted myself in using this particular hint, since I don't have a clear idea whether this can be used extensively. Can you please advice about its usage. Thanks, K.Vidhyasagar

Read the answer...

Execution Order in PLAN_TABLE

Hello, there exists a "standard" 10g sql select (...without pl/sql) of the plan_table (or v$sql_plan) table that additionally displays the order of execution (column exord) as part of the output and i have it ! cannot be,yes,no, you have it since long time ? e.g.: ID|EXORD|OPERATION |OPTIONS |OBJECT_NAME ---|-----|------------------------------|---------------|-------------------- 0| 21|SELECT STATEMENT | | 1| 20|SORT |ORDER BY | 2| 19|++NESTED LOOPS | | 3| 17|++++NESTED LOOPS | | 4| 15|++++++NESTED LOOPS | | 5| 12|++++++++NESTED LOOPS | | 6| 9|++++++++++NESTED LOOPS | | 7| 6|++++++++++++NESTED LOOPS | | 8| 2|++++++++++++++TABLE ACCESS |BY INDEX ROWID |REGIONS 9| 1|++++++++++++++++INDEX |UNIQUE SCAN |REG_ID_PK 10| 5|++++++++++++++INLIST ITERATOR | | 11| 4|++++++++++++++++TABLE ACCESS |BY INDEX ROWID |DEPARTMENTS 12| 3|++++++++++++++++++INDEX |UNIQUE SCAN |DEPT_ID_PK 13| 8|++++++++++++TABLE ACCESS |BY INDEX ROWID |LOCATIONS 14| 7|++++++++++++++INDEX |UNIQUE SCAN |LOC_ID_PK 15| 11|++++++++++TABLE ACCESS |BY INDEX ROWID |COUNTRIES 16| 10|++++++++++++INDEX |UNIQUE SCAN |COU_ID_PK 17| 14|++++++++TABLE ACCESS |BY INDEX ROWID |EMPLOYEES 18| 13|++++++++++INDEX |RANGE SCAN |EMP_DEPARTMENT_IX 19| 16|++++++INDEX |UNIQUE SCAN |JOB_ID_PK 20| 18|++++TABLE ACCESS |BY INDEX ROWID |JOBS why isn't exord not a column of the plan_table (v$sql_plan) table, think it could help people in understanding explain plans ? Regards, Joachim

Read the answer...

How can you purge ex employees hr related data

How can you purge ex employees hr related data in order to meet data protection and data retention policies? There is no concurrent request or process to do it. Version 12.0.6. We want to do this in batches with date parameters not by using the delete function. Thanks

Read the answer...

Alternative to WRAP

oracle provide wrap utility and DBMS_DDL.WRAP Function to encrypt pl/sql code. its not possible to unwrap again. but on following site its easily possible just paste the encrypted code and u got unwrap code on only one click please tell me if any one have any other option or method for encrypt ps/sql code.

Read the answer...

Use OpenOffice

after i installed oracle linux 6, i discovered that there is no office application on it, is there a way i can install an office suite? if yes, please recommend Thanks Tim.

Read the answer...


Hello all, first of all, I wanted to tell you that this is not a challenge. I have tried to find a solution for my below question, but I couldn't. I asked for help on some forums like orafaq and OTN but no one helped me. I am expecting atleast some help here. I have set up a single instance standbys for rac databases. Now, I need to offload the backups from the primary on to the standbys. I learnt a few points to achieve this. 1. cancel managed recovery 2. connect to target(standby) and catalog and backup the standby 3. put standby in managed recovery mode. I think following these steps, I can restore primary(?). Now, My question is, how can I use these standby backups to clone/refresh databases? I tried it by connecting to target(primary), catalog and auxiliary but rman is using the primary backups instead of standby's to refresh the auxiliary database. If anyone can help me with their suggestions that would be great. I have a 11202 oracle on a linux box. Few followups to my question at this link

Read the answer...

Latch - cache buffer chains on small index (primary key) caused by concurrent batch scripts (select statement) around 300

The Oak Table challenge is not currently live - it goes active when a group of members of the OakTable arrange to meet at a public event - but since that wasn't clear when you raised the question, here are a couple of thoughts.
First - The Oracle version number really matters in this one; there have been bugs with latching on the root block of an index - the most recent that I know of being when an index had been rebuilt. See this blog item  
Second - is the latching problem on the root block or on the leaf blocks ? If it's only on the root block then trying to spread the index entries across multiple leaf blocks isn't going to help anyway
Third - Using pctfree to limit the number of rows per block only works when you create or rebuild the index; if you're deleting and inserting rows in the batch then the pattern of activity may simply leave you with well-packed blocks after a very short time. (Possibly this is why you are rebuilding the index every two minutes - but that's likely to cause other, more significant, overheads anyway).
Fourth - your question suggests that  you're only using select statements against this table: if that's true I can't see how rebuilding the index every two minutes could help - unless you're on an old version of Oracle that had bugs that generated excessive read-consistent copies of blocks.

S.O.S. Swedish Oracle Specialist...

SQL and PL/SQL, response time and resource optimisation, performance tuning and health checks, development and DBA, problem solving and trouble shooting, coaching and mentoring.
But also... voice-over, training, learning, english teaching

Welcome to the Oaktable

Welcome to the OakTable. The OakTable network is a network for the Oracle scientist, who believes in better ways of administering and developing Oracle based systems. The network is an informal organisation, which will get together at various Oracle events.