Ghost Views ;)
Recently I was reported of a problem that some views were getting invalid regularly in the database. I used to compile it and on the next day i found that they were invalid again. Though there was no problem with the invalid views as they would be compiled when they would be used in a query.
I tried to find out the tables used in those views through the view dba_dependencies and whether any of the table used in the views was altered in the night which rendered my views invalid. I found that a batch would truncate one of the table and populate it again, but truncating does not alter the structure of the view so the dependent objects would not become invalid.
Later I found that before truncating the table the index (both unique and non unique ) were dropped and after the table was populated the indexes were created again. So there lied my problem.
Thanks to Saubhik for helping me out.
The dropping of unique index invalidated all objects dependent on that particular table, So dropping a normal index would not invalidate any dependent objects. Here is a demo of the problem.
I create a table from the all_objects table and created a unique index on the column object_id. I also create a force view referencing my table my_objects.
SQL> create table my_objects as select * from all_objects; SQL> create unique index uniq_indx_my_objects on my_objects(object_id); Index created. SQL> create or replace force view v_my_object(owner,object_name,object_id) as 2 select owner,object_name,object_id from my_objects; View created.
I checked the status and the dependency of the view.
SQL> select object_name,object_type,status from all_objects where object_name like '%V_MY_OBJECT%'; OBJECT_NAME OBJECT_TYPE STATUS ------------------------------ ------------------- ------- V_MY_OBJECT VIEW VALID SQL> select * from dba_dependencies where name ='V_MY_OBJECT'; OWNER NAME TYPE REFERENCED_OWNER REFERENCED_NAME ------------------------------ ------------------------------ ----------------- ------------------------------ --------------------- SCOTT V_MY_OBJECT VIEW SCOTT MY_OBJECTS
Next Step was to drop the index and look for the status of the view.
SQL> drop index uniq_indx_my_objects; Index dropped. SQL> select object_name,object_type,status from all_objects where object_name like '%V_MY_OBJECT%'; OBJECT_NAME OBJECT_TYPE STATUS ------------------------------ ------------------- ------- V_MY_OBJECT VIEW INVALID
Finally the index was recreated and the view was compiled.
SQL> create unique index uniq_indx_my_objects on my_objects(object_id); Index created. SQL> alter view V_MY_OBJECT compile; View altered. SQL> select object_name,object_type,status from all_objects where object_name like '%V_MY_OBJECT%'; OBJECT_NAME OBJECT_TYPE STATUS ------------------------------ ------------------- ------- V_MY_OBJECT VIEW VALID
So now every time my batches run I make sure the i run the compile all utility to validate all the invalid objects. 🙂
Parallel Query and Instances in Oracle
Each and every day of your life you learn something. I am sharing something which I learnt a few days back.
Some days back I was working on a query on a newly migrated database (from 8i to 10g).A end user was having a problem with a report and said that every time he ran the report ,it would run for hours and after waiting for hours, in frustration he would kill his session.
After working on the query i found that a number of parallel slave processes were created for the query. And to my surprise 56 parallel slaves were created for that single sql.The parallel query coordinator actually waits for the
execution servers running in parallel to finish and thus the query would wait for all the 56 parallel slaves to complete execution.
but why was the query creating parallel slaves ??? The degree for the tables and indexes used in the query was “1”.Then i came to know a new thing.One of the indexes which was being used by the query had a degree of “1” but the instances value was set to “default”.
I have tried to show the effect of instances value through a test case.I created a table my_objects by selecting rows from the all_objects view and an index on the column object_id.
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production
SQL> create table my_objects as select * from all_objects;
Table created.
SQL> create index indx_my_objects on my_objects(object_id);
Index created.
SQL>
SQL> select index_name,degree,instances from all_indexes where index_name='INDX_MY_OBJECTS';
INDEX_NAME DEGREE INSTANCES
------------------------------ ---------------------------------------- ----------------------------------------
INDX_MY_OBJECTS 1 1
SQL> select count(*) from my_objects where object_id>12;
COUNT(*)
----------
49910
Execution Plan
----------------------------------------------------------
Plan hash value: 1996840010
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 30 (7)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
|* 2 | INDEX FAST FULL SCAN| INDX_MY_OBJECTS | 48378 | 614K| 30 (7)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_ID">12)
SQL> alter index indx_my_objects parallel(degree 1 instances default);
Index altered.
SQL> select index_name,degree,instances from all_indexes where index_name='INDX_MY_OBJECTS';
INDEX_NAME DEGREE INSTANCES
------------------------------ ---------------------------------------- ----------------------------------------
INDX_MY_OBJECTS 1 DEFAULT
SQL> select count(*) from my_objects where object_id>12;
COUNT(*)
----------
49910
Execution Plan
----------------------------------------------------------
Plan hash value: 318682625
--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 30 (7)| 00:00:01 | | | |
| 1 | SORT AGGREGATE | | 1 | 13 | | | | | |
| 2 | PX COORDINATOR | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | 13 | | | Q1,00 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | 13 | | | Q1,00 | PCWP | |
| 5 | PX BLOCK ITERATOR | | 48378 | 614K| 30 (7)| 00:00:01 | Q1,00 | PCWC | |
|* 6 | INDEX FAST FULL SCAN| INDX_MY_OBJECTS | 48378 | 614K| 30 (7)| 00:00:01 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - filter("OBJECT_ID">12)
So after changing the value of instances of the index to default the query started using parallel slaves.So whether the query need to create parallel slaves or not is also determined by the value of instances of an index.
So after the instances value of the index was set to 1 the query stopped creating parallel salves and ran in pretty quick time 🙂
Ankit Goel's Blog 
leave a comment