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

Exadata :: Dynamic Views For Flash Cache Usage?

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

SQL Query To Find CPU Usage And Memory Usage

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

SQL & PL/SQL :: Fix Execution Plan Using Hints

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

Ordered Hints In Oracle

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

Server Administration :: Graphical Analysis Of Dynamic Performance Views (V$ Views)?

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

RAC & Failsafe :: Query Doesn't Have Any Parallel Hints

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

Server Administration :: Oracle Hints In SQL Statement

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

Refresh Materialized Views Based On Remote Views?

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

PL/SQL :: Get List Of Materialized Views / Views Using Column Of Table

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

Convert Some Existing Materialized Views (fast Refresh) To Partition Materialized Views

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

SQL & PL/SQL :: Regarding NVL Function Usage

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

SQL & PL/SQL :: NULL Usage?

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

PL/SQL :: How To Check CPU Usage

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

SQL & PL/SQL :: Function BFILENAME() Usage?

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

SQL & PL/SQL :: Conditional Sequence Usage?

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

Created Partition - Usage?

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

How To Find CPU Usage By Each User Session

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

Analytic Functions / TEMP Usage?

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

SQL & PL/SQL :: Usage Of Cursors In Stored Procedure

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

SQL & PL/SQL :: Instr Function And Index Usage

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

Listing Usage To Get O/p In A Specific Form?

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

CDC Publish / Subscribe Usage With ETL On Oracle 10g

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

SQL & PL/SQL :: What Is A Global Procedure In Oracle And Its Usage

Aug 6, 2010

what is a global procedure in oracle and its usage

View 5 Replies View Related

Oracle Apps 11i - Limited Memory Usage?

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

RAC & Failsafe :: Memory Usage Increasing On Oracle

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

Performance Tuning :: TEMP Usage History

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

SQL & PL/SQL :: Usage Of Context Variables Inside The Trigger?

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

Client Tools :: Usage Of Package In 10g Form

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

Performance Tuning :: Query With High Cpu Usage?

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







Copyrights 2005-15 www.BigResource.com, All rights reserved