ALL_OBJECTS vs ALL_SEQUENCES [message #429387] |
Tue, 03 November 2009 08:14  |
gokul_ifs
Messages: 41 Registered: March 2006 Location: india
|
Member |
|
|
Hi,
As my requirement demands, I am extensively querying data dictionary in my PLSQL code.
I am trying to find out querying which view is faster. i.e. ALL_OBJECTS or ALL_SEQUENCES.
For my testing, I just created a plsql block, it runs a loop 1..20000 and select the same sequence name from ALL_OBJECTS and ALL_SEQUENCES.
The loop on ALL_OBJECTS ends in just 10 seconds but the other loop which selecting from ALL_SEQUENCES runs for 100000 seconds.
I tried in a couple of other instances and experienced the same performance.
P.S: Don't care much about the logic. My intentions is to know why querying on ALL_SEQUENCES is taking long time.
|
|
|
|
|
Re: ALL_OBJECTS vs ALL_SEQUENCES [message #429401 is a reply to message #429393] |
Tue, 03 November 2009 09:11   |
gokul_ifs
Messages: 41 Registered: March 2006 Location: india
|
Member |
|
|
-- USING USER_SEQUENCES
CONNECT CISET/CISET
CREATE OR REPLACE FUNCTION getnextseq_userobj
RETURN NUMBER
AUTHID DEFINER
IS
i number:=dbms_utility.get_time;
j number;
v_obj_name varchar2(35);
v_query varchar2(1000);
v_seq NUMBER;
v_ecode NUMBER(38);
v_emesg VARCHAR2(250);
begin
for j in 1..20000
loop
SELECT sequence_name into v_obj_name
FROM user_sequences
WHERE sequence_name = UPPER('COMPANYKEY_SEQ_324');
end loop;
v_query := 'SELECT CISETAVFRD105.'
|| v_obj_name
|| '.nextval FROM DUAL';
EXECUTE IMMEDIATE v_query INTO v_seq;
RETURN v_seq;
EXCEPTION
WHEN NO_DATA_FOUND THEN
raise_application_error (-20033, '99992, SEQUENCE NOT FOUND');
WHEN OTHERS THEN
v_ecode := SQLCODE;
v_emesg := SQLERRM;
dbms_output.put_line(v_emesg);
end;
/
declare
a varchar2(100);
v_seqval number(10);
i NUMBER:=dbms_utility.get_time;
begin
v_seqval := getnextseq_userobj();
i:=round((dbms_utility.get_time-i)/100,2);
dbms_output.put_line(' ');
dbms_output.put_line(' USER_SEQUENCES queried in Seconds: ' || i);
dbms_output.put_line(v_seqval || ' Seq value');
end;
/
This example is to select from USER_SEQUENCES. Similarly I am selecting from ALL_OBJECTS and ALL_SEQUENCES.
Quote:Time taken to execute:
-------------------------------
ALL_OBJECTS queried in Seconds: 1.61
128 Seq value
USER_SEQUENCES queried in Seconds: 0.72
129 Seq value
ALL_SEQUENCES queried in Seconds: 100.78
130 Seq value
[Updated on: Tue, 03 November 2009 09:13] Report message to a moderator
|
|
|
Re: ALL_OBJECTS vs ALL_SEQUENCES [message #429405 is a reply to message #429401] |
Tue, 03 November 2009 09:45   |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
Quote:I am selecting from ALL_OBJECTS and ALL_SEQUENCES.
Yes but I need the EXACT queries.
In addition, why do you include in your times the time for much things that have nothing with the loop? Remove all that is not the loop, remove the call and put the loop in the main program... Simplify, simplify...
And as I said, call once the query without measure and then start the loop.
Regards
Michel
[Updated on: Tue, 03 November 2009 09:48] Report message to a moderator
|
|
|
Re: ALL_OBJECTS vs ALL_SEQUENCES [message #429489 is a reply to message #429405] |
Wed, 04 November 2009 03:27  |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Running a simple timing harness test, just fetching a row from ALL_SEQUENCES a lot of times, and fetching a row from ALL_OBJECTS a lot of times, I find that selects from ALL_SEQUENCES are about 5x faster than ones from ALL_OBJECTS
|
|
|