DBA Blogs

Database performance problem

Tom Kyte - Fri, 2016-12-02 09:26
Hi team, I want to ask you some questions. 1. My office using oracle database. We have 9 schemas in one database. Each schemas having branches. Sometimes the database application running heavy so much. When I see the CPU usage of server database i...
Categories: DBA Blogs

Compare two tables

Tom Kyte - Thu, 2016-12-01 15:06
I want to compare two tables which have exactly same columns. Tables contains 200 columns each and have million of records in them. My query is I want to compare these two tables and find out which columns have different data and then I need to po...
Categories: DBA Blogs

Converting a GUID to specific number of characters.

Tom Kyte - Thu, 2016-12-01 15:06
Hi Experts, I have major experience in SQL Server and I am quite new to Oracle and trying to implement something . I need a help from you guys. I have converted a varchar value into a GUID which looks like this 7171D6557B34C0BF47F166092EE93E06...
Categories: DBA Blogs

Bad Performance of the PLSQL block when it is being executed by Execute Immediate.

Tom Kyte - Thu, 2016-12-01 15:06
I have 50 tables, each with millions of data rows in the Staging Table, that need to be validate by Business Rules. each record and each field have n number of validation, Example Validation Description- 1. (STAGING_FACT_ORG_ID IS NULL) To va...
Categories: DBA Blogs

About IN keyword in Oracle

Tom Kyte - Thu, 2016-12-01 15:06
Dear Tom, Can i ask question. If i have a array of varchar2 with name is arr_val in oracle. How can i use "where in arr_val" instead of "where in (select column_name from table_name)".And if it can be done. Can y explain which way is better? Thank...
Categories: DBA Blogs

Truncate Load gather stats

Tom Kyte - Thu, 2016-12-01 15:06
Hi, I have a DW activity. <b>Steps include</b>. Drop index on Table A Truncate Table A. Load table A with millions of records. Create indexes back on table A. Drop index on Table B Truncate Table B. Load table B with millions of records....
Categories: DBA Blogs

Partitioned Sequence

Tom Kyte - Thu, 2016-12-01 15:06
Is sequence partitioning available in Oracle Database version ? When I query dba_sequences, I see the column partition_count but I was not able to find any documentation related to it, is this feature available for production use ?
Categories: DBA Blogs

CQLSH – Unable to connect to any servers Tried connecting to [(‘’, 9042)

VitalSoftTech - Thu, 2016-12-01 09:30
When trying to connect to a node in the Cassandra Cluster using cqlsh, I get the "Unable to connect to any servers. Tried connecting to [('', 9042)" message. Learn how to modify the configuration so that the hostname is not required.
Categories: DBA Blogs

12.2 New Features -- 4 : AWR for Pluggable Database

Hemant K Chitale - Thu, 2016-12-01 03:29
12.2 now allows AWR Snapshots and Reports to be created at the PDB level.

Here I demonstrate a Manual Snapshot.  Although Automatic PDB AWR Snapshots are possible (with the AWR_PDB_AUTOFLUSH_ENABLED parameter) , they are disabled by default and Oracle recommends Manual Snapshots.

SQL> connect / as sysdba
SQL> alter session set container=PDB1;

Session altered.

SQL> exec dbms_workload_repository.create_snapshot();

PL/SQL procedure successfully completed.


I then proceed to create an AWR Report, still in the PDB1 container.

SQL> @?/rdbms/admin/awrrpt

Specify the Report Type
AWR reports can be generated in the following formats. Please enter the
name of the format at the prompt. Default value is 'html'.

'html' HTML format (default)
'text' Text format
'active-html' Includes Performance Hub active report

Enter value for report_type: text

Type Specified: text

Specify the location of AWR Data
AWR_ROOT - Use AWR data from root (default)
AWR_PDB - Use AWR data from PDB
Enter value for awr_location: AWR_PDB

Current Instance
DB Id DB Name Inst Num Instance Container Name
-------------- -------------- -------------- -------------- --------------

Root DB Id Container DB Id AWR DB Id
--------------- --------------- ---------------
947935822 3774315809 3774315809

Instances in this Workload Repository schema
DB Id Inst Num DB Name Instance Host
------------ ---------- --------- ---------- ------

Using 3774315809 for database Id
Using 1 for instance number

Specify the number of days of snapshots to choose from
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed. Pressing without
specifying a number lists all completed snapshots.

Enter value for num_days: 1

Listing the last day's Completed Snapshots
Instance DB Name Snap Id Snap Started Snap Level
------------ ------------ ---------- ------------------ ----------

HKCORCL HKCORCL 1 01 Dec 2016 08:48 1
2 01 Dec 2016 08:49 1
3 01 Dec 2016 08:52 1

Specify the Begin and End Snapshot Ids
Enter value for begin_snap: 1
Begin Snapshot Id specified: 1

Enter value for end_snap: 3
End Snapshot Id specified: 3

Specify the Report Name
The default report file name is awrrpt_1_1_3.txt. To use this name,
press to continue, otherwise enter an alternative.

Enter value for report_name:

Using the report name awrrpt_1_1_3.txt

Here's a look at the header of the AWR report.


DB Name DB Id Unique Name DB Role Edition Release RAC CDB
------------ ----------- ----------- ---------------- ------- ---------- --- ---

Instance Inst Num Startup Time
------------ -------- ---------------
HKCORCL 1 16-Nov-16 06:13

PDB Name PDB Id PDB DB Id Open Time
------------ ------ ---------- ---------------
PDB1 3 3774315809 25-Nov-16 14:11

Host Name Platform CPUs Cores Sockets Memory(GB)
---------------- -------------------------------- ---- ----- ------- ----------
HKCORCL.compute- Linux x86 64-bit 2 2 1 7.05

Snap Id Snap Time Sessions Curs/Sess
--------- ------------------- -------- ---------
Begin Snap: 1 01-Dec-16 08:48:46 0 4.0
End Snap: 3 01-Dec-16 08:52:08 1 12.0
Elapsed: 3.36 (mins)
DB Time: 0.29 (mins)

Load Profile Per Second Per Transaction Per Exec Per Call
~~~~~~~~~~~~~~~ --------------- --------------- --------- ---------
DB Time(s): 0.1 4.3 0.00 0.06
DB CPU(s): 0.1 2.9 0.00 0.04
Background CPU(s): 0.0 0.0 0.00 0.00
Redo size (bytes): 138,443.8 6,976,599.0
Logical read (blocks): 1,798.4 90,625.0
Block changes: 282.3 14,224.3
Physical read (blocks): 21.0 1,055.8
Physical write (blocks): 0.7 34.5
Read IO requests: 20.9 1,051.8
Write IO requests: 0.3 12.5
Read IO (MB): 0.2 8.3
Write IO (MB): 0.0 0.3
IM scan rows: 0.0 0.0
Session Logical Read IM: 0.0 0.0
User calls: 1.5 77.5
Parses (SQL): 17.9 904.0
Hard parses (SQL): 3.2 161.5
SQL Work Area (MB): 2.5 123.5
Logons: 0.0 1.0
Executes (SQL): 45.7 2,302.3
Rollbacks: 0.0 0.0
Transactions: 0.0

Top 10 Foreground Events by Total Wait Time
Total Wait Avg % DB Wait
Event Waits Time (sec) Wait time Class
------------------------------ ----------- ---------- --------- ------ --------
DB CPU 11.5 66.9
db file sequential read 3,758 2.5 667.74us 14.6 User I/O
direct path write 23 .7 29.08ms 3.9 User I/O
flashback log file sync 36 .6 16.98ms 3.6 User I/O
local write wait 12 .4 37.17ms 2.6 User I/O
acknowledge over PGA limit 9 .1 9.50ms .5 Schedule
control file sequential read 189 .1 293.42us .3 System I
PGA memory operation 3,687 0 11.02us .2 Other
db file scattered read 4 0 8.32ms .2 User I/O
log file sync 3 0 9.35ms .2 Commit

The Header identifies the PDB being reported on.Note that Snapshots 1 to 3 are local to the PDB and are not in the Root.  PDB Snapshots can be maintained (create or drop snapshot) in the same manner as CDB snapshots.  (Note : PDB AWR Snapshots are in the view AWR_PDB_SNAPSHOT,  not DBA_HIST_SNAPSHOT).

In contrast, this below is the Header for a CDB where Automatic Snapshots have meant Snap IDs are already at 379,380.  Thus, the CDB snapshots are different from the PDB snapshots.


DB Name DB Id Unique Name DB Role Edition Release RAC CDB
------------ ----------- ----------- ---------------- ------- ---------- --- ---

Instance Inst Num Startup Time
------------ -------- ---------------
HKCORCL 1 16-Nov-16 06:13

Host Name Platform CPUs Cores Sockets Memory(GB)
---------------- -------------------------------- ---- ----- ------- ----------
HKCORCL.compute- Linux x86 64-bit 2 2 1 7.05

Snap Id Snap Time Sessions Curs/Sess PDBs
--------- ------------------- -------- --------- -----
Begin Snap: 379 01-Dec-16 08:00:47 51 .6 2
End Snap: 380 01-Dec-16 09:00:09 62 .9 2
Elapsed: 59.36 (mins)
DB Time: 1.14 (mins)

Note how it doesn't identify a PDB.

You need to be explicitly connected to a PDB before awrrpt shows you the option to generate PDB-level AWR report.
Categories: DBA Blogs

Y2038 issue

Tom Kyte - Wed, 2016-11-30 20:46
Hello AskTom team, Would this be an issue with Oracle? https://en.wikipedia.org/wiki/Year_2038_problem Thanks!
Categories: DBA Blogs

Archivelog backups taking too long

Tom Kyte - Wed, 2016-11-30 20:46
I am expriencing slowness in archivelog backups. In the last two days we have seen long running archive log backups despite the archive log generation being very low (less than 1gb every day). On Nov 29th it ran for 11 hours (4 am - 3 pm). Even lvl 0...
Categories: DBA Blogs

Compare varchar column with number

Tom Kyte - Wed, 2016-11-30 20:46
Hi Team, I need to update values for a column in my table to null, which are not numeric & greater than 999. Here is sample table - create table checknum ( col1 varchar2(10), col2 varchar2(10) ); Insert into CHECKSUM (COL1,COL2) valu...
Categories: DBA Blogs

PL SQL Functions that work with Read-Only Access

Tom Kyte - Wed, 2016-11-30 20:46
Tom! I've enjoyed your point of view and the clarity of your interactions for a couple of years now. I?ve been using SQL Developer off and on for years but I just recently started running into more complicated needs and I?m hoping you can point...
Categories: DBA Blogs

Global Temporary table use to avoid execution of same SQL's twice

Tom Kyte - Wed, 2016-11-30 20:46
Hi, I have one procedure which has two OUT ref cursors. First REF CURSOR returns below SQL output - SELECT * from tab1 , tab2 , tab3 , tab4 , tab5 WHERE <<certain join conditions>> AND value IN (SELECT VALUE from...
Categories: DBA Blogs

Oracle Subquery Bug?

Tom Kyte - Wed, 2016-11-30 20:46
Hi all, per random I came to this problem. My PL/SQL-Procedure was parsed without any ERROR, whereby the column "tb_id" itself did not exist in "suh_tb_test_1". (If I use an alias in the subquery then the ERROR is thrown!) Here the (strong s...
Categories: DBA Blogs

Delphix Users Panel Webinar

Bobby Durrett's DBA Blog - Wed, 2016-11-30 16:19

Delphix is sponsoring a user webinar on Wednesday from 10 to 11 am California time. I and a couple of other technical Delphix users will take part in a panel discussion. The webinar will give you a good chance to hear about other users’ experience with Delphix and a chance to ask questions. Like any good user meeting it will not be a non-technical sales pitch but it will instead focus on users sharing helpful technical information with other Delphix users.

Noted Oracle expert and Delphix employee Kellyn Pot’Vin-Gorman will also be there facilitating the discussion.

Here is the link to sign up for the free webinar: https://www.delphix.com/resources/webinar/delphix-users-panel

Be there or be square! &#x1f642;


Categories: DBA Blogs

Database Resource Manager, v$active_session_history p1text=location p2text=consumer group id

Tom Kyte - Wed, 2016-11-30 02:46
Hi Tom, Wish you all the best. 1] I have a 12c database 2] Not created as a CDB SQL> select cdb from v$database; CDB --- NO 3] No resource manager is configured SQL> SHOW PARAMETER RESOURCE_MANAGER_PLAN NAME ...
Categories: DBA Blogs

Combining WITH clauses

Tom Kyte - Wed, 2016-11-30 02:46
I've tried to combine an SQL defined in a WITH clause with an inline PL/SQL in 12c. <code>with function is_number(p_str varchar2) return number is l_number number; begin l_number := to_number(p_str); return l_number; exception when v...
Categories: DBA Blogs


Tom Kyte - Wed, 2016-11-30 02:46
I have several different applications using multiple schemas in a single database which used VPD and an after logon trigger to establish initial VPD driving context variables. A second db call, secmgr.my_appl_env spec listed below, is required by al...
Categories: DBA Blogs

Fine-grained Access Control (FGAC) vs. Row Level Security (RLS) vs. Virtual Private Database (VPD) vs. Oracle Label Security (OLS)

Tom Kyte - Wed, 2016-11-30 02:46
Good Evening, Throughout my career, I heard about Fine-grained Access Control (FGAC), Row Level Security (RLS), Virtual Private/Policy Database (VPD) and Oracle Label Security (OLS). These all sound very similar. I've only heard of OLS since it ...
Categories: DBA Blogs


Subscribe to Oracle FAQ aggregator - DBA Blogs