Few General Analysis In AWR

Few General Analysis In AWR

Starting and Stopping Pluggable Databases In 12c

Starting and Stopping Pluggable Databases In 12c

Starting the pdbs:

Check the status of the pdb's

sqlplus '/ as sysdba'

SQL> select name, open_mode from v$pdbs;

SQL> show pdbs

Note: All the PDBs are in MOUNT state.

Start a single pdb

show pdbs

alter pluggable database lub1 open;

Start all pdb's in single command

show pluggable

alter pluggable database all open;

Note: While starting the pdbs database characterset and pluggable database status information will be written in alertlog.

Stopping the pdbs:

Check the status of the pdb's

sqlplus '/ as sysdba'

SQL> select name, open_mode from v$pdbs;

SQL> show pdbs

Note: All the PDBs are in READ WRITE state.

Stop a single pdb

show pdbs

alter pluggable database lub1 close immediate;

Stop all pdb's in single command

show pluggable

alter pluggable database all close immediate;

Note: While closing the pdb's buffer cache will be flushed.

Create a trigger to open all pluggable databases.

sqlplus '/ as sysdba'

CREATE OR REPLACE TRIGGER pdb_startup AFTER STARTUP ON DATABASE

BEGIN

EXECUTE IMMEDIATE 'alter pluggable database all open';

END pdb_startup;

/

Increasing Load On The Database Server

Increasing Load On The Database Server

Creating a table:

create table t (id number, sometext varchar2(50),my_date date) tablespace test;

Now we will create a simple procedure to load bulk data:

create or replace procedure manyinserts as

v_m number;

begin

for i in 1..10000000 loop

select round(dbms_random.value() * 44444444444) + 1 into v_m from dual ;

insert /*+ new2 */ into t values (v_m, 'DOES THIS'||dbms_random.value(),sysdate);

commit;

end loop;

end;

/

Now this insert will be executed in 10 parallel sessions using dbms_job, this will fictitiously increase load on database:

create or replace procedure manysessions as

v_jobno number:=0;

begin

FOR i in 1..10 LOOP

dbms_job.submit(v_jobno,'manyinserts;', sysdate);

END LOOP;

commit;

end;

/

Now we will execute manysessions which will increase 10 parallel sessions:

exec manysessions;

Check the table size:

select bytes/1024/1024/1024  from dba_segments where segment_name='T';

ORA-01940: Cannot Drop A User that is Currently Connected

ORA-01940: Cannot Drop A User that is Currently Connected

While dropping a user in oracle database , you may face below error. ORA-01940

Problem:

SQL> drop user SCOTT cascade

2 /

drop user SCOTT cascade

*

ERROR at line 1:

ORA-01940: cannot drop a user that is currently connected

Solution:

1. Find the sessions running from this userid:

SQL> SELECT SID,SERIAL#,STATUS from v$session where username='SCOTT';

SID SERIAL# STATUS

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

44 56381 INACTIVE

323 22973 INACTIVE

2. Kill the sessions:

SQL> ALTER SYSTEM KILL SESSION '44,56381' immediate;

System altered.

SQL> ALTER SYSTEM KILL SESSION '323,22973' immediate;

System altered.

SQL> SELECT SID,SERIAL#,STATUS from v$session where username='SCOTT';

no rows selected

3. Now the drop the user:

SQL> drop user SCOTT cascade

user dropped.

Parsing In Oracle

Parsing In Oracle

Parsing is primary phase in the SQL execution. It is followed by other stages: Execute and Fetch.

PARSING BASICS:

Whenever a SQL statement is executed, Oracle Engine performs below actions on it:

-Validate the Syntax

-Validate the objects being referenced in the statement

-Privileges assigned to user executing the Job

-Verifying if statement is already available in the shared pool. Oracle engine will calculate the hash value for the SQL statement and look in

-If statement is not already present then allocate shared memory and create a cursor in shared pool

-Generate the Execution Plan

TYPES OF PARSES

-HARD parse: It means that statement is not available in shared memory or this is a brand new statement that user is trying to execute. If your shared pool is small then also hard parse may be required as the old statement got aged out the shared pool. All the steps mentioned above for parsing need to be done for this situation. Hard parse requires extra system resources. This is also known as ‘Library Cache Miss’.

-SOFT Parse: It means that statement was executed earlier and was already parsed and is available in memory. So Oracle need to do steps 1-3 only as mentioned above since rest of the tasks were already done earlier. It is like work hard once and reap benefits multiple times. This is also known as ‘Library cache Hit’ as you got the statement parsed and available to use in the Library cache.

 

Why hard parses should be avoided:

There are two key reasons why hard parses should be kept to bare minimum required:

-Generation of an execution plan is a very CPU-intensive operation.

-Memory in the shared pool is limited and also memory operations are serialized. Memory operations happens using shared pool latches and if so many hard parses are happening then other processes in the database will have to wait in queue to get the shared pool latch. So hard parse impacts both umber of shared pool latch and library cache latch.

Analyzing Basic Performance Issues

Analyzing Basic Performance Issues 

Whenever any performance issue it is important that DBA and Developers should work together when facing a database/application issue.

When DBAs are reported a performance issue, first step is to get as much information as possible that is related to the issue. 

You can ask below questions to users/developer to collect the first level of information..

 1. What operations/program are executed?

 2. Is it Oracle seeded or custom program?

 3. How much time it used to take earlier?

 4. Is the run time increased over time or you are seeing sudden increase in run time?

 5. Was there any recent code change/migration?

 6. Is it always slow or for certain time of the day only?

 7. Is it slow for all parameters or for some specific parameters?

 8. How much data is getting processed?

 9. What is the frequency of job execution? Was there any change in frequency?

 10. Does the problem happens on both their test and production systems?

Asking above kind of questions will help you in deciding what part of system you should target.

-Target the whole system

-Target a single session

-Target a single SQL statement

 

Your goal should be to answer below three questions:

Where is time spent?

You need to identify where the time is getting consumed. Is it the code or network latency or disk I/O bottlenecks or the CPU/Memory crunch

How is time spent?

You need to find out how the time is spent and how much time is spend in each layer – database, application, network, disk etc .

How to reduce the time spent?

Finally, based on above information see where the major chunk of time is spent and how to reduce it.

 --> High level steps for System level performance issues

Use vmstat top/prstat to identify system wide CPU/Memory consumption.

Use iostat to verify if disks are the bottleneck

Use netstat/tnsping etc to verify if network is issue.

Verify if any other resource intensive processes are running on server.

Verify filesystem space.

Check alert logs, application logs, traces etc.

Check database locks

Generate AWR reports to see what is eating up resources.

Check if increasing application/database memory/redo/undo/temp/SGA will help.

 

--> High level steps for Session level performance issues

Find the Top SQLs executing under session.

Apply SQL optimization techniques on top SQLs.

Verify locking at session level

Generating AWR/ASH for that duration may help in providing useful information.

 

--> High level steps for SQL level performance issues

Apply trace, Generate TKPROF file and analyze for top resource consuming SQL.

Avoid full table scans on large tables

Possible indexes on columns contained in the WHERE clause

Use AWR/ASH reports to get collective information

Also use SQLTRPT/SQLT

Verify is statistics are current

Verify if indexes are valid and are adequate.

Verify is parallelism/materialized views/Baselines/SQL Profiles will help

Monitor V$SESSION_LONGOPS to detect long running operations

Decide on using Hints if those are helping.

Table partitioning can be thought of as an option based on kind and size of tables.