Feed aggregator

How to detect if insert transactions in oracle db are really slow?

Tom Kyte - 10 hours 13 min ago
At work, I have an Oracle DB (11g) in which I want to detect if there's slow performance while inserting data. Here's the situation: Some production devices send data results from tests to Server A, this server is a important server and it replica...
Categories: DBA Blogs

How i can optimize this operation DELETE if the values ares setted in codehard.

Tom Kyte - 10 hours 13 min ago
Hi, I'm a bit new to the development of plsql. I would like to know how I could optimize the delete operation with a forall if my query is the following: DELETE FROM SCH.TA_DELETE WHERE FIACUM < 1 AND FIPAIS = 1 AND F...
Categories: DBA Blogs

problem of inserting a long string of characters

Tom Kyte - 10 hours 13 min ago
Hello Team , I'm trying to insert into a table " TEST COM " the result of selecting rows of another table. I used the wm_concat function . /**********/ insert into COMMENTAIRE_TEST (SELECT wm_concat((DBMS_LOB.SUBSTR(COM_TEXTE,400...
Categories: DBA Blogs

Use RESULT_CACHE in subqueries

Tom Kyte - 10 hours 13 min ago
Dear Tom, I am thinking to use the new feature "RESULT_CACHE" to optimize some search queries for my paginated pages. So far, for a search page I have : 1.) a count query and 2.) the query that returns a page from the result set Both 1 an...
Categories: DBA Blogs

database links

Tom Kyte - 10 hours 13 min ago
how can i create database links to access remote databases. please tell me the procedure of creating database links.
Categories: DBA Blogs

How to avoid repeated function call for multiple columns' values.

Tom Kyte - 10 hours 13 min ago
Hi I'm refactoring an old procedure that calls a function for determining whether passed in values consist of only characters allowed in the front end app on top of the database. The procedure has a cursor that gathers all records it needs to ...
Categories: DBA Blogs

Docker: Network configuration - none / brigde / hostname / dns entries

Dietrich Schroff - 15 hours 20 min ago
If you are starting your docker container you can add some network configuration details via command line.
Let's start with the easiest network setting:
docker run -it  --net none alpine /bin/ash
This setting starts the container without any connectivity to the network:
# ip addr show
1: lo: mtu 65536 qdisc noqueue state UNKNOWN qlen 1
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
The default is --net bridge:
docker run -it  --net bridge alpine /bin/ash
With this setting your network access is via a bridge of your host:
# ip addr show
1: lo: mtu 65536 qdisc noqueue state UNKNOWN qlen 1
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8 scope host lo
valid_lft forever preferred_lft forever
7: eth0@if8: mtu 1500 qdisc noqueue state UP
link/ether 02:42:ac:11:00:02 brd ff:ff:ff:ff:ff:ff
inet 172.17.0.2/16 scope global eth0
valid_lft forever preferred_lft forever
This is the docker0 interface on your docker server machine:
alpine:~# ip addr show
1: lo: mtu 65536 qdisc noqueue state UNKNOWN qlen 1
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8 scope host lo
valid_lft forever preferred_lft forever
inet6 ::1/128 scope host
valid_lft forever preferred_lft forever
2: eth0: mtu 1500 qdisc pfifo_fast state UP qlen 1000
link/ether 08:00:27:72:ae:ef brd ff:ff:ff:ff:ff:ff
inet 192.168.178.46/24 brd 192.168.178.255 scope global eth0
valid_lft forever preferred_lft forever
inet6 fe80::a00:27ff:fe72:aeef/64 scope link
valid_lft forever preferred_lft forever
3: docker0: mtu 1500 qdisc noqueue state DOWN
link/ether 02:42:ba:e9:4d:6a brd ff:ff:ff:ff:ff:ff
inet 172.17.0.1/16 scope global docker0
valid_lft forever preferred_lft forever
inet6 fe80::42:baff:fee9:4d6a/64 scope link
valid_lft forever preferred_lft forever
4: docker_gwbridge: mtu 1500 qdisc noqueue state DOWN
link/ether 02:42:62:f0:92:82 brd ff:ff:ff:ff:ff:ff
inet 172.18.0.1/16 scope global docker_gwbridge
valid_lft forever preferred_lft forever
Finally you can configure your hostname and manipulate dns entries:
# docker run -it  --net bridge  --hostname myhostname --add-host mygoogle.com:8.8.8.8  alpine /bin/ash
/ # hostname
myhostname
/ # nslookup mygoogle.com
nslookup: can't resolve '(null)': Name does not resolve

Name: mygoogle.com
Address 1: 8.8.8.8 mygoogle.com

Are Wireframes and Prototypes Different?

Nilesh Jethwa - Mon, 2018-06-18 23:08

Both wireframing and prototyping are two essential processes in web and app development. Their main purpose is to establish the basic functions of the project and to present the working design concept. It is mostly shown to clients, but they … Continue reading ?

Via: MockupTiger Wireframes

Custom JavaScript Client Code in Oracle Visual Builder

Andrejus Baranovski - Mon, 2018-06-18 16:17
Hey, this is my first post about VBCS, you should expect more posts in the future about this topic. Red Samurai decided to choose VBCS as our primary JavaScript development IDE in the cloud. We are going to use it for declarative JS development, similar as we use JDeveloper for ADF.

I was going through the custom JS client code functionality in VBCS and thought it would be good idea to describe how it works. There is good material available for the same topic from Oracle, I recommend to go through it - Variables, Modules, and Functions, OH MY! Custom Client Code in Visual Builder.

I have created simple UI with one input and one output field. Button calls custom JS method, where value from input field will be processed and returned to be displayed in disabled field:


Below I will describe how all parts are wired together. Across different parts of VBCS there is a lot of resemblance with the way how ADF development done - this helps to reuse ADF skills for VBCS.

VBCS allows to define variables on 3 levels:

1. Page - page scope
2. Flow - flow scope
3. Application - application scope

In my example I decided to go with page scope variables (defined in page called main-start) - first one is assigned with input field and second with output:


There is property inspector, it allows to assign expressions to UI fields. Below you can see first variable assigned to input field:


Second variable is assigned to output field:


Button is assigned with action chain call - in VBCS we can call action chains. In ADF we call action listener and code Java logic in the method, here action chain gives more flexibility, you will see this below in action chain implementation:


VBCS allows to switch to code view and check HTML structure built with JET components. This is useful when you want to adjust generated code by yourself or copy layout to external JET project:


There is JS tab, associated with each VBCS page. There we can find JS file, where custom code can be included. I have created basic custom function, just for a test purpose:


VBCS JS code editor offers extensive auto suggest functionality - great help during development:


In case of syntax issues - errors are reported in audit window:


There is separate tab for action chains, I have already one - called from button (see above):


Action chain editor view - along with diagram, we have various components available. This looks slightly similar to SOA/BPM extension in JDeveloper, isn't it? In this action chain, first of all we call custom action - custom JS method define above:


Input parameter for JS call is assigned from page variable (input component):


In the next step - assign variables logic is called, this helps to assign function return value to page variable, which is mapped with output UI field:


Function return value mapping with page variable:


Application can be tested with single click, our message is printed in the log:


I have exported VBCS application and uploaded to GitHub repository. Once you export from VBCS, can access and check generated code. Here is main page code:


In main-start-page.json we can see metadata definition. For example, there we can find button event mapping with action chain:


VBCS looks very promising to me and I think this might be declarative JS development future.

PL/SQL Procedure - Catching "ORA - 01013 - User Requested Cancel of Current Operation"

Tom Kyte - Mon, 2018-06-18 12:46
It may be a silly question but I am wondering if there is any way to catch this error "ORA-01013 - User requested cancel of current operation" in a PL/SQL procedure. The requirement that I have is to update a database record before exiting when t...
Categories: DBA Blogs

Ambiguous overloading when parameter is null

Tom Kyte - Mon, 2018-06-18 12:46
<code></code>I have a package with an overloaded function, wich accepts one parameter (INTEGER / VARCHAR2) and returns a booblean indicating if exists a register with the correspondient column having the same value as the parameter. In tests, w...
Categories: DBA Blogs

JDBC thin dirver vs OCI driver

Tom Kyte - Mon, 2018-06-18 12:46
Hi Tom, i had some discussions with other colleagues about which driver should / could be used for connecting to an oracle database. I searched the web but found no sufficient answer for me (only a pretty old question from this site https://askto...
Categories: DBA Blogs

Replace characters in string

Tom Kyte - Mon, 2018-06-18 12:46
What's the best way to replace every character in string on random character and every number on other random number. I think about best performance. Input: MatijZ34 Output: sWirpt77
Categories: DBA Blogs

SQL functions in control file when direct load

Tom Kyte - Mon, 2018-06-18 12:46
Hello Tom, I have 100 million records to load to a table. I am sqlldr to do so. I have been using the conventional load but is taking long. In the control file I have SQL functions to substring and other data manipulations. My question is, can I use...
Categories: DBA Blogs

How to find time taken by query at each stage while being processed by db

Tom Kyte - Mon, 2018-06-18 12:46
Hey, We have some queries that perform too badly during load test. As per DBA the explain looks good. I want to know if there is a way DBA can monitor the time taken by query at each stage like parsing, executing, returning rows, etc? Thanks!
Categories: DBA Blogs

After logon on database

Tom Kyte - Mon, 2018-06-18 12:46
<code>CREATE OR REPLACE TRIGGER LOG_T_LOGON AFTER LOGON ON DATABASE DECLARE osUser VARCHAR2(30); machine VARCHAR2(100); prog VARCHAR2(100); ip_user VARCHAR2(15); BEGIN SELECT OSUSER, MACHINE, PROGRAM, ora_client_ip_addres...
Categories: DBA Blogs

Generate a date range

Tom Kyte - Mon, 2018-06-18 12:46
Hi tom, I have one question in which suppose i take two date range '10-jun-2014' and '10-jun-2018' then i want the output like 10-jun-2014 to 10-jun-2015 10-jun-2015 to 10-jun-2016 10-jun-2016 to 10-jun-2017 10-jun-2017 to 10-jun-2018 Can...
Categories: DBA Blogs

SQLERRM:ORA-06531: Reference to uninitialized collection

Tom Kyte - Mon, 2018-06-18 12:46
<code>Hi I am facing this error. my script is like below. please suggest: / create table address_test( Addr_id number, addr_cus_id number, street_name varchar2(100), town varchar2(100), county varchar2(100), sub_county_state_province varchar2...
Categories: DBA Blogs

How to fix your own SQL plan in Oracle ?

Yann Neuhaus - Mon, 2018-06-18 10:23

There is time when you have build an SQL plan and then you want to fix it for all next executions of the query by your application.

In this post I show how fix a plan you have created by yourself.

First we need to identified the query

SQL> select /* INDEX_ACCESS */ line_item_id, product_id, unit_price, quantity from ORDER_ITEMS where order_id = 234098 ;

LINE_ITEM_ID PRODUCT_ID UNIT_PRICE   QUANTITY
------------ ---------- ---------- ----------
           1        414        851          5
           2        499        818          3

Execution Plan
----------------------------------------------------------
Plan hash value: 4159986352

-----------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |               |     3 |    57 |     5   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| ORDER_ITEMS   |     3 |    57 |     5   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | ITEM_ORDER_IX |     3 |       |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

In that example I’ll take an application query against the ORDER_ITEMS table. I find hat query too fast because it is using the index ITEM_ORDER_IX which is based on the primary key. So we are going to force that query to be executed by accessing the whole table without using any index. Here I’ll use the hint FULL to do the job.

SQL> select /* FULL_ACCESS */ /*+ full(ORDER_ITEMS) */ line_item_id, product_id, unit_price, quantity from ORDER_ITEMS where order_id = 234098 ;

LINE_ITEM_ID PRODUCT_ID UNIT_PRICE   QUANTITY
------------ ---------- ---------- ----------
           1        414        851          5
           2        499        818          3

Execution Plan
----------------------------------------------------------
Plan hash value: 456270211

---------------------------------------------------------------------------------
| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |             |     3 |    57 |  2022   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| ORDER_ITEMS |     3 |    57 |  2022   (1)| 00:00:01 |
---------------------------------------------------------------------------------

 

On both queries I added a comment to make it easier to retrieve information in the SQL views from Oracle. Now I can get the statistic about my queries:

SQL> @sql

SQL_ID          PLAN_HASH_VALUE SQL_PLAN_BASELINE              EXECUTIONS ELAPSED_TIME EXACT_MATCHING_SIGNATURE SQL_TEXT
-------------- ---------------- ------------------------------ ---------- ------------ ------------------------ --------------------------------------------------
8ms87fhrq01xh        4159986352                                        25         5199     18199500880047668241 select /* INDEX_ACCESS */ line_item_id, product_id
55x955b31npwq         456270211                                         3       155947     10822814485518112755 select /* FULL_ACCESS */ /*+ full(ORDER_ITEMS) */

 

Plan control

So my goal is to force the application query “8ms87fhrq01xh” to use the plan from my manual modified query “55x955b31npwq”. To do so, I’m going to use the  “SQL Plan Management” from Oracle which is embedded from the release 11 and can be used with the DBMS_SPM package.

First I need to load the plan from my application query into SPM baseline:

SQL> set serveroutput on
declare
  plans_loaded pls_integer ;
begin
  plans_loaded := dbms_spm.load_plans_from_cursor_cache( sql_id => '8ms87fhrq01xh' ) ;
  dbms_output.put_line( 'plans loaded: '||plans_loaded ) ;
end ;
/
plans loaded: 1

PL/SQL procedure successfully completed.

 

I have now a new cursor in the SQL view with the SQL_PLAN_BASELINE identifier:

SQL> @sql

SQL_ID          PLAN_HASH_VALUE SQL_PLAN_BASELINE              EXECUTIONS ELAPSED_TIME EXACT_MATCHING_SIGNATURE SQL_TEXT
-------------- ---------------- ------------------------------ ---------- ------------ ------------------------ --------------------------------------------------
8ms87fhrq01xh        4159986352                                        25         5199     18199500880047668241 select /* INDEX_ACCESS */ line_item_id, product_id
8ms87fhrq01xh        4159986352 SQL_PLAN_gt4cxn0aacz0j91520601          1        21703     18199500880047668241 select /* INDEX_ACCESS */ line_item_id, product_id
55x955b31npwq         456270211                                         3       155947     10822814485518112755 select /* FULL_ACCESS */ /*+ full(ORDER_ITEMS) */

 

I can now find the SPM content for my SQL:

SQL> @spm
Enter value for signature: 18199500880047668241
old   9: where signature = '&signature.'
new   9: where signature = '18199500880047668241'

            SIGNATURE SQL_HANDLE                     PLAN_NAME                      ENABL ACCEP FIXED
--------------------- ------------------------------ ------------------------------ ----- ----- -----
 18199500880047668241 SQL_fc919da014a67c11           SQL_PLAN_gt4cxn0aacz0j91520601 YES   YES   NO

 

What we need to do now is to inject into the SPM baseline the plan from my modified query. To do so, I need the SQL_HANDLE of my application query and the couple of SQL_ID+PLAN_HASH_VALUE of the modified query to inject its plan into the plan baseline of my application query:

SQL> set serveroutput on
declare
  plans_loaded pls_integer ;
begin
  plans_loaded := dbms_spm.load_plans_from_cursor_cache(
    sql_id => '55x955b31npwq',
    plan_hash_value => 456270211,
    sql_handle => 'SQL_fc919da014a67c11'
  ) ;
  dbms_output.put_line( 'plans loaded: '||plans_loaded ) ;
end ;
/
plans loaded: 1

PL/SQL procedure successfully completed.

 

Now, let’s seen what’s in the baseline of our application query:

SQL> @spm
Enter value for signature: 18199500880047668241
old   9: where signature = '&signature.'
new   9: where signature = '18199500880047668241'

            SIGNATURE SQL_HANDLE                     PLAN_NAME                      ENABL ACCEP FIXED
--------------------- ------------------------------ ------------------------------ ----- ----- -----
 18199500880047668241 SQL_fc919da014a67c11           SQL_PLAN_gt4cxn0aacz0j91520601 YES   YES   NO
 18199500880047668241 SQL_fc919da014a67c11           SQL_PLAN_gt4cxn0aacz0jf91228bb YES   YES   NO

 

A new plan called “SQL_PLAN_gt4cxn0aacz0jf91228bb” has been generated and I know want to be sure it is the only one that are goin gto be used. Thus we need to fix it:

SQL> set serveroutput on
declare
  plans_loaded pls_integer ;
begin
  plans_loaded := dbms_spm.alter_sql_plan_baseline(
    sql_handle => 'SQL_fc919da014a67c11',
plan_name => 'SQL_PLAN_gt4cxn0aacz0jf91228bb',
    attribute_name => 'fixed',
    attribute_value => 'YES'
  ) ;
  dbms_output.put_line( 'plans modified: '||plans_loaded ) ;
end ;
/
plans modified: 1

PL/SQL procedure successfully completed.

SQL> @spm
Enter value for signature: 18199500880047668241
old   9: where signature = '&signature.'
new   9: where signature = '18199500880047668241'

            SIGNATURE SQL_HANDLE                     PLAN_NAME                      ENABL ACCEP FIXED
--------------------- ------------------------------ ------------------------------ ----- ----- -----
 18199500880047668241 SQL_fc919da014a67c11           SQL_PLAN_gt4cxn0aacz0j91520601 YES   YES   NO
 18199500880047668241 SQL_fc919da014a67c11           SQL_PLAN_gt4cxn0aacz0jf91228bb YES   YES   YES

 

Then, my Full access plan is fixed and I can check if the index on the primary key is still in used:

SQL> select /* INDEX_ACCESS */ line_item_id, product_id, unit_price, quantity from ORDER_ITEMS where order_id = 234098

LINE_ITEM_ID PRODUCT_ID UNIT_PRICE   QUANTITY
------------ ---------- ---------- ----------
           1        414        851          5
           2        499        818          3

Execution Plan
----------------------------------------------------------
Plan hash value: 456270211

---------------------------------------------------------------------------------
| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |             |     3 |    57 |  2022   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| ORDER_ITEMS |     3 |    57 |  2022   (1)| 00:00:01 |
---------------------------------------------------------------------------------

 

I can monitor the plan usage from the SQL view to check if the application has is executing the query with the new plan:

SQL> @sql

SQL_ID          PLAN_HASH_VALUE SQL_PLAN_BASELINE              EXECUTIONS ELAPSED_TIME EXACT_MATCHING_SIGNATURE SQL_TEXT
-------------- ---------------- ------------------------------ ---------- ------------ ------------------------ --------------------------------------------------
8ms87fhrq01xh         456270211 SQL_PLAN_gt4cxn0aacz0jf91228bb          6       145687     18199500880047668241 select /* INDEX_ACCESS */ line_item_id, product_id
55x955b31npwq         456270211                                         3       155947     10822814485518112755 select /* FULL_ACCESS */ /*+ full(ORDER_ITEMS) */

 

As I see that both queries are currently using the same plan, I know that my application is now using the new plan with the full access to the ORDER_ITEMS table.

I hope this demonstration may help and please do not hesitate to contact us if you have any further questions or observations.

Scripts used in this article:

-- script sql.sql
set lines 180 pages 500
col sql_id format a14
col sql_plan_baseline format a30
col plan_hash_value format 999999999999999
col exact_matching_signature format 99999999999999999999
col sql_text format a50
select sql_id,
plan_hash_value,
sql_plan_baseline,
executions,
elapsed_time,
exact_matching_signature,
substr(sql_text,0,50) sql_text
from v$sql
where parsing_schema_name != 'SYS'
and sql_text like '%_ACCESS%' ;
-- script spm.sql
set lines 200
set pages 500
col signature format 99999999999999999999
col sql_handle format a30
col plan_name format a30
col enabled format a5
col accepted format a5
col fixed format a5
select
signature,
sql_handle,
plan_name,
enabled,
accepted,
fixed
from dba_sql_plan_baselines
where signature = '&signature.'
 

Cet article How to fix your own SQL plan in Oracle ? est apparu en premier sur Blog dbi services.

ADWC new OCI interface

Yann Neuhaus - Sun, 2018-06-17 14:51

A few things have changed about the Autonomous Data Warehouse Cloud service recently. And I’ve found the communication not so clear, so here is a short post about what I had to do to start the service again. The service has always been on the OCI data centers but was managed with the classic management interface. It has been recently migrated to the new interface:
CaptureADWCnew
Note that ADWC here is the name I’ve given for my service. It seems that the Autonomous Data Warehouse Cloud Service is now referred by the ADW acronym.

The service itself did not have any outage. The migration concerns only the interface. However, once the migration done, you cannot use the old interface. I went to the old interface with the URL I bookmarked, tried to start the service, and got a ‘last activity START_SERVICE failed’ error message without additional detail.
CaptureADWCfail

You can forget the old bookmark (such as https://psm-tenant.console.oraclecloud.com/psmui/faces/paasRunner.jspx?serviceType=ADWC) and you now have to use the new one (such as https://console.us-ashburn-1.oraclecloud.com/a/db/adws/ocid1.autonomousdwdatabase.oc1.iad.al-long-IAD-identifier)

So I logged to the console https://console.us-ashburn-1.oraclecloud.com (My service is in Ashburn-1 region). There I provided the tenant name (was the cloud account in the old interface) which can also be provided in the URL as https://console.us-ashburn-1.oraclecloud.com/?tenant=tenant. I selected oracleidentitycloudservice as the ‘identity provider’, my username and password and I am on the OCI console.

From the top-left menu, I can go to Autonomous Data Warehouse. I see nothing until I choose the compartement in the ‘list scope’. The ADWC service I had created when in the old interface is in the ‘tenant (root)’ compartment. Here I can start the service.

The previous PSM command line interface cannot be used anymore. We need to install the OCI CLI:

$ bash -c "$(curl -L https://raw.githubusercontent.com/oracle/oci-cli/master/scripts/install/install.sh)"

You will need the Tenancy ID (Tenancy OCID:ocid1.tenancy.oc1..aaaaaaaa… that you find on the bottom of each page in the console), the User ID (User OCID ocid1.user.oc1..aaaaaaa… that you find in the ‘users’ menu). All those ‘OCID’ are documented in https://docs.us-phoenix-1.oraclecloud.com/Content/API/Concepts/apisigningkey.htm

If you used the REST API, they change completely. You will have to post to something like:

/20160918/autonomousDataWarehouses/ocid1.autonomousdwdatabase.oc1.iad.abuwcljrb.../actions/start

where the OCID is the database one that cou can copy from the console.

 

Cet article ADWC new OCI interface est apparu en premier sur Blog dbi services.

Pages

Subscribe to Oracle FAQ aggregator