SQL & PL/SQL :: Usage Of Hints In The Views
Oct 11, 2011
I have a table "tl" which is partitioned--say 30 partition and for each partition there is a seperate view like as follows
create view view_t130 as select * from tl partition (p30);
create view view_t129 as select * from t1 partition (p29);
.
.
.
create view view_t101 as select * from t1 partition (p01);
my question is how to use hints on this table if your are using view to access the data from internal table.
Normal structure is if i don't wrong:-
index( <<view name|view alia name>> <<table name|table alias name>> name of index)
Consider my case
select * from view_t130 where <index_column> --not picking up index
i want to give expicit index hint.so i used the same structure that i specified above but it didn't work.
select /*+ index( view_t130 t1 <index_name) */ * from view_t130 where <index_name>
how to give explicit index hint..but one constraint is i cannot give any alias names for internal tables because those(view structure) are generated by predefined scripts..so it's not possible to change it.
View 1 Replies
ADVERTISEMENT
Mar 19, 2013
Despite it being one of the major selling points of Exadata (especially from X3 onwards), I'm struggling to find much information on our usage of the Exadata Smart FlashCache (I'm running RDBMS 11.2.0.2 BP7 on a V2 quarter-rack).
I can verify usage of the FlashCache by checking whether the object has been 'pinned' to the FlashCache via DBA_SEGMENTS and I can check for FlashCache usage by querying gv$sysstat (and even v$mystat), but are there other views that I could use? It seems a bit strange for Oracle not to provide the DBA all that much insight into their usage of this feature...
View 2 Replies
View Related
Jun 28, 2013
Is their any query to find cpu usage & memory usage of all the queries currently running on DB?
View 5 Replies
View Related
Mar 6, 2012
I am executing below query, but optimizer generating 2 different plans for the same. I don't want to use sql profiles to fix execution plan.
Query
SELECT R.VENDOR_RECORD_SEQ_NO ,
R.VENDOR_SUBJECT_SEQ_NO ,
NVL(D.RESOLVED_VALUE, D.ORIGINAL_VALUE) VAL,
D.CONTROL_COLUMN_SEQ_NO
[code]....
View 3 Replies
View Related
Aug 5, 2007
There's a fairly popular Ordered Hint example on the web as follows:
<CODE>
select /*+ ordered use_nl(bonus) parallel(e, 4) */
e.ename,
hiredate,
b.comm
from
emp e,
bonus b
where
e.ename = b.ename
;
</CODE>
I would like to know what the "parallel(e, 4)" clause does. Where does the "4" come from? What is "parallel" here? I also have another question: If I have 5 tables--T_OREGON, T_UTAH, T_VIRGINIA, T_TEXAS, and T_OKLAHOMA--lined up in a join right behind a FROM, coming in at row counts of
T_OREGON: [a lot of rows; a lot more than T_UTAH]
T_UTAH: [smaller than T_OREGON] 40550 rows
T_VIRGINIA: 14 rows
T_TEXAS: 66 rows
T_OKLAHOMA: 8 rows
from T_OREGON or, T_UTAH ut, T_VIRGINIA va, T_TEXAS tx, T_OKLAHOMA ok...
my question is, if an Ordered hint can be used here, is it that the smallest table (in this case T_OKLAHOMA) gets placed first in the join and the rest of the table ascension in the join doesn't matter? Or is it that T_OKLAHOMA gets placed first in the join, followed by T_VIRGINIA [at 14 rows], followed by T_TEXAS, T_UTAH, and finally T_OREGON?
View 3 Replies
View Related
Nov 5, 2012
is there some open source or free tool which can graphical display V$ Views. Can TOAD do that in a good maner?
in UNIX there is the "sar" command, but a Java tool "ksar" for displaying the statistics in user friendly fashion.
View 2 Replies
View Related
Jul 10, 2013
Below query doesn't have any parallel hints. Though, it is without where clause, so full scan is must but why "PX Deq Credit: send blkd" occurred?
SELECT ROWID, REGION, STATE, CITY, DEALERNAME, DEALERCODE, DEALERID, BUSINESSUNIT, GARDEALERID, MASTERCLAIMNUMBER, CLAIMNUMBER, COMPANY, POLICYNUMBER, STARTDATE, ENDDATE, POLICYISSUEDEALERNAME, ISSUEDEALERCODE, CUSTOMERNAME, CUSTOMERADDRESS, ACCIDENTDATE, ACCIDENTTIME, INTIMATIONDATE, INTIMATIONTIME, REGISTRATIONDATE, REGISTRATIONTIME, DRIVERNAME, ACCIDENT_DESCRIPTION, INTIMATION_DELAY_REASON, INTIMATORNAME, INTIMATORMOBILENO, CLAIM_INTIMATED_BY, TOWING_ASSISTANCE, ACCIDENTPLACE, ACCIDENTLOCATION, GARAGENAME, WORKSHOPADDRESS, WORKSHOPMOBILENO, MANUFACTURINGYEAR, CHASSISNUMBER, ENGINENUMBER, REGISTRATIONNUMBER, VEHICLEMODEL, BUSINESSTYPE, TOTALSI, CLAIMEDAMOUNT, ESTIMATEDAMOUNT, "LABOUR(E)", "PARTS(E)", "PAINTING(E)", EXCESS,
[code]....
understand about "PX Deq Credit: send blkd"..
View 1 Replies
View Related
Sep 30, 2011
What is the use of using Hints within SQL statements w.r.t query tuning? How to know which hint to use when?
View 1 Replies
View Related
Aug 4, 2011
I have a created a materialized view which is based on a view on remote database. Now how do I refresh the view.
Materialized view is created by
CREATE MATERIALIZED VIEW mv_employee_name
AS SELECT EMPLID, EMPL_NAME
FROM VEMPDATA@REMOTEDB
WHERE REGION = 'US';
I am wondering how the refersh happens or how do I specify the refresh clause.REFRESH FAST option is looking for VIEW LOG on the master table but in this case its a remote view, so I cannot create any object on remote db.
View 1 Replies
View Related
Oct 17, 2013
I am removing sal column from table tab_emp; i want to check whether any materialized view or view using this column by querying using data dictionary :- if i use like condition against query column of all_mviews it is throwing error sicne it is long data type. is there a way to search it without creating any function and use it in a query.
View 3 Replies
View Related
Jul 7, 2010
I have to convert some existing materialized views (fast refresh) to partition materialized views.
Database version is oracle 10.1.0.4. I have decided to use on prebuilt table option to do the partitioning as it minimizes the time to transfer from the master site.
1) stop replication
1) create interim tables with similar structure as the materialized views
2) transfer all data from the materialized views to the interim tables
4) script out the materialized views structure and add in on prebuilt table option in the scripts
5) drop the materialized views
6) rename the interim tables with the same name as the materialized views
7) run the scripts to create the materialized views with on prebuilt table option
8) refresh the newly created materialized views -> it should take a short time since I am using on prebuilt table option
But I am facing one major issue. That is if I drop the materialized views, the materialized view logs of the master tables are purged. When the materialized views are refreshed fast, there are some data missing. the data that are purged out when the materialized view are dropped.
Do you happen to know other ways that existing materialized views can be converted to partitioned materialized views? Do you have any workaround to prevent the materialized view logs from being purged?
View 3 Replies
View Related
Oct 20, 2011
There is an 'emp' table with a column name as 'mgr' with datatype 'number'. following is the detailed description of the table:
SQL> desc emp;
Name Null? Type
----------------------------------------- -------- ---------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
Now when I run the 'select mgr from emp e;' query the output which I get is:
7902
7698
7698
7839
7698
7839
7839
7566
7698
7788
7698
7566
7782
Note: One value in between here is null, the required to me is that I want to print a character value 'President' in place of null .
View 7 Replies
View Related
Mar 31, 2011
when to use || NULL oerator or what is the meaning of this statement when it is used with the column name.
and A.RtmtCd = B.RtmtCd|| NULL
or
A.RtmtCd||NULL IS NOT NULL
View 5 Replies
View Related
Mar 6, 2013
I have one procedure which is executing daily two times(i.e morning run and evening run) . In morning run it's executing around 150 mins and evening run executing around 25 mins.
It's happening for all procedures executing in longer run in morning. There is NO LOCKS, I have checked LOCKS while executing morning run. I suspects CPU usage in morning run.
How to check CPU usage ? AND also each session how much utilizing CPU. I'm using oracle 8i database, I know it's older version but my company is using oracle 8i. So i have to look in this database version only.
View 6 Replies
View Related
Jul 31, 2011
I had a question on BFILENAME() :
first,
create or replace directory UTL_FILE_DIR as 'k:lob_test';
GRANT READ,WRITE ON DIRECTORY UTL_FILE_DIR TO user1;
and then ,I login use user : user1
create or replace package body p_utl_mail_attach_raw as
procedure send_single_user_attach_raw(........)
fil BFILE;
filenm VARCHAR2(50) := 'aaa.jpg'
begin
fil := BFILENAME('UTL_FILE_DIR', filenm);
end ;
this result it show :
UTL_FILE_DIR/aaa.jpg, exists=F, length=0, open=F
View 1 Replies
View Related
Jan 31, 2013
I'd like to have a conditional that only gets the next value from a sequence is the current (or supplied) value is null.
Here are some trivial examples:
CREATE SEQUENCE ts
MINVALUE 1
START WITH 1
INCREMENT BY 1;
SELECT ts.NEXTVAL FROM DUAL;
SELECT COALESCE(ts.CURRVAL, ts.NEXTVAL) FROM DUAL;
SELECT NVL(ts.CURRVAL, ts.NEXTVAL) FROM DUAL;
SELECT CASE
WHEN ts.CURRVAL IS NOT NULL THEN ts.CURRVAL
ELSE ts.NEXTVAL
END
FROM DUAL;
Ideally multiple executions of any of these (or a better one of your design) should return the same value from the sequence, but mine do not.
View 6 Replies
View Related
Nov 9, 2012
I have one table and I created partition month wise. I also creates index on that table. How can I check whether parition will be used when I query that table? In explain plan I can see PARTITION RANGE as ALL. Is it using the partition?
View 2 Replies
View Related
Sep 7, 2011
I can get "CPU used by this session' value from v$sesstat. That is the value in centiseconds used by each session. How to convert the value into a percentage of total CPU resource ?
Or there are some other ways to gain the value. i.e. Percentage of Total CPU usage for each Oracle session.
View 5 Replies
View Related
Jan 26, 2011
I am building a reporting table using the count analytic function in order to count up several different attributes in one statement.What I find is that this method quickly eats up my TEMP space. This is 10gR2. I have attempted to use MANUAL workarea policy with as large ofsort_area_size as possible (2G) but that does not seem to have any effect on performance or TEMP usage. The RAW table is about 12G with 75 million rows. I am not that concerned about execution time, but rather TEMP usage.
--INSERT into <object>...
select distinct
file_sid,filename,control_numb,processing_date,file_class,
vendor_id,vendor_desc,
c_status_id,c_status_desc,
[code]...
I am not seeing any increase in onepass or multipass executions on the PGA during execution of this statement using...
SELECT CASE WHEN low_optimal_size < 1024*1024
THEN to_char(low_optimal_size/1024,'999999') ||
'kb <= PGA < ' ||
(high_optimal_size+1)/1024|| 'kb'
ELSE to_char(low_optimal_size/1024/1024,'999999') ||
[code]...
I'd like to get a better explaination of how analytics use the instance resources and TEMP space. For example if I add
a count with a different window (such as the last two columns commented in the above query) I blow out my temp space (70G).
Is the critcal factor the use of distinct? or multiple windows? or something else?
View 2 Replies
View Related
Feb 22, 2012
i have created a stored procedure with a cursor in order to perform a function where the annual_sal from the employee_annual_sal table is refered and checked. The empno for all the records which satisfies the condition mentioned inside the loop should be displayed in an variable. My code is below
create or replace PROCEDURE sp_test_cursor(out_empno OUT number)
IS
v_get_data number;
v_get_empno number;
cursor c1 is
select annual_salary
from employee_annual_sal;
[Code]...
What should i do to return mulitple values in a single variable??
View 4 Replies
View Related
Apr 14, 2012
I have a requirement where the user input values will be passed as comma separated string to backend, which is queried against the table using instr. But the index present on the table is not using the index , due to the instr function.How can I create a index in such a way that The instr function uses it.
CREATE TABLE TEST_IDX
(
CCN VARCHAR2(10 CHAR),
SKU_NUM VARCHAR2(10 CHAR),
MOD VARCHAR2(10 CHAR),
SKU_STATUS VARCHAR2(2 CHAR),
RPT_FLAG VARCHAR2(2 CHAR),
CYCLE_AS_OF_DATE DATE,
SMP_IDENTIFIER NUMBER,
MEASURE_NAME VARCHAR2(100 CHAR)
);
CREATE INDEX TEST_IDX1 ON TEST_IDX
(CCN, SMP_IDENTIFIER, MOD, CYCLE_AS_OF_DATE, RPT_FLAG,
MEASURE_NAME);
The below query is going for full table scan due to this.
select * from test_idx where (INSTR (','||'E10000'||',', ',' || ccn || ',') <> 0 OR 'E10000' = 'DEFAULT')
and mod='90396' and rpt_flag='O' and smp_identifier=2
how to recreate the above index so that these queries uses this index.
View 16 Replies
View Related
Mar 18, 2013
I have a table with data in below format:
Table A
Id Value
1 R
2 S1
2 S2
3 T
the o/p in below format:
Col
R|S1|T
R|S2|T
The general is that multiple value for any given id must be aggregated separately.
View 1 Replies
View Related
Oct 12, 2012
I have a question about the CDC publish/subscribe usage with ETL on Oracle 10g. Is the ETL logic code part of the subscriber part? In other words, when the publisher is notified of a record change and sends the record to the staging area then calls the subscriber. Is the subscriber that runs the ETL logic that processes the record before it is sent to the DW/Dim tables? Something like this flow:
Source Database ---> Change table/Publish ---> Subscribe view/ETL ---> Target Database
View 3 Replies
View Related
Aug 6, 2010
what is a global procedure in oracle and its usage
View 5 Replies
View Related
Nov 5, 2012
We are running Oracle Apps 11i on Windows 2003 R2 Enterprise SP2 (32 bit). After enabling /3GB switch parameter in Boot.ini file, the OS recognizes the 8GB RAM and consumes it upto 5.6GB during non-peak hours but as workload increases in evening when end users generate auto receipts the same memory usage ratio reaches upto 6GB resulting the OS gets hang and don't respond at all. Inspite of having 2GB free memory it doesn't consume it. We have to shut down it by pressing the power off button and restart it
The system configuration is as under:
HP ProLiant DL380 G6
Intel Xeon 2.4 GHz
8GB RAM (4 x 2 GB)
View 8 Replies
View Related
Jan 10, 2012
After the reboots each system was using around 30 GB of memory... now that it's been up for a week memory is up to 98GB used on each system. None of the systems are swapping.
Mem: 98999084k total, 97937116k used, 1061968k free, 774900k buffers
Swap: 16779884k total, 0k used, 16779884k free, 89510312k cached
View 2 Replies
View Related
Jul 19, 2010
My stats jobs failed last night with "ORA-01652 :Unable to extend TEMP" error.
Is there any way to check this history data, what other session was using TEMP tablespace extensively ?
View 6 Replies
View Related
Feb 17, 2010
Am trying to use the SYS_CONTEXT variables inside a trigger. After reading the documentation I found that the below query when used inside the trigger code will return the trigger owner.
Select Username from User_Users
Will any of the below variables return the trigger owner when used inside the trigger code
sys_context('USERENV','CURRENT_USER')
or
sys_context('USER','CURRENT_SCHEMA')
View 13 Replies
View Related
Mar 16, 2013
I have made a package and define a variable in it as:
CREATE OR REPLACE package Scott.My_Pac as
CNT_UPD_USR Varchar2(20)
END My_Pac;
When I used the following in Toad it works fine but when I use the same in 10g form it gives the error "Cannot directly access remote package variable or cursor".
Begin
Scott.My_Pac.CNT_UPD_USR := 'Ali';
DBMS_OUTPUT.PUT_LINE(Scott.My_Pac.CNT_UPD_USR);
End;
View 6 Replies
View Related
Jan 13, 2009
Is there any way to tune the following query using lot of CPU:-select description,time_stamp,user_id from bhi_tracking where description like 'Multilateral:%'The explain plan for this is query is:-
---------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
----------------------------------------------------------------
| 0 | SELECT STATEMENT | | 178K| 6609K| 129K|
| 1 | TABLE ACCESS FULL| BHI_TRACKING | 178K| 6609K| 129K|
----------------------------------------------------------------
Bhi_tracking is used for reporting purpose and contain millions of records.Generally we keep one year data in this table and delete the remaining.Can I drop the table after taking export and then import it back or can i truncatethe table and then insert the rows into it to enhancethe performance.
View 14 Replies
View Related