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

Turn on INTERVAL PARTITIONING on a range partition table

Create a range partition table.

SQL:

drop _TABLE_ date_range_t purge;

CREATE TABLE date_range_t
(  
d1   DATE,
n1   NUMBER
) 
PARTITION BY RANGE ( d1 ) 
SUBPARTITION BY LIST ( n1 ) 
SUBPARTITION TEMPLATE ( 
SUBPARTITION s_1    VALUES ( 1,2 ) , 
SUBPARTITION s_2    VALUES ( 3,4 ) 
)
( PARTITION p200911  VALUES LESS THAN ( TO_DATE('2009/12/01', 'YYYY/MM/DD') )
)
;

Turn on INTERVAL PARTITIONING by month on a range partition table.

SQL:

ALTER TABLE date_range_t SET INTERVAL ( NUMTOYMINTERVAL ( 1, 'MONTH' ) );

Effective table selectivity index selectivity and index column sequence

/*********
-- file://A:/SQL/CBO/index_selectivity.sql

| Modification History:
| Date Who What
| 18-Nov-2009: Charlie(Yi): Create the file,
|

Effective table selectivity selectivity and index column sequence

Goal
----
Research the order of the index columns and the effective index selectivity.

Solution
--------
Create a table with number column and date column,
Create 2 indexes on the table, one with ID in index first position, one with date_col on first position.
Benchmark the query with equal scan and range scan predicate on each index.

Outcome
-------
The logical reads on index access is: BLevel + leaf_blocks * effective index selectivity.

* LIO is 5 when access index with column ID on first position
select 2 + Ceil(1520 * 0.2 * (40 * 100)/500000) from dual;

* LIO is 15 when access index with date_col on first position,
select 2 + Ceil(1520 * (40 * 100)/500000) from dual;


"Because as soon as we have a range scan on a column used somewhere in the
middle of an index definition or fail to supply a test on such a column, the predicates on later
columns don’t restrict the selection of index leaf blocks that we have to examine."

From page74 of book Cost Based Oracle Fundamentals

For 2nd query, the effective index selectivity has to be calculated from the predicate on just the
date_col column. Because the test on date_col is range-based, the predicates on ID do not
restrict the number of index leaf blocks we have to walk.

Note
----
In plan output, Buffers = Logical Reads.

*******/

Setup
-----

create table date_index_sel
(
id number,
date_col date,
n2 number
)
nologging;

insert --+ append
into date_index_sel(id,date_col)
select mod(rownum,5), sysdate + rownum/100
from dual
connect by level <= 500000;

commit;

create index date_index_sel_i1 on date_index_sel(id,date_col) nologging;
create index date_index_sel_i2 on date_index_sel(date_col,id) nologging;

exec dbms_stats.gather_table_stats(user,'date_index_sel');

select INDEX_NAME, BLEVEL, LEAF_BLOCKS, DISTINCT_KEYS, CLUSTERING_FACTOR, NUM_ROWS
from user_indexes
where index_name like 'DATE_INDEX_SEL%';

INDEX_NAME BLEVEL LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR NUM_ROWS

DATE_INDEX_SEL_I1 2 1520 500000 5475 500000
DATE_INDEX_SEL_I2 2 1520 500000 1095 500000


set serveroutput off
set linesize 370
set pagesize 0

REM TEST1, date_col is NOT the first index column, range scan on date_col,

select --+ index(a,date_index_sel_i1) gather_plan_statistics
count(*) from date_index_sel a
where id=3 
and date_col between sysdate + 100 and sysdate + 140;

--SELECT * FROM table(dbms_xplan.display_cursor(NULL,NULL, 'basic iostats memstats last partition'));
SELECT * FROM table(dbms_xplan.display_cursor(NULL,NULL, 'basic cost allstats last partition'));

Plan hash value: 341633530

-------------------------------------------------------------------------------
| Id  | Operation          | Name              |Cost (%CPU)| A-Rows | Buffers |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                   |    3 (100)|      1 |       5 |
|   1 |  SORT AGGREGATE    |                   |           |      1 |       5 |
|*  2 |   FILTER           |                   |           |    800 |       5 |
|*  3 |    INDEX RANGE SCAN| DATE_INDEX_SEL_I1 |    3   (0)|    800 |       5 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(SYSDATE+100<=SYSDATE+140)
   3 - access("ID"=3 AND "DATE_COL">=SYSDATE+100 AND "DATE_COL"<=SYSDATE+140)

REM TEST2, date_col is the first index column,

select --+ index(a,date_index_sel_i2) gather_plan_statistics
count(*) from date_index_sel a
where id=3 
and date_col between sysdate + 100 and sysdate + 140
;

SELECT * FROM table(dbms_xplan.display_cursor(NULL,NULL, 'basic cost iostats memstats last partition'));

Plan hash value: 1386725119

-------------------------------------------------------------------------------
| Id  | Operation          | Name              | Cost (%CPU)|A-Rows | Buffers |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                   |    15 (100)|     1 |      15 |
|   1 |  SORT AGGREGATE    |                   |            |     1 |      15 |
|*  2 |   FILTER           |                   |            |   800 |      15 |
|*  3 |    INDEX RANGE SCAN| DATE_INDEX_SEL_I2 |    15   (0)|   800 |      15 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(SYSDATE+100<=SYSDATE+140)
   3 - access("DATE_COL">=SYSDATE+100 AND "ID"=3 AND "DATE_COL"<=SYSDATE+140)
       filter("ID"=3)

***.

row level timestamp implementation

Goal
----
Identify latest row in one Bulk SQL INSERT,
e.g. JDBC Update Batching, Batch Value is 1000,
or PL/SQL FORALL statement.

Case
----
One Bulk SQL insert 1000 rows, select from an Oracle external table,
De-duplicate by primary key, identify latest row in the file.

Constraint: All rows get same SCN or timestamp value if they are in one INSERT SQL.

Solution
-- -- ---
plain simple Oracle Sequence, for row update timestamp implementation.

drop table t purge;
create table t(id number, ts timestamp(6), seq number);
drop sequence s;
create sequence s;

declare
lt_id dbms_sql.number_table;
begin
select rownum bulk collect into lt_id
from dual
connect by level <= 10;

forall i in 1..lt_id.count
insert into t(id, ts)
values(lt_id(i), systimestamp);

commit;

end;
/

select id, ts from t;


--
-- With update_change_sequence

declare
lt_id dbms_sql.number_table;
begin
select rownum bulk collect into lt_id
from dual
connect by level <= 10;

forall i in 1..lt_id.count
insert into t(id, ts, seq)
values(lt_id(i), systimestamp, s.nextval );

commit;

end;
/

select id, seq, ts from t;

Tune SQL with SQL plan baseline

Goal
----
Tune SQL plan without change the source code(SQL text),

A:\SQL\CBO\tune_sql_by_baseline01.sql

| Modification History:
| Date Who What
| 11-Nov-2009: Charlie(Yi): Create the file,
| 18-Nov-2009: Charlie(Yi): Show binding variable value,


Solution
--------
* tune SQL execution plan by SQL plan baseline, load_plans_from_cursor_cache to
load the hint/changed resulting plan(s) into the SQL plan baseline of the original SQL statement,

Step
----

REM display current "bad" sql plan,

SQL>
set serveroutput off
set linesize 300

var l_bad_sql_id varchar2(30);
var l_sql_text varchar2(200);
exec :l_bad_sql_id := 'cbzfmb3mc9047';
exec :l_sql_text := q'{FROM WANTS WHERE CLIENTID = :1 AND ROWDF = 'F' AND UPPERTITLE >= :2}';

REM 2nd option to get SQL_ID,
SELECT b.executions EXEC, INST_ID, b.sql_id, CHILD_NUMBER,
ROUND(b.ROWS_PROCESSED/b.executions,1) rows_per,
ROUND(b.BUFFER_GETS/b.executions,1) gets_per,
ROUND(b.ELAPSED_TIME/b.executions/1000000,3) ela_per,
b.module, parse_calls, first_load_time, disk_reads,
b.sql_text
FROM gv$sql b
WHERE UPPER(b.sql_text) LIKE UPPER('%'||:l_sql_text||'%')
AND b.executions > 0
and b.sql_text not like 'SELECT b.executions EXEC%'
ORDER BY b.executions DESC;


select * from table(dbms_xplan.display_awr(sql_id=>:l_bad_sql_id, format=>'basic rows cost partition note last'));

or

select * from table(dbms_xplan.display_cursor(sql_id=>:l_bad_sql_id, format=>'basic rows cost partition note last'));


PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------

SQL_ID cbzfmb3mc9047
--------------------
SELECT * FROM WANTS WHERE CLIENTID = :1 AND ROWDF = 'F' AND UPPERTITLE >= :2 ORDER BY UPPERTITLE;

Plan hash value: 1289812832

----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 363 (100)|
| 1 | TABLE ACCESS BY INDEX ROWID| WANTS | 38233 | 363 (0)|
| 2 | INDEX RANGE SCAN | WANTSUPPERTITLE | 530 | 12 (0)|
----------------------------------------------------------------------------

REM - show binding variable value,
select name, position,datatype_string, last_captured, value_string, b.child_address, b.address
from v$sql_bind_capture b
where sql_id = :l_bad_sql_id
and was_captured <> 'NO';


REM create sql plan baseline for the SQL,

declare
l_pls number;
begin
l_pls := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (
sql_id => :l_bad_sql_id,
plan_hash_value => NULL,
fixed => 'NO',
enabled => 'NO'
);
u$err.trc('Number of plans loaded:'||l_pls);

end;
/

REM get sql text, sql_handle
select sql_text, sql_handle, plan_name, enabled, accepted, fixed, autopurge from dba_sql_plan_baselines
where sql_text like '%'||:l_sql_text||'%';

var l_sql_handle varchar2(30);
exec :l_sql_handle := 'SYS_SQL_ce7588e9ccd926d0';

REM change sql hint to tune SQL execution plan,
set serveroutput off
set linesize 300
var l_sql_id varchar2(30);
var l_sql_plan_hash_value number;
var l_sql_child_number number;

var B1 number;
exec :B1 := 123;
var B2 varchar2(30);
exec :B2 := '1S & 2ND KINGS';

SELECT --+ index_rs_asc(wants WANTSCLIENTID)
* FROM WANTS WHERE CLIENTID = :B1 AND ROWDF = 'F' AND UPPERTITLE >= :B2 ORDER BY UPPERTITLE;

REM capture new SQL_ID and plan_hash_value,
begin
with m as (select sid from v$mystat where rownum <= 1)
select prev_sql_id, prev_child_number
into :l_sql_id, :l_sql_child_number
from m, v$session s
where s.sid = m.sid;
select plan_hash_value into :l_sql_plan_hash_value from v$sql
where sql_id = :l_sql_id
and rownum <= 1;

end;
/

print :l_sql_id;
print :l_sql_plan_hash_value;
print :l_sql_child_number;

REM verify the new SQL plan,
select * from table(dbms_xplan.display_cursor(sql_id=>:l_sql_id, cursor_child_no=>:l_sql_child_number,
format=>'basic rows cost partition note last'));

REM load new sql plan into plan baseline, with same sql_handle,
declare
l_pls number;
begin

l_pls := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (
sql_id => :l_sql_id,
plan_hash_value => :l_sql_plan_hash_value,
sql_handle => :l_sql_handle,
fixed => 'NO',
enabled => 'NO'
);
u$err.trc('Number of plans:'||l_pls);
end;
/

REM display sql plan baseline,
SELECT *
FROM table(dbms_xplan.display_sql_plan_baseline(
sql_handle => :l_sql_handle,
format => 'typical'
))
;


PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------
SQL handle: SYS_SQL_ce7588e9ccd926d0
SQL text: SELECT * FROM WANTS WHERE CLIENTID = :1 AND ROWDF = 'F' AND UPPERTITLE
>= :2 ORDER BY UPPERTITLE
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
Plan name: SYS_SQL_PLAN_ccd926d0604f1815
Enabled: NO Fixed: NO Accepted: YES Origin: MANUAL-LOAD
--------------------------------------------------------------------------------

Plan hash value: 1289812832

-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 217 | 17021 (1)| 00:03:08 |
|* 1 | TABLE ACCESS BY INDEX ROWID| WANTS | 1 | 217 | 17021 (1)| 00:03:08 |
|* 2 | INDEX RANGE SCAN | WANTSUPPERTITLE | 19756 | | 361 (0)| 00:00:04 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("CLIENTID"=TO_NUMBER(:1) AND "ROWDF"='F')
2 - access("UPPERTITLE">=:2)

--------------------------------------------------------------------------------
Plan name: SYS_SQL_PLAN_ccd926d0ff894fe1
Enabled: YES Fixed: YES Accepted: YES Origin: MANUAL-LOAD
--------------------------------------------------------------------------------

Plan hash value: 1555025420

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 217 | 8 (13)| 00:00:01 |
| 1 | SORT ORDER BY | | 1 | 217 | 8 (13)| 00:00:01 |
|* 2 | TABLE ACCESS BY INDEX ROWID| WANTS | 1 | 217 | 7 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | WANTSCLIENTID | 7 | | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("UPPERTITLE">=:2 AND "ROWDF"='F')
3 - access("CLIENTID"=TO_NUMBER(:1))


REM enable new plan in plan baseline,
REM changes an attribute of a single plan or all plans,
declare
l_pls number;
begin

l_pls := dbms_spm.ALTER_SQL_PLAN_BASELINE(
sql_handle => :l_sql_handle,
plan_name => 'SYS_SQL_PLAN_ccd926d0ff894fe1',
attribute_name => 'enabled',
attribute_value => 'YES'
);
u$err.trc('Number of plans:'||l_pls);
end;
/

REM fixed new plan in plan baseline,
REM if it is realy a good plan,
declare
l_pls number;
begin

l_pls := dbms_spm.ALTER_SQL_PLAN_BASELINE(
sql_handle => :l_sql_handle,
plan_name => 'SYS_SQL_PLAN_ccd926d0ff894fe1',
attribute_name => 'fixed',
attribute_value => 'YES'
);
u$err.trc('Number of plans:'||l_pls);
end;
/

REM verify attribute ENABLED and FIXED,
select sql_text, sql_handle, plan_name, enabled, accepted, fixed, autopurge
from dba_sql_plan_baselines
where sql_handle = :l_sql_handle;


REM verify new plan is picked up by CBO optimizer,
explain plan for
SELECT * FROM WANTS WHERE CLIENTID = :1 AND ROWDF = 'F' AND UPPERTITLE >= :2 ORDER BY UPPERTITLE;

select * from table(dbms_xplan.display());