Skip to main content

Get Oracle Perf. Reports by Scripting

To generate the AWR report:
  1. Log in to the machine hosting Oracle server.
  2. Connect to Oracle server with SYS or equivalent DBA level access user.
  3. At a command prompt, issue the following command: @$ORACLE_HOME/rdbms/admin/ awrrpt.sql
<< This script will create an interactive session follow the instruction as shown below.>>
  1. Specify a format for the report and press Enter.
  2. Specify the Database instance on which to run the report by typing in the database ID from the list of instances and press Enter (in most cases there is only one choice, but if there is more than one,  find out which instance hosts the your schema).
  3. Type the Inst Num and press Enter.
  4. Specify when the report should start:
    • You can choose to enter a numeric value for the most recent number of days (e.g., entry of 1 produces a report on the most recent one day of workload).
    • To have finer unit than days hit enter with no values and a list of snapshots will appear:
        • Type the Snap Id for the start time
        • Type the Snap Id for the end time
  1. Name the report and press Enter.
  2. Issue the quit command to exit from SQLPlus.
    • The report is generated at the path where the SQLPlus command was issued.

For enabling trace at entire database level you may use the following 2 steps :
           1.    To enable SQL tracing for the entire database, execute:
               ALTER SYSTEM SET sql_trace = true SCOPE=MEMORY;
To stop, execute:
               ALTER SYSTEM SET sql_trace = false SCOPE=MEMORY;

           2.    Locate the trace file under $ORACLE_HOME/diag/rdbms/<<database name>>/<<Instance name>>/trace/
           3.    Convert the trace file in readable format :
               Shell command :
                        $tkprof <<tress file path>> <<output_file_path.txt>> insert=tkprof.sql record=Allsql.sql

           4.    Get the AWR report too as this will rank all the wait and read events in well formatted way.

If you want to traces the specific session then following steps can be followed,
           1.     alter session set tracefile_identifier = 'My_Trace' ;
           2.     alter session set sql_trace = true;
           3.     << execute the report/application code >>
           4.     alter session set sql_trace = false;
           5.     Locate the trace file under $ORACLE_HOME/diag/rdbms/<<database name>>/<<Instance name>>/trace/
           6.     Convert the trace file in readable format :
               Shell command :
                        tkprof <<tress file path>> <<output_file_path.txt>> insert=tkprof.sql record=Allsql.sql

Comments

Post a Comment

Popular posts from this blog

Some facts and Figures of WCF

SOAP Message in WCF: 1.        The max size of SOAP message in WCF is 9,223,372,036,854,775,807 bytes. Including metadata. 2.        For actual user data we can use 2,147,483,647 bytes out of it. 3.        With default setting WCF uses only 65536 bytes. 4.        We can change it by setting maxReceivedMessageSize in clients app.config file.    5.        So selection of data types in Data Contract and Data table will matter a lot! 6.       Reference :   http://blogs.msdn.com/drnick/archive/2006/03/16/552628.aspx          http://blogs.msdn.com/drnick/archive/2006/03/10/547568.aspx       “Amazing blog for WCF!” Data Contract: 1.        By Default WCF can serialize 65536 DataMember. 2.        We can change it to max  2147483646. 3.       How?  Please go to: http://social.msdn.microsoft.com/Forums/en-US/wcf/thread/4a81a226-175b-41d3-864a-181792c71ffe Tracing WCF service message: 1.       Enable message log of WCF: http://www.avingtonsolutions.com/blog/post/2008/07/25/Tracing-SOAP-Message
Post a Comment
My Other Post >>

Drop all Objects from Schema In Postgres

To Drop all objects from Postgres Schema there could be following two approaches: Drop Schema with cascade all and re-create it again.  In some cases where you dont want to/not allowed to drop and recreate schema, its easy to look for objects on current schema and drop them. following script would help to do so, Create function which would do the task and then drop that function too. --- CREATE OR REPLACE FUNCTION drop_DB_objects() RETURNS VOID AS $$ DECLARE  rd_object RECORD; v_idx_statement VARCHAR(500);   BEGIN ---1. Dropping all stored functions RAISE NOTICE '%', 'Dropping all stored functions...'; FOR rd_object IN ( SELECT format('%I.%I(%s)', ns.nspname, p.proname, oidvectortypes(p.proargtypes)) as functionDef     FROM pg_proc p     INNER JOIN pg_namespace ns ON (p.pronamespace = ns.oid)    WHERE ns.nspname = current_schema      AND p.proname <> 'drop_db_objects' )
2 comments
My Other Post >>

Vacuum Analyze Full Schema in PostgreSQL Database

To analyze full database schema, you can use following shell script : -------------------------------------------------------------------------------------------------------------------------- #!/bin/sh rm -rf /tmp/vacuum_analyze_MyData.sql dbname="mydata" username="mydatadatamart" namespace="mydatadatamart" # Vacuum only those tables which are fragmented over 30% # Analyze tables only if they are not already # Process CMF and Groups Master table every time as they are master Tables=`psql $dbname $username << EOF SELECT 'VACUUM ANALYZE VERBOSE ' || tablename || ' ;' AS TableList   FROM ( SELECT *,       n_dead_tup > av_threshold AS "av_needed",       CASE WHEN reltuples > 0      THEN round(100.0 * n_dead_tup / (reltuples))       ELSE 0       END AS pct_dead FROM (SELECT C.relname AS TableName, pg_stat_get_tuples_inserted(C.oid) AS n_tup_ins, pg_stat_get_tuples_updated(
3 comments
My Other Post >>