Performance Tuning :: Hint Is Not Working Properly
Jul 14, 2010
I have a query with FULL hint that is behaving in a strange manner. The query fetches around 700000 of data. Sometimes it fetches the data with the hint and sometimes it does not fetch any data with the hint and then I have to remove the hint and have to fetch the data. Below is the query,
select /*+ FULL(COMP_TM) FULL(TRANS_TM) FULL(INVC_TM) */
CUST_BE_ID ,
DISTR_BE_ID ,
FG_BE_ID ,
KIT_BE_ID ,
BG_ID_NO_BE_ID ,
[code]....
The statistics gathering activity of FACT_DLY_ALGND_SLS table takes around 5 hours to complete. It is a range partitioned table with subpartitions.
View 14 Replies
ADVERTISEMENT
Aug 28, 2013
Does parallel hint works in cursor queries? The cursor query is something like :
cursor c is
select /*+ parallel(s,8) */
from table ref_tab s ---- >>
<where condition>;
The table ref_tab hold data for a single day at any point of time and gets truncate before loading the next days data.On average the table holds around 7 million rows and doesn't contains any index (think that's fine as all together we are loading the whole set).And, we are using bulk logic with save exceptions to open the cursor and load the data into the target table.
View 13 Replies
View Related
Mar 31, 2011
I have a SQL query where I am making UNION of two select statements. The table that I am joining in each select statement have indexes defined for those tables.
Now the UNION of the two select statements again in enclosed in an inline view , from which I fetching my final field values.
The select statements inside the inline view returns huge number of row (like 50 million rows).
The whole query fails with time out.
Is there a way to pass Oracle Hints so that Oracle uses indexes?
View 1 Replies
View Related
Sep 25, 2013
select
serialnumber from product where productid in
(select /*+ full parallel(producttask 16) */productid from producttask where
startedtimestamp > to_date('2013-07-04 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
and startedtimestamp < to_date('2013-07-05 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
and producttasktypeid in
[code]....
Explain plan output:
Plan hash value: 2779236890
-----------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name| Rows| Bytes | Cost (%CPU)| Time| Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT || 1 | 29 | 9633M (8)|999:59:59 |||
|* 1 | FILTER |||| ||||
| 2 | PARTITION RANGE ALL || 738M| 19G| 6321K (1)| 21:04:17 | 1 | 6821 |
[code]....
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( EXISTS (<not feasible>)
4 - filter("PRODUCTID"=:B1)
5 - filter(ROWNUM<100)
12 - access("MODELID"=:B1)
[code]....
Note: - SQL profile "SYS_SQLPROF_014153616b850002" used for this statement
View 2 Replies
View Related
Aug 16, 2012
I have an APP that truncates tables and loads data, which in turn makes the stats stale. I ran the query advisor (see attachment) and of course it ecommends running stats or accept a profile.I really don't want to do that as it may cause a load on my DB.
In turn, I would like to consider having my APP team change the query to pass a hint to use the best query plan.syntax to pass the hint to emulate good attached plan? Or is this a bad way to proceed?
select /* INDEX FAST FULL SCAN PK_PLACEMENT_REQUEST_QUEUE */
sum(lastshares) as "ROSEN"
from nyeo.fix_exec_reports fer, nyeo.placement_request_queue q,
nyeo.nyeo_block_control bc
where fer.clordid = q.sequence_number
and q.blockid = bc.blockid
and upper(bc.deskname) like '%ROSEN%'
View 2 Replies
View Related
Oct 20, 2011
does parallel hint in query can create bottleneck/Slowdown/crash database..??
View 4 Replies
View Related
Oct 25, 2010
I am querying a table having 6 million records,It takes arround 35 seconds in command line terminal using putty in a windows client.
Also the server is linux centos with oracle 11g installed. I saw the method Oracle result_cache which i am suspecting to be fast
my query is
"select /*+ result_cache */ * from relationshipobject;"
SQL> set autotrace traceonly stat
SQL> set timi on
[Code]....
i can i reduce my query execution time and consistent gets?
View 10 Replies
View Related
Sep 9, 2010
Oracle UNION ALL performance issue: when I try to run below SQL query separately SQL part1 and SQL part2 it takes some seconds only but if I run together with group by and without group by it take much time.
SELECT AVG(date_completed-login_date),to_char(to_date(login_date), 'YYYY') as wYear FROM
(
SELECT test.date_completed 'date_completed',sample.login_date 'login_date')
FROM sample test
where (some conditions) ) ---SQL part 1
UNION ALL
[code]...
View 33 Replies
View Related
Jan 2, 2013
I'm trying to demonstrate the working of the OCI client result cache. I've set some parameters,orcl> sho parameter result_cache
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
client_result_cache_lag big integer 3000
client_result_cache_size big integer 1000000
result_cache_max_result integer 5
result_cache_max_size big integer 1984K
result_cache_mode string FORCE
result_cache_remote_expiration integer 0
If I understand the docs correctly, that should be all that is needed.I've complied and run the cdemoqc and cdemoqc2 OCI demos, but I never get anything in the V$CLIENT_RESULT_CACHE_STATS or CLIENT_RESULT_CACHE_STATS$ views. This is probably because the sessions exit after running the queries.
So I've also tried repeating arbitrary queries through cdemo2, which gives a persistent session, but there is still nothing in those views and furthermore the v$result_cache_objects.scan_count for my queries keeps increasing. So I don't think think the client side cache is working.
View 4 Replies
View Related
Apr 23, 2013
I am using the following merge statement it's not working properly. If I tried to insert (or) update the existed record. I am getting unique key constraint violated error.
DECLARE
p_buid NUMBER;
p_ordernum NUMBER;
[Code].....
View 12 Replies
View Related
Nov 29, 2011
I'm trying to Create a procedure that generates:
Customer
Description of Order
Amount of Order
Total Of Order
As this Generates More than One Row I thought i would need to use cursor in order to store the results and then use them from the object So this is what i Coded:
CREATE OR REPLACE PROCEDURE UP_CustOrders
AS
/*I Am Selecting A Cursor Cause It Return More Than One Row*/
CURSOR ReportCursor IS
SELECT Company
[code]..
It appears It creates the procedure but im not sure why it has compilation errors Perhaps im missing some symbols? Here are the tables:
CREATE TABLE Customers
(CustNum NUMBER(10) NOT NULL,
Company VARCHAR2(20) NOT NULL,
CustRep NUMBER(10),
[code]...
View 12 Replies
View Related
Jul 18, 2013
There are two servers A and B ,and i am maintaining one table suppose table1 and another table suppose table2 , DML triggers are made for both of these tables.The action of DML trigger is that the movement we are inserting values , suppose a table1 on server A so automatically the same set of value in table2 on server Bso my problem is that the movement i inserted value on table1 on server A ,
the same set of values were inserted into table2 of server B and as the values were inserted intotable2 , the same set of values were inserted into the tabld1 because of the trigger. How to stop the non ending loop which occurs due to the simultaneous firing of triggers and insertion of same records into both the table endlessly...
version :---Personal Oracle Database 10g Release 10.2.0.3.0 TNS for 32-bit Windows: Version 10.2.0.3.0
View 0 Replies
View Related
Nov 8, 2010
It seems some issue with With Clause along with Row Number. Output is strange when it comes with SELECT MIN()... But Looks fine Once you add "order by" or "where" clasue inside WITH Clause
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE 10.2.0.5.0 Production
[code]....
But Output looks fine once you add ORDER By clause inside WITH clause
SQL> WITH EM AS
2 (
3 SELECT EMPNO, ROWNUM RN
4 FROM SCOTT.EMP T ORDER BY ROWNUM
[code]....
why it happens ?
View 4 Replies
View Related
Apr 22, 2009
#service setup:
$ srvctl add service -d dc1ext -s addval.dc1 -r "dc1ext1" -a "dc1ext2" -P BASIC
$ srvctl start service -d dc1ext -s addval.dc1
begin
DBMS_SERVICE.MODIFY_SERVICE(
service_name=>'addval.dc1',
aq_ha_notifications => true,
failover_method=>DBMS_SERVICE.FAILOVER_METHOD_BASIC,
[code]......
my attempt at verifying the TAF is working.
SQL> run
1 SELECT MACHINE, FAILOVER_TYPE, FAILOVER_METHOD, FAILED_OVER, COUNT(*)
2 FROM GV$SESSION
3* GROUP BY MACHINE, FAILOVER_TYPE, FAILOVER_METHOD, FAILED_OVER
MACHINE FAILOVER_TYPE FAILOVER_M FAI COUNT(*)
---------------------------------------------------------------- ------------- ---------- --- ----------
anetapp02.dc2 NONE NONE NO 10
anetapp01.dc2 NONE NONE NO 10
app02.st2 NONE NONE NO 5
racdb02 NONE NONE NO 44
anetapp03 NONE NONE NO 10
anetapp04 NONE NONE NO 10
GNOME\APTSEA-6GKJHF1 SELECT BASIC NO 3
app01.st2 NONE NONE NO 5
anetapp05 NONE NONE NO 10
anetapp01 NONE NONE NO 10
anetapp03 NONE NONE NO 10
so it looks as thought the sessions are failing over, but my app guy says he get a few errors during the process.. i need it setup to move the current query over without returning an error.. do i not have this setup properly?
View 3 Replies
View Related
Oct 25, 2012
I have created a Table and a log of materialized view in an Schema SchemaAA of Server A
CREATE TABLE TABLA_TEST
(
TEST_PK NUMBER,
TEST_TEXTO VARCHAR2(50),
CONSTRAINT PK_TEST PRIMARY KEY (TEST_PK)
);
CREATE MATERIALIZED VIEW LOG ON TABLA_TEST;
I have created a DB Link from server B with user REPLIC
I have granted SELECT to REPLIC to the table and the log materialized View.
I have created a table in instanceBA of Server B
CREATE TABLA_TEST
(
HOSPCODE NUMBER(3),
TEST_PK NUMBER,
TEST_TEXTO VARCHAR2(50),
FECHA_INSERT DATE,
FECHA_UPDATE DATE,
FECHA_DELETE DATE,
CONSTRAINT TTEST_PK PRIMARY KEY (HOSPCODE, TEST_PK);
I have created a materialized view in schema SchemaBB of Server B with a trigger
CREATE MATERIALIZED VIEW WORKAREA.TABLA_TEST70
BUILD IMMEDIATE
REFRESH FAST ON DEMAND
WITH PRIMARY KEY
AS
SELECT TEST_PK,
TEST_TEXTO
[code].........
All schemas have the appropriate grants.
When inserting in TABLA_TEST (SchemaAA) and refreshing MView TABLA_TEST70 things go nicely.
But, when updating a record in the original TABLA_TEST and refreshing the MView the results in TABLA_TEST in SchemaBA are as if I have deleted the record (FECHA_DELETE is set to SYSDATE).
View 1 Replies
View Related
Apr 8, 2009
i have created a tree hierarchy....when the user clicks on any node,that particular form will open....But there is some problem...i click on form A,it opens.,...then i exit that form via a exit button on the toolbar....and then I click on form B on the tree....but first the form A only opens...and then again i exit form A, then form B will open..
i have written the following code in when_tree_node_selected trigger :
if condition then
call_form(path,no_hide,do_replace);
View 3 Replies
View Related
Feb 10, 2009
I applied the Jan 09 CPU to my database this past Saturday and now my daily schema export is not running properly:
With the Partitioning, OLAP, Data Mining and Real Application Testing optionsExport done in WE8MSWIN1252 character set and UTF8 NCHAR character set server uses UTF8 character set (possible charset conversion)
About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user TEST
. exporting PUBLIC type synonyms
. exporting private type synonyms
[code]...
View 8 Replies
View Related
Feb 27, 2013
Every time I hit up, down, left, and right arrow keys to return to a previous sql command I got ^[[D, fix this issue? The backspace works fine with me.
View 2 Replies
View Related
Jun 6, 2013
I created one Form manually. how to Drag and drop from items in Data Blocks to Canvas. when I created text in Canvas it was automatically creating the item in Data block. but when i create the items in Data blocks and then trying to copy to canvas.
View 1 Replies
View Related
Mar 7, 2013
The multiple row insert is not working
CREATE TABLE example (
example_id INT NOT NULL,
name VARCHAR( 50 ) NOT NULL,
value VARCHAR( 50 ) NOT NULL,
OTHER_VALUE VARCHAR( 50 ) NOT NULL
);
For the below query its showing error .
INSERT INTO example
VALUES
(100, 'Name 1', 'Value 1', 'Other 1'),
(101, 'Name 2', 'Value 2', 'Other 2'),
(102, 'Name 3', 'Value 3', 'Other 3'),
(103, 'Name 4', 'Value 4', 'Other 4');
the error message is.
Error starting at line 1 in command:
INSERT INTO example
VALUES
(100, 'Name 1', 'Value 1', 'Other 1'),
(101, 'Name 2', 'Value 2', 'Other 2'),
(102, 'Name 3', 'Value 3', 'Other 3'),
(103, 'Name 4', 'Value 4', 'Other 4')
Error at Command Line:3 Column:39
Error report:
SQL Error: ORA-00933: SQL command not properly ended
00933. 00000 - "SQL command not properly ended"
*Cause:
*Action:
View 8 Replies
View Related
Oct 11, 2010
here i m giving the code
DECLARE
TYPE SUBNO_TABLE_TYPE IS TABLE OF
TRANS_DEICMAIN1.SUBNO%TYPE
INDEX BY BINARY_INTEGER;
SUBNO_TABLE SUBNO_TABLE_TYPE;
K NUMBER := 1;
S NUMBER := 1;
[code]...
HERE CHECK1 IS A CHECKBOX.WHEN I AM USING THIS CODE ONLY ONE RECORD IS SAVED AT A TIME INSTEAD OF SAVING ALL RECORDS.
View 2 Replies
View Related
Jun 29, 2010
I am trying to write a trigger on a new table. (dest_test) This is the first trigger that I have ever attempted (fairly new DBA) and I am having some trouble with the trigger body.It is a before insert trigger that will need to select from another table (dest) for a particular value being inserted (destination).
create table dest_test (
destination varchar2(4) not null,
db_name varchar2(10) not null
)
desc dest
[code]...
I am getting the exact opposite results than I want, though. If the value appears in dest, it is inserting into dest_test... NOT whatI want it to do!If the value doesn't appear in dest, it is throwing ora-6512 and ora-4088 errors. Is there a way to suppress these errors, or to graceful exit from the block so that the trigger completes without throwing these errors?
View 3 Replies
View Related
Feb 12, 2010
I have a form in which menu tree is populated with this sql
SELECT 1, Level, MenuLabel, MenuName, MenuId
FROM MenuFile
CONNECT BY PRIOR MenuId = ParentId START WITH menuId = :moduleid||'0000'
The result is attached in the CSV file enclosed in sheet mrnufile1As we can see in row 3 the item LPC has menuid BDME13.
In my view This item should be in ROW 19.How can i change query do get the desired the result.I have also tried another sql query.
select * from menufile where applicationid = 'BD' order by parentid,menuid
Result of this query is in 2nd Half of CSV file
View 2 Replies
View Related
Jun 12, 2013
I am using Apex 4.2, The Interactive report Search is not working properly, when i am entering complete date or time it is not showing any results...how to increase performance of the search...
View 5 Replies
View Related
Jul 12, 2010
Looking to understand the difference between instance tuning and database tuning.
What is the difference between these two tuning exercises? I understand that an instance is memory based structures (logical) where as database consists of physical structures.
However, how does one tune a database the physical structure? Does it have to do with file placements/block sizes etc. Would you agree that a lot of that is taken care by ASM now in 11g? What tools are required/available (third party as well as oracle supplied) for these types of tuning scenarios?
View 1 Replies
View Related
Oct 31, 2011
I have two tables with 113M records in DWH_BILL_DET & 103M in prd_rerate_chg_que and Im running following merge query, which is running for 13 hrs to update records, which is quiet longer time.
SQL> explain plan for MERGE /*+ parallel (rq, 16) */
INTO DWH_BILL_DET rq
USING (SELECT rated_que_rowid,
detail_rerate_flag_code,
rerate_sel_key,
[code].....
View 39 Replies
View Related
Sep 30, 2010
How the length of column width effects index performance?
For example if i had IOT table emp_iot with columns:
(id number,
job varchar2(20),
time date,
plan number)
Table key consist of(id, job, time)
Column JOB has fixed list of distinct values ('ANALYST', 'NIGHT_WORKED', etc...).
What performance increase i could expect if in column "job" i would store not names but concrete numbers identifying job names.
For e.g. i would store "1" instead 'ANALYST' and "2" instead 'NIGHT_WORKED'.
View 24 Replies
View Related
Jun 16, 2010
I have a question about database fragmentation.I know that fragmentation can reduce performance in query times. The blocks are distributed in many extents and scans process takes a long time. Oracle engine have to locate the address of the next extent..
I want to know if there is any system view in which you can check if your table or index has high fragmentation. If it's needed I will have to re-create, move or rebulid the table or index, but before I want to know if the degree of fragmentation is high.
Any useful script or query to do this, any interesting oracle system view?
View 2 Replies
View Related
Oct 20, 2010
There is a simple way to increase the performance of a query by reducing the row-size of the table it hits. I used it in the past by dividing the table into smaller parts and querying respective smaller table in each query.
what is this method called ? just forgot the method and can't recall it. what this type of row-reduction optimization is called ?
View 6 Replies
View Related
Jun 16, 2011
How many records could I have in a single table without performance degradation with Standard Edition without partitioning with cutting-edge server (8 or 12 cores, 72 GB RAM, FC 4 Gbit, etc...) and good storage?
300 Millions in only one table with 500K transactions / day is too much?
Simple database with simple schema.
How many records begin to be too many?
View 2 Replies
View Related
