Senior Oracle DBA Q&A

 

Q) My incremental bkp is running more time than usual any particular reason for that?

If you haven't seen this issue before and assuming database size didn't changed then you have to check the speed between database server and media/backup server.

Sometimes if we change the NIC card configuration linked with backup server then  such issues pop-ups

Also check with OS team first and ask them if they have seen same issues with OS backups also (if Scheduled).

 

Q) Why voting is disk is odd in number?

More 50 % always available concept by Oracle...

2 when you divide by 2 is 1 and 3 when divided by 2 is 1.5 (consider it 2 as a whole Number) .

So odd number will always be higher in case of a voting disk availability.

So more than 50% is availability achieved by it then.

Whoever scores more will survive, else will get eliminated.

 

Q) How long SQL_ID will be in shared pool ?

Till it is not flushed out

If there is a pressure on shared pool, old SQLs might get flushed

 

 

Q) Why SQL_plan_hash value is changed and why child_number change for SQLID?

The answer for why SQL PHV change is .

When a SQL of a particular SQL ID is parsed optimizer generate multiple plans and CBO to select the best plan among multiple. Plan based on the cost. After some days if you insert loads of data or stats gather the object then optimizer will again generate the best plan and if this plan is best in terms of cost it will start using this new plan.

 You can also manually change the phv of a SQL id by using DBMS _spm package

 

Table EMP with 1 Million Rows.

Index on GENDER Column. M is 98% and F is 2 %.

SELECT * FROM EMP WHERE GENDER=:B1 for M will be a Full Table Scan with a PLAN_HASH_VALUE and CHILD_NUMBER as 0.

The same query with value as F will be an Index Scan and therefore, will have different PLAN_HASH_VALUE and CHILD_NUMBER as 1.

A query can have multiple plans depending upon the bind variable values and each plan will create a child cursor.

 

Another example...

Table EMP with 1 Million Rows. No Index on Gender Column. M is 98% and F is 2%.

Query 1 : select * from emp where gender=:b1 and the value is M.

It goes for a Full Table Scan and Plan_hash_value is some value with child_number 0.

Now, I set optimizer_index_cost_adj to 10 at session level and re-run the same query for M.

Again a Full Table Scan as I do not have an Index on Gender.

The Plan_hash_value remains same, but child_number is now 1..so, same plan_hash_value and 2 Child_numbers..

 

Oracle will use histogram to identify skewedness of data in order to decide whether child no 0 or 1 is appropriate for execution.

Yes. Histogram will play a critical role here

Nice example for new child cursor creation. This is one case where new child cursor is created for same plan_hash_value.. there are multiple factor which cause new child creation like length of bind from application, different character sets for the bind, different cardinality estimate for bind aware cursor.

 

 

Q) How can we decide, when to use SQL profiling and SQL Baseline ?

We can decide to use SQL plan baseline when your SQL statement have multiple plane hash value. Out of those multiple phv if optimizer is using bad phv when there are good phv present you can use baseline to force optimizer to use good phv to avoid performance regression

Dba_sql_plan_baseline

See how phv that SQL has

Check the columns enabled accepted fixed

Of enabled is yes then that phv is currently in use

If accepted is no then that phv is accepted in baseline but not used

If you want to permanent allocate phv to a SQL then fix it

But in SQL profiling, we can use good phv based on elapsed time and IO.

 

Q) SQL profiling good or SQL Baseline?

SQL baseline will be good

SQL Profile for reactive situation and SQL baseline for Proactive

 

                           

Q) Why to rebuid indexes?

               Index data constantly changes due to the underlying table’s DML activity. Indexes often become too large if there are many deletions, because the space used by the deleted values is not reused automatically by the index. You can use the REBUILD command on a periodic basis to reorganize indexes to make them more compact and thus more efficient.

 

Q) What happens in background during patching?

               1.Opatch takes backup of those files from O_H which it is going to update

               2.It takes this backup in O_H/.patch_storage directory

               3.It then copies files from /etc and /files to O_H

               4.After that it takes backup of Inventory in /files

               5.Relinks all O_H binaries and update inventory with patch ID

               6.Finally it displays- patch applied

 

Q) Facing performance issues with these wait events not completing the processing requests.

 

1.Library cache Mutex x

 

Finding 2: Shared Pool Latches

Impact is .07 active sessions, 2.28% of total activity.

-------------------------------------------------------

Contention for latches related to the shared pool was consuming significant

database time.

Waits for "cursor: pin S" amounted to 2% of database time.

 

Explanation:

Most common reason for mutex X is server process trying to add entry in libray cache.

Reason could be application is not using bind variables...is so ask appl to use binds else change cursor sharing to force

Check if there are sqls which has multiple child cursors...generally multiple child cursors are result of bind mismatch...ask appl team to use variable type assigned to variable in consistent manner. For example variable which takes number can be declared as init, small int, float etc...this is example at rudimentary level

If SQL involves tables which is partitioned and partitions are created on the fly at frequent intervals...in that case also cursors  associated with sql get invalidated...for this it required cursor in exclusive mode...

Make sure sga is properly sized..

Limit the number of child cursors for sqls by changing hidden parameter _cursor_obsolete_threshold to 100 ..this requires blessing from Oracle though..

These are few steps out of many to start with

You can ignore cursor pinS as in your case it amounts to only 2% of db  time.

 

Q) Consider a manual configuration in which 1 GB of memory is available for the SGA and distributed to the following initialization parameters:

–       SHARED_POOL_SIZE=128M

–       DB_CACHE_SIZE=896M

•        If an application attempts to allocate more than 128 MB of memory from the shared pool, an error is raised that indicates that the available shared pool has been exhausted. There could be free memory in the buffer cache, but this memory is not accessible to the shared pool. You would have to manually resize the buffer cache and the shared pool to work around this problem.

•        With automatic SGA management, you can simply set the SGA_TARGET initialization parameter to 1G. If an application needs more shared pool memory, it can obtain that memory by acquiring it from the free memory in the buffer cache.