Tales From A Lazy Fat DBA

Its all about Databases, their performance, troubleshooting & much more …. ¯\_(ツ)_/¯

Posts Tagged ‘Tuning’

Next Entries »

Flashback Drop (Recycle Bin)

Posted by FatDBA on July 16, 2012

When you’ve deleted a Table and soon after thyat deletion one of the user created a table with the same name then in that case we have to perform some steps to mitigate the conflict otherwise you’ll recieve error ‘Object trying to create already exists’ and will not allow you to restore the Old Table.

Deleted Table emp1 from system —

SQL> drop table emp1;
Table dropped.

SQL> show recyclebin;
ORIGINAL NAME    RECYCLEBIN NAME        OBJECT TYPE  DROP TIME
—————- —————————— ———— ——————-
EMP1             BIN$xNis/0KmyEPgQAB/AQBy6Q==$0 TABLE        2012-07-15:12:16:41

Created a table with same name after deletion – emp1 —

SQL> create table emp1 (name varchar2(10));
Table created.
SQL> drop table emp1;
Table dropped.

SQL> show recyclebin;
ORIGINAL NAME    RECYCLEBIN NAME        OBJECT TYPE  DROP TIME
—————- —————————— ———— ——————-
EMP1             BIN$xNis/0KnyEPgQAB/AQBy6Q==$0 TABLE        2012-07-15:12:17:31
EMP1             BIN$xNis/0KmyEPgQAB/AQBy6Q==$0 TABLE        2012-07-15:12:16:41

Now if i’ll try to Flashback table emp1 then it will restore the one with latest Time Stamp.
SQL>  show recyclebin;
ORIGINAL NAME    RECYCLEBIN NAME        OBJECT TYPE  DROP TIME
—————- —————————— ———— ——————-
EMP1             BIN$xNis/0KmyEPgQAB/AQBy6Q==$0 TABLE        2012-07-15:12:16:41
This can also be performed using Time Stamp —
FLASHBACK TABLE emp1 TO TIMESTAMP TO_TIMESTAMP(‘2012-07-15:12:16:41’, ‘YYYY-MM-DD HH:MI:SS’);
During the flashback operation the table can be renamed.
FLASHBACK TABLE flashback_drop_test TO BEFORE DROP RENAME TO flashback_drop_test_old;

Posted in Advanced | Tagged: , | Leave a Comment »

Dig AWR (Auto Workload Repository) …

Posted by FatDBA on June 28, 2012

Oracle 10g provides the AWR. The AWR is a repository of performance information collected by the database to aid in the tuning process for DBAs.

Historically people used bstat and estat to collect Oracle statistics over a time period and then compare them to each other. The bstat/estat approach was replaced with statspack available in Oracle 8i. Statspack was a package provided
by Oracle that did roughly the same thing but better. Statspack has now been surpassed in functionality by AWR which is always collecting execution statistics for future analysis and tuning performed by all of the expert components
provided by Oracle. Oracle recommends that all statspack users switch to AWR in 10g.

The metrics collected by AWR are easily obtained from v$metricname:

AWR is automatically installed and running with 10g. The new MMON process is responsible for collecting data and populating the AWR. The MMON process takes snapshots of performance data at regular intervals and inserts that data into the AWR tables. The tables containing AWR information are stored in the SYSAUX tablespace (also new in 10G) under the SYS schema. There are a ton of tables in this tablespace – 806 to be exact. However, the AWR related tables all begin with “WR”:

WRM$_WR_CONTROL       WRH$_TEMPFILE       WRI$_SQLSET_BINDS

The third letter of each table name signifies the type of data that it contains.
• I – advisory functions (SQL Advice, Space Advice, etc)
• M – metadata information
• H – historical data

The frequency of data collection is 60 minutes by default but that can be adjusted. All functionality for driving the workload repository is done via the Oracle supplied package DBMS_WORKLOAD_REPOSITORY. In 11g version, These snapshots are retained for 8 days. 7 days is the retention period in 10g.

The current settings for AWR retention and interval parameters can be viewed using dba_hist_wr_control.

Posted in Advanced | Tagged: | 2 Comments »

Difference between awrrpt.sql and awrrpti.sql ?

Posted by FatDBA on June 28, 2012

Difference between awrrpt.sql and awrrpti.sql?

The output of these reports is essentially the same, except that awrrpti.sql script allows you to define a specific instance to report on.

AWR Report types (Most Commonly used Reports):

  • The awrrpt.sql SQL script generates an HTML or text report that displays statistics for a range of snapshot Ids.
  • The awrrpti.sql SQL script generates an HTML or text report that displays statistics for a range of snapshot Ids on a specified database and instance.
  • The awrsqrpt.sql SQL script generates an HTML or text report that displays statistics of a particular SQL statement for a range of snapshot Ids. Run this report to inspect or debug the performance of a SQL statement.
  • The awrsqrpi.sql SQL script generates an HTML or text report that displays statistics of a particular SQL statement for a range of snapshot Ids on a specified database and instance. Run this report to inspect or debug the performance of a SQL statement on a specific database and instance.
  • The awrddrpt.sql SQL script generates an HTML or text report that compares detailed performance attributes and configuration settings between two selected time periods.
  • The awrddrpi.sql SQL script generates an HTML or text report that compares detailed performance attributes and configuration settings between two selected time periods on a specific database and instance.
  • The awrgrpt.sql  Generate AWR Cluster aggregated statistics from all the instances.
  • The awrgdrpt.sql  report compares the statistic results of differences between two different snapshot intervals, for the whole cluster database.

SQL> @awrsqrpt.sql —- Generate AWR Report Single Select Statement:

Run the awrsqrpt.sql script from $ORACLE_HOME/rdbms/admin/ as a sys user. The script will ask you for the output format of the report (HTML or TEXT), the the number of days (n) will result in the most recent
(n) days of snapshots being listed (If you hit enter, you will get all the available snapshots listed), begin and end snapshot interval Ids intervals and the SQL_ID for the specific SELECT statement …

To check SQL ID —

SQL> select SQL_ID  from V$SQLAREA where rownum =1 ;

SQL_ID
————-
1fkh93md0802n

Posted in Advanced, Basics, Uncategorized | Tagged: | Leave a Comment »

Next Entries »