It is about Database (Need Help in it) With regard to the Oracle clustering factor.

Question

It is about Database (Need Help with it)

1. 1. With regard to the Oracle clustering factor.
   2. Explain what it is and how Oracle compute clustering factor.
   3. Explain how the clustering factor is used to estimate the cost of indexed based access to a Table.
   4. After exercising “deadlock” part.example.sql, summarize how Oracle detect and resolve deadlock situation.
   5. With regard to the WAL (write-ahead logging) protocol,
   6. Describe what is WAL protocol
   7. Explain what happens when the WAL protocol is not followed?
   8. Explain how the WAL protocol enable to achieve the atomicity of non-committed transactions upon failures
   9. Assuming Non-Volatile RAM (NVRAM in short, where the data persist upon power outage) and undo log is stored in NV DRAM, do we still need to follow the WAL protocol? Justify it.

Summary

1. The definition of the clustering factor and how it is calculated is described in question 1. Also mentioned is the method for estimating the cost.
2. The method by which the oracle discovers the stalemate and resolves it is discussed in detail in the answer to question 2.
3. The use of the WAL protocol, as well as how it assures integrity and atomicity, is discussed in the answer to question 3.

Explanation

2) Describe what clustering factor is and how Oracle calculates it.
When comparing an index to the table on which it is based, the measure of its orderedness.
It determines how much a table lookup will cost.
It is based on index access.
The following is how a clustering factor is calculated:
o The index will be scanned in order first.
o The current index value is used to compare the block component of the ROWID to the previous      indexed value.
o If the ROWIDs point to separate table blocks, the clustering factor will be increased.

 

3) Describe how the clustering factor is utilised to calculate the cost of indexed-based Table access.
The clustering factor is mostly used to calculate the cost of indexed-based table access.
It calculates the cost by multiplying the clustering factor by the selectivity of the index.
It specifies the cost of the procedure.

 

4) Summarize how Oracle detects and resolves deadlock situations after running “deadlock” part.example.sql.

In SQL Server’s lock monitor, a deadlock detector will be present.
This examines the locks on a regular basis to see whether any circular locking chains are present.
If the detector detects a circular locking chain, it chooses a session associated with a suspended thread, kills it, rolls back the transaction (to undo any modifications made up to that point), and then unlocks the lock.
The deadlock victim is the killed session, which receives an error of 1205.

A lock monitor will choose the deadlock victim based on the DEADLOCK PRIORITY setting for each session, as well as the amount of labour it would take to roll back the transaction.

 

5)  Explain what the WAL protocol is.

6) WAL refers to a group of algorithms that provide database systems with atomicity and durability (Write-Ahead Logging).

All modifications are basically written to a log before being applied in a system that uses WAL.

 

7) Describe what occurs if the WAL protocol is not followed.
WAL (Write-Ahead Logging) is a common method of maintaining data integrity.
The main idea behind it is that changes to data files are only written to them once they have been logged. Logging refers to the process of storing log data describing changes in a permanent storage location.

We can’t maintain integrity while making changes to data files if we don’t use WAL.

 

8) Describe how the WAL protocol allows for the atomicity of non-committed transactions in the event of a failure.
An atomic transaction is one in which the entire transaction is either successful or fails and then completely rolls back to the beginning.
For instance, suppose a programme is in the middle of running a process when the power goes off.
When the programme is restarted, it must determine whether or not the procedure was successful.
When a WAL is utilised and the same situation occurs, the application checks this log and compares it to what was meant to be done. It decides whether to finish it or undo the changes based on this comparison.
As a result, atomicity is ensured.

 

9) Do we still need to follow the WAL protocol if we’re using Non-Volatile RAM (NVRAM, for short, where data persists even if the power goes off) and the undo log is saved in NV DRAM? Justify your position.

If the RAM is non-volatile, the data will be saved indefinitely even if the system’s power source is lost.

In this scenario, the WAL protocol is not required to maintain atomicity and integrity because the transaction is completed automatically when NVRAM is employed.

 

Also, read check the python code below that doesn’t work properly, adjust it, and explain?