DBA Blogs

I'm now learning PL/SQL for develop Oracle ERP

Tom Kyte - Wed, 2016-08-10 16:46
Hi,Tom As a fresh graduate to be an programmer with Oracle ERP, I'm now learning PL/SQL. I've read lots of pages about it, and I've practiced how to use PL/SQL to create a report for Oracle ERP. But I've searched on the internet and cannot find ...
Categories: DBA Blogs

Query regarding join order in ANSI

Tom Kyte - Wed, 2016-08-10 16:46
I wrote below two queries, one written using ANSI syntax and other using conventional syntax. While writing these, I expected them to be logically equivalent and return same number of records (whatever that may be). When I actually ran these queries,...
Categories: DBA Blogs

After import/export rowdependecies is lost

Tom Kyte - Wed, 2016-08-10 16:46
Hello, We did dupm and load of our schema using impdp. And few months later we noticed that all tables that in original database where created with rowdependencies in new database are created without without rowdependencies. 1. Is this possib...
Categories: DBA Blogs

When does uncommited data rollback from data file?

Tom Kyte - Tue, 2016-08-09 22:26
Dear Team, DBWR write flush data from db buffer cache at the time ? Checkpoint occurs ? Dirty buffers reach threshold ? There are no free buffers ? Timeout occurs ? Tablespace OFFLINE ? Tablespace READ ONLY ? Table DROP or TRUNCATE ...
Categories: DBA Blogs

Create a database trigger (Dynamic) on a table

Tom Kyte - Tue, 2016-08-09 22:26
Hi Tom, I have a requirement where I have to create a table level trigger dynamically. Here are more details : A user will be provided an option to choose the columns in a table XYZ which will be tracked for any changes - inserting, updati...
Categories: DBA Blogs

excessive log generation

Tom Kyte - Tue, 2016-08-09 22:26
hi - we have a 11.2.0.4 database in production that we saw was generating too many logs even when the database is dormant. We initially thought it was due to rman backups but now we are seeing that if we issue one log switch it is causing too many lo...
Categories: DBA Blogs

SMTP ACL gets dropped ocassionally

Tom Kyte - Tue, 2016-08-09 22:26
Hi, I have a schema which uses the ACL like below to send mails.The ACL gets dropped ocassionally and application using teh schema is unable to send the mails.We need to manually add the ACL again and everything gets back to normal.Can anyone expe...
Categories: DBA Blogs

Query Mysql database from oracle

Tom Kyte - Tue, 2016-08-09 22:26
Hi!, I am trying to query a mysql database from oracle. I have configured everything and everything is working fine but when i query any table of mysql database from oracle, i dont get the columns having number data type. In hs file i have follwing ...
Categories: DBA Blogs

ORACLE Query to return First Row for a DataSet

Tom Kyte - Tue, 2016-08-09 22:26
I'm writing a query which would return first row of a set of grouped data. I tried using OVER PARTITION BY clause, but somehow I'm not getting the desired result : select row_number() OVER(PARTITION BY leafv , value_group , l1d ,l2d ,l3d ,l4d ...
Categories: DBA Blogs

Mutating table

Tom Kyte - Tue, 2016-08-09 22:26
Hi i was supposed to make script that will find data in table and based on these insert into same table. When i was doing this operation while using temporary table to prevent mutating table error(it did not worked and i know my before insert trigge...
Categories: DBA Blogs

Migrating from smallfile tablespace to bigfile

Tom Kyte - Tue, 2016-08-09 04:06
hi - we recently migrated our 11.2.0.4 database from a non rac to a rac system, so the tablespaces came as smallfile tablespaces. this is our platform database and a 24x7 oltp system. what is the best way to move them into bigfile tablespaces with mi...
Categories: DBA Blogs

Script to compare data in all tables in two different databases, if the table is present in both the databases.

Tom Kyte - Tue, 2016-08-09 04:06
Hi, I am looking for a stored procedure to compare the data in all the tables in two different databases. I have 2 databases DB1 and DB2. From DB1, a Dblink is created to access DB2. First step is - to find all the tables that exists in...
Categories: DBA Blogs

Restrict Access on Active DG Primary DB

Tom Kyte - Tue, 2016-08-09 04:06
In our environment we have a production server which handles all reads/writes and we have an active DG which we use to offload backups as well as read only reporting on. We have already established a mechanism to handle password resets when an end u...
Categories: 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

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs