ASH - Active Session History

Hmm .. .so one more topic in Performance tuning other than AWR is ASH

So, What is ASH ?

·        The data which is held in Buffer cache, It samples (Items selected at random from a population and used to test hypotheses about the population) the activity of each active database session every second.

·        The design goal is to keep about an hour (your mileage will vary).

·         If a session is not active it will not be sampled. The in-memory buffer is exposed via a view called v$active_session_history.

·        Active Session History (ASH) was introduced in Oracle 10g.

Note :

You can disable ash by setting the underscore parameter

SQL > alter system set “_ash_enable”=False;

Earlier I said that, if the session is not active it will not be sampled. In this regards, You can actually set a parameter as below

 SQL > alter system set “_ash_enable_all” = TRUE

to force all sessions, including idle sessions, to be sampled.

How To Generate ASH Report

Oracle Provide a standard script just like AWR to analyze the ASM data

I will explain how to generate ASH(Active Session History) reports from SQLPLUS. The same can be done using Enterprise Manager also.

Script Location is same as other standard script $ORACLE_HOME/rdbms/admin/

Example :

SQL> @$ORACLE_HOME/rdbms/admin/ashrpt.sql

Current Instance

~~~~~~~~~~~~~~~~

   DB Id    DB Name  Inst Num Instance

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

 4174341635 F1                               1 f1

Specify the Report Type

~~~~~~~~~~~~~~~~~~~~~~~

Enter 'html' for an HTML report, or 'text' for plain text

Defaults to 'html'

Enter value for report_type: html

Type Specified:  html

Instances in this Workload Repository schema

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   DB Id     Inst Num DB Name          Instance     Host

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

* 4174341635               1 F1               f1                  oracleasm1.l

                                                                        ocaldomain

Defaults to current database

Using database id: 4174341635

Enter instance numbers. Enter 'ALL' for all instances in a

RAC cluster or explicitly specify list of instances (e.g., 1,2,3).

Defaults to current instance.

Using instance number(s): 1

ASH Samples in this Workload Repository schema

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Oldest ASH sample available:  16-Nov-16 12:34:10   [  71385 mins in the past]

Latest ASH sample available:  05-Jan-17 02:19:18   [     0 mins in the past]

Specify the timeframe to generate the ASH report

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Enter begin time for report:

--    Valid input formats:

--         To specify absolute begin time:

--           [MM/DD[/YY]] HH24:MI[:SS]

--           Examples: 02/23/03 14:30:15

--                         02/23 14:30:15

--                         14:30:15

--                         14:30

--         To specify relative begin time: (start with '-' sign)

--           -[HH24:]MI

--           Examples: -1:15  (SYSDATE - 1 Hr 15 Mins)

--                         -25    (SYSDATE - 25 Mins)

Defaults to -15 mins

Enter value for begin_time: 01/01/17 12:34:10

Report begin time specified: 01/01/17 12:34:10

Enter duration in minutes starting from begin time:

Defaults to SYSDATE - begin_time

Press Enter to analyze till current time

Enter value for duration: 10

Report duration specified:   10

Using 01-Jan-17 12:34:10 as report begin time

Using 01-Jan-17 12:44:10 as report end time

Summary of All User Input

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

Format                  : HTML

DB Id         : 4174341635

Inst num       : 1

Begin time     : 01-Jan-17 12:34:10

End time       : 01-Jan-17 12:44:10

Slot width     : Default

Report targets : 0

Report name    : nk

How to start your carrier as a Database Administrator ?

First, Think First…..

During your academic finals, get Internships first, even at the small firm will be OK. Request your employers for any kind of database related work, or work as a SQL Developer. Don’t restrict yourself with any one kind of database like MySQL, MSSQL, Oracle, MongoDB. Whatever you get, Just go for it.

Now impress your bosses to convert your internships to full time job. Even if the company wants you to be a Windows admin or Network admin first, just take the job. Don’t ever neglect any opportunity. Now, You can slowly swivel to DBA career.

Conduct Informational Interviews with people you know or your friends  know. Join Communities on Facebook, Quora and OTN.  Discover a WhatsApp group for DBA’s and stay connected with the technology. You will meet new people. These people could be working for companies that you may be interested in. You may get opening/vacancies information. Find out what kind of databases they use. Get specific. Find out the database versions and what their daily tasks and challenges are. Try to add value to them. Just drop in every now and then and present your findings.

It may be really small. But once people start seeing that you can add value, you will be hired. Think hard. You will definitely find some people you can talk to.

During learning, always spend more time understanding the fundamentals, Once you  gets through the basics, the content or theory, later part would be a piece of cake for you now. So I suggest you to learn the basics and try to revise everything daily, example, Oracle Architecture.

Oracle Architecture is very vast and even the core components still remain the same, but the Oracle’s architecture has evolved on every version. When you become an Oracle DBA, one of your main responsibilities will be monitoring production databases.

During Technical Interview, there is 99.99 % probability that Interview panel will ask you to Explain Oracle Architecture.  So, be well prepared for this and explore every ‘nook and hook’ of Oracle Architecture and make a note and write the architecture in your own words. Revise it daily.

Because once you explain the architecture properly, there is 60 % chances you are selected, because rest is depend on your technical answers.

Do not explain architecture in rush. Never do this mistake. I saw many candidates, they just finish explaining the architecture in 5-7 minutes. This is Bad.

You should explain the Architecture for at least 25-30 minutes. So that the interviewer get impressed.

Further part of Interview preparation is written in my next article..

Click here .. .

How to Crack DBA Interviews

General Tips for DBA Interviews

What a Junior DBA will do, When he Faces “Slowness in the Database” ?

Many a times, a DBA gets a call from Client side regarding the slowness of the Database.

Datapump Architecture. What is Master table in Datapump ?

Datapump Architecture. What is Master table in Datapump ?

Master Table :

The Master Table is created in the schema of the current user running the Pump Dump export or import, and it keeps tracks of lots of detailed information. 

The Master Table is used to track the detailed progress information of a Data Pump job.

This will store the following information :

·         The status of every worker process involved in the operation.

·         The current set of dump files involved.

·         The job’s user-supplied parameters.

·         The state of current job status and restart information.

·         The current state of every object exported or imported and their locations in the dump file set.

Note :  The Master Table is the key to Data Pump’s restart capability in the event of a planned or unplanned job stoppage.

Behaviour of Master Table :

This table is created at the beginning of a Data Pump operation and is dropped at the end of the successful completion of a Data Pump operation. The Master Table can also be dropped if the job is killed using the kill_job interactive command. If a job is stopped using the stop_job interactive command or if the job is terminated unexpectedly, the Master Table will be retained. 

The keep_master parameter can be set to Y to retain the Master Table at the end of a successful job for debugging purposes

The name of the Master Table is the same as the Data Pump job name and has the following columns:

SQL>  Desc <job_name> ;

Process in Datapump Architecture

The master control process

·         Maintains job state, job description, restart, and dump file set information in the Master Table.

·         This process controls the execution and sequencing of a Data Pump job.

·         The master process has two main functions

1.       To divide the loading and unloading of data and metadata tasks and handle the worker processes;

2.       To manage the information in the Master Table and record job activities in the log file.

Worker Process:

·         This handles the request assigned by the master control process.  This process maintains the current status of the job, like : ‘pending’ or ‘completed’ or ‘failed’.

·         The worker process is responsible for loading and unloading data and metadata.

·         The number of worker processes needed can be defined by assigning a number to the parallel parameter.

Parallel Query Process:

·         This process is used when the Data Pump chooses External Table API as the data access method for loading and unloading data. 

·         The worker process that uses the External Table API creates multiple parallel query processes for data movement, with the worker process acting as the query coordinator.

Shadow Process :

·         This process is created when a client logs into the Oracle server. 

·         The shadow process creates a job, which primarily consists of creating the Master Table, creating the queues in Advanced Queues (AQ) used for communication among the various processes, and creating the master control process.

·         Once a job is running, the shadow process’ main job is to check the job status for the client process.  If the client process detaches, the shadow process goes away; however, the remaining Data Pump job processes are still active.

·         Another client process can create a new shadow process and attach to the existing job.

What is the biggest lesson you have learned in the corporate world?

Source:  QUORA

1. Never be the smartest person in the room.
2. Always listen to your inner voice.  If it "feels wrong" it is.
3. Debt makes you and your company a slave to someone.
4. It is never wrong to do the right thing.  It is never right to do the wrong thing.  The ends DO NOT justify the means.   The Ten Commandments are also great safety tips no matter who said them.
5. Don't hire employees or friends.  Hire people who believe in the vision and want to partner because it makes them feel alive to be apart of something bigger than them or us.
6. Remember who you wanted to be when you were a kid.  You didn't EVER want to be the dream killer, paper pusher or suit.  You wanted to change the world.  Why aren't you changing the world?  Age is a mind set and the older you are mentally, the less dynamic change you will capture and create.
7. Love your people. If your end users are viewed as people who are "clicks"  or just customers you will fail.   If you care about them, you will make the product that will actually make their life better or easier.  You both win. Most companies are upside down.  Prioritize:
   A) principals
   B) people (love and respect your end users/vendors and employees/partners
   -- If you have those as your solid foundation and you protect those two things, the next two fall into place naturally.
   C) product. If you know the first two the product becomes obvious
   D) profit.  It is a byproduct.  Never forget that. Making profit your goal is the easiest way to become all that you despise.
8. Make your word you bond.  Contracts are important but your word is more so.  Let your yes mean yes and no mean no always.  The spirit of the deal is just as important as the words of the deal.  Never dishonor yourself.
9. Plan on being mocked or told it will never work.  Sometimes they are right, but the best ideas do seem crazy at first to most or everyone would already be doing it.
10. Know not what you do or make.  But why.  That is the difference between a guy who makes computers and Steve jobs.  He knew why.  ART.
11. With God all things are possible.
12. There is no such thing as compartmentalizing ethics.  You can not cheat on your wife or taxes but be honest and trustworthy at the office.   Fix your character flaws.  We all have them.   Master them or they will master you.
13. Make your weakness your strength        SOURCE : https://www.quora.com/What-is-the-biggest-lesson-you-have-learned-in-the-corporate-world

AWR - Automatic Workload Repository :: Beginners Guide

FREE Sign-Up with Gmail and Receive Pdf's and Updates via Mail -Click Here