Practices for Lesson 6
Practice 6-3: ADDM and RAC, Part III (continued)
create index it on t(c);
create sequence s start with 1 increment by 1 nomaxvalue cache 90000;
begin
for i in 1..90000 loop
insert into t values (i,'initial');
end loop;
end;
/ EOF
[oracle@vx0306 less06]$
[oracle@vx0306 less06]$ ./setupseq3.sh PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
User dropped.
Tablespace dropped.
Tablespace created.
User created.
Grant succeeded.
drop table s purge *
ERROR at line 1:
ORA-00942: table or view does not exist
drop sequence s * ERROR at line 1:
ORA-02289: sequence does not exist
Oracle Internal & Or acle Academy Use Only
Practice 6-3: ADDM and RAC, Part III (continued)
Oracle Database 11g: RAC Administration A - 49 Table created.
Index created.
Sequence created.
PL/SQL procedure successfully completed.
[oracle@vx0306 less06]$
2) Using Database Control, and connected as user SYS, navigate to the Performance page of your Cluster Database.
a) Click the Performance tab on the Cluster Database Home page.
b) On the Cluster Database Performance page, make sure Real Time: 15 Seconds Refresh is selected from the View Data dropdown list.
3) Use PL/SQL to create a new AWR snapshot.
[oracle@vx0306 less06]$ cat sol_06_03_03.sh
#!/bin/bash
#
# sol_06_03_03.sh
#
# Must be executed on NODE1 !!!!!!!!!!
#
y=`cat /home/oracle/nodeinfo | sed -n '1,1p'`
z=`cat /home/oracle/nodeinfo | sed -n '2,2p'`
DBNAME=`ps -ef | grep dbw0_RDB | grep -v grep | grep -v callout1 | awk '{ print $8 }' | sed 's/1/''/' | sed 's/ora_dbw0_/''/'`
I1NAME=$DBNAME"1"
I2NAME=$DBNAME"2"
export ORACLE_HOME=/u01/app/oracle/product/11.1.0/db_1 export ORACLE_SID=$I1NAME
$ORACLE_HOME/bin/sqlplus -s /NOLOG <<EOF connect / as sysdba
exec dbms_workload_repository.create_snapshot
Oracle Internal & Or acle Academy Use Only
Practice 6-3: ADDM and RAC, Part III (continued)
EOF
[oracle@vx0306 less06]$
[oracle@vx0306 less06]$ ./sol_06_03_03.sh
PL/SQL procedure successfully completed.
[oracle@vx0306 less06]$
4) Execute the startseq2.sh script to generate the same workload on both instances of your cluster as for the previous practice. Do not wait; instead, proceed with the next step.
[oracle@vx0306 less06]$ cat startseq2.sh
#!/bin/bash
#
# startseq2.sh
#
# Must be executed on NODE1 !!!!!!!!!!
#
y=`cat /home/oracle/nodeinfo | sed -n '1,1p'`
z=`cat /home/oracle/nodeinfo | sed -n '2,2p'`
DBNAME=`ps -ef | grep dbw0_RDB | grep -v grep | grep -v callout1 | awk '{ print $8 }' | sed 's/1/''/' | sed 's/ora_dbw0_/''/'`
I1NAME=$DBNAME"1"
I2NAME=$DBNAME"2"
export ORACLE_HOME=/u01/app/oracle/product/11.1.0/db_1
$ORACLE_HOME/bin/sqlplus -s /nolog @runseq1 $I1NAME &
$ORACLE_HOME/bin/sqlplus -s /nolog @runseq1 $I2NAME &
[oracle@vx0306 less06]$
[oracle@vx0306 less06]$ cat runseq2.sql
set echo on
connect jfv/jfv@&1
Oracle Internal & Or acle Academy Use Only
Practice 6-3: ADDM and RAC, Part III (continued)
Oracle Database 11g: RAC Administration A - 51 select sn into v from s;
insert into t values(v,'&1');
update s set sn=sn+1;
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
[oracle@vx0306 less06]$
5) Until the scripts are executed, look at the Sessions: Waiting and Working graphic.
What are your conclusions?
a) This time, looking at the Sessions: Waiting and Working graphic, it is clear that there are no significant waits. The sequence has a big enough cache value to avoid the most significant waits.
b) Click the Cluster Database locator link at the top of the page to return to the Cluster Database Performance page.
c) On the Performance page, make sure that the View Data field is set to Real Time:
15 Seconds Refresh. After both scripts finish execution, the Average Active Sessions graph should clearly show that there are no significant waits on your cluster. You should also notice that the transaction rate is now around 1,000 per second.
6) After the workload finishes, use PL/SQL to create a new AWR snapshot.
[oracle@vx0306 less06]$ cat sol_06_03_06.sh
#!/bin/bash
#
# sol_06_03_06.sh
#
# Must be executed on NODE1 !!!!!!!!!!
Oracle Internal & Or acle Academy Use Only
Practice 6-3: ADDM and RAC, Part III (continued)
#
y=`cat /home/oracle/nodeinfo | sed -n '1,1p'`
z=`cat /home/oracle/nodeinfo | sed -n '2,2p'`
DBNAME=`ps -ef | grep dbw0_RDB | grep -v grep | grep -v callout1 | awk '{ print $8 }' | sed 's/1/''/' | sed 's/ora_dbw0_/''/'`
I1NAME=$DBNAME"1"
I2NAME=$DBNAME"2"
export ORACLE_HOME=/u01/app/oracle/product/11.1.0/db_1 export ORACLE_SID=$I1NAME
$ORACLE_HOME/bin/sqlplus -s /NOLOG <<EOF
connect / as sysdba
exec dbms_workload_repository.create_snapshot
EOF
[oracle@vx0306 less06]$
[oracle@vx0306 less06]$ ./sol_06_03_06.sh
PL/SQL procedure successfully completed.
[oracle@vx0306 less06]$
7) Using Database Control, review the latest ADDM run. What are your conclusions?
a) On the Cluster Database Home page, click the Advisor Central link.
b) On the Advisor Central page, make sure that the Advisory Type field is set to All Types and that the Advisor Runs field is set to Last Run. Click Go.
c) In the Results table, select the latest ADDM run corresponding to Instance All.
Then click View Result. This takes you to the Automatic Database Diagnostic Monitor (ADDM) page.
d) On the Automatic Database Diagnostic Monitor (ADDM) page, the ADDM Performance Analysis table shows you the consolidation of ADDM reports from all instances running in your cluster. This is your first entry point before drilling down to specific instances. From there, investigate the “Top SQL by DB Time”
and “Top SQL by Cluster Wait” findings. You should no longer see the Sequence
Oracle Internal & Or acle Academy Use Only
Practice 6-3: ADDM and RAC, Part III (continued)
Oracle Database 11g: RAC Administration A - 53
g) On the Automatic Database Diagnostic Monitor (ADDM) page, you now have the ability to drill down to each instance by using the links located in the Affected Instances table. Click the link corresponding to the most affected instance.
8) On the corresponding ADDM Database Diagnostic Monitor (ADDM) instance page, you should retrieve exactly the same top findings that you previously saw at the cluster level.