DBA Blogs

Number of Execution per snapshot too high for rman sql

Tom Kyte - Tue, 2016-08-09 04:06
Hi Team, I have been seeing many executions of below query in our database. begin sys.dbms_backup_restore.createRmanOutputRow( l0row_id => :l0row_id, l0row_stamp => :l0row_stamp, row_id => :row_id, row_stamp =>:row_stamp, txt=> :txt, sameline ...
Categories: DBA Blogs

Trigger to allow insertion only on Sunday

Tom Kyte - Tue, 2016-08-09 04:06
I have employee table. I want to create a trigger that will not allow insertion in the table on sunday.</b> Tell me the program please.. Thank you.
Categories: DBA Blogs

JSON from Relational Data

Tom Kyte - Tue, 2016-08-09 04:06
With all the new JSON Features, is there a way to take queries over relational data (ie. normal Oracle tables) and on the fly generate JSON objects? More and more vendors are using REST based apis that process JSON (a key one for us is Oracle Sales ...
Categories: DBA Blogs

Index Competition in #Oracle 12c

The Oracle Instructor - Tue, 2016-08-09 02:39

Suppose you want to find out which type of index is best for performance with your workload. Why not set up a competition and let the optimizer decide? The playground:

ADAM@pdb1 > select max(amount_sold) from sales where channel_id=9;

MAX(AMOUNT_SOLD)
----------------
            5000

ADAM@pdb1 > @lastplan

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
SQL_ID  3hrvrf1r6kn8s, child number 0
-------------------------------------
select max(amount_sold) from sales where channel_id=9

Plan hash value: 3593230073

----------------------------------------------------------------------------------------------
| Id  | Operation                            | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |       |       |       |     4 (100)|          |
|   1 |  SORT AGGREGATE                      |       |     1 |     6 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| SALES |     1 |     6 |     4   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN                  | BSTAR |     1 |       |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

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

   3 - access("CHANNEL_ID"=9)


20 rows selected.

There is a standard B*tree index on the column CHANNEL_ID that speeds up the SELECT above. I think a bitmap index would be better:

ADAM@pdb1 > create bitmap index bmap on sales(channel_id) invisible nologging;

Index created.

ADAM@pdb1 > alter index bstar invisible;

Index altered.

ADAM@pdb1 > alter index bmap visible;

Index altered.

ADAM@pdb1 > select max(amount_sold) from sales where channel_id=9;

MAX(AMOUNT_SOLD)
----------------
            5000

ADAM@pdb1 > @lastplan

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
select max(amount_sold) from sales where channel_id=9

Plan hash value: 2178022915

----------------------------------------------------------------------------------------------
| Id  | Operation                            | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |       |       |       |     3 (100)|          |
|   1 |  SORT AGGREGATE                      |       |     1 |     6 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| SALES |     1 |     6 |     3   (0)| 00:00:01 |
|   3 |    BITMAP CONVERSION TO ROWIDS       |       |       |       |            |          |
|*  4 |     BITMAP INDEX SINGLE VALUE        | BMAP  |       |       |            |          |
----------------------------------------------------------------------------------------------

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

   4 - access("CHANNEL_ID"=9)


21 rows selected.

With this 12c New Feature (two indexes on the same column), I got a smooth transition to the new index type. But this left no choice to the optimizer. What about this?

ADAM@pdb1 > alter index bmap invisible;

Index altered.

ADAM@pdb1 > alter session set optimizer_use_invisible_indexes=true;

Now both indexes are invisible and the optimizer may choose any of them. Turns out that it likes the bitmap index better here. Instead of watching the execution plans, V$SEGMENT_STATISTICS can also be used to find out:

ADAM@pdb1 > select object_name,statistic_name,value
            from v$segment_statistics
            where object_name in ('BSTAR','BMAP')
            and statistic_name in ('physical reads','logical reads');

OBJECT STATISTIC_NAME                      VALUE
------ ------------------------------ ----------
BSTAR  logical reads                       22800
BSTAR  physical reads                       6212
BMAP   logical reads                        1696
BMAP   physical reads                          0

The numbers of BSTAR remain static while BMAP numbers increase. You may also monitor that with DBA_HIST_SEG_STAT across AWR snapshots. Now isn’t that cool?:-)
Couple of things to be aware of here:
Watch out for more than just physical/logical reads – bitmap indexes may cause a locking problem in an OLTP environment.
Don’t keep the two indexes invisible forever – after you saw which one performs better, drop the other one. Invisible indexes need to be maintained upon DML and therefore slow it down.


Tagged: 12c New Features, Performance Tuning
Categories: DBA Blogs

Performance imact of adding 200+ columns to a table

Tom Kyte - Mon, 2016-08-08 09:46
Hi Team, I have doubt on number of columns on a table. currently we have 66 columns in a table and we want to add extra 204 columns in that table. My question is it will impact the DB performance and lead to row chaining or not? The DB block si...
Categories: DBA Blogs

SQL query sometimes taking longer (most of the time working good)

Tom Kyte - Mon, 2016-08-08 09:46
This is the duplicate (unable to edit the old) question where Connor Sir has asked to add code tags but as I was not aware of code tags. Here it is... This is to ask some suggestion about a query which is infrequently taking minutes->hours time...
Categories: DBA Blogs

Why Scalar sub query is reducing my query cost to 50%

Tom Kyte - Mon, 2016-08-08 09:46
Hi Tom, Please have a look: Below are the 2 queries with the same result set but differing in the cost. When i used scalar sub query instead of inner join its cost is low when compared with the other. Could you please explain. Please u...
Categories: DBA Blogs

Getting data from Multiple Users

Tom Kyte - Mon, 2016-08-08 09:46
Hi Tom, Please find the below problem statement and kindly provide the solution. Problem Statement: I have a schema and that schema contains a table T1. T1 table contains 2 columns i.e. UserId and CityId. T1 ------------- USERID CITYID -...
Categories: DBA Blogs

RMAN restore into a new database

Tom Kyte - Mon, 2016-08-08 09:46
Hi.. 3 days before I took Level 0 and Level 1 backup from my database( name:sample) by using RMAN .Yesterday my target database was crashed.So I created another new database(sample1). Now I want to use those backup files and restore that all data in...
Categories: DBA Blogs

Lost control files

Tom Kyte - Sun, 2016-08-07 15:26
I have couple of questions regarding controlfiles 1.I am connected through catalog and lost all the control files of database how can i recover the database and get all control files back please suggest 2.If in lost of all control files will I be a...
Categories: DBA Blogs

Updating / Resetting columns

Tom Kyte - Sun, 2016-08-07 15:26
Hey Guys, I have a process where I need to update one column with the value of another if the 1st column is null, when I transfer the value I need to set the value of the second column to null, I am doing this in a single update statement, see exa...
Categories: DBA Blogs

MVIEW Dependencies

Tom Kyte - Sun, 2016-08-07 15:26
Where are the MVIEW dependencies stored? SQL> create view y1 as select 1 as y1 from dual; View created. <code>SQL> create materialized view y2 as select y1 as y2 from y1; Materialized view created. SQL> create view y3 as select y2 as y...
Categories: DBA Blogs

Selecting rank wise records

Tom Kyte - Sun, 2016-08-07 15:26
Hi Tom, Its been such a long time to visit here and resolve the doubts but this is the first time I am asking. There is an Employee table with Name, Department, Salary and Date of Joining in it. I need to write a query which will give the outpu...
Categories: DBA Blogs

Oracle Text CONTAINS with NULL input string

Tom Kyte - Sun, 2016-08-07 15:26
Hi Tom, I am on Oracle 11gR2. I have a table where I have created a text index. I have written a query as below: SELECT * FROM my_transactions t WHERE 1=1 AND CONTAINS (t.search_transactions_flag, '%'||:str||'%') > 0; It prompt...
Categories: DBA Blogs

out-of-place refresh of a materialized view

Tom Kyte - Sun, 2016-08-07 15:26
I am researching how to improve the availability of data in an MV. The doc says that while doing a complete refresh of MV using the out-of-place option that the data in the mv is still available, which I find is true. But it also says the mv is av...
Categories: DBA Blogs

Oracle

Tom Kyte - Sun, 2016-08-07 15:26
TABLEA ****** C1 C2 C3 (COLS) * - - - * - * - * - * * I have 6(*) in the above table? But when i select my table i get the count 6 result how? sir? From my side i have done like select count(c1) from tb...
Categories: DBA Blogs

plsql trigger

Tom Kyte - Sun, 2016-08-07 15:26
create or replace trigger foremp before insert on emp declare day char(10); begin if (to_char(sysdate,day) in ('sunday')) then raise_application_error(-20300,'not allowed on sunday'); end if; end; / I created this trigger t...
Categories: DBA Blogs

Are we really create table aumatically as per our requirement

Tom Kyte - Sun, 2016-08-07 15:26
Hi, I want to create a table automatically as per based of various column data types and various number of column. Please ! dont cconsider the constraint. for example: table name :table1 required output is : DDl structure of crea...
Categories: DBA Blogs

PLSQL syntax

Tom Kyte - Sun, 2016-08-07 15:26
VARIABLE NAME CANNOT START WITH A) NUMBER B) SPECIAL CHARACTER C) CHARACTER D) ALL OF THE ABOVE I KNOW THAT WE CANNOT START A VARIABLE USING NUMBER ALSO SPECIAL CHARACTERS EXCLUDING(&,#,_). THIS IS A QUESTION I GOT FOR AN EXAM AND I SELECTED...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs