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.
Sometimes it is a rocket science.
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.
Subscribe to:
Posts (Atom)