Moneycontrol Brokerage Recos

Thursday, November 6, 2014

SQL Trace Facility & TKPROF - Part - I

TKPROF is a performance diagnostic tool that can help us monitor and tune applications running on the Oracle Database Server. The main purpose of this tool is to formatting the captured trace file to a more readable format so that a DBA can have an ease look on statistics captured by SQL Trace Facility.

In order to achieve our goal to generate the statistics report of a SQL statement we will be capturing/tracing the session activity and use the TKPROF formatting tool.

The SQL Trace facility provides performance information on individual SQL statement. It generates the following statistics for each SQL statements.
  • Parse, execute, and fetch counts.
  • CPU and elapsed times.
  • Physical reads and logical reads
  • Number of rows processed
  • Misses on the library cache
  • Username under which each parse occurred
  • Each commit and rollback


We can enable the SQL tracing for a session or at instance level. When we enable the SQL trace facility then performance statistics for all SQL statements are generated in a trace file for the session it is enabled for. The most important thing to note here while enabling the SQL tracing facility is to make sure you’re not letting it run at instance level in a production box. Preferably we enable this facility at session level as it will take disk storage to store the trace file and degrades the database performance.
Note: when we enable the SQL Trace facility for a session then it creates a single trace file containing statistics for all SQL statements for that session. When we enable the SQL Trace facility at instance level then oracle creates a separate trace file for each process.

Pre-requisite:
Before enabling the SQL Tracing make sure you have TIMED_STATISTICS and USER_DUMP_DEST parameters set appropriately.

Note: if you have not enabled the TIMED_STATISTICS parameter then SQL Trace facility will not capture the timed statistics such as CPU and elapsed times.

Enabling the Tracing:

SQL> alter session set sql_trace=true;
Session altered.

After, enabling the SQL tracing run the SQL statements you want to capture the statistics of and finally disable the SQL tracing.

SQL> alter session set sql_trace=false;
Session altered.


You can see a trace file named ora10db_ora_5512.trc has been generated in the snapshot depicted below



Note: Setting SQL_TACE to TRUE can have a severe performance impact on database server so make sure you disable it having completed with tracing.

Formatting Trae file with TKPROF Utility:

The resulting trace file is stored at USER_DUMP_DEST location and it can be formatted using TKPROF utility as follow.

$tkprof <trace file location> <output file locaction> 

Note: parameters passed in above command are as follows:

<trace file location> - specify here complete location of generated trace file including its file_name.trc

<output file location> - specify here a location where the formatted TKPROF file would be placed

For Example:

 [oracle@ora10srv bin]$ ./tkprof /u01/app/oracle/product/10.2.0/db_1/admin/ora10db/udump/ora10db_ora_5512.trc /u01/app/oracle/product/10.2.0/db_1/admin/ora10db/udump/scott_sess_trace.log

TKPROF: Release 10.2.0.3.0 - Production on Thu Nov 6 13:26:59 2014
Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Now you can see TKPROF has formatted the input trace file into a readable scott_sess_trace.log file as depicted in the below snapshot.



[oracle@ora10srv udump]$ cat scott_sess_trace.log | more

TKPROF: Release 10.2.0.3.0 - Production on Thu Nov 6 13:26:59 2014

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Trace file: /u01/app/oracle/product/10.2.0/db_1/admin/ora10db/udump/ora10db_ora_5512.trc
Sort options: default

********************************************************************************
count    = number of times OCI procedure was executed
cpu      = cpu time in seconds executing
elapsed  = elapsed time in seconds executing
disk     = number of physical reads of buffers from disk
query    = number of buffers gotten for consistent read
current  = number of buffers gotten in current mode (usually for update)
rows     = number of rows processed by the fetch or execute call
********************************************************************************


SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE
  NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false')
  NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),0), NVL(SUM(C2),0)
FROM
 (SELECT /*+ NO_PARALLEL("EMP") FULL("EMP") NO_PARALLEL_INDEX("EMP") */ 1 AS
  C1, 1 AS C2 FROM "SCOTT"."EMP" "EMP") SAMPLESUB


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          0          7          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          7          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS   (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=7 pr=0 pw=0 time=218 us)
     14   TABLE ACCESS FULL EMP (cr=7 pr=0 pw=0 time=225 us)

********************************************************************************


********************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        7      0.01       0.08          0          8          0           0
Execute      7      0.00       0.07          0          0          0           0
Fetch        6      0.00       0.00          0         24          0          42
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       20      0.02       0.16          0         32          0          42

Misses in library cache during parse: 1


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          0          7          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          7          0           1

Misses in library cache during parse: 1

    7  user  SQL statements in session.
    1  internal SQL statements in session.
    8  SQL statements in session.
********************************************************************************
Trace file: /u01/app/oracle/product/10.2.0/db_1/admin/ora10db/udump/ora10db_ora_5512.trc
Trace file compatibility: 10.01.00
Sort options: default

       1  session in tracefile.
       7  user  SQL statements in trace file.
       1  internal SQL statements in trace file.
       8  SQL statements in trace file.
       6  unique SQL statements in trace file.
     125  lines in trace file.
     787  elapsed seconds in trace file.


Note : I'll be writing another article with detailed explanation on this along with trcess Utility to format multiple trace files into a single output file................!!

Wednesday, October 29, 2014

Oracle Database Upgrade from 10.2.0.1 to 10.2.0.3

Long day off makes me tired of being at home so thought to do something for my friends........

People, who are new to Oracle world think that database upgrade is a big deal go walk with, yes - it is if we talk about a production and mission critical systems. But once you will go through this article and implement it at our own then you would realize - yeah, it is not that big deal ;)

In this section I will describe how we apply an oracle platform upgrade patch step by step to upgrade the database from 10.2.0.1. To 10.2.0.3

Pre-requisites:

1. First of all, stop all the running oracle components:

 [oracle@ora10srv bin]$ ./emctl stop dbconsole
TZ set to Asia/Calcutta
Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0
Copyright (c) 1996, 2005 Oracle Corporation.  All rights reserved.
http://ora10srv:1158/em/console/aboutApplication
Stopping Oracle Enterprise Manager 10g Database Control ...
 ...  Stopped.

[oracle@ora10srv bin]$ lsnrctl stop listener
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
The command completed successfully
LSNRCTL>

 [oracle@ora10srv bin]$ ./isqlplusctl stop
iSQL*Plus 10.2.0.1.0
Copyright (c) 2003, 2005, Oracle.  All rights reserved.
Stopping iSQL*Plus ...
iSQL*Plus stopped.

[oracle@ora10srv bin]$ sqlplus /nolog;
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Oct 28 21:45:47 2014
Copyright (c) 1982, 2005, Oracle.  All rights reserved.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

2. Second step is to take a backup of existing Oracle home for restore in case of failure in upgrade process to get our database operation back to its previous state for normal business operations.

 [oracle@ora10srv bin]$ echo $ORACLE_HOME
/u01/app/oracle/product/10.2.0/db_1

$tar –zcvf /u01/app//oracle/OraHomeBkp.tar.gz /u01/app/oracle/product/10.2.0/db_1

3. Take a database cold backup now.
Copy the following list of physical database file sets
a.       Datafile
b.      Control files
c.       Redo log files
d.      Pfile & password file can optionally be copies to backup location as well.

4. Extract the downloaded database patch to a /tmp or other location on the database server.

[oracle@ora10srv bin]$ Unzip p5337014_10203_LINUX.zip

It will unzip the archive to a folder named ‘Disk1
Now navigate to the extracted folder “Disk1” and start the installer.

./runInstaller



Click on the Next Button.

  

Specify the Oracle Home Path and its Name – Click Next.


Click on Install.




Now login as root user on a separate terminal and run the root.sh script and then click on OK button.

 Click on Exit.



5. Start the Database in upgrade mode now.


6. Run the Database Upgrade Pre-Requisite Information Tool to get and implement the recommendation before actual upgrade script execution.

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

Oracle Database 10.2 Upgrade Information Utility    10-28-2014 22:31:10
.**********************************************************************
Database:
**********************************************************************
--> name:       ORA10DB
--> version:    10.2.0.1.0
--> compatible: 10.2.0.1.0
--> blocksize:  8192
.**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
--> SYSTEM tablespace is adequate for the upgrade.
.... minimum required size: 491 MB
.... AUTOEXTEND additional space required: 11 MB
--> UNDOTBS1 tablespace is adequate for the upgrade.
.... minimum required size: 13 MB
--> SYSAUX tablespace is adequate for the upgrade.
.... minimum required size: 235 MB
.... AUTOEXTEND additional space required: 5 MB
--> TEMP tablespace is adequate for the upgrade.
.... minimum required size: 58 MB
.... AUTOEXTEND additional space required: 38 MB
--> EXAMPLE tablespace is adequate for the upgrade.
.... minimum required size: 69 MB
.
**********************************************************************
Update Parameters: [Update Oracle Database 10.2 init.ora or spfile]
**********************************************************************
WARNING: --> "streams_pool_size" needs to be increased to at least 50331648
.**********************************************************************
Renamed Parameters: [Update Oracle Database 10.2 init.ora or spfile]
**********************************************************************
-- No renamed parameters found. No changes are required.
.**********************************************************************
Obsolete/Deprecated Parameters: [Update Oracle Database 10.2 init.ora or spfile]
**********************************************************************
-- No obsolete parameters found. No changes are required
.**********************************************************************
Components: [The following database components will be upgraded or installed]
**********************************************************************
--> Oracle Catalog Views         [upgrade]  VALID
--> Oracle Packages and Types    [upgrade]  VALID
--> JServer JAVA Virtual Machine [upgrade]  VALID
--> Oracle XDK for Java          [upgrade]  VALID
--> Oracle Java Packages         [upgrade]  VALID
--> Oracle Text                  [upgrade]  VALID
--> Oracle XML Database          [upgrade]  VALID
--> Oracle Workspace Manager     [upgrade]  VALID
--> Oracle Data Mining           [upgrade]  VALID
--> OLAP Analytic Workspace      [upgrade]  VALID
--> OLAP Catalog                 [upgrade]  VALID
--> Oracle OLAP API              [upgrade]  VALID
--> Oracle interMedia            [upgrade]  VALID
--> Spatial                      [upgrade]  VALID
--> Expression Filter            [upgrade]  VALID
--> EM Repository                [upgrade]  VALID
--> Rule Manager                 [upgrade]  VALID
.

PL/SQL procedure successfully completed.

Note: - Above you can see that Database upgrade Pre-Requisite Tool has given the recommendation for Streams_pool_size parameter to set to the recommended size so now we will alter that parameter with the recommended size and will run the pre-requisite tool again until it has no more recommendations to implement.

SQL> alter system set streams_pool_size=50331648 scope=both;
System altered.

Run the Pre-requisite Tool again:

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

Oracle Database 10.2 Upgrade Information Utility    10-28-2014 22:37:14
.**********************************************************************
Database:
**********************************************************************
--> name:       ORA10DB
--> version:    10.2.0.1.0
--> compatible: 10.2.0.1.0
--> blocksize:  8192
.**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
--> SYSTEM tablespace is adequate for the upgrade.
.... minimum required size: 491 MB
.... AUTOEXTEND additional space required: 11 MB
--> UNDOTBS1 tablespace is adequate for the upgrade.
.... minimum required size: 13 MB
--> SYSAUX tablespace is adequate for the upgrade.
.... minimum required size: 235 MB
.... AUTOEXTEND additional space required: 5 MB
--> TEMP tablespace is adequate for the upgrade.
.... minimum required size: 58 MB
.... AUTOEXTEND additional space required: 38 MB
--> EXAMPLE tablespace is adequate for the upgrade.
.... minimum required size: 69 MB
.**********************************************************************
Update Parameters: [Update Oracle Database 10.2 init.ora or spfile]
**********************************************************************
-- No update parameter changes are required.
.**********************************************************************
Renamed Parameters: [Update Oracle Database 10.2 init.ora or spfile]
**********************************************************************
-- No renamed parameters found. No changes are required.
.
**********************************************************************
Obsolete/Deprecated Parameters: [Update Oracle Database 10.2 init.ora or spfile]
**********************************************************************
-- No obsolete parameters found. No changes are required
.
**********************************************************************
Components: [The following database components will be upgraded or installed]
**********************************************************************
--> Oracle Catalog Views         [upgrade]  VALID
--> Oracle Packages and Types    [upgrade]  VALID
--> JServer JAVA Virtual Machine [upgrade]  VALID
--> Oracle XDK for Java          [upgrade]  VALID
--> Oracle Java Packages         [upgrade]  VALID
--> Oracle Text                  [upgrade]  VALID
--> Oracle XML Database          [upgrade]  VALID
--> Oracle Workspace Manager     [upgrade]  VALID
--> Oracle Data Mining           [upgrade]  VALID
--> OLAP Analytic Workspace      [upgrade]  VALID
--> OLAP Catalog                 [upgrade]  VALID
--> Oracle OLAP API              [upgrade]  VALID
--> Oracle interMedia            [upgrade]  VALID
--> Spatial                      [upgrade]  VALID
--> Expression Filter            [upgrade]  VALID
--> EM Repository                [upgrade]  VALID
--> Rule Manager                 [upgrade]  VALID

.PL/SQL procedure successfully completed.

SQL> spool off

7. Now you can see no more recommendation to implement so we would run the actual database upgrade script(catupgrd.sql) in next step.

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

After running the above script just go out for a walk and have some tea/fun as it will take more than half an hour for a starter database to complete ;-)


DOC>######################################################################
DOC>######################################################################
DOC> The following statement will cause an "ORA-01722: invalid number"
DOC> error if the user running this script is not SYS.  Disconnect
DOC> and reconnect with AS SYSDBA.

DOC>######################################################################
DOC>######################################################################
DOC>#no rows selected
DOC>#######################################################################
DOC>#######################################################################
DOC> The following statements will cause an "ORA-01722: invalid number"
DOC> error if the SYSAUX tablespace does not exist or is not
DOC> ONLINE for READ WRITE, PERMANENT, EXTENT MANAGEMENT LOCAL, and
DOC> SEGMENT SPACE MANAGEMENT AUTO.
DOC>
DOC> The SYSAUX tablespace is used in 10.1 to consolidate data from
DOC> a number of tablespaces that were separate in prior releases.
DOC> Consult the Oracle Database Upgrade Guide for sizing estimates.
DOC>
DOC> Create the SYSAUX tablespace, for example,
DOC>
DOC> create tablespace SYSAUX datafile 'sysaux01.dbf'
DOC>      size 70M reuse
DOC>      extent management local
DOC>      segment space management auto
DOC>      online;
DOC>
DOC> Then rerun the catupgrd.sql script.
DOC>#######################################################################
DOC>#######################################################################
DOC>#no rows selected
no rows selected
no rows selected
no rows selected
no rows selected
Session altered.
Session altered.
Table created.
2 rows deleted.
1 row created.
Commit complete.
TIMESTAMP                                                                      ------------------------------------------------------------                   COMP_TIMESTAMP UPGRD__BGN 2014-10-28 22:38:32 2456959 81512                    

Table created.
Index created.
Table created.
Index created.
Table created.
Index created.
Index created.
Table created.
Index created.
Index created.
Table created.
Index created.
Table created.
Table altered.……………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………TIMESTAMP--------------------------------------------------------------------------------COMP_TIMESTAMP UPGRD_END  2014-10-28 23:11:29.Oracle Database 10.2 Upgrade Status Utility           10-28-2014 23:11:29.Component                                Status         Version  HH:MM:SSOracle Database Server                    VALID      10.2.0.3.0  00:15:26JServer JAVA Virtual Machine              VALID      10.2.0.3.0  00:04:53Oracle XDK                                VALID      10.2.0.3.0  00:00:33Oracle Database Java Packages             VALID      10.2.0.3.0  00:00:23Oracle Text                               VALID      10.2.0.3.0  00:00:32Oracle XML Database                       VALID      10.2.0.3.0  00:00:52Oracle Data Mining                        VALID      10.2.0.3.0  00:00:26OLAP Analytic Workspace                   VALID      10.2.0.3.0  00:00:36OLAP Catalog                              VALID      10.2.0.3.0  00:01:10Oracle OLAP API                           VALID      10.2.0.3.0  00:00:54Oracle interMedia                         VALID      10.2.0.3.0  00:03:35Spatial                                   VALID      10.2.0.3.0  00:01:04Oracle Expression Filter                  VALID      10.2.0.3.0  00:00:13Oracle Enterprise Manager                 VALID      10.2.0.3.0  00:02:07Oracle Rule Manager                       VALID      10.2.0.3.0  00:00:07.Total Upgrade Time: 00:32:56
DOC>#######################################################################
DOC>#######################################################################
DOC>
DOC>   The above PL/SQL lists the SERVER components in the upgraded
DOC>   database, along with their current version and status.
DOC>
DOC>   Please review the status and version columns and look for
DOC>   any errors in the spool log file.  If there are errors in the spool
DOC>   file, or any components are not VALID or not the current version,
DOC>   consult the Oracle Database Upgrade Guide for troubleshooting
DOC>   recommendations.
DOC>
DOC>   Next shutdown immediate, restart for normal operation, and then
DOC>   run utlrp.sql to recompile any invalid application objects.
DOC>
DOC>#######################################################################
DOC>#######################################################################
DOC>#
SQL>


SQL> spool off

8. Wow…… database upgrade script finished with success as above we can see no any database component in INVALID state so let’s restart the database instance now normally.

SQL> shutdown immediate;
Database closed.

SQL> startup
ORACLE instance started.
Total System Global Area  436207616 bytes
Fixed Size                  1262236 bytes
Variable Size             222301540 bytes
Database Buffers          209715200 bytes
Redo Buffers                2928640 bytes
Database mounted.
Database opened.

9. The database upgrade may leave many database objects in INVALID state so in order to compile those INVALID objects we would have to run the oracle provided re-compile in-build tool making it in VALID state.

SQL> @/ORACLE_HOME/rdbms/admin/utlrp.sql

10. Finally, Let’s verify if our all database components are upgraded and are in VALID state.

SQL> set linesize 220
SQL> column COMP_NAME format a50;
SQL> select comp_name, version, status from sys.dba_registry;

COMP_NAME                                          VERSION                        STATUS
-------------------------------------------------- ------------------------------ -----------
Oracle Database Catalog Views                      10.2.0.3.0                     VALID
Oracle Database Packages and Types                 10.2.0.3.0                     VALID
Oracle Workspace Manager                           10.2.0.3.0                     VALID
JServer JAVA Virtual Machine                       10.2.0.3.0                     VALID
Oracle XDK                                         10.2.0.3.0                     VALID
Oracle Database Java Packages                      10.2.0.3.0                     VALID
Oracle Expression Filter                           10.2.0.3.0                     VALID
Oracle Data Mining                                 10.2.0.3.0                     VALID
Oracle Text                                       10.2.0.3.0                     VALID
Oracle XML Database                                10.2.0.3.0                     VALID
Oracle Rule Manager                                10.2.0.3.0                     VALID
Oracle interMedia                                  10.2.0.3.0                     VALID
OLAP Analytic Workspace                            10.2.0.3.0                     VALID
Oracle OLAP API                                    10.2.0.3.0                     VALID
OLAP Catalog                                       10.2.0.3.0                     VALID
Spatial                                           10.2.0.3.0                     VALID
Oracle Enterprise Manager                          10.2.0.3.0                     VALID

17 rows selected.

[oracle@ora10srv bin]$ ./sqlplus /nolog


SQL*Plus: Release 10.2.0.3.0 - Production on Wed Oct 29 00:56:22 2014Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

SQL> conn /as sysdba
Connected.

SQL> select * from v$version;

BANNER
----------------------------------------------------------------Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 
- ProdPL/SQL Release 10.2.0.3.0 
- ProductionCORE 10.2.0.3.0 
ProductionTNS for Linux: Version 10.2.0.3.0 
- ProductionNLSRTL Version 10.2.0.3.0 - Production

Yes..looks fine as all oracle database components got upgraded successfully to 10.2.0.3 with VALID state ……….Enjoy the DB upgrade now :-)