1) Why we need to do Compute Statistic on index.
2) Is it only for optimizer to make a better plan?
3) If yes, which means, optimizer will not able to collect statistic by itself?
4) if I'm not collect statistic, then optimizer will do it or skip.
I have to create some indexes in a production database. Do I need to Compute Statistics after creating indexes? Or when I create they automatically are computed?
The version I'm using is:
Oracle Database 10g Release 10.2.0.5.0 - 64bit Production
PL/SQL Release 10.2.0.5.0 - Production
CORE 10.2.0.5.0 Production
TNS for 64-bit Windows: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production
I have created a table: INDEX_SIZE_TRACKING with the following attributes
Index Name: name of the index. Type: VARCHAR(255). This is the primary key of the table.
Allocated Space: the memory space (in bytes) allocated to the index. Type: NUMBER
Used Space: the memory space used by the index. Type: NUMBER
Last Update: the time when index details are updated to this table. Type: VARCHAR(255)
I want to write a PL/SQL script to query index statistics data and update tracking entries in the INDEX_SIZE_TRACKING table. If there is no existing entry for the index, create a new one; otherwise, update the existing one. have a PL/SQL statement that can do this in oracle XE?
I'm a student researching SQL, Oracle queries. I'm currently writing a oracle:JDBC to query by id, then receive all tuples that belong to id and compute GPA. I convert letter-grade to # in query how to count rows pertaining to id, then average GPA of all tuples. I understand Count(*) where sid=?, but i need to count the rows for id, then convert letter to number, finally compute gpa for all pertaining tuples belonging to id.
Everything works find but the last query for my JDBC prog.Here is my query as of now...
"SELECT grade, CASE WHEN grade = 'F' THEN 0 WHEN grade = 'D' THEN 1 WHEN grade = 'C' THEN 2 WHEN grade = 'B' THEN 3 WHEN grade= 'A' THEN 4 END AS letter_grade FROM Grades"
How do I count all tuples then compute the gpa pertaining to id.For all IDs?
how can i compute row wise multiplication for each unique value of id. I am trying to get the output as showing id and their respective results after multiplying the corresponding values.
CREATE TABLE mult_tbl(
id NUMBER,
val number
);
insert all
into mult_tbl values (1,2)
into mult_tbl values (1,3)
[code]....
My issue is with a calculation using columns as variables. below is the code to create and attached is the code (INSERT_data.txt) to fill-in the required tables....
what's required here?
I need to update (through update or simply create new table statement)
BPF(i), BPC(i), BPY(i) of table root_tbl (A) based on InF_IDX, InC_IDX & InY_IDX and OutF_IDX, OutC_IDX & OutY_IDX using BPF(i), BPC(i), BPY(i) from table pvt_vectors_tbl(B).
Join Clause: A.P_NODE=B..P_NODE and
A.C_NODE=B.C_NODE and
A.P_NODE_Date=B.P_NODE_Date and
A.C_NODE_Date=B.C_NODE_Date
the formula are:
BPF(i):
if A.InF_IDX<=A.OutF_IDX then
FOR i=1 to A.OutF_IDX+1
if i<InF_IDX then A.BPF(i)= A.BPF(i)+ B.BPF(OutF_IDX + i - InF_IDX); else A.BPF(i)=0;
i=i+1;
else
FOR i=1 to A.InF_IDX+1
if i<OutF_IDX then A.BPF(i)= A.BPF(i)+ B.BPF(InF_IDX + i - OutF_IDX); else A.BPF(i)=0;
i=i+1;
idem for BPc and BPY.
in real word:
* root_tbl table has ~2 million records, 50 BPF(i) columns, 50 BPC(i) columns and ~475 BPY(i) columns
* pvt_vectors_tbl has ~50.000 records
/*create and fill-in pvt_vectors_tbl table*/
CREATE TABLE root_tbl
(
P_NODE VARCHAR2(3),
C_NODE VARCHAR2(3),
P_NODE_Date date,
C_NODE_Date date,
InF_IDX number,
InC_IDX number,
InY_IDX number,
[code].........
I am new to PL/SQL and how to create a trigger to compute the population of the school from the groups of students and store back in population. It also needs to check that there is a min of 10 students to a school.
CREATE OR REPLACE TYPE group_type AS OBJECT
(
group_nameVARCHAR2(20),
tutor_idNUMBER(5),
[code]...
i have a Field item on my report which i would like to show "Price INCL of VAT". Any PL/SQL code or trigger to do this? VAT is 16%
I'm a Forms programmer, not so familiar with Reports.I suspect, it should be something like this:
F_1 := F_1 * 1.16
the problem below:
I have a table AlertData below:
DeptIDMONTHCount
192010-041392
192010-051134
192010-061094
192010-071333
292010-042217
[Code]...
Within each DeptID group I need to calculate absolute change of 'Count' column between previous and current months and compare change value with threshold.
If ratio >= threshold N number of times I need to make a note of that event. Threshold = 0.1 N = 2 - alert needs to exceed threshold two consequtive times
Here is data processing algorithm:
1. Calculate change between month 2010-04 and 2010-05: abs((1134/1392 - 1))= 0.18;
2. check change value against threshold: 0.18 > 0.1
3. Threshold was exceeded, set alert_fired_cnt counter to = 1
4. Once alert fired it creates a baseline for comparison - I need to use Count from month 2010-04: We're now in month 2010-06: abs(1094 / 1392 - 1)=0.21
5. check change value against threshold: 0.21 > 0.1
6. Threshold was exceeded, increment alert_fired_cnt counter by 1 = 2
7. At this point alert exceede threshold two times, I need to set a alert_triggered flag = 1 and reset alert_fired_cnt = 0 for further calculations
8. We're in montn 2010-07: abs(1333/1294-1)=0.03
8. check change value against threshold: 0.03 < 0.1
9. Since threshold was not exceeded, keep alert_fired_cnt counter to = 0
Above algorithm needs to be run for all DeptID groups.
I load above data into an associative array and loop through elements. I am having trouble keeping computations within each DeptID group.
I have an interactive report with two columns timestamp type. I want to use the compute action in order to get the difference between those dates. Nevertheless I got values like this: +000000008 00:00:00.000000.
View 1 Replies View RelatedIs that possible to modify the ***** and ----- in the TEXT report?
DEPARTMENT_ID JOB_ID LAST_NAME SALARY
============= ========== ========================= ==========
20 MK_MAN Hartstein 13000
************* ********** ----------
sum 13000
From above lines, you can see ****** and ----- which are above sum&13000.
Is that possible to change them as '='?
DEPARTMENT_ID JOB_ID LAST_NAME SALARY
============= ========== ========================= ==========
20 MK_MAN Hartstein 13000
============= ========== ==========
sum 13000
"set underline =" is only shot the headers.
I tried BRK and COMPUTE commands myself after reading the documentation but its not working...The output of my script is correct but What I want is the information to be displayed on a different way:
Original script for the report:
set serveroutput on size 1000000
set pages 10000
set lines 1000
set arraysize 1
set trimspool on
set heading off
[code]....
The output should be exactly as above as the requirement is this should be in this particular order and how many is the count in each thoroughfare or locality, as shown in the final output, above.
I load a table through sql loader which takes nearly 14 min for 8-9 millions records, once the records complete i run the analyze table compute statics to gather stats and it takes nearly 15 min. is there any ways so that i can reduce the stats timing. the stats collection command runs from other schema not from where the table is residing.
View 1 Replies View RelatedI am on 11.2.0.3 Enterprise Edition. We are using the new feature "Composite Domain Index" for a Domain index on a very large table (>250.000.000 rows). It really works with mixed queries. We added two number columns using FILTER BY.We have lots of DML on this table. Therefore, we are executing synchronize and optimize once the week. The synch behaves pretty normal. But "optimize_index" takes a very very long time to complete. I have switsched on 'logging' for the optimize process. The $I table takes some time but is finished normally. But the optimization of the $S table (that is the table created for the CDI feature) is running over 12 hours now - and far from being finished. From the logfile, I can see that it optimizes 1000 rows every 20 minutes. Here is the output of the logfile:
Oracle Text, 11.2.0.3.0
14:33:05 06/26/12 begin logging
14:33:05 06/26/12 event
14:33:05 06/26/12 process $N for optimize: SEQDEV.GEN_GES_DESCRIPTION_CTX_I
14:33:16 06/26/12
14:33:16 06/26/12
[code]....
I haven't found a recommendation from Oracle not to use "optimize_index" for Domain Indexes with CDI. But in my case, it would be much faster just to drop and recreate the Domain Index in question.
How can we check the I/O statistics on a ASM diskgroup?
Do we have any command which can be run @ sqlplus prompt after logging into the asm instance?
What steps can we take to increase the Read speed and the write speed on the ASM diskgroup.
I have a huge table (about 60 gb) partition over range. The index on this table is global index created on 4 columns together. I have a query which is running very slowly. The explain plan is showing the use of this global index.Explain plan is not showing pstart and pend because the index is global.
View 6 Replies View RelatedI want to take the statistics of my Production database and import to my local database, to calculate the Production statistics.I have used the statistics=compute, to export statistics. In the log file, for some tables, there was a waring like "exp-00091 exporting questionable statistics"
Is this dump will be useful for me to calculate production statistics?What option i have to use while importing statistics=recalculate or statistics=safe?
am having Oracle 9i RAC on IBM AIX .
I have large partitioned tables ( 4 partitions are added every month ). Is is possible to collect Incremental Statistics Gathering on these objects ( 9i ). If I collect stats with Ggranularity => ALL and ESTIMATE_PERCENT =100 the stats are accurate but it takes so much time .
One way may be to collect stats as Ggranularity => PARTITION for each new partition ( this quite fast ). but what about the Global Table Stats?
I have a global index and I want to convert it to local index.Is there a way to recreate local index with out dropping the global index.
I can create a local index first and then drop the global index. But is there a way to create it with out dropping the global index, just convert it.
I am facing the error "ORA-01502: index or partition of such index is in unusable state " while loading the text data using
sql loader with direct path (direct = Y ,rows = 10000) option. Table consists an composite non unique index. If I query the dba indexes for the effected index it shows the index status as VALID. There was no maintaince done on the effected table or index. I have tried loading the same data using conventional path but didn't found any issues for the same.
I have a query which had a join:
a.c1=b.c1 and a.c2=@var
where @var is user supplied input at runtime...We had a index on a.c2 . The CBO would use this index to generate an opitimised query plan.We found some records from table "b" were dropping due to inner join. So we made a change in join. It'd be like
a.c1(+)=b.c1 and nvl(a.c2,@var)=@var
This query is no longer using the index, instead its doing a full table scan causing the query to slowdown.I have tried creating index on nvl(a.c2,'31-dec-9999')
But the CBO won't use it.Anyway to create index on this col so that full table scan can be avoided?
How to force an index if the table not using the index?
View 10 Replies View RelatedWe have occurrences of enq : TX - index contentions in the database. Using the SQL ID, we have identified the INSERT statement and the table which they are trying to insert.
This table has almost 25 different indexes, some of which are unique as well.I am wondering how to identify the actual index causing issue, out of these 25 indexes.
Is there any way to pin point to the name of index which is causing the lock?My plan is, once the index is identified, I would like to check the extents and inittrans and other attributes of this index to fix.
In the article regarding gathering CBO Statistics, it states: QUOTE When an Oracle database is created, a job will be scheduled that will generate the database statistics for you. You will still need to collect system statistics however, as these are not collected by the automatic statistics gathering mechanism.
what is the difference between "database statistics" and "system statistics"? In other words, do I need to run this script for each schema owner in my 10g/11g instance?
variable whoami varchar2(20);
begin
select user into :whoami from dual;
end;
exec dbms_stats.gather_schema_stats( -
ownname => :whoami, -
options => 'GATHER AUTO', -
estimate_percent => 15, -
cascade => true).
I am using Oracle 11g R2 version.I want to import the DB statistics. But i am getting an exception when i execute the command DBMS_STATS.IMPORT_SCHEMA_STATS ('user1','STATS_INFO', '','', TRUE, FALSE).
The error is ORA-20000: no statistics are imported ORA-06512: at "SYS.DBMS_STATS", line 10603 ORA-06512: at line 1.
The privileges 'ANALYZE ANY' and 'ANALYZE ANY DICTIONARY' is already given to the user.Also i executed this command as sys. But still error occurs.
Same command is successfully executed in Oracle 10g. Is there any difference in importing the statistics in Oracle 10g and 11g ?
how many rows certain tables have.
updating the statistics for a table (with GATER_TABLE_STATS) and using NUM_ROWS then. This works fine for me as long as I am the owner of the table, but when someone else is, I always get this error: ORA-20000: Table does not exist or insufficient privileges.what privileges do I need to use GATHER_ TABLE_ STATS on all Tables, which were created by Users?
when I tried to use ANALYZE TABLE TEST_TABLE COMPUTE STATISTICS on a certain table I got the following error: a view is not appropriate here. The strange thing is, TEST_TABLE is not a view (at least it is not listed in ALL_VIEWS and is listed in ALL_TABLES, so it cant be a view right?).
Besides, is there another way to gather Table Statistics (not using Analyze Table or Gather_Table_Stats)?
I need to do a cmd script for launching oracle statistics every week and scheduling it from task scheduler of my server with win 2008 R2 O.S.
the script is:
"statistiche-oracle.bat"
set ORACLE_SID=GW1
sqlplus "/ as sysdba" @statistiche.sql
and "statistiche.sql" is:
exec dbms_stats.gather_schema_stats(ownname => 'PDMUSER', cascade=>true)
exit
When I launch it from command line it stops with error message: "insufficient privileges" and ask me the user, so I put 'PDMUSER' that is my user, it asks also pwd, I put it and the works.
I have a table which have 300+ columns and have 13 million rows. It is on a 32 kb block size. This is a table in data ware house environment. There no# of rows in the table haven't changed much but I see that the time taken to collect statistics have increased significantly.Initially it took only 15 minutes (with the same 13M rows) now it runs for 4+ hours. The max parallel servers is 4 (which is unchanged). The table is not partitioned.
OS: HP UX Itanium
Database: Oracle 11g (11.2.0.2)
Command is:
exec dbms_stats.gather_table_stats(ownname=>'ABC',tabname=>'ABC_LOAD',estimate_percent=>dbms_stats.auto_sample_size,cascade=>TRUE,DEGREE=>dbms_stats.auto_degree);
I would like to understand:
1) What could have been the causes of this change in time. 15 minutes to 4+hours ?
2) How can we gather statistics of huge table at a faster rate?
I have created materialized view which hold few million records.Should i have to analyse the view and compute the statistics after i create the materialized view?
Also,just in case i need further indexing,should i have to take the statistics for the table again??
Is it possible to gather stats for a schema which its in use. When i try to analyze the tables of a schema it shows that the statistics for that table is locked. So is it possible that instead of analyzing a table one by one , can i go for gathering the Schema stats while the objects of that Schema is still in use ( like DML or select statements being issued on those schema objects) .
DB version : 10.2.0.4
OS version : RHEL 5.8
DB type : RAC