Architecture Part 3

Q. Difference between Shared Global Area (SGA) and Program Global Area (PGA).

Ans:

SGA contains login information, session variable, bind variable and whatever SQL we have written is stored in the SQL area along with hash value.

PGA is also part of logical area. PGA will store all the login information and session memory, which contains session variable, bind variable, SQL execution environment and work area.

Q. What is session variable?

Ans: For example in a webpage we are filling out our contact details, after submitting them we are redirected to a new page and we see all the previous page details are there in the new page too. So whatever information we typed in previous page appear in the next page too because the information from the previous page is stored into a session variable in the background.

Q. What is work area or whose work area or why does PGA need it?

Ans: The work area is for PGA. Lets say 100 users data block are in DB Buffer Cache and suddenly we need to sort a table with millions rows. DB Buffer Cache can't possible handle this sort of calculation or sorting, so it gets redirected to PGA work area to do the calculations. If the area in PGA space is not enough because of it's limited space, then it will get redirected to a temporary file in Data file named temp01.dbf. Just remember any sort of calculations are done in PGA area.

Q. What is the use of PGA?

Ans: PGA is used by the sever process for any sort of calculation or sorting in the query.

Q. Disadvantages of small PGA space and big PGA space for every user.

Ans: If the PGA space is small for every user then the server will get slowed down. If the PGA space gets bigger then there is no disadvantages, it should work properly and there should be no lagging, but remember SGA shouldn't be small. The space should be divided like this 80% SGA and 20% PGA. Every individual gets their own PGA space unlike the SGA where users are in a shared space.

Q. Does a SGA space shrink?

Ans: A SGA space does not physically shrink. Since SGA is a shared area, each user's space will be limited if there are 100 or 200 people present.

Q. What is hard parsing & soft parsing?

Ans:

When the server process picks up a query it parses it and hash code is generated which is then stored in a SQL area in Library Cache. Suppose the same query was run a few hours ago then the same hash code should be generated, so if both hash value matches then the same execution plan that was made by the Optimizer for the previous query will be executed again. So the syntax check of the Library Cache and the semantic check of the Data Dictionary will be skipped. This is called soft parsing.

Hard Parsing the normal process where a users query is parsed and hash code are made after that. Than they go through the same the same check of Library Cache and Data Dictionary finally leading to the Optimizer where multiple plans are made and the best plan is chosen. For the full process check out Architecture 1 page.

Q. What is Cache hit?

Ans: Suppose a same query was run after some time, then DB Buffer Cache will have the data block of the previous query that is called Cache hit.

Q. What is Cache miss?

Ans: If the data block is not found in DB Buffer Cache, so the server process has to copy the data block from Data File and send it to DB Buffer Cache then it is called Cache Miss.

Q. What is the content of Control File.

Ans: Control File is also called the heart of the database. It contains Database name, Database creation data and time, metadata of all data files, Checkpoint (CKPT) information & Backup Repository.

Q. What is the content of Redo log file?

Ans: DML and DDL statements are stored in Redo log file.

Q. What do we store of system01.dbf in Data File?

Ans: In system01.dbf of Data File we store Data Dictionary physically.

Q. What do we store of sysaux01.dbf in Data File?

Ans: In sysaux01.dbf in Data File we store hourly snapshot of Database.

Q. What do we store of undo01.dbf in Data File?

Ans: Every time we do a transaction, the old values are stored in undo01.dbf. So that when every time we rollback the old values are taken from undo01.dbf and pasted in the original location. Now if we commit then the values stored in the undo01.dbf will be deleted.

Q. What is the use of temp01.dbf in Data File?

Ans: Whenever we do some sorting or calculation or having clause in query if the PGA doesn't have any space then we use temp01.dbf.

Q. What do we store of user01.dbf in Data File?

Ans: Whatever a user makes table or stores record in that table, that table and record information are stored in user01.dbf file.

Q. What are three files that are important for a database to start.

Ans: They are:-

1. The parameter files pfile and spfile
2. Control File
3. Data File

Q. What are the three states of a DB before it starts or open up?

Ans: Nomount, Mount and Open state are the three states of a DB before it starts or open up.

[Note: When the data base is closed the SGA section will not exist because it is not physical it is only a digital structure.]

Q. What is nomount state of a Database.

Ans:

Total System Global Area: In nomount state of a data base the first thing that is done is creating a outline structure in the memory i.e. SGA area. This SGA areas size is 1543500144 bytes.

Fixed Size:

Variable Size:

Database Buffers: Now the Redo log Buffer Cache structure are made. It's size is 620756992 bytes.

[Note: This structures are made in nomount state of Database but they are empty.]

Q. What is mount state of a Database.

Ans: In this mount state the Control file is read. Database name, Database creation data and time, meta data of all the objects and data files, Checkpoint (CKPT) information & Backup Repository, all these information that are stored in Control File are read. If any of the file are missing from the control file ten the Database will not open.

Q. What is open state of a Database.

Ans: In this state the database is open.

[ Note:-

Here as you can see after all the three states of nomount, mount and open finally when are exiting SQL and reentering it, we are not getting the message "Connected to idle instance". We are getting the above shown message that means that our system now has all the information about our Database.]

Q. How to check which database is running from LINUX?

code: ps -ef | grep pmon

[Note: As you can see that prod database is running. This was done previously. If the databse was closed ten only the second line would have been displayed.]

Q. How to check how many database are in our server?

code: cat /etc/oratab

Q. How to check which database is running from SQL prompt?

Code Step 1: desc v$database [This view gives you the option for finding database name, its stat, etc.]

[Note: For situations like this where you are only allowed to use SQL, oracle has given 4000 views.
There are two types of view dynamic view and static view. All dynamic views has a prefix of 'v$' and static views have a prefix of 'dba_'. The examples are v$database and dba_database.]

Code Step 2: select name, open_mode from v$database; [This step shows our database name which is prod and the open state of the database which is read and write mode.]

Q. How to show that our database is in mounted state.

Code Step 1: startup mount [First take the DB to a mount state and yes you cna directly take the DB to a mount state after shutting it down.]

Code Step 2: select name, open_mode from v$database; [This step shows our database name which is prod and the open state of the database which is mounted.]

Q. Can we connect to hr user when our database is in mounted state?

code: conn hr/oracle Ans: NO, because although in mounted state of the database the system have read the Control file which the meta data of the Data Files and all user info are stored in user01.dbf file of data file, but since the Data File is closed we cannot connect to hr. Unless and util the Data File opens the system cannot access user information.

As you can see from the image above we are no longer connected to oracle now how will I open my Database "Warning: You are no longer connected to ORACLE.".

Code Step 1: conn / as sysdba [Connect to sys user] Code Step 2: alter database open; [Now alter your database state to open].

Q. How to see all the 4000 views that was given by oracle as previously stated.

code: select table_name from dict; [dict is the short form of dictonary.]

[Note: These views are all related to database.]

Q. How to find the Data file location in Data Base.

Code Step 1: desc dba_data_files

Code Step 2: col file_name a20[set the column size]

Code Step 3: select file_name from dba_data_files; [Now the location of data files are shown]

Q. How to know which parameter file is used to start the database is it spfile or pfile.

code: show parameter spfile

[Note: If the spfile location is shown then we can believe that the database has been started using spfile.]

Q. How to start the database using pfile?

Code Step 1: Open a two terminals. In one terminal log into SQL prompt as sys user and start the DB.

Code Step 2: show parameter spfile [Check which parameter file is used to start the DB].

Code Step 3: cd /u01/app/oracle/product/19.0.0/dbhome_1/dbs [Once the location of spfile is shown. Go to the second terminal and go to spfile location].

Code Step 5: mv spfileprod.ora spfileprod.ora_2025_Dec_9

[Now once you are in the spfile location you can remove it or rename it so that the system can't find the spfile and is forced to use the pfile to start].

Code Step 6: shu immediate startup

[Shutdown the database and start it up again].

Code Step 7: show parameter spfile [Now if the spfile location is empty then we can assume that the database has been started using pfile.]

[Note: Generally database starts with spfile.]

Q. How to create pfile from spfile.

code: create spfile from pfile.

Q. what are the parameters in our servers database?

code: desc v$parameter [Note: This view will show all the parameters that are set in our database. Remember this view only works if our database is open.]

Q. How to check which databases parameter can be changed?

code: col name for a20 code: col value for a20 code: col issys_modifiable for a20 code: select name, value, issys_modifiable from v$parameter;

[Note: set the v$parameter views columns name, value, issys_modifiable to a20. Next display them so you can check which parameters are modifiable.]

[Note: When we start the database all these parameters are stored in data file.]

Q. How to make changes in a databases parameter?

Ans: If we want we can make changes in the pfile and open our database using it because it is a plain text file so editing it will be easier. But in industry we open our database using spfile and spfile is a binary file so changing parameter in my spfile cannot be done manually. We need SQL statements to change the parameters in spfile.

The field issys_modifiable tells us about our parameters modifiable options. There are three types of parameters modifiable states they are IMMEDIATE, FALSE and DEFERRED.

1. IMMEDIATE- This means that any changes done in this parameter, effects will take place immediately.

2. DEFERRED- This means that any changes done in this parameter, effects will only take place after restarting the session. Restarting the session would means logging off and logging in again.

3. FALSE- This means that any changes done in this parameter, effects will only take place after restarting the database.

For example we want to change the "open_cursors" parameter from 300 to 350. The below image shows a query which displays open_cursors parameter, value and it's modifiable state.

code: select name, value, issys_modifiable from v$parameter where upper(name) like 'OPEN_CURSORS';

Now as you can see the value of open_cursors is 300, to change the paramaeter the query is

code: alter system set open_cursor scope=spfile;

Now scope is the place where the alteration are made. Now changing the value in the spfile is okay, but the database has already been started after reading the spfile, so even we change the parameters in spfile it won't be immediately visible, but the issys_modifiable column says that the changes are immediate. For the changes to be visible we need to restart the database, but in industry we can't easily turn on and off the database easily. We need a lot of permissions from higher ups for that. So to show immediate changes we use this query.

code: alter system set open_cursors scope=memory;

Q. What will the value for open_cursors after restarting the database, will it remain 350 or change?

Ans: It will change to 450 because database is starting again so it will read spfile and change the open_cursors parameter to 450.

Q. How to change the open_cursors parameter in both spfile and memory?

code: alter system set open_cursors=400 scope=both;

Q. How to change a parameter named 'processes' whose modifiable state is 'FALSE'?

code: alter system set processes=400 scope=spfile;

[Note: Since the modifiable state is FALSE we can't change the memory. So the only place where this parameter is modifiable is in spfile. But to see te effects of the parameter change we need to restart the database since the parameter is in FALSE state.]

Q. What is the difference between static view & dynamic view.

Ans:

Dynamic view

• It's name starts with a prefix 'v$.......'. Example- v$datafile.
• We can also execute dynamic views when our database is in mount state.
• It takes data from control files.

[Note: We can execute dynamic views in the mount state of the database because it is taking data from control file, and control file has meta data of all the datafiles. Plus in mount state the control files are read by the system.]

Static View

• It's name start with a prefix 'dba_.......'. Example- dba_data_data_files.
• We can only execute static views when our database is in open state.
• It takes data from datafiles.

[Note: Same things are stored in both the views.

Q. Prove that static views can only be executed in open state of database.

Step 1: Close the database and open it in mount state. code: startup mount

Step 2: Run the a query from the dynamic view. It will not run. code: select name from v$datafile;

Step 3: Change the database state to open. code: alter database open;

Step 4: Run the previous query again this time it run successfully. code: select name from v$datafile;

Q. How to check out all the dynamic views in the database.

code: select table_name from dict where table_name like 'V$%';

[Note: We are searching for 'V$%' view names because dynamic views starts from 'v$'.]