Setup StatsPack to monitor standby database performance

AWR/ADDM are not supported (and don't work) against a read only standby database, so they are not useful for diagnostic for an Active Data Guard environment.

A modifed version of statspack availabe on metalink (note 454848.1) can be deployed on the primary production database. This modified version uses a STDBYPERF schema which writes locally but reads remotely from PERFSTAT@standbydb.

Initial Setup (Oracle 11.1.0.7)

export ORACLE_SID=standbydb
cd $ORACLE_HOME/rdbms/admin
sql
CREATE SMALLFILE TABLESPACE PERFSTAT DATAFILE '+DATA1/perfstat01.dbf' SIZE 4G
 LOGGING 
 EXTENT MANAGEMENT LOCAL 
 SEGMENT SPACE MANAGEMENT AUTO;
@spdrop
@spcreate
exit;

sql
@sbcreate

--wait a second or two, the account has to propagate to the standby

@sbaddins

Configure Snapshots

Remember that PERFSTAT is not automatically purged/managed by the database, so this must be done by the DBA or scheduled.

-- workaround to avoid serious performance problem due to Bug 8323663
. setdb ordprod2
sql
execute dbms_stats.gather_table_stats(ownname => 'SYS',tabname => 'X$KCCTS', estimate_percent => 20 );
execute dbms_stats.gather_table_stats(ownname => 'SYS',tabname => 'X$KCFIO', estimate_percent => 20 );
execute dbms_stats.gather_table_stats(ownname => 'SYS',tabname => 'X$KCBFWAIT', estimate_percent => 20 );
execute dbms_stats.gather_table_stats(ownname => 'SYS',tabname => 'X$KCCFN', estimate_percent => 20 );
execute dbms_stats.gather_table_stats(ownname => 'SYS',tabname => 'X$KCCFE', estimate_percent => 20 );


grant create job to stdbyperf;
grant manage scheduler to stdbyperf;

conn stdbyperf/***

-- if this takes more than ~40 seconds there is a performance issue that will need to be traced
exec statspack_ordprod1.snap;

-- automate stats collection every 30 min during Mon-Fri 7am-5pm

exec dbms_scheduler.disable('ordrpt_statspack_snap_daily');

begin
   dbms_scheduler.create_job
   (
      job_name => 'ordrpt_statspack_snap_daily',
      job_type => 'PLSQL_BLOCK',
      job_action => 'begin statspack_ordprod1.snap; end;',
      repeat_interval => 'FREQ=MINUTELY; INTERVAL=30; BYHOUR=7,8,9,10,11,12,13,14,15,16,17; BYDAY=MON,TUE,WED,THU,FRI',
      enabled => true,
      comments => 'Take statspack snapshot on remote standby db ordrpt daily Mon to Fri 7am to 5pm'
   );
end;
/

exec dbms_scheduler.enable('ordrpt_statspack_snap_daily');

Create Reports

Similar to AWR reports, run reports from caldbrac02

SQL> conn stbyperf/***

SQL> @?/rdbms/admin/sbreport

-- follows prompts to enter dbid, instanceid, snapshot intervals, output is text file

Author: Blair Boadway  |  Production DBA

Query paging row count

Do we need to return the accurate query row count?


We will always evaluate the query performance and cost to get the row count, and based on function/features priority and data accuracy requirements, then decide which option to go.


Also assess which option list below that the end users value more?

• The quick response time to return the first page

or

• to get the exact row count.

Here are some options I know so far:

• Materialize the row count in MV(Summary table), schedule a regular job to refresh the summary data.
  
• Estimate the row count, by CBO parse

Here is the idea, check the plan output Rows column.

The CBO (Cost Base Optimizer) estimate it when generate the SQL execution plan.
CBO get it from object(table/index) stats, e.g. dba_tables.num_rows .
But not touching the table.

scott@txdev_FLURRY> set autot trace exp
scott@txdev_FLURRY>
scott@txdev_FLURRY> select * from emp;
Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    14 |   518 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMP  |    14 |   518 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

• Estimate the row count, with oracles SAMPLE clause
  
• Hard limit in Transactional database API,

Show user that there could be more rows than hard code limit, and always enable [Next Page] button, and dynamic change or increase the count number as paging forward.

SELECT count(*) from scott.emp where rownum <= 100;

.

SOA - Transactional Database API - Database access guideline

"Applications come, applications go, they crash, they change, they are fleeting."
The data however -- that'll be here long.

Programming language come, programming languages go, DBMSs come, DBMSs go,

In fact, I've seen successful Cobol, Fortran, VB, Perl, PHP, Java, Python, whatever language; FoxPro, Sybase, DB2, Oracle, MySQL, Cassandra HBase, whatever database.

The data, well, Our POs(orders) and Clients data have been in a database for over 12 years now -- the applications that access (should say "have accessed in the past for they no longer exist") it come and they go and there will be many many many more in the future.


The solution is there: SOA (Modular programming)

SOA is a design principle, whereas web services is an implementation technology. You can build a Service-Oriented Application with Python, .Net, C, Java, or PL/SQL, T-SQL.

The basic building block... is the service. A service is a self-contained software module that performs a predetermined task: e.g. a business transaction to submit order, audit the changes, update inventory, update finance data, queue the changes to asynchronous batch notice buyers ...; that comes to the Transactional database API.

The main theme behind SOA is to find the appropriate modularity ... where the modules don't have too much tight coupling. such as UI, Business rule and DAL.

An typical bad example is the Buyer Order Tracker UI, the reporting UI rule is tightly coupled with data access, make it so hard to refactor the data access part. Buyer Order Track has made database performance plunged many times.

If we implement the SOA, the database handle the data access, and the Java handle the UI, it'll be a piece of cake to fix the Buyer Order Track database performance issue.

When we want to migrate from Oracle to Sybase, just write a new DB API on Sybase, UI application call the new API.
When we want to migrate from .Net to Java, just write a new UI module, that calls the same DB API to access the data.

Notes: many statements are referenced from AskTom