31 August, 2011

CREATE INDEX ..... PARALLEL

We generally use the PARALLEL keyword to speed up index creation thus : CREATE INDEX ..... PARALLEL n. But also results in the Parallel attribute being set for the index, which means that Oracle may choose to read the index using Parallel Query operators.
If the same index were read by Index Fast Full Scan by many sessions concurrently, the database instance could end up having many PQ slaves reading the index blocks and not buffering it in the SGA (database cache). Every user makes a fresh read call for all the index blocks, and they are not cached and reused.

I generally prefer that every Index be set to NOPARALLEL immediately after creation --- unless it can be proven that Parallel reads of the index would be beneficial.


Here is a table with 1.6million rows and 50 distinct CUST_IDs :


SQL> select partition_name, num_rows from user_tab_partitions
2 where table_name = 'SALES_FACT';

PARTITION_NAME NUM_ROWS
------------------------------ ----------
SF_P_2007 249307
SF_P_2008 350691
SF_P_2009 398842
SF_P_2010 499781
SF_P_2011 101374

SQL>
SQL> drop index sf_cust_b_ndx;

Index dropped.

SQL> create bitmap index sf_cust_b_ndx on SALES_FACT(CUST_ID) local parallel 4 nologging;

Index created.

SQL>
SQL> explain plan for select distinct cust_id from sales_fact;

Explained.

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

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1638564360

------------------------------------------------------------------------------------------------------------------------------------
------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT|
PQ Distrib |

------------------------------------------------------------------------------------------------------------------------------------
------------

| 0 | SELECT STATEMENT | | 50 | 150 | 356 (14)| 00:00:05 | | | | |
|

| 1 | PX COORDINATOR | | | | | | | | | |
|

| 2 | PX SEND QC (RANDOM) | :TQ10001 | 50 | 150 | 356 (14)| 00:00:05 | | | Q1,01 | P->S |
QC (RAND) |

| 3 | HASH UNIQUE | | 50 | 150 | 356 (14)| 00:00:05 | | | Q1,01 | PCWP |
|

| 4 | PX RECEIVE | | 1599K| 4687K| 309 (0)| 00:00:04 | | | Q1,01 | PCWP |
|

| 5 | PX SEND HASH | :TQ10000 | 1599K| 4687K| 309 (0)| 00:00:04 | | | Q1,00 | P->P |
HASH |

| 6 | PX BLOCK ITERATOR | | 1599K| 4687K| 309 (0)| 00:00:04 | 1 | 5 | Q1,00 | PCWC |
|

| 7 | BITMAP INDEX FAST FULL SCAN| SF_CUST_B_NDX | 1599K| 4687K| 309 (0)| 00:00:04 | 1 | 5 | Q1,00 | PCWP |
|

------------------------------------------------------------------------------------------------------------------------------------
------------


14 rows selected.

SQL>
SQL> alter index sf_cust_b_ndx noparallel;

Index altered.

SQL>
SQL> explain plan for select distinct cust_id from sales_fact;

Explained.

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

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1637766894

---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50 | 150 | 356 (14)| 00:00:05 | | |
| 1 | HASH UNIQUE | | 50 | 150 | 356 (14)| 00:00:05 | | |
| 2 | PARTITION RANGE ALL | | 1599K| 4687K| 309 (0)| 00:00:04 | 1 | 5 |
| 3 | BITMAP INDEX FAST FULL SCAN| SF_CUST_B_NDX | 1599K| 4687K| 309 (0)| 00:00:04 | 1 | 5 |
---------------------------------------------------------------------------------------------------------------

10 rows selected.

SQL>


Thus, after the Index is created with PARALLEL, the default behaviour for an Index Fast Full Scan is to use Parallel Query.

What other sorts of queries would use PQ (without having specified a Hint) ? When would it be beneficial to allow PQ ?

.
.
.



26 August, 2011

Gather Column (Histogram) Stats can use an Index

We use the METHOD_OPT parameter to specify columns where we want specific Histograms when running a GATHER_TABLE_STATS. Oracle can actually make use of an Index to gather such column statistics.

To demonstrate, I run these statements :

23:24:40 SQL> exec dbms_session.session_trace_enable;

PL/SQL procedure successfully completed.

23:28:46 SQL> exec dbms_stats.gather_table_stats('HEMANT','GTS_DIRECT',-
23:29:01 > method_opt=>'FOR COLUMNS CUST_ID SIZE 250',estimate_percent=>100);

PL/SQL procedure successfully completed.

23:29:26 SQL> exec dbms_session.session_trace_disable;

PL/SQL procedure successfully completed.

23:29:49 SQL>


The trace indicates that these statements are executed :

SQL ID: 7u3g0tnxmck0n
Plan Hash: 1065175879
select /*+ no_parallel(t) no_parallel_index(t) dbms_stats
cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring
no_substrb_pad */count(*), sum(sys_op_opnsize("CUST_ID")),
sum(sys_op_opnsize("PROD_ID")), count("SALE_QTY"),
sum(sys_op_opnsize("SALE_QTY")), count("SALE_PRICE"),
sum(sys_op_opnsize("SALE_PRICE")), count("DISCOUNT_RATE"),
sum(sys_op_opnsize("DISCOUNT_RATE"))
from
"HEMANT"."GTS_DIRECT" t


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 2 1.91 2.04 10603 10607 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 1.91 2.04 10603 10607 0 1

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

Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=10607 pr=10603 pw=0 time=0 us)
1599995 TABLE ACCESS FULL GTS_DIRECT (cr=10607 pr=10603 pw=0 time=23223592 us cost=2928 size=55952400 card=1598640)


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
asynch descriptor resize 2 0.00 0.00
********************************************************************************

SQL ID: gqcrdy9818rwv
Plan Hash: 585751186
select substrb(dump(val,16,0,32),1,120) ep, cnt
from
(select /*+ no_parallel(t) no_parallel_index(t) dbms_stats
cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring
no_substrb_pad */"CUST_ID" val,count(*) cnt from "HEMANT"."GTS_DIRECT" t
where "CUST_ID" is not null group by "CUST_ID") order by val


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 51 1.18 1.39 3132 3144 0 50
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 53 1.18 1.39 3132 3144 0 50

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

Rows Row Source Operation
------- ---------------------------------------------------
50 SORT GROUP BY (cr=3144 pr=3132 pw=0 time=0 us cost=911 size=150 card=50)
1599995 INDEX FAST FULL SCAN GTS_DIRECT_CUST_NDX (cr=3144 pr=3132 pw=0 time=122696624 us cost=864 size=4795920 card=1598640)(object id 87367)


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file scattered read 40 0.01 0.15
********************************************************************************


Thus, Oracle used the GTS_DIRECT_CUST_NDX on the CUST_ID column.

.
.
.

25 August, 2011

Does GATHER_TABLE_STATS update Index Statistics ?

In 9i, the GATHER_TABLE_STATS procedure would default CASCADE to FALSE.
In 10g and 11g, the procedure defaults CASCADE to AUTO.
In theory, you would expect that if the optimizer knows that the number of updates to the index since the last gather index stats is not significant, the AUTO behaviour for CASCADE would mean that Oracle would not include Indexes when running a GATHER_TABLE_STATS.
(Of course, if the Index statistics are old and there has been significant DML since then, AUTO would cause Index statistics to be gathered).

Here's a test in 11.2 (11.2.0.1) :

First I confirm that my CASCADE still defaults to AUTO :

SQL> select dbms_stats.get_prefs('CASCADE') from dual;

DBMS_STATS.GET_PREFS('CASCADE')
--------------------------------------------------------------------------
DBMS_STATS.AUTO_CASCADE

SQL>


Next I run two tests whereby
a) I CREATE INDEX *after* the table is populated -- so the CREATE INDEX does it's own GATHER_INDEX_STATS
b) I run GATHER_TABLE_STATS
c) I then verify if the GATHER_TABLE_STATS did re-gather index stats

In the first test, the table is populated via "normal" DML. In the second test, the table is populated via a Direct Path INSERT.

However (surprisingly ??) I find that although the CREATE INDEX has actually populated the Index Statistics with 100% sampling, the subsequent call to GATHER_TABLE_STATS seems to re-gather Index Statistics !

23:28:10 SQL> alter session set nls_date_format='DD-MON-RR HH24:MI:SS';

Session altered.

23:28:10 SQL>
23:28:10 SQL> REM REM ##########################################
23:28:10 SQL> REM -- This table has data inserted normally
23:28:10 SQL>
23:28:10 SQL> create table GTS_NORMAL as select * from sales_fact where 1=2;

Table created.

23:28:10 SQL> insert into GTS_NORMAL select * from sales_fact;

1599995 rows created.

23:28:17 SQL> create index GTS_NORMAL_CUST_NDX on GTS_NORMAL (CUST_ID);

Index created.

23:28:27 SQL> -- check existing statistics
23:28:27 SQL> select table_name Object_name, num_rows, last_analyzed
23:28:27 2 from user_tables
23:28:27 3 where table_name = 'GTS_NORMAL'
23:28:27 4 union
23:28:27 5 select index_name Object_name, num_rows, last_analyzed
23:28:27 6 from user_indexes
23:28:27 7 where table_name = 'GTS_NORMAL'
23:28:27 8 /

OBJECT_NAME NUM_ROWS LAST_ANALYZED
------------------------------ ---------- ------------------
GTS_NORMAL
GTS_NORMAL_CUST_NDX 1599995 25-AUG-11 23:28:27

23:28:27 SQL> exec dbms_lock.sleep(180);

PL/SQL procedure successfully completed.

23:31:28 SQL> -- gather statistics
23:31:28 SQL> exec dbms_stats.gather_table_stats('','GTS_NORMAL',estimate_percent=>30,degree=>2);

PL/SQL procedure successfully completed.

23:31:37 SQL> -- recheck statistics
23:31:37 SQL> select table_name Object_name, num_rows, last_analyzed
23:31:37 2 from user_tables
23:31:37 3 where table_name = 'GTS_NORMAL'
23:31:37 4 union
23:31:37 5 select index_name Object_name, num_rows, last_analyzed
23:31:37 6 from user_indexes
23:31:37 7 where table_name = 'GTS_NORMAL'
23:31:37 8 /

OBJECT_NAME NUM_ROWS LAST_ANALYZED
------------------------------ ---------- ------------------
GTS_NORMAL 1597223 25-AUG-11 23:31:36
GTS_NORMAL_CUST_NDX 1572991 25-AUG-11 23:31:37

23:31:37 SQL>

The CREATE INDEX had gathered index statistics with 100% sampling and presenting the correct NUM_ROWS count at 23:28:27. However, the GATHER_TABLE_STATS re-updated Index Statistics as well, this time using the 30% sampling ! It overwrote the Index statistics that had ben gathered only 3minutes earlier (with no new DML occurring in-between !).

Here's the test with a table where DIRECT PATH INSERT is used :

23:31:37 SQL> REM REM ##########################################
23:31:37 SQL> REM -- This table has data inserted via DirectPath
23:31:37 SQL>
23:31:37 SQL> create table GTS_DIRECT as select * from sales_fact where 1=2;

Table created.

23:31:37 SQL> insert /*+ APPEND */ into GTS_DIRECT select * from sales_fact;

1599995 rows created.

23:31:44 SQL> create index GTS_DIRECT_CUST_NDX on GTS_DIRECT (CUST_ID);

Index created.

23:31:57 SQL> -- check existing statistics
23:31:57 SQL> select table_name Object_name, num_rows, last_analyzed
23:31:57 2 from user_tables
23:31:57 3 where table_name = 'GTS_DIRECT'
23:31:57 4 union
23:31:57 5 select index_name Object_name, num_rows, last_analyzed
23:31:57 6 from user_indexes
23:31:57 7 where table_name = 'GTS_DIRECT'
23:31:57 8 /

OBJECT_NAME NUM_ROWS LAST_ANALYZED
------------------------------ ---------- ------------------
GTS_DIRECT
GTS_DIRECT_CUST_NDX 1599995 25-AUG-11 23:31:57

23:31:57 SQL> exec dbms_lock.sleep(180);

PL/SQL procedure successfully completed.

23:34:58 SQL> -- gather statistics
23:34:58 SQL> exec dbms_stats.gather_table_stats('','GTS_DIRECT',estimate_percent=>30,degree=>2);

PL/SQL procedure successfully completed.

23:35:06 SQL> -- recheck statistics
23:35:06 SQL> select table_name Object_name, num_rows, last_analyzed
23:35:06 2 from user_tables
23:35:06 3 where table_name = 'GTS_DIRECT'
23:35:06 4 union
23:35:06 5 select index_name Object_name, num_rows, last_analyzed
23:35:06 6 from user_indexes
23:35:06 7 where table_name = 'GTS_DIRECT'
23:35:06 8 /

OBJECT_NAME NUM_ROWS LAST_ANALYZED
------------------------------ ---------- ------------------
GTS_DIRECT 1599103 25-AUG-11 23:35:04
GTS_DIRECT_CUST_NDX 1645512 25-AUG-11 23:35:06

23:35:06 SQL>

Again, the GATHER_TABLE_STATS resulted in a fresh gathering of Index Statistics, overwriting those that the CREATE INDEX had meticulously done !

What if I try again after some time ?

23:43:04 SQL> exec dbms_stats.gather_table_stats('','GTS_NORMAL',estimate_percent=>30,degree=>2);

PL/SQL procedure successfully completed.

23:43:24 SQL> select table_name Object_name, num_rows, last_analyzed
from user_tables
where table_name = 'GTS_NORMAL'
union
select index_name Object_name, num_rows, last_analyzed
from user_indexes
where table_name = 'GTS_NORMAL'
/
23:43:55 2 23:43:55 3 23:43:55 4 23:43:55 5 23:43:55 6 23:43:55 7 23:43:55 8
OBJECT_NAME NUM_ROWS LAST_ANALYZED
------------------------------ ---------- ------------------
GTS_NORMAL 1597310 25-AUG-11 23:43:23
GTS_NORMAL_CUST_NDX 1594325 25-AUG-11 23:43:24

23:43:55 SQL>
23:43:55 SQL> exec dbms_stats.gather_table_stats('','GTS_DIRECT',estimate_percent=>30,degree=>2);

PL/SQL procedure successfully completed.

23:44:58 SQL> select table_name Object_name, num_rows, last_analyzed
from user_tables
where table_name = 'GTS_DIRECT'
union
select index_name Object_name, num_rows, last_analyzed
from user_indexes
where table_name = 'GTS_DIRECT'
/
23:45:13 2 23:45:13 3 23:45:13 4 23:45:13 5 23:45:13 6 23:45:13 7 23:45:13 8
OBJECT_NAME NUM_ROWS LAST_ANALYZED
------------------------------ ---------- ------------------
GTS_DIRECT 1598393 25-AUG-11 23:44:57
GTS_DIRECT_CUST_NDX 1629881 25-AUG-11 23:44:58

23:45:13 SQL>


So, a run of DBMS_STATS.GATHER_TABLE_STATS seems to re-gather Index Statistics, even if Index Statistics are very fresh and do not need to be updated. What gives ? Why does AUTO_CASCADE behave in this manner ?

.
.
.



21 August, 2011

Reading an AWR Report -- 3

Continuing the Reading an AWR report series ....

I run these statements :
SQL> exec dbms_workload_repository.create_snapshot;


PL/SQL procedure successfully completed.

Elapsed: 00:00:01.15
SQL> set serveroutput on
SQL> declare
2 cnt number;
3 begin
4 select sum(sale_qty) into cnt
5 from very_large_fact_table
6 where prod_id = 5;
7 dbms_output.put_line('Sales for Prod 5 : ' || cnt);
8 select sum(sale_qty) into cnt
9 from very_large_fact_table
10 where prod_id in (4,11);
11 dbms_output.put_line('Sales for Prods 4,11 : ' || cnt);
12 select sum(sale_qty) into cnt
13 from very_large_fact_table
14 where prod_id = 10
15 and cust_id = 10;
16 dbms_output.put_line('Sales for Prod 10 to Cust 10 : ' || cnt);
17 end;
18 /
Sales for Prod 5 : 4012173200
Sales for Prods 4,11 : 8004026720
Sales for Prod 10 to Cust 10 : 2017249360

PL/SQL procedure successfully completed.

Elapsed: 00:01:34.05
SQL> exec dbms_workload_repository.create_snapshot;


The AWR report indicates that the whole PLSQL block took 57.47 seconds but that the individual SQLs in the block took 70.52 to 112.95 seconds (Note :The client reported a total elapsed time of 94.05seconds, including parsing and SQLNet time !).

SQL ordered by Elapsed Time              DB/Inst: ORCL/orcl  Snaps: 1320-1321

-> Resources reported for PL/SQL code includes the resources used by all SQL
statements called by the code.
-> % Total DB Time is the Elapsed Time of the SQL statement divided
into the Total Database Time multiplied by 100
-> %Total - Elapsed Time as a percentage of Total DB time
-> %CPU - CPU Time as a percentage of Elapsed Time
-> %IO - User I/O Time as a percentage of Elapsed Time
-> Captured SQL account for 99.7% of Total DB Time (s): 284
-> Captured PL/SQL account for 20.7% of Total DB Time (s): 284

Elapsed Elapsed Time
Time (s) Executions per Exec (s) %Total %CPU %IO SQL Id
---------------- -------------- ------------- ------ ------ ------ -------------
112.9 1 112.95 39.8 23.5 .4 cc8khw75jw0ha
Module: SQL*Plus
SELECT SUM(SALE_QTY) FROM VERY_LARGE_FACT_TABLE WHERE PROD_ID = 10 AND CUST_ID =
10

97.2 1 97.22 34.3 34.7 1.1 dwm8pq8awt96v
Module: SQL*Plus
SELECT SUM(SALE_QTY) FROM VERY_LARGE_FACT_TABLE WHERE PROD_ID IN (4,11)

70.5 1 70.52 24.9 37.2 .6 gscvdz32smtgr
Module: SQL*Plus
SELECT SUM(SALE_QTY) FROM VERY_LARGE_FACT_TABLE WHERE PROD_ID = 5

57.5 1 57.47 20.3 .3 .0 1dh6uf6hyr3us
Module: SQL*Plus
declare cnt number; begin select sum(sale_qty) into cnt from very_large_fact_t
able where prod_id = 5; dbms_output.put_line('Sales for Prod 5 : ' || cnt); sel
ect sum(sale_qty) into cnt from very_large_fact_table where prod_id in (4,11);
dbms_output.put_line('Sales for Prods 4,11 : ' || cnt); select sum(sale_qty) int

1.1 1 1.14 .4 88.2 10.6 1uk5m5qbzj1vt
Module: SQL*Plus
BEGIN dbms_workload_repository.create_snapshot; END;


The table has a PARALLEL DEGREE of 4 defined so each of the three queries, doing a FullTableScan actually used 4 PQ slaves.

What are the time model statistics ?
Time Model Statistics                    DB/Inst: ORCL/orcl  Snaps: 1320-1321

-> Total time in database user-calls (DB Time): 283.5s
-> Statistics including the word "background" measure background process
time, and so do not contribute to the DB time statistic
-> Ordered by % or DB time desc, Statistic name

Statistic Name Time (s) % of DB Time
------------------------------------------ ------------------ ------------
sql execute elapsed time 281.8 99.4
DB CPU 88.2 31.1
parse time elapsed 0.4 .1
PL/SQL execution elapsed time 0.2 .1
hard parse elapsed time 0.2 .1
hard parse (sharing criteria) elapsed time 0.1 .0
PL/SQL compilation elapsed time 0.0 .0
repeated bind elapsed time 0.0 .0


The Time Model Statistics indicates that the summed elapsed time for all sessions (including PQ slaves) is 281.8seconds and the summed DB CPU t ime is 88.2seconds.

The Timed Foreground Events shows :
                                                          Avg

wait % DB
Event Waits Time(s) (ms) time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
DB CPU 88 31.1
direct path read 61 2 31 .7 User I/O
cursor: pin S wait on X 3 0 67 .1 Concurrenc
PX Deq: Signal ACK RSG 2 0 89 .1 Other
db file sequential read 50 0 4 .1 User I/O


If Database CPU time was 88.2seconds and total Database SQL Elapsed Time was 281.8seconds, how do we account for the large discrepancy ? All the other wait event hardly add up to a few seconds.

.
.
UPDATE 22-Aug-11 :
What if the table is set to NOPARALLEL so that the the three SQL executions do not use Parallel Query slaves ?
SQL> select degree from user_tables where table_name = 'VERY_LARGE_FACT_TABLE';


DEGREE
----------------------------------------
4

Elapsed: 00:00:00.18
SQL> alter table very_large_fact_table noparallel;

Table altered.

Elapsed: 00:00:00.73
SQL> select degree from user_tables where table_name = 'VERY_LARGE_FACT_TABLE';

DEGREE
----------------------------------------
1

Elapsed: 00:00:00.01
SQL>
SQL> exec dbms_workload_repository.create_snapshot;

PL/SQL procedure successfully completed.

Elapsed: 00:00:03.13
SQL> set serveroutput on
SQL> declare
2 cnt number;
3 begin
4 select sum(sale_qty) into cnt
5 from very_large_fact_table
6 where prod_id = 5;
7 dbms_output.put_line('Sales for Prod 5 : ' || cnt);
8 select sum(sale_qty) into cnt
9 from very_large_fact_table
10 where prod_id in (4,11);
11 dbms_output.put_line('Sales for Prods 4,11 : ' || cnt);
12 select sum(sale_qty) into cnt
13 from very_large_fact_table
14 where prod_id = 10
15 and cust_id = 10;
16 dbms_output.put_line('Sales for Prod 10 to Cust 10 : ' || cnt);
17 end;
18 /
Sales for Prod 5 : 4012173200
Sales for Prods 4,11 : 8004026720
Sales for Prod 10 to Cust 10 : 2017249360

PL/SQL procedure successfully completed.

Elapsed: 00:02:31.73
SQL> exec dbms_workload_repository.create_snapshot;

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.43
SQL>


So, the entire block completed in 151.73seconds.

What does the AWR report show ?

SQL ordered by Elapsed Time              DB/Inst: ORCL/orcl  Snaps: 1325-1326

-> Resources reported for PL/SQL code includes the resources used by all SQL
statements called by the code.
-> % Total DB Time is the Elapsed Time of the SQL statement divided
into the Total Database Time multiplied by 100
-> %Total - Elapsed Time as a percentage of Total DB time
-> %CPU - CPU Time as a percentage of Elapsed Time
-> %IO - User I/O Time as a percentage of Elapsed Time
-> Captured SQL account for 94.2% of Total DB Time (s): 167
-> Captured PL/SQL account for 94.0% of Total DB Time (s): 167

Elapsed Elapsed Time
Time (s) Executions per Exec (s) %Total %CPU %IO SQL Id
---------------- -------------- ------------- ------ ------ ------ -------------
151.7 1 151.70 91.1 62.6 19.1 1dh6uf6hyr3us
Module: SQL*Plus
declare cnt number; begin select sum(sale_qty) into cnt from very_large_fact_t
able where prod_id = 5; dbms_output.put_line('Sales for Prod 5 : ' || cnt); sel
ect sum(sale_qty) into cnt from very_large_fact_table where prod_id in (4,11);
dbms_output.put_line('Sales for Prods 4,11 : ' || cnt); select sum(sale_qty) int

58.9 1 58.87 35.3 64.2 17.0 dwm8pq8awt96v
Module: SQL*Plus
SELECT SUM(SALE_QTY) FROM VERY_LARGE_FACT_TABLE WHERE PROD_ID IN (4,11)

46.8 1 46.79 28.1 61.6 26.1 cc8khw75jw0ha
Module: SQL*Plus
SELECT SUM(SALE_QTY) FROM VERY_LARGE_FACT_TABLE WHERE PROD_ID = 10 AND CUST_ID =
10

46.0 1 45.99 27.6 61.6 14.5 gscvdz32smtgr
Module: SQL*Plus
SELECT SUM(SALE_QTY) FROM VERY_LARGE_FACT_TABLE WHERE PROD_ID = 5


Isn't that a very interesting difference from yesterday's report ? Now we have the real execution time for each of the three SQLs which add up to the time for the PLSQL block.

Is there a discrepancy in the Time Model Statistics now ?
Time Model Statistics                    DB/Inst: ORCL/orcl  Snaps: 1325-1326

-> Total time in database user-calls (DB Time): 166.6s
-> Statistics including the word "background" measure background process
time, and so do not contribute to the DB time statistic
-> Ordered by % or DB time desc, Statistic name

Statistic Name Time (s) % of DB Time
------------------------------------------ ------------------ ------------
sql execute elapsed time 161.7 97.1
DB CPU 99.4 59.7
parse time elapsed 2.8 1.7
PL/SQL execution elapsed time 2.6 1.6
hard parse elapsed time 2.2 1.3
hard parse (sharing criteria) elapsed time 0.2 .1
PL/SQL compilation elapsed time 0.0 .0
repeated bind elapsed time 0.0 .0
sequence load elapsed time 0.0 .0


Top 5 Timed Foreground Events
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Avg
wait % DB
Event Waits Time(s) (ms) time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
DB CPU 99 59.7
direct path read 4,648 27 6 16.5 User I/O
db file sequential read 388 1 4 .9 User I/O
db file scattered read 18 1 36 .4 User I/O
enq: KO - fast object checkpoi 1 0 53 .0 Applicatio



What are your comments / observations ?
.
.
.



10 August, 2011

Singapore RACSIG meeting today

Today's Singapore RACSIG meeting went well. Ravi brought up a "problem" case in RAC Architecture which we discussed for some time. Next was a review of GoldenGate. Here, too, there were many questions and many answers !

We plan to have another workshop in September. Stay Tuned !

Piet de Visser was a guest at our meeting today. I am sure he was happy to have attended the meeting and participated in the discussions.

.
.
.

09 August, 2011

Reading an AWR -- 2

I have extracted this from an AWR report :
    

Elapsed Elapsed Time
Time (s) Executions per Exec (s) %Total %CPU %IO SQL Id
---------------- -------------- ------------- ------ ------ ------ -------------
277.8 1 277.85 97.6 14.9 59.4 8madvr3cvp929
Module: SQL*Plus
select count(*), sum(sale_qty), sum(discount_rate) from very_large_fact_table


CPU CPU per Elapsed
Time (s) Executions Exec (s) %Total Time (s) %CPU %IO SQL Id
---------- ------------ ---------- ------ ---------- ------ ------ -------------
41.5 1 41.49 91.4 277.8 14.9 59.4 8madvr3cvp929
Module: SQL*Plus
select count(*), sum(sale_qty), sum(discount_rate) from very_large_fact_table


User I/O UIO per Elapsed
Time (s) Executions Exec (s) %Total Time (s) %CPU %IO SQL Id
---------- ------------ ---------- ------ ---------- ------ ------ -------------
164.9 1 164.94 99.4 277.8 14.9 59.4 8madvr3cvp929
Module: SQL*Plus
select count(*), sum(sale_qty), sum(discount_rate) from very_large_fact_table


Buffer Gets Elapsed
Gets Executions per Exec %Total Time (s) %CPU %IO SQL Id
----------- ----------- ------------ ------ ---------- ------ ------ -----------
530,607 1 530,607.0 98.0 277.8 14.9 59.4 8madvr3cvp9
Module: SQL*Plus
select count(*), sum(sale_qty), sum(discount_rate) from very_large_fact_table


Physical Reads Elapsed
Reads Executions per Exec %Total Time (s) %CPU %IO SQL Id
----------- ----------- ---------- ------ ---------- ------ ------ -------------
530,326 1 530,326.0 99.9 277.8 14.9 59.4 8madvr3cvp929
Module: SQL*Plus
select count(*), sum(sale_qty), sum(discount_rate) from very_large_fact_table


How long did the query really take to execute ? Did it take approximately 278seconds ? We know that the query returns only 1 row so we don't have to factor SQL*Net round-trips or ARRAYSIZE. This is the output of the query :
SQL> select count(*), sum(sale_qty), sum(discount_rate)

2 from very_large_fact_table
3 /

COUNT(*) SUM(SALE_QTY) SUM(DISCOUNT_RATE)
---------- ------------- ------------------
80000000 1.0044E+11 1280636517

A query on 80million rows and about 530thousand blocks (slightly over 4GB). Did it take about 278seconds to execute ?

??
.
.

UPDATE : 13-Aug :

Here is the actual execution of the query (with elapsed time included) :
SQL> select count(*), sum(sale_qty), sum(discount_rate)

2 from very_large_fact_table
3 /

COUNT(*) SUM(SALE_QTY) SUM(DISCOUNT_RATE)
---------- ------------- ------------------
80000000 1.0044E+11 1280636517

Elapsed: 00:01:11.95
SQL>


So, the actual elapsed time was about 72seconds.
How did the query really execute ?
SQL> select * from table(dbms_xplan.display_cursor);


PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 8madvr3cvp929, child number 0
-------------------------------------
select count(*), sum(sale_qty), sum(discount_rate) from
very_large_fact_table

Plan hash value: 3187774711

-----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 40148 (100)| | | | |
| 1 | SORT AGGREGATE | | 1 | 26 | | | | | |
| 2 | PX COORDINATOR | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | 26 | | | Q1,00 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | 26 | | | Q1,00 | PCWP | |
| 5 | PX BLOCK ITERATOR | | 87M| 2176M| 40148 (1)| 00:08:02 | Q1,00 | PCWC | |
|* 6 | TABLE ACCESS FULL| VERY_LARGE_FACT_TABLE | 87M| 2176M| 40148 (1)| 00:08:02 | Q1,00 | PCWP | |
-----------------------------------------------------------------------------------------------------------------------------

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

6 - access(:Z>=:Z AND :Z<=:Z)
Note -----
- dynamic sampling used for this statement (level=5)


(The "dynamic sampling level 5 is new "automatic" behaviour in 11gR2. I will talk about that in a later blog post).
Also note that the elapsed time includes time for dynamic sampling -- which is executed as recursive SQL.

More information about the execution statistics :
SQL> select executions, disk_reads, buffer_gets, elapsed_time/1000, px_servers_executions

2 from v$sqlstats where sql_id = '&myqry'
3 /
Enter value for myqry: 8madvr3cvp929
old 2: from v$sqlstats where sql_id = '&myqry'
new 2: from v$sqlstats where sql_id = '8madvr3cvp929'

EXECUTIONS DISK_READS BUFFER_GETS ELAPSED_TIME/1000 PX_SERVERS_EXECUTIONS
---------- ---------- ----------- ----------------- ---------------------
1 530326 530607 277849.818 4


Conclusive evidence that the query actually used 4 PQ slaves. There is no PARALLEL Hint in the query but a Parallel DEGREE of 4 has been defined on the table.

The table is about 4GB in size :
SQL> select blocks from user_tables where table_name = 'VERY_LARGE_FACT_TABLE';


BLOCKS
----------


SQL> select blocks from user_segments where segment_name = 'VERY_LARGE_FACT_TABLE';

BLOCKS
----------
533504

SQL>
SQL> select table_name, degree from user_tables
2 where table_name = 'VERY_LARGE_FACT_TABLE';

TABLE_NAME DEGREE
------------------------------ ----------------------------------------
VERY_LARGE_FACT_TABLE 4

SQL>


So, the Buffer Gets and Physical Reads statistics (530thousand) reported by AWR are for the SQL but the Total Elapsed Time is the summation of time for the 4 PQ slaves !
Here are some statistics from the AWR :
Statistic                                     Total

-------------------------------- ------------------
consistent gets 539,810
consistent gets direct 530,138
physical read IO requests 4,747
physical read bytes 4,348,256,256
physical read total IO requests 5,008
physical read total bytes 4,352,385,024
physical read total multi block 4,190
physical reads 530,793
physical reads direct 530,138
table scan blocks gotten 530,228
table scan rows gotten 80,010,539
PX local messages recv'd 120
PX local messages sent 120

About 530thousand table blocks holding 80million rows were fetched using 4thousand multi block read calls to read 4GB. Those multiblock read calls were made by PX operators (what I call PQ slaves).

Therefore, although the AWR statistics about I/O by the SQL are correct, the information about elapsed time is wrong !

The next time you look at Elapsed Time for a query in an AWR report, you might want to confirm if the query did or did not use Parallel Slaves !

.
.
.

07 August, 2011

Oracle 11g RAC Essentials

PACKT Publishing had invited me to review the new 11g RAC book "Oracle 11g R1/R2 Real Application Clusters Essentials". This book, published in May 2011, is an update on the earlier book "Oracle 11g R1/R2 Real Application Clusters Handbook" published in June 2010.

The RAC Clusters Essentials book has been co-authored by Ben Prusinski and Syed Jaffer Hussain.

The reader needs to have familiarity with Oracle Database Administration (and, possibly, installation). However, knowledge of 10gRAC and ASM is not a pre-requisite. That is refreshing. If you already know 10g RAC or 11gR1 or 11gR1 RAC, you would appreciate the "New Features" listings for 11gR1 and 11gR2 that are included in each chapter. However, if you are beginning with, say, 9i knowledge, do not fear. This books brings you up to the right knowledge level.

I like the fact that the book begins with a review of the concept of High Availability. It doesn't jump into Oracle RAC but sets the stage by having the reader understand and appreciate downtime, the concept of 5 nines, business continuity and recoverability and the available options. Many DBAs and IT Management may not understand why Oracle seems to have different products (DataGuard, Streams, GoldenGate and RAC). Each of these is conceptually different and meets a different requirement. These are introduced here and covered again in Chapter 12. (The Scalability aspect of RAC comes in Chapter 6 on Workload Management). {However, I would disagree with the wording in a description of how Oracle handles transaction errors because it uses the phrase 'database recovery' to describe a rollback followed by a roll-forward. That section needs a review and rewrite to explain the differences between rollback of failed statements and transactions and database (system) recovery.}

Chapter 2 is an overview of RAC architecture. I applaud the inclusion of descriptions of OS and hardware details (although Storage Management applies to non-RAC implementations as well). Given the level of detail on storage protocols a differentiation between SAN and NAS would have been in order.
It also then goes on to provide brief, introductory, explanations to RAC processes.

Chapter 3 is a walk-through on the Installation of the Grid and the RAC database. It very well differentiates between 11gR1 and 11gR2 Grid installations. The authors have included explanations of what happens when the root scripts are executed and also how to execute post installation checks.
Curiously, the creation of the "grid" Linux account is a missing step. The authors have used "oracle" to install the Grid Infrastructure. The documentation allows for "oracle" to own the Grid Infrastructure (particularly in a Typical Installation) but my preference would have been to clearly use "grid" as a separate account for the G.I. Note that multicasting on the private interconnect are mandatory from 11.2.0.2. This may need getting the Network team to modify router configuration.

Chapter 4 is an overview of ASM. As I have pointed out earlier, if you have not administered an ASM install in 10g, this chapter helpfully begins with the "basics" -- even though it quickly goes into 11gR1/11gR2 commands. It also goes on to cover ACFS.

Chapter 5 is on managing and troubleshooting Clusterware. Beginning with descriptions of Clusterware processes, it goes on to cover the diagnostic commands. I am impressed by the content in this chapter.

Chapter 6 is focussed on the RAC database. DBAs not familiar with 11g would find the section on ADR useful as well. In my opinion, Services, TAF, FCF and FAN could have been a separate chapter so as to provide more details on these hard-to-understand features.

Chapter 7 is on Backup and Recovery. It also goes on to describe Instance Recovery in RAC. This I appreciate for it's inclusion.

Chapter 8 is on Performance Tuning. As with Backup and Recovery, it is also an introduction to 11g features for 10g non-RAC DBAs. I am happy to find that the manner in which 11g features are introduced is consistent throughout the book.

Chapter 9 on Clusterware Upgrades is for existing 10g/11g RAC DBAs.

Chapter 10, called "Real-world Scenarios" discusses adding and removing nodes and instances, relocating database instances and converting single-instance databases to RAC. DBAs need to practice these scenarios so this chapter well placed and provides adequate detail.

Chapter 11 is specific to EBusiness Suite implementations.

Chapter 12 called "High Availability" provides descriptions of Streams, GoldenGate and DataGuard.


All in all, I rate the book a 7.5 on 10. I am impressed by the content. The book is useful for all DBAs : From those running non-RAC 9i to those already running 11gRAC databases.
I would not hesitate recommending this book to anyone who wants "a good back on Oracle Clusterware and/or RAC". Even as we notice as in recent Oracle University Courses, Clusterware and RAC are separate topics. The book handles them appropriately.
I would have liked to see more coverage of RAC One Node and Oracle Restart as well -- possibly as a Chapter 13.

.
.
DISCLOSURE : Although PACKT Publishing sent me a soft-copy of the book for the review, I am not being paid to write or publish the review.
.
.