SQL> set pages 9999
SQL> set lines 140
SQL> column sql_text format a80
SQL> select plan_hash_value, sql_text, sql_id, executions
2 from v$sql x
3 where plan_hash_value in (select plan_hash_value
4 from v$sql
5 where plan_hash_value != 0
6 and upper(trim(sql_text)) not like '%DUAL'
7 and parsing_schema_name not like 'SYS%'
8 group by plan_hash_value
9 having count(distinct sql_id) > 1)
10 order by x.plan_hash_value
11 /
PLAN_HASH_VALUE SQL_TEXT SQL_ID EXECUTIONS
--------------- -------------------------------------------------------------------------------- ------------- ----------
5300452 SELECT ATTRIBUTE,SCOPE,NUMERIC_VALUE,CHAR_VALUE,DATE_VALUE FROM SYSTEM.PRODUCT_P 1uzhrfn574t12 1
RIVS WHERE (UPPER('SQL*Plus') LIKE UPPER(PRODUCT)) AND (UPPER(USER) LIKE USERID)
5300452 SELECT CHAR_VALUE FROM SYSTEM.PRODUCT_PRIVS WHERE (UPPER('SQL*Plus') LIKE UPPE c4pc3jhzjcmc7 1
R(PRODUCT)) AND ((UPPER(USER) LIKE USERID) OR (USERID = 'PUBLIC')) AND (UPPE
R(ATTRIBUTE) = 'ROLES')
1023777010 select count(1) from scott.test x where x.object_id=5651 2ck17fz7g1wz0 1
1023777010 select count(1) from scott.test x where x.object_id=132 fpkm8ganm036t 1
1950795681 select count(1) from scott.test x where x.object_type='TABLE' 25gm9vc4cgh6r 1
1950795681 select count(1) from scott.test x where x.object_type='VIEW' dmpf0r95suww4 1
6 rows selected.
Of course, you can receive either false negative or false positive results with this method. On the one hand, for example, there are a lot of cases where queries can share plans, i. e. they have common "from" and "where" clauses but different "select" clauses. On the other hand, data distribution patterns can influence CBO`s decisions and lead it to create different plans.
2010/12/15
Quick search of queries without binds
Reading the Oracle Performance tuning guide put in my head an idea about how to find queries which are used literals instead of binds. We can use fact that such queries usually share their plans.
2010/10/08
Preparing a tablespace to become read only
Now I am reading the Oracle Database Administrator's Guide 11g Release 2 (11.2). And I have found some interesting detail here. That chapter says about preparing a tablespace to move in readonly state. And it proposes to take some action for doing blocks cleanout. But documentation suggests using something like this "select count(*) from table". It is not enough! Make sure Oracle really scans tables (not only some indexes) to give you row count.
I will use "test" table to demonstrate my point.
Furthermore, make sure Oracle scans all indexes for given table! They should be cleaned too.
Conclusion: I think, it is worth to use dbms_stats.gather_table_stats with cascade = > true and estimate_percent => 100 to achieve full blocks cleanout for each table in tablespace which is going to become readonly.
I will use "test" table to demonstrate my point.
SQL> select * from v$version where rownum=1;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
SQL> sho sga
Total System Global Area 209235968 bytes
Fixed Size 1373600 bytes
Variable Size 184552032 bytes
Database Buffers 16777216 bytes
Redo Buffers 6533120 bytes
SQL> sho parameter db_block_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_size integer 8192
SQL> select 16777216/8192 from dual;
16777216/8192
-------------
2048
SQL> create table test as select * from all_objects;
Table created.
SQL> desc test
Name Null? Type
---------------------------- -------- ----------------------------------------
...
OBJECT_NAME NOT NULL VARCHAR2(30)
OBJECT_ID NOT NULL NUMBER
...
SQL> create index idx_test_oid on test(object_id);
Index created.
SQL> exec dbms_stats.gather_table_stats(user,'test',cascade=>true)
PL/SQL procedure successfully completed.
SQL> select blocks from user_tables where table_name='TEST';
BLOCKS
----------
1051
SQL> select x.name,y.value
2 from v$statname x, v$mystat y
3 where x.statistic# = y.statistic#
4 and x.name like '%cleanout%'
5 and value > 0;
NAME VALUE
---------------------------------------------------------------- ----------
...
cleanouts only - consistent read gets 4
...
10 rows selected.
SQL> update test set last_ddl_time=sysdate;
71858 rows updated.
SQL> commit;
Commit complete.
SQL> select x.name,y.value...
NAME VALUE
---------------------------------------------------------------- ----------
cleanouts only - consistent read gets 4
SQL> select count(1) from test;
COUNT(1)
----------
71858
SQL> select x.name,y.value...
NAME VALUE
---------------------------------------------------------------- ----------
cleanouts only - consistent read gets 4
SQL> select /*+full(test)*/ count(1) from test;
COUNT(1)
----------
71858
SQL> select x.name,y.value...
NAME VALUE
---------------------------------------------------------------- ----------
cleanouts only - consistent read gets 963
Furthermore, make sure Oracle scans all indexes for given table! They should be cleaned too.
SQL> create index idx_test_oname on test(object_name);
Index created.
SQL> update test set object_name = substr(object_name,1,25);
71858 rows updated.
SQL> commit;
Commit complete.
SQL> select x.name,y.value...
NAME VALUE
---------------------------------------------------------------- ----------
cleanouts only - consistent read gets 2881
SQL> set autotrace on explain
SQL> select /*+index(test idx_test_oname)*/ count(object_name) from test;
COUNT(OBJECT_NAME)
------------------
71858
Execution Plan
----------------------------------------------------------
Plan hash value: 1651596932
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 358 (1)| 00:00:05 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FULL SCAN| IDX_TEST_ONAME | 71858 | 358 (1)| 00:00:05 |
---------------------------------------------------------------------------
SQL> select x.name,y.value...
NAME VALUE
---------------------------------------------------------------- ----------
cleanouts only - consistent read gets 3553
SQL> select /*+full(test)*/ count(*) from test;
COUNT(*)
----------
71858
Execution Plan
----------------------------------------------------------
Plan hash value: 1950795681
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 287 (1)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| TEST | 71858 | 287 (1)| 00:00:04 |
-------------------------------------------------------------------
SQL> select x.name,y.value...
NAME VALUE
---------------------------------------------------------------- ----------
cleanouts only - consistent read gets 4578Conclusion: I think, it is worth to use dbms_stats.gather_table_stats with cascade = > true and estimate_percent => 100 to achieve full blocks cleanout for each table in tablespace which is going to become readonly.
2010/05/31
DML through indexes
Recently I have been rereading Effective Oracle by Design. And I realized something new for myself.
Inter alia I reread "Modification DML from Start to Finish" section:
In general, these statement have a query component...
In general, a modification statement will find data it needs to update using the consistent-read mechanism described in the previous section. Then it will get that information again in current (as of right now) mode in order to modify it.
What does "find data" mean?...
And short time after that I ran into an interesting issue with deleting all rows from a table. I saw hint FULL within delete statement in code that was written by my collegue. I tried to remove that hint and CBO started to use INDEX ONLY access path. It was very unexpectedly for me, because I was used to think about delete statement in the same way as about select statement. I mean when I want select all rows from some table FULL TABLE ACCESS path is more efficient comparing with access path through any index. But as it turned out things must be rethink as for delete statement and update statement too.
So Thomas Kyte wrote, statement will find data it need during execution query component of DML. What does it exactly mean? What kind of data Oracle must find?
I think Oracle finds rowids of rows it will update or delete. And which way is most efficient to do this? If I delete all rows from a table any index with not null fields have got all necessary rowids! And Oracle will use it if I will not interfere with hints! And it is not all about using indexes in DML. I think the same positive effect can be observed when we need to update or delete a part (may be a huge part) of rows.
Note:
Inter alia I reread "Modification DML from Start to Finish" section:
In general, these statement have a query component...
In general, a modification statement will find data it needs to update using the consistent-read mechanism described in the previous section. Then it will get that information again in current (as of right now) mode in order to modify it.
What does "find data" mean?...
And short time after that I ran into an interesting issue with deleting all rows from a table. I saw hint FULL within delete statement in code that was written by my collegue. I tried to remove that hint and CBO started to use INDEX ONLY access path. It was very unexpectedly for me, because I was used to think about delete statement in the same way as about select statement. I mean when I want select all rows from some table FULL TABLE ACCESS path is more efficient comparing with access path through any index. But as it turned out things must be rethink as for delete statement and update statement too.
So Thomas Kyte wrote, statement will find data it need during execution query component of DML. What does it exactly mean? What kind of data Oracle must find?
I think Oracle finds rowids of rows it will update or delete. And which way is most efficient to do this? If I delete all rows from a table any index with not null fields have got all necessary rowids! And Oracle will use it if I will not interfere with hints! And it is not all about using indexes in DML. I think the same positive effect can be observed when we need to update or delete a part (may be a huge part) of rows.
SQL> create table t1 tablespace users
2 as select * from all_objects;
Table created.
SQL> describe t1
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER NOT NULL VARCHAR2(30)
OBJECT_NAME NOT NULL VARCHAR2(30)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NOT NULL NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED NOT NULL DATE
LAST_DDL_TIME NOT NULL DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
NAMESPACE NOT NULL NUMBER
EDITION_NAME VARCHAR2(30)
SQL> create index t1_idx1 on t1(object_id) tablespace users;
Index created.
SQL> exec dbms_stats.gather_table_stats(null,'t1',cascade=>true);
PL/SQL procedure successfully completed.
SQL> set autotrace on explain statistics
SQL> set timing on
SQL> delete from t1;
71312 rows deleted.
Elapsed: 00:00:04.12
Execution Plan
----------------------------------------------------------
Plan hash value: 3237753727
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 71312 | 348K| 160 (1)| 00:00:02 |
| 1 | DELETE | T1 | | | | |
| 2 | INDEX FULL SCAN| T1_IDX1 | 71312 | 348K| 160 (1)| 00:00:02 |
----------------------------------------------------------------------------
Statistics
----------------------------------------------------------
144 recursive calls
80831 db block gets
223 consistent gets
35 physical reads
27601692 redo size
673 bytes sent via SQL*Net to client
546 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
71312 rows processed
SQL> rollback;
Rollback complete.
Elapsed: 00:00:06.21
SQL> delete /*+FULL(t1)*/ from t1;
71312 rows deleted.
Elapsed: 00:00:15.67
Execution Plan
----------------------------------------------------------
Plan hash value: 775918519
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 71312 | 348K| 285 (1)| 00:00:04 |
| 1 | DELETE | T1 | | | | |
| 2 | TABLE ACCESS FULL| T1 | 71312 | 348K| 285 (1)| 00:00:04 |
---------------------------------------------------------------------------
Statistics
----------------------------------------------------------
198 recursive calls
224029 db block gets
1108 consistent gets
2 physical reads
40684972 redo size
673 bytes sent via SQL*Net to client
560 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
71312 rows processed
SQL> rollback;
Rollback complete.
Elapsed: 00:00:12.89
As you can see LIO (db block gets + consistent gets) is very different between first and second case. And the statement with index access is clear winner in this competition.Note:
SQL> select table_name,blocks,num_rows from user_tables where table_name='T1'; TABLE_NAME BLOCKS NUM_ROWS ------------------------------ ---------- ---------- T1 1041 71312 SQL> select index_name,leaf_blocks,num_rows from user_indexes where table_name='T1'; INDEX_NAME LEAF_BLOCKS NUM_ROWS ------------------------------ ----------- ---------- T1_IDX1 158 71312
2010/03/06
ORA-08006
I have observed a lack information about ORA-08006 in world wide web.
I am want to show yet another one case with situation that produces such error.
You can trap the ORA-08006 if your code meet following conditions:
Here is simple test-case:
It return 2 rows with flag “for_delete”=TRUE for ID=2. Consequently merge try to delete row with ID=2 two times.
I have discovered this kind of conditions in my production system. Rows had been duplicated in target table accidentally. And it would not occurred if primary or unique constraint existed on ID field on TARGET table.
I am want to show yet another one case with situation that produces such error.
You can trap the ORA-08006 if your code meet following conditions:
- you have got some kind of ETL engine and it work pattern is: insert row if it new (exists in source and does not exist in target), update row if it already exists and delete row in target if it does`t exist in source;
- you use merge statement with delete clause;
- you forget to use primary key constraint on target table with fields participating in "on clause" of merge statement;
Here is simple test-case:
SQL> create table source (id number, data varchar2(10)); Table created. SQL> create table target (id number, data varchar2(10)); Table created. SQL> insert into source values (1,'data1'); 1 row created. SQL> insert into target values (1,'data1'); 1 row created. SQL> insert into target values (2,'data2'); 1 row created. SQL> insert into target values (2,'data2'); --Make a duplicate row in target table 1 row created. SQL> commit; Commit complete. SQL> merge into target t 2 using ( 3 select 0 for_delete, source.* from source 4 union all 5 select 1 for_delete, target.* from target 6 where target.id not in (select id from source) 7 ) s 8 on (t.id=s.id) 9 when matched then update set 10 t.data=s.data 11 delete where s.for_delete=1 12 when not matched then insert (t.id,t.data) 13 values (s.id,s.data); merge into target t * ERROR at line 1: ORA-08006: specified row no longer existsSo, see carefully on “source” subquery
SQL> select 0 for_delete, source.* from source
2 union all
3 select 1 for_delete, target.* from target
4 where target.id not in (select id from source);
FOR_DELETE ID DATA
---------- ---------- ----------
0 1 data1
1 2 data2
1 2 data2
It return 2 rows with flag “for_delete”=TRUE for ID=2. Consequently merge try to delete row with ID=2 two times.
I have discovered this kind of conditions in my production system. Rows had been duplicated in target table accidentally. And it would not occurred if primary or unique constraint existed on ID field on TARGET table.
2009/12/02
Data generator
Yesterday I had fallen to new issue with bad performance of "reusable code". It was some piece of code seemed to be very elegant from programmer point of view.
Complicated select statement contained the some kind of "data generator" as inline view like following:
This subquery aimed to generate a list of month between two dates which are specified as bind variables.
As a result, the complex query was failed with "ORA-01652: unable to extend temp segment by string in tablespace string" approximately at 13 step of plan (see below)
The main cause of problem is very bad cardinality estimating for "data generator".
CBO will decide to assume the single row cardinality for such subquery without any hint. But it is wrong for most combinations of values which are in use.
My recommendation was to add the CARDINALITY hint with enough high value, 100 for example. It will cover most of cases at this time and in future, when the distance will rise between P_BEGIN_DATE and P_END_DATE.
Complicated select statement contained the some kind of "data generator" as inline view like following:
select add_months(:p_begin_date, level - 1) dt
from dual
connect by level < months_between(:p_end_date, :p_begin_date) + 2
This subquery aimed to generate a list of month between two dates which are specified as bind variables.
As a result, the complex query was failed with "ORA-01652: unable to extend temp segment by string in tablespace string" approximately at 13 step of plan (see below)
--------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------------
|* 1 | FILTER | | 1 | | 0 |00:00:00.01 | 0 | | | |
| 2 | NESTED LOOPS OUTER | | 1 | 1 | 0 |00:00:00.01 | 0 | | | |
| 3 | NESTED LOOPS | | 1 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 4 | HASH JOIN | | 1 | 1 | 0 |00:00:00.01 | 0 | 740K| 740K| |
| 5 | VIEW | | 1 | 1 | 0 |00:00:00.01 | 0 | | | |
| 6 | SORT GROUP BY | | 1 | 1 | 0 |00:00:00.01 | 0 | 73728 | 73728 | |
|* 7 | HASH JOIN OUTER | | 1 | 1 | 0 |00:00:00.01 | 0 | 742K| 742K| |
| 8 | VIEW | | 1 | 1 | 0 |00:00:00.01 | 0 | | | |
| 9 | NESTED LOOPS | | 1 | 1 | 0 |00:00:00.01 | 0 | | | |
| 10 | NESTED LOOPS | | 1 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 11 | HASH JOIN | | 1 | 1 | 0 |00:00:00.01 | 0 | 723K| 723K| |
|* 12 | HASH JOIN | | 1 | 1 | 0 |00:00:00.01 | 0 | 776K| 776K| |
|* 13 | HASH JOIN | | 1 | 524 | 301M|04:06:15.61 | 257 | 82M| 5277K| 143M (0)|
| 14 | MERGE JOIN CARTESIAN | | 1 | 39178 | 1781K|00:00:00.04 | 161 | | | |
| 15 | VIEW | | 1 | 1 | 48 |00:00:00.01 | 0 | | | |
| 16 | CONNECT BY WITHOUT FILTERING| | 1 | | 48 |00:00:00.01 | 0 | | | |
| 17 | FAST DUAL | | 1 | 1 | 1 |00:00:00.01 | 0 | | | |
| 18 | BUFFER SORT | | 48 | 39178 | 1781K|00:00:00.04 | 161 | 1753K| 641K| 1558K (0)|
| 19 | INDEX FAST FULL SCAN | XIF_TCCP_SET1 | 1 | 39178 | 37124 |00:00:00.01 | 161 | | | |
| 20 | TABLE ACCESS FULL | TMP_TAR_DATES_DAY | 1 | 41996 | 16274 |00:00:00.07 | 96 | | | |
| 21 | INLIST ITERATOR | | 0 | | 0 |00:00:00.01 | 0 | | | |
| 22 | TABLE ACCESS BY INDEX ROWID | TMP_CALC_PROWS | 0 | 92152 | 0 |00:00:00.01 | 0 | | | |
|* 23 | INDEX RANGE SCAN | XIF_TCPR_SET2 | 0 | 1750 | 0 |00:00:00.01 | 0 | | | |
|* 24 | TABLE ACCESS FULL | TMP_PTO_CALC | 0 | 19983 | 0 |00:00:00.01 | 0 | | | |
|* 25 | TABLE ACCESS BY INDEX ROWID | TMP_TAR_DATES | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 26 | INDEX RANGE SCAN | XIF_TTD_TMP_SET1 | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 27 | TABLE ACCESS BY INDEX ROWID | TMP_PRT_CALC_PARAMS | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 28 | INDEX RANGE SCAN | XIF_TPCP_SET1 | 0 | 449 | 0 |00:00:00.01 | 0 | | | |
|* 29 | TABLE ACCESS FULL | TMP_CALC_IDX_ROWS | 0 | 2166 | 0 |00:00:00.01 | 0 | | | |
|* 30 | TABLE ACCESS FULL | TMP_PTO_CALC | 0 | 19983 | 0 |00:00:00.01 | 0 | | | |
|* 31 | TABLE ACCESS BY INDEX ROWID | ERRD | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 32 | INDEX RANGE SCAN | XIF1661_ERRD | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 33 | TABLE ACCESS BY INDEX ROWID | MPMNT | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 34 | INDEX UNIQUE SCAN | XPK_PMONTH | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
--------------------------------------------------------------------------------------------------------------------------------------------------------
The main cause of problem is very bad cardinality estimating for "data generator".
SQL> explain plan for
2 select add_months(:p_begin_date, level - 1) dt
3 from dual
4 connect by level < months_between(:p_end_date, :p_begin_date) + 2;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------
Plan hash value: 1236776825
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |
|* 1 | CONNECT BY WITHOUT FILTERING| | | | |
| 2 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------
CBO will decide to assume the single row cardinality for such subquery without any hint. But it is wrong for most combinations of values which are in use.
My recommendation was to add the CARDINALITY hint with enough high value, 100 for example. It will cover most of cases at this time and in future, when the distance will rise between P_BEGIN_DATE and P_END_DATE.
2009/11/28
Reusable program code
I had finished the first stage of highly intensive performance tuning project yesterday. I had found the problem that is very illustrative for statement about "poor effectiveness of universal program code". In my case it was reusable code of "individual calculations" that have been reused in "mass calculations". This is mistaken itself. But another effect had took place in the run time. It was happened that "individual calculation" had started first and sql query plans were parsed with little volume of data. Notice: global temporary tables is used for most queries in this task.
For example, one of query plans was obtained with
Its run time statistics confirms the fact that last start was executed as "individual calculation"
But after I have examined overall run time statistics for this query with
it became clear that plan for "mass calculation" exactly match the plan for "individual calculation". In other words it was reused.
Notice, that last start of this query was failed with "ORA-01652: unable to extend temp segment by string in tablespace string", which had occurred approximately at step 2 of plan. Probably that`s why the run time statistics seems to be inconsistent. Especially between the second and the first steps of plan in "A-Rows" and "Buffers" columns.
For solving this problem I had recommended to create another copy of code of "individual calculations" and slightly change the text of sql queries, which was shared for both calculation approaches.
After implementing my recommendations, the new plan had appeared:
I emphasize, that it is only the first stage of optimization with very low cost of implementation. It was allowed to speed up "mass calculation" about three times and reduce the tension of overall project.
Further steps will be a) investigation of each query from top query consumers but more likely b) redesign of whole "mass calculation" algorithm.
For example, one of query plans was obtained with
select * from table(dbms_xplan.display_cursor('&1',null,'LAST ALLSTATS'));
Its run time statistics confirms the fact that last start was executed as "individual calculation"
Plan hash value: 645901738
-----------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------
|* 1 | HASH JOIN | | 1 | 1 | 172 |00:00:00.01 | 16 | 766K| 766K| |
| 2 | MERGE JOIN CARTESIAN | | 1 | 12 | 228 |00:00:00.01 | 10 | | | |
|* 3 | TABLE ACCESS BY INDEX ROWID| PORDER | 1 | 1 | 12 |00:00:00.01 | 7 | | | |
| 4 | NESTED LOOPS | | 1 | 1 | 14 |00:00:00.01 | 6 | | | |
|* 5 | TABLE ACCESS FULL | TMP_PNF_TO_CALC | 1 | 1 | 1 |00:00:00.01 | 3 | | | |
|* 6 | INDEX RANGE SCAN | IDX$$_0EFF0001 | 1 | 2 | 12 |00:00:00.01 | 3 | | | |
| 7 | BUFFER SORT | | 12 | 12 | 228 |00:00:00.01 | 3 | 2048 | 2048 | 2048 (0)|
| 8 | TABLE ACCESS FULL | TMP_TAR_DATES | 1 | 12 | 19 |00:00:00.01 | 3 | | | |
|* 9 | TABLE ACCESS FULL | TMP_CALC_P_ROWS | 1 | 178 | 287 |00:00:00.01 | 6 | | | |
-----------------------------------------------------------------------------------------------------------------------------------------
But after I have examined overall run time statistics for this query with
select * from table(dbms_xplan.display_cursor('&1',null,'ALL ALLSTATS'));
it became clear that plan for "mass calculation" exactly match the plan for "individual calculation". In other words it was reused.
Plan hash value: 645901738
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | O/1/M |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|* 1 | HASH JOIN | | 84 | 1 | 404 | 11 (10)| 00:00:01 | 5680 |00:00:00.14 | 1201 | 0 | 766K| 766K| |
| 2 | MERGE JOIN CARTESIAN | | 84 | 12 | 900 | 8 (0)| 00:00:01 | 191M|00:03:11.20 | 22759 | 282 | | | |
|* 3 | TABLE ACCESS BY INDEX ROWID| PORDER | 84 | 1 | 27 | 4 (0)| 00:00:01 | 8995 |00:00:03.14 | 22424 | 282 | | | |
| 4 | NESTED LOOPS | | 84 | 1 | 53 | 6 (0)| 00:00:01 | 19632 |00:00:03.04 | 22040 | 280 | | | |
|* 5 | TABLE ACCESS FULL | TMP_PNF_TO_CALC | 84 | 1 | 26 | 2 (0)| 00:00:01 | 10554 |00:00:00.03 | 308 | 0 | | | |
|* 6 | INDEX RANGE SCAN | IDX$$_0EFF0001 | 10554 | 2 | | 2 (0)| 00:00:01 | 8995 |00:00:02.96 | 21732 | 280 | | | |
| 7 | BUFFER SORT | | 8995 | 12 | 264 | 4 (0)| 00:00:01 | 191M|00:00:00.07 | 335 | 0 | 2048 | 2048 | 66/0/0|
| 8 | TABLE ACCESS FULL | TMP_TAR_DATES | 66 | 12 | 264 | 2 (0)| 00:00:01 | 23269 |00:00:00.01 | 335 | 0 | | | |
|* 9 | TABLE ACCESS FULL | TMP_CALC_P_ROWS | 65 | 178 | 58562 | 2 (0)| 00:00:01 | 12305 |00:00:00.03 | 313 | 0 | | | |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Notice, that last start of this query was failed with "ORA-01652: unable to extend temp segment by string in tablespace string", which had occurred approximately at step 2 of plan. Probably that`s why the run time statistics seems to be inconsistent. Especially between the second and the first steps of plan in "A-Rows" and "Buffers" columns.
For solving this problem I had recommended to create another copy of code of "individual calculations" and slightly change the text of sql queries, which was shared for both calculation approaches.
After implementing my recommendations, the new plan had appeared:
Plan hash value: 3979030168
------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------
| 1 | NESTED LOOPS | | 1 | 4 | 128K|00:00:05.82 | 447K| | | |
| 2 | NESTED LOOPS | | 1 | 1 | 128K|00:00:04.15 | 318K| | | |
|* 3 | HASH JOIN | | 1 | 8 | 9200 |00:00:00.13 | 338 | 1308K| 1162K| 1820K (0)|
|* 4 | TABLE ACCESS BY INDEX ROWID| PORDER | 1 | 8 | 9200 |00:00:00.03 | 276 | | | |
|* 5 | INDEX RANGE SCAN | XIF1660_PORDER | 1 | 39 | 9200 |00:00:00.01 | 47 | | | |
|* 6 | TABLE ACCESS FULL | TMP_PNF_TO_CALC | 1 | 11167 | 11167 |00:00:00.03 | 62 | | | |
|* 7 | TABLE ACCESS BY INDEX ROWID | TMP_CALC_P_ROWS | 9200 | 1 | 128K|00:00:03.88 | 318K| | | |
|* 8 | INDEX RANGE SCAN | XIF_TCPR_SET1 | 9200 | 26 | 302K|00:00:00.55 | 19333 | | | |
|* 9 | INDEX RANGE SCAN | XIF_TTD_TMP_SET1 | 128K| 3 | 128K|00:00:01.50 | 128K| | | |
------------------------------------------------------------------------------------------------------------------------------------------
I emphasize, that it is only the first stage of optimization with very low cost of implementation. It was allowed to speed up "mass calculation" about three times and reduce the tension of overall project.
Further steps will be a) investigation of each query from top query consumers but more likely b) redesign of whole "mass calculation" algorithm.
2009/11/19
Parameter File Search Order in Oracle RAC
It is still not documented or rather poorly documented thing about "Parameter File Search Order in Oracle RAC". Even documentation release 11.2 continue to assert the following:
If you start your RAC database using srvctl or it is autostarted by clusterware during system boot, you may discover that sometimes database will seem to be started from another server parameter file than that, which must be in use according to Oracle Documentation, especially when you migrated your database from non-RAC configuration to RAC on same hardware.
In fact, which spfile will be used in such circumstance is determined by configuration information stored in OCR, namely:
And if you start RAC database instance using sqlplus, than spfile will be used from places that specified in documentation.
Oracle Database searches for your parameter file in a particular
order depending on your platform.
On Linux and UNIX platforms, the search order is as follows:
1. $ORACLE_HOME/dbs/spfilesid.ora
2. $ORACLE_HOME/dbs/spfile.ora
3. $ORACLE_HOME/dbs/initsid.ora
If you start your RAC database using srvctl or it is autostarted by clusterware during system boot, you may discover that sometimes database will seem to be started from another server parameter file than that, which must be in use according to Oracle Documentation, especially when you migrated your database from non-RAC configuration to RAC on same hardware.
In fact, which spfile will be used in such circumstance is determined by configuration information stored in OCR, namely:
oracle@srv1:~> srvctl config database -d iadb -a
cntn1 iadb1 /opt/oracle/product/10.2/db_1
cntn3 iadb2 /opt/oracle/product/10.2/db_1
DB_NAME: iadb
ORACLE_HOME: /opt/oracle/product/10.2/db_1
SPFILE: +DATA1/iadb/spfileiadb.ora
DOMAIN: null
DB_ROLE: PRIMARY
START_OPTIONS: null
POLICY: AUTOMATIC
ENABLE FLAG: DB ENABLED
And if you start RAC database instance using sqlplus, than spfile will be used from places that specified in documentation.
Subscribe to:
Posts (Atom)