10.2.0.5 on Solaris 10.
We need to do load testing on our DB before going live. Mainly with 5 queries which are mainly used in our web interface to pull data from the database. For example. I need to send morethan 400-500 sessions to our database using this 5 queries (with different values) and to note down the time taken with every 50 number of users..
which are recommended Tool for load testing (for performance) on Oracle-J2EE, 3 Tier applications?
Is 'Oracle Application Test Suite' the best for such test where we can simulate numbers of users and their various actions?
Does it come with Oracle Database license or we have to buy it separately?
Oracle 10.2.0.4 I belive the following query is incorrect in my opinion. (there is an index on col but NULLS are ignored?
SELECT COUNT(*) from <TABLE>
where col in (null,'a','b','c') this works (no errors) and returns pretty quick. However I think the correct query would be SELECT COUNT(*) from <TABLE> where col IS NULL OR col in ('a','b','c') This one takes a long time. As I see it it does a table scan for NULL part and uses the index for the rest as the index cannot be used for NULL values. explanation on this, especially why Oracle accepts the first query "where col in (null,'a','b','c')" without any issue.
How can check that which query is taking how much load or time in execution on server in oracle.I want the soluation as like that, run the query and get the detail in desc order and identify that this query is required to tune or kill the session.
View 1 Replies View RelatedI have a big table in which we load about 37M recrods. We have informatica ETL which Loads the data in bulk Mode and creats index after completion. The data load takes about 1Hr and Index Creation takes about 1/2 hr. In total it takes about 90 to 95 Mnts.
Now I thought if Partition and Load paralley, It will improve perfromance. We did 4 partition and and each Partition about 9M records. The data load in Bulk mode is completing in 25 Mnts. Again When I am creating index over it, It is taking about 40 Mnts. and in Total Load time is 65 Mnts.
Is there way I can better performance to complete the load in 1/2 hr ?
i have tried it , but it load the data in text file , but i want to load this data in excel sheet in such a way that each column should be in different-2 cell of excel sheet.
SQL> spool on
SQL> spool 'd:data.text'
SQL> select * from scott.emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 300 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
SQL> spool off;
SQL>
I am on APEX 4.1.1, db 11g and I have a report which is quite slow to retrieve results from its query. The page has some filter fields (e.g. store code, dept code) in addition to the generic search field.
When the user lands on this page (from a menu page), the query runs before the page displays and this typically takes 1-2 mins without any filters yet set. The usual browser loading indicators are not obvious to the user and they might not be aware that it is working on the request.
I want it so that it will only run the query AFTER allowing the user to get to the page, let the user fill in some filter criteria and then click on the GO button. Therefore the query will run after the page is submitted. When you hit the GO button on the interactive report, a nice rotating apex_loader.gif appears, making it obvious to the user that it is working on the request.
I would prefer NOT to create 2 pages, one for user filter, and a second for the actual report as I have many reports/inquiries which fit this category and I would prefer NOT to have to create pairs of pages for each.
My task is to test a field in a certain database. We shall refer to the field as ship_to. There is an algorithm for which the field ship_to is populated and higher up in the algorithm, a variable we shall call X is created. The algorith states that I should assign the variable to ship_to unless X is = -1. If X is = -1, the algorithm continues with multiple joins and assignments. What would be the best way to go about coding this solution. There are 4 individual paths. I have only described the first path, but they are similiar in structure. I was thinking of using either Case's or decodes?The field ship_to is a number. I have already created a statement to test the sum of the Target, but now I need to test the entire algorithm to see if it too sums the Target.
View 1 Replies View RelatedI work as a Sys. Admin. for several RHEL 3.8 servers, most of them are clusters of 2 machines. All these servers are running Oracle 9.2.0.7 database. They are running fine on a separate filesystem. So everytime the system has to be formatted for some particular reason, there is no need to re-install the database.
I am trying to make some tests by running the same filesystem containing the oracle database in a fresh Red Hat Enterprise Linux 4.8 X86_64 Install. This task has become impossible, and I'm not quite sure why.
I installed all the compat- packages required for a fresh oracle 9 install in a RHEL4 machine, but at the time of stating the STARTUP sentence, it gives me the next error:
CMCLI ERROR: OpenCommPort: connect failed with error 2.
CMCLI ERROR: OpenCommPort: connect failed with error 2.
CMCLI ERROR: OpenCommPort: connect failed with error 2.
CMCLI ERROR: OpenCommPort: connect failed with error 2.
CMCLI ERROR: OpenCommPort: connect failed with error 2.
After this error (repeated) it says something like: Cannot start an already running database.... But if i stat a shutdown sentence, it says that the instance has not been initialized....
I don't know whether i have to re-install all the oracle software in order to make a clean install in the new kernel version or not, i tried to apply a patch, and the oracle installer recognized the installation i had.
I think it might be because the original system is configured to work as a cluster, and i'm running it on only a virtual machine.
I am trying to add a not null constraint on a collection for testing. It not allowed null values as below
pointers@ORCL> declare
2 type test_type is table of number not null;
3 t_test test_type;
4 begin
5 t_test := test_type(2,4,null); --null is being added
6 dbms_output.put_line('Type variable is initialized and the first element value is '||t_test(1));
[code]....
But what I observed is, i can add, null elements by using 'EXTEND' routine though 'not null' constraint is used for that type.
The below is the example.
pointers@ORCL> ed
Wrote file afiedt.buf
1 declare
2 type test_type is table of number not null;
3 t_test test_type;
4 begin
5 t_test := test_type(2,4);
[code]....
I have problems in opening the database of the physical standby in read- write mode/ read only mode. I have a primary server which is running on 2 node RAC and the standby on a seperate single server being used as DR. I recently got this server and my aim was to isolate the standby server from primary server and perform few test. As it has never been tested even once.
Primary Database spec: (2 Node Rac on ASM)
Oracle Version : 10.2.0.3.0
O/s : HP-UX B.11.23
Standby Database spec: (Single Node)
Oracle Version : 10.2.0.3.0
O/s: HP-UX db01 B.11.23
Error:
--------------------------------------------------------------------------------
alter database recover managed standby database cancel;
Database altered.
SQL> alter database open
2 ;
alter database open
*
ERROR at line 1:
ORA-16004: backup database requires recovery
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '+DATA/dprod/datafile/system01.dbf'
--------------------------------------------------------------------------------
Parameters :
log_archive_dest_2 string SERVICE=PROD1 LGWR ASYNC VALID
_FOR=(ONLINE_LOGFILES,PRIMARY_
ROLE) DB_UNIQUE_NAME=PROD
remote_archive_enable string true
fal_client string DPROD
fal_server string PROD1, PROD2
Steps tried so far:
Changed log_archive_dest_2 = DEFER on both the primary nodes
Standby :
startup nomount
alter database mount standby database;
alter database recover managed standby database disconnect;
alter database recover managed standby database cancel;
alter database open/readonly (tried both)
Same error.
On Primary:
SQL> select max(sequence#) from v$log_history;
MAX(SEQUENCE#)
--------------------------------------------------------------------------------
55702
on Standby:
MAX(SEQUENCE#)
--------------------------------------------------------------------------------
33289
Primary Database:
SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# FIRST_TIME NEXT_TIME
--------------------------------------------------------------------------------
55700 13-JUN-11 13-JUN-11
55700 13-JUN-11 13-JUN-11
55701 13-JUN-11 13-JUN-11
55701 13-JUN-11 13-JUN-11
55702 13-JUN-11 13-JUN-11
60824 rows selected.
Standby Database:
SEQUENCE# FIRST_TIME NEXT_TIME
--------------------------------------------------------------------------------
55698 13-JUN-11 13-JUN-11
55699 13-JUN-11 13-JUN-11
55700 13-JUN-11 13-JUN-11
55701 13-JUN-11 13-JUN-11
15206 rows selected.
Additional Information : There is a delay of 20 minutes before the logs get applied. which has been intentional set by team. Dataguard broker is not configured as well.
i want to run this procedure manually for testing purpose
procedure Reject_Doc(itemtype in varchar2,
itemkey in varchar2,
actid in number,
funcmode in varchar2,
resultout out NOCOPY varchar2 )
what are the possible values for the variables itemtype,itemkey, actid,funcmode?
I am testing nested blocks, but my logic is apparently not correct.
I am running this script:
DECLARE
v_one number(1) := 1;
v_two number(1) := 2;
[Code]....
I receive the output from only one of the nested blocks:
bad
PL/SQL procedure successfully completed.
SQL>
I understand that I don't need nested blocks for the example above, but this was just a condensed version of what I'm trying to do. I think nesting blocks will be easier to read and maintain, instead of having a huge CASE statement.
How can I execute only the nested block for which the condition is true and ignore the nested blocks that follow?
Are nested blocks not the correct answer here? Should I be looking at invoking procedures/functions instead?
I'm using Oracle Database 11g R2 for study purposes.Currently I'm learning about the DBCA clonic templates.I have an Oracle DB 11g R2 X86 running properly in Ms Windows XP Professional SP 3 X86, using DBCA I created the seed template file .dbc and the .CTL and .DBF files, later I copied those files to a server running Oracle DB 11g R2 X86 in Ms Windows 7 Ultimate X86. Again, using DBCA I successfully created the source database through the seed template file, everything was ok.
Now, I formatted my testing server and I installed Ms Windows 7 Ultimate X64 and Oracle Database 11g R2 X64. I copied the seed template file .dbc and the .CTL and .DBF files to "assistantsdbca emplates" directory. Well, I started DBCA to try create the source database and when the DBCA is creating and starting the Oracle instance it shows the errors:
ORA-00604: error occurred at recursive SQL Level 1
ORA-06553: PLS-801: Internal error [56327]
Is it possible to clone my testing DB from X86 to X64 using DBCA clonic templates?
We are currently replacing all public database links with private db links in our prod environment. The requirement is
1) private db link name should be same as public
2) Need to create and test out private db links before we can drop public db links.
How can we test out private db links while public db links are still there with the same name?
when I execute SELECT * FROM DUAL@DBLINK from user who created this private db link, which db link it will be using- public or private?
i am testing a proc after tuning it but the problem is, it is taking a very very less time which it shouldn't. I know that it is because of the buffer cache and the shared pool. that why i need to clean the cache to retest it.
I cannot bounce the database as other schemas are part of it. so is there any way to clean the cache for that particular schema i.e bouncing any particular schema(i know that the term is not appropriate).
I am trying to load data from Db A to Db B. My filter is where date@A >= to_date('20130101','YYYYMMDD') and date@A <= to_date('20130131','YYYYMMDD') .But when I check my target table in db B , it only has dates between Jan-01 and Jan-30. No Jan-31.
View 5 Replies View RelatedDECLARE
T_PASSWORD VARCHAR2(40) :=null;
BEGIN
T_PASSWORD := select MAX(TPASSWORD) from APEXPWD;
if :p101_PASSWORD = T_PASSWORD then
I'm not getting the syntax to LOAD the value of APEXPWD.TPASSWORD from the Database into the VARIABLE T_PASSWORD in APEX so that I can compare it against the users PASSWORD. This allows me to compare if the user has a temp password and then redirects them to the correct change password page. Everything is working except getting the T_PASSWORD variable set correctly.
The problem I'm having is my XML tree doesn't contain all possible elements. In this example the second entry doesn't contain <age> - only the first entry will be added to the database
All columns are varchar2 datatype
XML:
CODE<rowset>
<row>
<name>Name</name>
<age>Age</age>
<city>City</city>
</row>
[code].......
Below is the data which i have to load
SQL*Plus: Release 11.1.0.6.0 - Production on Wed Jan 18 17:47:01 2012
Copyright © 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SQL> SQL> SQL> SQL> SQL> 2 3 4 5 6
57140002205124| 23| ST04| 9418285932| 17-JAN-12 11.17.31.820253 AM| Used
54171025176597| 49.86| TU03| 9411165512| 17-JAN-12 11.20.32.943855 AM| Used
54171025182725| 49.86| TU03| 9456310464| 17-JAN-12 11.37.14.346299 AM| Used
(1) first thing i want to remove the txt which is in the bold
(2) my query for creating the table is
CREATE TABLE VMSDATA
(
SERIALNO NUMBER(20),
AMOUNT NUMBER(7,2),
CLASS VARCHAR2(10),
MSISDN NUMBER(12),
VDATE TIMESTAMP(6),
STATUS VARCHAR2(8 BYTE)
and my control file for loading the data is
load data
infile 'path'
badfile 'path'
DISCARDFILE 'path'
truncate into table vmsdata
[code]...
Data purging and loading into different table.
Currently we a have a table with size 300gb, and we wanted to remove this table to release storage. but have to keep the 6 month data which would be around 3 million per day ( 3 million X 180 days).
i have the following questions.
1) what would be the best strategy to move this 6 month data from Table A (will be removed) to table B ( will be online table).
2) later we want to delete the data from table B which is 6 month older every day or week ( frequency is still to be decided)
note that We cannot use the partition as don't have a access to create and delete partition. what would be the best strategy for deleting the data?
I have a table in oracle and i want to load data to flat file which is present on the server. how to and where to provide the destination file location to load the flat file.
Can it be also provided in the plsql program.
I was reading about Oracle SPM feature, but i have questions in mind for automatic feature..
* if we have set both capture and use parameter true. Oracle will automatically capture Plans, but will it automatically evolve new plans to accepted or it will wait for evolve it manually before using new plan.
* If it Automatically evolve, when and how will it?
* If it Automatically, will change old plan's flag accepted to No? or add one more accepted plan
I've always only loaded data into the database by using SQL-Loader and the data format was Excel or ASCII
Now I have to load a XML.
How can I do?
The company where I work has Oracle vers. 8i
Possible to call a tnsnames file from master tnsnames like the principle of ifile in the init.ora...My primary tnsnames.ora has a lot of development entries which are repointed frequently, it would work with logistics if I could manage these in a second tns file and load from primary so when my dev instances are repointed I'm not editing the master tnsnames
View 4 Replies View RelatedI am very much new to this vast world of Pl/SQL. Recently I have moved to pl/sql domain. I want to write a package to move data from Table A to Table B by looking up the table C. Bellow is skeleton of my package.
Table name: source_a
columns: X1,X2,X3,X4,X5
Target table name: target_b
columns:Y1,Y2,Y3,Y4,Y5
Lookup Table : lookup_c
columns : Z1,Z2,Z3,Z4,Z5
1) I have to load data from source_a to target_b.
2) If value of column X1(source_a) matched with value of column Z1( lookup_c) then only we will process the recordsmeans those records will be the valid records. Rest of the records will be dropped.
3) From the valid records If X2 != Z2 then call a procedure pk_rec.generate_Y2(X2) to generate the value of Y2
{pk_rec.generate_Y2 already existing no need to create) else take the records form Z2 and use it to load the y2.
4) From the valid records If X3 != Z3 then call a procedure pk_rec.generate_Y3(X3) to generate the value of Y3
{pk_rec.generate_Y3 already existing no need to create) else take the records form Z3 and use it to load the y3.
5) X4,X5 are directly loaded into Y4,Y5.
,I've some database in 11.2 RAC on AIX.I was analyzing the root causes of eviction.Looking AWR Report before the reboot I see:
DB1 Host CPU (CPUs: 6 Cores: 3 Sockets: )~~~~~~~~ Load Average Begin End %User %System %WIO %Idle --------- --------- --------- --------- --------- --------- 4.18 12.33 60.9 12.6 1.6 26.5 Instance CPU~~~~~~~~~~~~ % of total CPU for Instance: 27.4 % of busy CPU for Instance: 37.3 %DB time waiting for CPU - Resource Mgr: 10.6 DB2 Host CPU (CPUs: 6 Cores: 3 Sockets: )~~~~~~~~ Load Average Begin End %User %System %WIO %Idle --------- --------- --------- --------- --------- --------- 3.77 13.93 60.7 12.5 1.6 26.7 Instance CPU~~~~~~~~~~~~ % of total CPU for Instance: 6.9 % of busy CPU for Instance: 9.5 %DB time waiting for CPU - Resource Mgr: 0.0
Do you think these value ar high? This is vmstats at the time of reboot:
DATARUNBCKAVMFREPREPPIPPOPFRPSRPCYFINFSYFCSCUSCSYCIDCWA07/21/2013 00:08:173107.400.345579.92308100003.292187.01019.56084160007/21/2013 00:08:171717.390.187589.884017600003.681169.99421.48281190007/21/2013 00:08:172717.402.121577.816011500003.150157.21018.50384160007/21/2013
[code]...
How to resolve this issue?
CREATE OR REPLACE PROCEDURE fast_proc
IS
TYPE ARRAY IS TABLE OF mkt_total_lvl_indx_dly_stg%ROWTYPE;
l_data ARRAY;
cursor C IS
SELECT *
[code]..........
show error
Error code
PROCEDURE fast_proc compiled
Warning: execution completed with warning
17/47 PL/SQL: ORA-03001: unimplemented feature
17/5 PL/SQL: SQL Statement ignored
I've been working on Oracle for many years but fot the first time I was asked to load a XML file into a table.As an example, I've found this on the web, but it doesn't work. the file acct.xml is this:
<?xml version="1.0"?><ACCOUNT_HEADER_ACK><HEADER><STATUS_CODE>100</STATUS_CODE><STATUS_REMARKS>check</STATUS_REMARKS></HEADER><DETAILS><DETAIL><SEGMENT_NUMBER>2</SEGMENT_NUMBER><REMARKS>rp polytechnic</REMARKS></DETAIL><DETAIL><SEGMENT_NUMBER>3</SEGMENT_NUMBER><REMARKS>rp polytechnic administration</REMARKS></DETAIL><DETAIL><SEGMENT_NUMBER>4</SEGMENT_NUMBER><REMARKS>rp polytechnic finance</REMARKS>[code]......
For the two tags HEADER and DETAILS I have the table:
create table xxrp_acct_details(status_code number,status_remarks varchar2(100),segment_number number,remarks varchar2(100));
before I've created a create directory test_dir as 'c:esterno'; --
where I have my acct.xml and after, can you give me a script for loading data by using XMLTABLE? I've tried this but it doesn't work:
DECLARE acct_doc xmltype := xmltype( bfilename('TEST_DIR','acct.xml'), nls_charset_id('AL32UTF8') );BEGIN insert into xxrp_acct_details (status_code, status_remarks, segment_number, remarks) select x1.status_code, x1.status_remarks, x2.segment_number, x2.remarks from xmltable( '/ACCOUNT_HEADER_ACK/HEADER' [code]......
This should allow me to get something like this: select * from xxrp_acct_details; Statuscode status remarks segement remarks --------------- -------------------- ------------- ------------ 100 check 2 rp polytechnic100 check 3 rp polytechnic administration100 check 4 rp polytechnic finance100 check 5 rp polytechnic logistics500 process exception 20 base polytechnic500 process exception 30500 process exception 40 base polytechnic finance500 process exception 50 base polytechnic logistics but I get:
Error report:ORA-06550: line 19, column 11:PL/SQL: ORA-00932: inconsistent datatypes: expected - got NUMBERORA-06550: line 4, column 2:PL/SQL: SQL Statement ignored06550. 00000 - "line %s, column %s:
%s"*Cause: Usually a PL/SQL compilation error. and if I try to change the script without using the column HEADER_NO o keep track of the header rank inside the document:
DECLARE acct_doc xmltype := xmltype( bfilename('TEST_DIR','acct.xml'), nls_charset_id('AL32UTF8') );BEGIN insert into xxrp_acct_details (status_code, status_remarks, segment_number, remarks) select x1.status_code, x1.status_remarks, x2.segment_number, x2.remarks from xmltable( '/ACCOUNT_HEADER_ACK/HEADER' [code]......
I get this message: Error report:ORA-19114: error during parsing the XQuery expression: ORA-06550: line 1, column 13:PLS-00201: identifier 'SYS.DBMS_XQUERYINT' must be declaredORA-06550: line 1, column 7:PL/SQL: Statement ignoredORA-06512: at line 419114. 00000 - "error during parsing the XQuery expression: %s"*Cause:
An error occurred during the parsing of the XQuery expression.*Action: Check the detailed error message for the possible causes. My oracle version is 10gR2 Express Edition
I am receiving errors when trying to load the control file. The errors are as follows:
SQL*Loader-500 Unable to open file (homework.ctl)
SQL*Loader-553 file not found
SQL*Loader-559 SYstem error: The system cannot find the file specified.
My control file is located directly in the C drive (C:\homework.ctl). The control file contains the following
LOAD DATA INFILE 'c:\country.dat'
APPEND INTO TABLE homework
fields terminated by ',' optionally encloded by '"'
(country, month, day)
WHEN (month='April')
On the command line, I am entering:
sqlldr system/password control=homework.ctl